Hive 基础教程

hive
hive
发布日期:   2017-02-03

1. 什么是 HIVE

  • Hive 是建立在Hadoop 上的数据参考基础架构，它提供了一系列的工具，可以来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。

  • Hive定义了简单的类SQL查询语言，成为QL，它允许SQL的用户查询数据。同时，这个语言也允许熟悉MapReduce开发者的开发自定义的mapper和Reduce来处理内奸的mapper和reduce日无法完成的复杂分析工作。

  • Hive是sql解析引擎，它将SQL语句转译成MapReduce job然后在Hadoop执行

  • hive的表其实就是HDFS的文件夹，表里面的数据对应的是文件夹里面的文件，按表名把文件夹分开，如果是分区表，则分区值是子文件夹，可以直接在MapReduce job里使用这些数据

2. Hive的系统架构

  • 用户接口主要有三个，包括CLI，JDBC/ODBC，WebUI

  1. CLI： 即shell命令行

  2. JDBC/ODBC 是hive的Java，与使用传统数据库JDBC的方式类似

  3. WebUI 是通过浏览器访问hive

  • hive将元数据存储在数据库中（metastore），目前只支持mysql、derby。

hive中的元数据包括表的名字，表的列和分区机器属性，表的属性（是否为外部表等），表的数据所在目录等。

  • 解释器、编译器、优化器完成HQL查询语句从词法分析，语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行

  • hive的数据存储在HDFS中，大部分的查询由MapReduce完成（包含*的查询，比如select * from table 不会生成MapReduce任务）

  • Hadoop：利用HDFS进行存储利用MapReduce进行计算

3. 安装hive

  • Hive只在一个节点上安装即可

  1. 上传tar包

  2. 解压

1
tar -zxvf hive-0.9.0.tar.gz -C /cloud/
  1. 配置mysql metastore
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 配置HIVE_HOME环境变量

rpm -qa | grep mysql

mysql-libs-5.1.73-7.el6.x86_64

rpm -e mysql-libs-5.1.66-2.el6_3.i686 --nodeps

rpm -ivh MySQL-server-5.1.73-1.glibc23.i386.rpm 

rpm -ivh MySQL-client-5.1.73-1.glibc23.i386.rpm 

# 修改mysql的密码

/usr/bin/mysql_secure_installation

# （注意：删除匿名用户，允许用户远程连接）

# 登陆mysql

mysql -u root -p
  1. 配置hive
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
cp hive-default.xml.template hive-site.xml 

vim hive-site.xml
# 删除所有内容，只留一个<property></property>

# 添加如下内容：

<property>

 <name>javax.jdo.option.ConnectionURL</name>

 <value>jdbc:mysql://hadoop00:3306/hive?createDatabaseIfNotExist=true</value>

 <description>JDBC connect string for a JDBC metastore</description>

</property>

 

<property>

 <name>javax.jdo.option.ConnectionDriverName</name>

 <value>com.mysql.jdbc.Driver</value>

 <description>Driver class name for a JDBC metastore</description>

</property>

 

<property>

 <name>javax.jdo.option.ConnectionUserName</name>

 <value>root</value>

 <description>username to use against metastore database</description>

</property>

 

<property>

 <name>javax.jdo.option.ConnectionPassword</name>

 <value>123</value>

 <description>password to use against metastore database</description>

</property>

  1. 安装hive和mysq完成后，将mysql的连接jar包拷贝到$HIVE_HOME/lib目录下

如果出现没有权限的问题，在mysql授权(在安装mysql的机器上执行)

1
2
3
4
5
6
7
mysql -uroot -p

# \#(执行下面的语句 *.*:所有库下的所有表  %：任何IP地址或主机都可以连接)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;

FLUSH PRIVILEGES;//刷新权限表

遇到的异常详情如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Exception in thread "main"java.lang.RuntimeException: java.lang.IllegalArgumentException:java.net.URISyntaxException: Relative path in absolute URI:${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
     atorg.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:444)
     atorg.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:672)
     atorg.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:616)
     atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
     atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     atjava.lang.reflect.Method.invoke(Method.java:606)
     atorg.apache.hadoop.util.RunJar.main(RunJar.java:160)
Caused by: java.lang.IllegalArgumentException:java.net.URISyntaxException: Relative path in absolute URI:${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
     atorg.apache.hadoop.fs.Path.initialize(Path.java:148)
     atorg.apache.hadoop.fs.Path.<init>(Path.java:126)
     atorg.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:487)
     atorg.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:430)
     ... 7more
Caused by: java.net.URISyntaxException:Relative path in absolute URI:${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
     atjava.net.URI.checkPath(URI.java:1804)
     atjava.net.URI.<init>(URI.java:752)
     atorg.apache.hadoop.fs.Path.initialize(Path.java:145)
     ... 10more
  • 解决方案如下：

1.查看hive-site.xml配置，会看到配置值含有”system:java.io.tmpdir”的配置项

2.新建文件夹/opt/hive-3.1.1/tmp

3.将含有”system:java.io.tmpdir”的配置项的值修改为如上地址

启动hive，成功！

4. metaStore 初始化

1
./schematool -initSchema -dbType mysql

5. msyql对应meta元数据

TBLS 对应表
Columns_v2 对应列
Sds 对应目录

6. 数据类型

6.1 基本数据类型

整数类型 tinyint、smallint、int、bigint
浮点类型 float、 double
布尔类型 boolean
字符串类型 string
字符串 varchar 从0.12.0开始支持
字符 char 从0.13.0开始支持 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 建表
hive> create table person (pid int,pname string,married boolean,salary double);
-- 查看表结构
hive> desc person;
OK
pid                     int 
pname                   string 
married                 boolean 
salary                  double 

-- 建表
hive> create table person2 (pname varchar(20),vname char(20));
OK
Time taken: 0.06 seconds
-- 查看表结构
hive> desc person2;
OK
pname                   varchar(20) /*表示最大长度为20 有可能小于20*/                                
vname                   char(20)   /*固定长度是20不满20 也占20个字符的空间*/


6.2 时间类型

数据类型 说明
Timestamp 存储系统时间戳，秒数 从hive0.8.0开始支持
Date 描述特定日期 yyyy-mm-dd 只包含日期 没有时间 从hive0.12.0开始支持

6.3 复杂数据类型

  • MAP KEYS TERMINATED BY ：key value分隔符

  • FIELDS TERMINATED BY：字段与字段之间的分隔符

  • COLLECTION ITEMS TERMINATED BY：一个字段各个item的分隔符

复杂数据类型 说明
Array 数组类型，由一系列相同的数据类型的元素构成
Map 集合类型，包含key->value 键值对，可以通过kay来访问元素
Struct 结构类型，可以包含不同数据类型的元素，这些元素可以通过“点语法”的方式得到所需要的元素
Union Union类型，可以在同一时间点，保持恰好有一个指定的数据类型。
您可以使用create_union的UDF创建一个实例的类型，从0.7.0开始提供
  • Array
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 指定array中元素之间的分隔符 COLLECTION ITEMS TERMINATED BY ','

-- 建表
hive> create table stu (sid int,sname string,grade array<string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',';
OK
Time taken: 2.25 seconds

-- 查看表结构
hive> desc stu; 
OK
sid     int      
sname     string      
grade     array<string>      
Time taken: 0.166 seconds, Fetched: 3 row(s)

-- 导入数据
1    tom    tom,tim
2    tom    tom,tim
3    tom    tom,tim
4    tom    tom,tim
5    tom    tom,tim

-- 查询数据
select * from lxw_test2 where t[0]='tom';


  • Map
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 指定map中元素之间的分隔符，map与key之间为: 元素之间为,
-- COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';

-- 建表
hive> create table stu1 (sid int,sname string,grade map<string,string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';
OK
Time taken: 0.221 seconds

-- 查看表结构
hive> desc stu1;
OK
sid     int      
sname     string      
grade     map<string,string>      
Time taken: 0.28 seconds, Fetched: 3 row(s)
  
-- 导入数据
1    tom1    tom1:tim1,green1:t1
2    tom2    tom2:tim2,green2:t2
3    tom3    tom3:tim3,green3:t3
4    tom4    tom4:tim4,green4:t4
5    tom5    tom5:tim5,green5:t5

-- 查询数据
select * from stu1 where grade['tom1']='tim1';

--
create table stu2 (sid int,sname string,grades array< map<string,float>>);
  • Struct
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 指定struct之间的分隔符：COLLECTION ITEMS TERMINATED BY ':'

hive> create table stu2(id INT, info struct<name:STRING, age:string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ':'; 
OK
Time taken: 0.192 seconds

-- 查看表结构
hive> desc stu2;
OK
id     int      
info     struct<name:string,age:string>     
Time taken: 0.261 seconds, Fetched: 2 row(s)

-- 导入数据
1    tom1:tim1
2    tom2:tim2
3    tom3:tim3
4    tom4:tim4
5    tom5:tim5

-- 查询数据
select info.name from stu1;

select * from stu2 where info.name='tom1';
  • 嵌套复杂结构类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- 创建表
CREATE TABLE login_game_complex3 (
    ip STRING,
    uid STRING,
    gameinfo map<bigint,struct<name:string,score:bigint,level:string>> ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
  COLLECTION ITEMS TERMINATED BY '|' MAP KEYS TERMINATED BY ':' ;

hive> desc login_game_complex;
OK
ip     string      
uid     string      
gameinfo     map<bigint,struct<name:string,score:bigint,level:string>>     
dt     string      

-- 创建一张简单表
CREATE TABLE login_game_simple (
    ip STRING,
    uid STRING,
    gameid bigint,
    gamename string,
    gamescore bigint,
    gamelevel string,
    dt string
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

hive> desc login_game_simple;
OK
ip                string      
uid              string      
gameid       bigint      
gamename    string      
gamescore   bigint      
gamelevel   string      
dt                string      

-- 导入数据

192.168.1.0,3105007010,1,wow,100,v1
192.168.1.0,3105007010,2,cf,100,v2
192.168.1.0,3105007010,3,qqgame,100,v3
192.168.1.2,3105007011,1,wow,101,v1
192.168.1.2,3105007011,3,qqgame,101,v3
192.168.1.2,3105007012,1,wow,102,v1
192.168.1.2,3105007012,2,cf,102,v2
192.168.1.2,3105007012,3,qqgame,102,v3


-- 插入表数据
INSERT OVERWRITE TABLE login_game_complex 
select ip,uid,map(gameid, named_struct('name',gamename,'score',gamescore,'level',gamelevel) ) FROM login_game_simple;

-- 查询表数据
select ip,uid,gameinfo from login_game_complex;

192.168.1.0,3105007010,1:wow100v1|2:wow100v1
192.168.1.0,3105007010,2:cf100v2|3:cf100v2
192.168.1.0,3105007010,3:qqgame100v3|4:qqgame100v3
192.168.1.2,3105007011,1:wow101v1|5:wow101v1
192.168.1.2,3105007011,3:qqgame101v3|6:qqgame101v3
192.168.1.2,3105007012,1:wow102v1|7:wow102v1
192.168.1.2,3105007012,2:cf102v2|8:cf102v2
192.168.1.2,3105007012,3:qqgame102v3|9:qqgame102v3

7. Hive数据存储

  • 基于HDFS
  • 没有专门的数据存储格式
  • 存储结构主要包括：数据库、文件、表、视图
  • 可以直接接在文本文件（.txt文件等）
  • 创建表时，指定hive数据的列分隔符与行分隔符

7.1 内部表

  1. 与数据库中的Table 在概念上是类似的
  2. 每个table在hive中都有一个相应的目录存储数据
  3. 所有的table数据（不包括external table）都保存在这个目录中
  4. 删除表的时候，元数据与数据都会被删除

7.1.1 创建表

  • 创建一张内部表（默认创建在user/hive/warehouse文件夹下）
1
2
3
4
5
6
7
8
9
10


hive> Create table t1(tid int,tname string,age int);

hive> desc t1;

tid   int   
tname  string   
age   int

  • 创建表到指定路径
1
2
3
4
5
6
7
8
9
10
11
-- 默认列的分隔符是制表符
hive> Create table t2(tid int,tname string,age int) location '/login_log/t2';

Time taken: 0.217 seconds

hive> desc t2;

tid     int   
tname   string    
age     int

  • 创建表并指定分隔符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
hive> Create table t3(tid int,tname string,age int) row format delimited fields terminated by '\t'; 

OK

Time taken: 0.212 seconds

hive> desc t3;

OK

tid     int   

tname   string    

age     int   

Time taken: 0.308 seconds, Fetched: 3 row(s)
  • 通过查询结果创建一张新的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
hive> Create table t4 as select * from t3;

hive> desc t4;

OK

tid     int   

tname   string    

age     int   

Time taken: 0.246 seconds, Fetched: 3 row(s)

hive> select * from t4;

OK

1  a  11

2  b  22

3  c  33

Time taken: 0.19 seconds, Fetched: 3 row(s)
  • 通过查询结果创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
hive> Create table t5 row format delimited fields terminated by '\t' as select * from t3;

hive> desc t5; 

OK

tid    int   

tname  string    

age    int   

Time taken: 0.258 seconds, Fetched: 3 row(s)

hive> select * from t5;

OK

1  a  11

2  b  22

3  c  33

Time taken: 0.172 seconds, Fetched: 3 row(s)
  • 创建表指定保存文件类型
1
hive> Create table t5 row format delimited fields terminated by '\t' as select * from t3 stored as textfile;
  • 创建完整表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table machine_login_log_bak(

  account_id string,

  login_come string,

  login_time bigint,

  login_ip string,

  type string,

  machine_no string,

  date date

) partitioned by (date string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 

COLLECTION ITEMS TERMINATED BY ',' 

MAP KEYS TERMINATED BY ':'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE;

7.1.2 更新表结构

7.1.2.1 增加列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hive> Alter table t1 add columns (english int);

OK

Time taken: 0.367 seconds

hive> desc t1;

OK

tid     int   

tname    string    

age     int   

english  int    

Time taken: 0.235 seconds, Fetched: 4 row(s)
7.1.2.2 增加列并增加注释
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
hive> ALTER TABLE t1 ADD COLUMNS (history INT COMMENT 'a comment');

OK

Time taken: 0.318 seconds

hive> desc t1;

OK

tid     int   

tname   string    

age     int   

english  int    

history  int   a comment 

Time taken: 0.244 seconds, Fetched: 5 row(s)

 

hive> Alter table t1 add columns (computer string comment 'computer');

OK

Time taken: 0.325 seconds

hive> desc t1;

OK

tid       int   

tname     string    

age       int    

english   int   

history   int       a comment 

computer  string   computer 

Time taken: 0.244 seconds, Fetched: 6 row(s)
7.1.2.3 更改表名
1
2
3
4
5
6
7
8
9
10
11
12
13
hive> ALTER TABLE t1 RENAME TO t1_bak;

OK

Time taken: 0.338 seconds

hive> show tables;

OK

t1_bak

Time taken: 0.046 seconds, Fetched: 15 row(s)
7.1.2.4在表的最后增加列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--在表的最后增加一列字段

hive> Alter table t1 add columns (pen string comment 'pen');

OK

Time taken: 0.33 seconds

hive> desc t1;

OK

pen   string   pen 

Time taken: 0.272 seconds, Fetched: 1 row(s)

 

ADD:是代表新增一字段，字段位置在所有列后面(partition列前)
7.1.2.5用新列替换原表中的所有列

REPLACE:则是表示替换表中所有字段。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--替换原表中的所有字段

hive> Alter table t1 replace columns (pen string comment 'pen');

OK

Time taken: 0.33 seconds

hive> desc t1;

OK

pen   string   pen 

Time taken: 0.272 seconds, Fetched: 1 row(s)
7.1.2.6增加表的元数据信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
用户可以用这个命令向表中增加metadata

hive> alter table t1 set tblproperties('pen'='int'); 

OK

Time taken: 0.292 seconds

hive> desc formatted t1;

OK

\# col_name   data_type   comment 



pen   string   pen 


\# Detailed Table Information     

Table Parameters:     

  COLUMN_STATS_ACCURATE  false 

  last_modified_by        hadoop 

  last_modified_time     1459415725 

  numFiles   0 

  numRows   -1 

  pen         int 

  rawDataSize -1 

  totalSize   0 

  transient_lastDdlTime  1459415725
7.1.2.6 改变表文件格式与组织
1
2
3
4
5
-- 这个操作修改了表的物理存储属性
ALTER TABLE table_name SET FILEFORMAT file_format

ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS

7.1.2.7 修改列的名字、类型、位置、注释
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
hive> ALTER TABLE t1_bak CHANGE COLUMN pen clour int COMMENT 'change comment';

OK

Time taken: 0.357 seconds

hive> desc t1_bak;

OK

clour   int   change comment 

Time taken: 0.227 seconds, Fetched: 1 row(s)

 

hive> ALTER TABLE t1_bak CHANGE COLUMN clour pen string COMMENT 'change comment' after history;

OK

Time taken: 0.423 seconds

hive> desc t1_bak;

OK

english   int   

history   int   

pen       string   change comment 

Time taken: 0.204 seconds, Fetched: 3 row(s)

 

hive> ALTER TABLE t1_bak CHANGE COLUMN pen coloe int COMMENT 'change comment' FIRST;

OK

Time taken: 0.267 seconds

hive> desc t1_bak;

OK

coloe     int   change comment 

english   int   

history   int   

Time taken: 0.12 seconds, Fetched: 3 row(s)

 

hive> ALTER TABLE machine_log CHANGE COLUMN use_time use_time bigint ;

OK

 

Time taken: 0.543 seconds

7.1.3 删除表

1
hive> Drop table t1_bak;

7.1.4 清空表

1
insert overwrite table t_table1 select * from t_table1 where 1=0;

7.2 分区表 (Partition )

  • Partition 对应数据库的partition列的密集索引

  • 在hive中，表的一个partition对应与表下的一个目录，所有的partition的数据都存储在对应的目录中

  • 示例数据

1 iverson M 60 80 96

2 KOBE M 67 80 95

3 T-MAC F 59 67 86

4 DALA M 58 68 95

5 PARK M 75 62 58

6 curry F 58 68 69

7 wade M 85 69 85

8 yaom F 75 85 65

9 bosh F 58 94 26

10 GREEN M 24 98 78

分区表图示

7.2.1 创建分区表

1
2
3

Create table partition_table (sid int,sname string) partitioned by (gender string) row format delimited fields terminated by '\t';

7.2.2 查询数据插入分区表的指定分区

1
2
3
insert into table partition_table partition (gender=’M’) select sid,sname from samp_data where gender=’M’;

insert into table partition_table partition (gender=’F’) select sid,sname from samp_data where gender=’F’;

7.2.3 查看分区

1
show partitions partition_table

7.2.4 添加分区

1
2
3
-- 添加分区并创建二级分区

Alter table partition_table add if not exists partition(gender='X',type='text');

7.2.5 删除分区

1
Alter table partition_table drop if exists partition (gender='X',type='text');

7.2.6 hive动态分区

7.2.6.1动态分区的几个参数
1
2
3
4
5
6
7
8
9
10
11
12
-- 使用动态分区
set hive.exec.dynamic.partition = true;
-- 无限制模式,如果模式是strict,则必须有一个静态分区,且放在最前面
set hive.exec.dynamic.partition.mode = nonstrick;

set hive.exec.max.dynamic.partitions.pernode=10000;//每个节点生成动态分区的最大个数

set hive.exec.max.dynamic.partitions=10000;//生成动态分区的最大个数

set hive.exec.max.created.files=150000;//一个任务最多可以创建的文件数目

set dfs.datanode.max.xcievers=8192;//限定一次最多打开的文件个数
2.2.6.2动态分区表插入数据
  • 分区字段需要在查询的末尾，如果多等分区 分区字段的顺序要跟分区一致
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 1.创建分区表

Create table machine_login_log_bak(
  account_id string,
  login_come string,
  login_time bigint,
  login_ip string,
  type string,
  machine_no string
) partitioned by (date date)row format delimited fields terminated by '\t'

-- 2.动态向分区表插入数据

insert into table machine_login_log_bak partition(date)

-- 3. 查询数据
select account_id,login_come,login_time,login_ip,type,machine_no,from_unixtime(login_time,'yyyy-MM-dd') as date from machine_login_log;
7.2.6.3 动态生成二级分区向表中插入数据

分区字段需要在查询的末尾，如果多等分区 分区字段的顺序要跟分区一致

1
Insert into table d_part partition(value,sex)Select name,age as value,sex as sex from t1;

7.3.外部表（external table）

  1. 指向已经存在HDFS中存在的数据，可以创建partition

  2. 它和内部表在元数据的组织上是相同的，而实际数据的存在则有较大差异

  3. 外部表只有一个过程，加载数据和创建表同时完成，并不会移动数据到仓库目录中，只是与外部数据建立一个链接，当删除外部表时，仅删除该链接

外部表图示，将三个文件放到hdfs中，建立外部表指向三个文件

7.3.2 创建外部表

1
2
3
create table external_student (sid int,sname string,age int) row format delimited terminated by ‘,’location ‘/input’;

--  删除一个文件,再次查询表中的数据,刚刚删除的文件中的数据就从表中删除了

7.3.3 内部表转外部表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
hive> alter table t1 set TBLPROPERTIES ('EXTERNAL' = 'TRUE');

OK

Time taken: 0.264 seconds

hive> desc formatted t1;

OK

\# col_name   data_type   comment 


pen   string   pen 


\# Detailed Table Information     

Database:   default   

Owner:   hadoop    

CreateTime:   Thu Mar 31 16:37:08 CST 2016   

LastAccessTime:   UNKNOWN   

Protect Mode:   None   

Retention:   0    

Location:   hdfs://hdfs-nn/hive/warehouse/t1  

Table Type:   EXTERNAL_TABLE

7.3.4 外部表转内部表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
hive> alter table t2 set TBLPROPERTIES('EXTERNAL'='FALSE');

OK

Time taken: 0.283 seconds

hive> desc formatted t2; 

OK

\# col_name   data_type   comment 


tid   int   

tname  string   

age   int   


\# Detailed Table Information     

Database:    default    

Owner:        hadoop   

CreateTime:   Thu Mar 31 16:38:48 CST 2016   

LastAccessTime: UNKNOWN   

Protect Mode:  None    

Retention:    0   

Location:     hdfs://hdfs-nn/login_log/t2   

Table Type:   MANAGED_TABLE    

Table Parameters:     

  COLUMN_STATS_ACCURATE  false 

  EXTERNAL   FALSE 

  last_modified_by   hadoop 

  last_modified_time   1459416345 

  numFiles   0 

  numRows   -1 

  rawDataSize   -1 

  totalSize   0 

  transient_lastDdlTime  1459416345

7.4.桶表（bucket table）

  • 对于每一张表(table)或者分区,hive可以进一步组织成同,也就是说同是更为细粒度的数据范围划分

  • hive 是针对某一列进行分桶

  • hive 采用对列值哈西,然后除以同的个数求余的方式决定该条记录存放在哪一个桶中

  • 好处

    • 获得更高的查询效率
    • 是取样(sampling)更高效
桶表示意图

7.4.1 创建桶表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 设置开启分桶功能 
set hive.enforce.bucketing = true;

-- 建表
create table bucket_table (sid int,sname string,age int) clustered by (sname) into 5 buckets;

-- 指定列分隔符以及文件存储格式 
-- 按照id 聚集 按照name排序 默认升序 放进四个桶里
create table bucketed_table(
  id int,
  name string
)
clustered by (id) 
sorted by(name) into 4 buckets
row format delimited terminated by '\t' stored as textfile;

7.5 视图(view)

1.视图是一种虚表，是一个逻辑概念，可以跨越多张表

2.视图建立在已有的表的基础之上，视图依赖以建立的这些表成为基表

3.视图可以简化复杂的查询

使用方法：在mysql控制台里依次执行即可，将生成和oracle里scoot/tigger下完全一样的测试用示例表。方便需要测试数据l的使用mysql的同学

1
2
3
4
5
6
7
8
9
-- 建立视图
--查询员工信息：员工号，姓名，月薪，年薪，部门名称
create view empinfo as 
select e.empno,emename,e.sal,e.sal*12 annlsal,d.dname from emp e,dept d where e.deptno = d.deptno;

-- 查看视图结构
desc empinfo；

-- Hive 不支持物化视图

8. 数据导入导出

8.1 数据导入

  • 使用load语句加载数据
1
2
3
Load data [local] inpath 'filepath' [override] 

Into table tablename [partition(partcol1 = val1,partcol2 = val2…)]
命令 含义
Load data 导入数据
[local] 导入数据的位置 添加local 说明从linux本地文件导入， 不添加从hdfs中导入
inpath ‘filepath’ 文件地址
[override] 是否要覆盖表中原有的数据， 添加override会先删除表中原有的数据，然后添加新的数据 不添加override则在表中新增数据
tablename 要导入数据的表名
[partition (partcol1 = val1,partcol2 = val2…)] 将数据导入到哪个分区当中，后面是分区条件 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 在原来的数据基础上新增

-- 导入hdfs上面的数据
load data inpath '/login_log/tab_uc_loginlog' into table login_log;

-- 导入linux上面的数据
load data local inpath '/login_log/tab_uc_loginlog' into table login_log;

-- 导入linux上面的数据，并且覆盖原来的数据
load data local inpath '/login_log/tab_uc_loginlog' overwrite into table login_log;


-- 将数据导入分区表
insert into table t1 partition(gender='M') select * from t2;
insert overwrite table t1 partition(gender='M') select * from t2;

load data local inputh '/root/data/data1.txt' into table partition_table partition(gender='M');
load data local inputh '/root/data/data2.txt' into table partition_table partition(gender='F');


8.2 导出数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1. 导出到本地文件系统

hive> insert overwrite local directory '/home/hive/'
    > select * from test;

-- 2. 导出到HDFS中
和导入数据到本地文件系统一样的简单，可以用下面的语句实现：

hive> insert overwrite directory '/home/hive/'
    > select * from test;

-- 3. 导出到Hive的另一个表中

这也是Hive的数据导入方式，如下操作：

hive> insert into table test1
    > partition (age='25')
    > select id, name, tel
    > from test;

9. 数据查询

9.1 简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查看表结构
desc emp;

-- 查询所有员工的所有信息
select * from emp;

-- 查询员工号,名字,月薪
select empno,empname,sal from emp;

-- 查询员工号,名字,月薪 ,年薪
select empno,empname,sal,sal*12 from emp;

-- 查询员工号,名字,月薪 ,年薪,奖金,年收入(空值处理)
select empno,empname,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;

-- 查询奖金为null的员工
select * from emp where comm is null;

9.2 使用distinct去重

1
2
3
Select distinct deptno from dept;

Select distinct deptno,job from dept;

9.3 Where 查询

1
2
3
4
5
6
7
8
9
-- 查询10号部门的员工
Select * from emp where deptno=10;

-- 查询名字叫KING的员工 hive查询区分大小写
Select * from emp where ename='KING'
Select * from emp where ename='king'

--查询部门号是10 薪水小于2000的员工
Select * from emp where deptno=10 and sal <2000;

9.4 模糊查询

1
2
3
4
5
6
-- 查询名字以S开头的员工

select empno,ename,sal from emp where ename like 'S%';

-- 查询名字中带有下划线的员工
select empno,ename,sal from emp where ename like '%\\_%';

9.5 排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 查询员工信息:员工号,姓名,月薪按照月薪排序

-- 默认升序
Select empno,ename,sal from emp order by sal;
-- desc 为降序
Select empno,ename,sal from emp order by sal desc;

-- Order by 后面可以接 列, 表达式,别名,序号

-- 后接表达式
Select empno,ename,sal,sal*12 from emp order by sal*12;

-- 后接别名
Select empno,ename,sal,sal*12 annsal from emp order by annsal;

-- 后接序号 需要要先设置
set hvie.groupby.orderby.position.alias=true;//默认是false

-- 序号为查询的第几列,序号始于1
Select empno,ename,sal,sal*12 annsal from emp order by 4;

 

-- 空值排序:查询工信息,按照奖金排序

-- 升序排列null值默认排在前面,如果想升序排列null排在后面需要先把空值置0 然后排序

-- 降序排列null值默认排在后面

Select empno,ename,sal,comm from emp order by comm;

9.6 Hive 的表连接

  • 等值连接
1
2
3
--查询 员工号,姓名,月薪,部门名称

Select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2 where t1.deptno = t2.deptno;
  • 不等值连接
1
2
3
-- 查询员工号,姓名,月薪,工资级别 between and 包含两个值,小值在前 大值在后

Select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
  • 外连接
1
2
3
-- 按部门统计员工人数: 部门号,部门名称,人数

Select t2.deptno,t2.dname,count(t1.empno) From emp t1,dept t2 where t1.deptno = t2.deptno group by t2.deptno;
  • 右外连接

通过外连接可以将对于连接条件不成立的记录仍然包含在最后的结果中

1
2
3
4
5
6
7
8
9
10
11
-- 列出所有部门与员工数
select 
  t2.deptno,
  t2.dname,
  count(t1.empno)
from emp t1 right outer join dept t2 on (t1.deptno = t2.deptno) group by t2.deptno;

 

select t2.deptno,t2.dname,count(t1.empno)
from emp t1 right join dept t2 on t1.deptno = t2.deptno group by t2.deptno;
  • 自连接

自连接的核心是通过表的别名将同一张表视为多张表

1
2
3
4
-- 查询员工的姓名和员工老板的姓名

select t1.ename,t2.ename from emp t1,emp t2 where t1.mgr = t2.empno;

9.7 hive子查询

hive只支持 from 和 where子句中的子查询

1
2
3
4
5
6
7
8
9
select 
  e.ename 
from emp e 
where e.deptno in (
   select 
      d.deptno 
   from dept d 
   where d.dname='SALES' or d.dname='accounting'
);
  • 子查询注意问题

  1. 语法中的括号

  2. 书写风格

  3. hive中只支持where和from子句中的子查询

  4. 主查询和子查询可以不是同一张表

  5. 如果子查询返回的结果集有空值的话不可以使用not in 但是可以使用in

10. 使用 Java 操作 HIVE

  • 启动hive远程服务 后台运行
1
2
3
4
5
6
7
8
9
10
hive --service hiveserver &

# 指定端口号

hive --service hiveserver -p 10000 &
hive --service hiveserver2 &

 

Starting hive thrift server  # 表示启动过程已经成功

10.1 使用 JDBC 方式连接 hive

打开ide 新建Java项目 导入hive驱动(从hive lib目录下可以找到(hive-jdbc.jar)) 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
package hvie;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 工具类 获得连接释放资源
 * hnbian
 * Mar 31, 20168:13:21 PM
 */
public class JdbcUtils {
	private static String driver = "org.apache.hadoop.hive.jdbc.HiveDriver";
	private static String url = "jdbc:hive2://192.168.36.163:10000/default";
	/**
	 *注册驱动
	 */
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 获得连接
	 * @author hnbian
	 * Mar 31, 20168:28:14 PM
	 * @return
	 */
	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 释放资源
	 * @author hnbian
	 * Mar 31, 20168:28:27 PM
	 * @param con
	 * @param st
	 * @param rs
	 */
	public static void release(Connection con,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				rs = null;
			}
		}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				st = null;
			}
		}
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				con = null;
			}
		}
	}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package hvie;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class HiveTest {
	public static void main(String aegs[]){
		Connection conn=null;
		Statement st = null;
		ResultSet rs = null;
		String sql = "select * from emp";
		try{
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				System.out.print(rs.getString(2)+"-"+rs.getDouble(6));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtils.release(conn, st, rs);
		}
	}

}

10.2 使用 Thrift client 方式连接 hive

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package hvie;

import org.apache.thrift.protocol.TBinaryProtocol;
import org.apache.thrift.protocol.TProtocol;
import org.apache.thrift.transport.TSocket;

import java.util.List;

import org.apache.hadoop.hive.service.HiveClient;

public class HiveThriftClient {
	public static void main(String aegs[]) throws Exception{
		//创建 socket 连接
		final TSocket tSocket = new TSocket("192.168.36.163",10000);
		//创建 协议
		final TProtocol tProtocol = new TBinaryProtocol(tSocket);
		//创建客户端
		final HiveClient client = new HiveClient(tProtocol);
		//final HiveThriftClient client = new HiveThriftClient();
		//打开链接
		tSocket.open();
		//执行 sql
		client.execute("desc emp");
		//处理结果
		List<String> columns = client.fetchAll();
		for(String col:columns){
			System.out.println(col);
		}
	}
}

11. 自定义 UDF

  • hive自定义函数(UDF User Defined Function) : 可以直接处理应用与select语句，对于查询结果做格式化处理后,再输出内容

  • 自定义UDF需要继承 org.apache.hadoop.hive.ql.UDF，需要实现evaluate函数，evaluate函数支持重载

  • 把程序打包放到目标机器上去，进入hive客户端,添加jar包，Hive -add jar /root/udf-test.jar

  • 创建临时函数

    Hive> create temporary function <函数名> as ‘Java类名’;

  • 自定义函数的使用

    Select <函数名> from table;

  • 销毁自定义函数

    Hive> drop temporary function <函数名>

  • Hvie 自定义函数案例（拼接两个字符串）

1
2
3
4
5
6
7
8
9
10
11
package hvie;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class Conocat_UDF extends UDF {

	public Text evaluate(Text a,Text b){
		return new Text(a.toString()+"----"+b.toString());
	}
}

  • 导出jar文件，并上传到服务器，

  • 创建临时函数

1
create temporary function intersection as 'hvie.ListIntersectionUDF';
文章作者: hnbian
文章链接: https://www.hnbian.cn/posts/41ba1ebf.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
hive
评论
  目录