Hive DDL DML及SQL操作

一、Hive 数据模型及数据类型

1.Hive 数据抽象

hive 中主要包含以下几种数据模型：Database（数据库）、Table（表）、Partition（分区）、Bucket（桶）

Database（数据库） 
Hive中的数据库包含一系列的数据库，每个数据库都对应于HDFS上的一个目录，默认的数据库为default，对应于HDFS目录是/user/hadoop/hive/warehouse，可以通过hive.metastore.warehouse.dir参数进行配置（hive-site.xml中配置）

Table（表） 
Hive 中的表又分为内部表和外部表 
Hive 中的每张表对应于HDFS上的一个目录，HDFS目录为： 
/user/hadoop/hive/warehouse/[databasename.db]/table

Partition（分区） 
Hive 中每个分区对应于HDFS上表文件夹的一个子文件夹，比如order_partition表中有一个分区event_month=2014-05，则分区的数据在hdfs中的存放目录为为/user/hadoop/hive/warehouse/[databasename.db]/order_partition/event_month=2014-05

Bucket（桶） 
对指定的列计算其hash，根据hash值切分数据，目的是为了并行，每一个桶对应一个文件。比如将emp表empno列分散至10个桶中，首先对id列的值计算hash，对应hash值为0和10的数据存储的HDFS目录为： 
/user/hadoop/hive/warehouse/[databasename.db]/emp/part-00000 
而hash值为2的数据存储的HDFS 目录为： 
/user/hadoop/hive/warehouse/[databasename.db]/emp/part-00002

2.Hive 数据数据类型

Hive 基本数据类型

图中红色字体的数据类型为hive中比较常用的

Hive 复杂数据类型

Array：数组，一组有序字段，字段的类型必须相同 
Map：一组无序的键值对，键和值可以是任意类型，同一个map的键的类型必须相同，值的类型也必须相同 
Struct：结构体，一组命名的字段，字段类型可以不相同

hive 分隔符

分隔符	编码	作用
\n	\n	行与行之间的分隔符
^A	\001	记录字段之间的分隔符
^B	\002	数组和结构体之间的分隔符
^C	\003	键值对之间的分隔符

二、DDL 操作

DDL操作官方使用文档： 
https://cwiki./confluence/display/Hive/LanguageManual+DDL

1.Database DDL 操作

(1).创建

语法：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
1
2
3
4

• 创建数据库test

对应的HDFS目录为：/user/hive/warehouse 
Hive 中的数据库对应HDFS中的一个文件夹，文件夹命名格式为：数据库名.db

create database test;
1

• 如果test数据库不存在再创建

create database if not exists test1;
1

• 在指定路径下创建数据库

create database test2 location '/myhive/mydb';
1

• 创建数据库，并为数据库添加描述信息

create database test3 comment 'my test db'
with dbproperties ('creator'='zhangsan','date'='2017-8-8');
1
2

(2).查询

• 查询所有的数据库

show databases;
1

• 查看以test开头的数据库

show databases like 'test*';
1

• 查看数据库的详细信息

desc database test3;
1

在上面的基础上如果想查看数据库更详细的信息，可以加个参数extended

desc database extended test3;
1

• 查看创建数据库sql语句

show create database test3;
1

• 切换数据库 
  如果不指定数据库，那么默认使用的就是default

use databasename;
1

(3).修改

语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
1

更改数据库的修改人

alter database test3 set dbproperties ('modifier'='lisi');
1

(4).删除

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
1

• 删除数据库

删除数据库test3

drop database if exists test3;
1

删除时会报如下错误： 
InvalidOperationException(message:Database test3 is not empty. One or more tables exist.)

如果数据库中有0或多个表时，不能直接删除，需要先删除表再删除数据库

如果想要删除含有表的数据库，在删除时加上cascade，表示级联删除（慎用），可以使用如下命令

drop database if exists test3 cascade;
1

2.Table DDL 操作

(1).创建

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
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

创建表的语法是不是有点多哈，我也这么觉得^_^

• 创建表

create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t' 
lines terminated by '\n' stored as textfile;

• 导入数据

有一个emp.txt文件，里面的数据对应我们刚刚创建的表emp

7369    SMITH   CLERK   7902    1980-12-17      800.00          20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.00 300.00  30
7521    WARD    SALESMAN        7698    1981-2-22       1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2        2975.00         20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.00         30
7782    CLARK   MANAGER 7839    1981-6-9        2450.00         10
7788    SCOTT   ANALYST 7566    1987-4-19       3000.00         20
7839    KING    PRESIDENT               1981-11-17      5000.00         10
7844    TURNER  SALESMAN        7698    1981-9-8        1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23       1100.00         20
7900    JAMES   CLERK   7698    1981-12-3       950.00          30
7902    FORD    ANALYST 7566    1981-12-3       3000.00         20
7934    MILLER  CLERK   7782    1982-1-23       1300.00         10
8888    HIVE    PROGRAM 7839    1988-1-23       10300.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

格式看的可能有点乱，直接复制粘贴到文件中就可以了

使用如下命令将emp.txt文件中的数据导入到表emp

load data local inpath '/home/hadoop/data/emp.txt' overwrite into table emp;
1

• 复制表（只拷贝表结构）

create table emp2 like emp;
1

• 复制表（拷贝表结构及数据，会运行MapReduce作业）

create table emp3 as select * from emp;
1

导入成功后查看表中数据

• 创建表emp到emp_copy，emp_copy中只包含三列：empno，ename，job

create table emp_copy as select empno,ename,job from emp;
1

(2).查询

• 查看数据库下的所有表

show tables;
show tables 'emp*';
1
2

• 查看表结构

desc emp;
1

• 查看表结构的详细信息

desc extended emp;
1

上面查看到的表结构的详细信息格式较乱，可以使用formatted格式化查看（常用）

desc formatted emp;
1

• 查看表的创建语句

show create table emp;
1

(3).修改

语法：

ALTER TABLE table_name RENAME TO new_table_name;
1

修改表名emp2为emp_bak

alter table emp2 rename to emp_bak;
1

(4).删除

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];
1

• 删除表emp_bak

drop table if exists emp_bak;
1

• 清空表中所有数据

truncate table emp_copy;
1

三、DML（Table） 操作

DML 操作只是针对表的，DML 操作官方帮助文档 
https://cwiki./confluence/display/Hive/LanguageManual+DML

1.内部表和外部表

(1).内部表

• 创建一张内部表 emp_managed

create table emp_managed as select * from emp;
1

• 查看表emp_managed在hdfs中是否存在

$ hadoop fs -ls /user/hive/warehouse/db_hive.db/
1

• 登录mysql（TBLS中存放了hive中的所有表）数据库，查看hive的元数据信息

mysql -hlocalhost -uroot -p12345 -P3306  mysql -uroot -pselect * from TBLS;
1

上图红色框中的表为我们刚刚创建的一张内部表emp_managed

• 删除表emp_managed

drop table if exists emp_managed;
1

• hive 中的emp_managed表会被删除

• mysql 中hive的元数据被删除

• 表emp_managed 对应hdfs 中的文件夹被删除

(2).外部表

• 创建一张外部表

create external table emp_external(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t'
location '/hive_external/emp';
1
2
3
4
5
6
7
8
9
10
11
12

• mysql 中查看hive的元数据

• 新创建的表emp_external中是没有数据的，我们将emp.txt文件上传到hdfs的/hive_external/emp目录下

$ hadoop fs -put emp.txt /hive_external/emp/
1

上传完成后，表emp_external就有数据了，使用sql查看

• 然后我们来删除这张表，它是一张外部表，注意和内部表有什么区别

hive 中 表emp_external被删除

drop table if exists emp_external;
1

• mysql 中 元数据被删除

• hdfs 中的文件并不会被删除

$ hadoop fs -ls /hive_external/emp/
1

总结：内部表与外部表的区别 
如果是内部表，在删除时，MySQL中的元数据和HDFS中的数据都会被删除 
如果是外部表，在删除时，MySQL中的元数据会被删除，HDFS中的数据不会被删除

2.表数据加载（load）

(1).加载数据到文件中

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
1

LOCAL：如果加上表示从本地加载数据，默认不加，从hdfs中加载数据 
OVERWRITE：加上表示覆盖表中数据

• 从本地文件加载数据到hive表中 
  把emp.txt 文件加载到数据hive中的emp表中

load data local inpath '/home/hadoop/data/emp.txt' into table emp;
1

如果再次执行一次load，那么表中的数据会有30条，会追加，可以加上overwrite在加载数据前删除原有数据

(2).从hdfs中加载数据到hive表中

在hdfs中创建目录/hive/data，然后将emp.txt文件上传到hdfs

$ hadoop fs -mkdir -p /data/hive
$ hadoop fs -put emp.txt /data/hive/
1
2

加载数据到表emp中

load data inpath '/hive/data/emp.txt' overwrite into table emp;
1

3.插入数据（insert … into）

向表 emp 中插入数据

insert into emp(empno,ename,job) values(1001,'TOM','MANAGER');
insert into emp values(1002,'JOHN','CLERK',7786,'1995-8-28',1850.0,2.0,30);
1
2

4.查询数据（select）

emp 表中数据如下

• 查询部门编号为10的员工信息

select * from emp where deptno=10;
1

• 查询姓名为SMITH的员工

select * from emp where ename='SMITH';
1

• 查询员工编号小于等于7766的员工

select * from emp where empno <= 7766;
1

• 查询员工工资大于1000小于1500的员工

select * from emp where sal between 1000 and 1500;
1

• 查询前5条记录

select * from emp limit 5;
1

• 查询姓名为SCOTT或MARTIN的员工

select * from emp where ename in ('SCOTT','MARTIN');
1

• 查询有津贴的员工

select * from emp where comm is not null;
1

• 统计部门10下共有多少员工

select count(*) from emp where deptno=10;
1

结果：3

• 查询员工的最大、最小、平均工资及所有工资的和

select max(sal),min(sal),avg(sal),sum(sal) from emp;
1

结果：10300.0 800.0 2621.6666666666665 39325.0

• 查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;
1

结果： 
10 2916.6666666666665 
20 2175.0 
30 1566.6666666666667

• 查询平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
1

结果： 
10 2916.6666666666665 
20 2175.0

• 查询员工的姓名和工资等级，按如下规则显示

sal小于等于1000，显示lower 
sal大于1000且小于等于2000，显示middle 
sal大于2000小于等于4000，显示high 
sal大于4000，显示highest

select ename, sal, 
case 
when sal < 1000 then 'lower'
when sal > 1000 and sal <= 2000 then 'middle'
when sal > 2000 and sal <= 4000 then 'high'
else 'highest' end
from emp;
1
2
3
4
5
6
7

5.导出数据（insert…directory）

语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...
1
2
3

• 导出数据到本地

将表emp中的数据导出到本地目录/home/hadoop/data/tmp下

insert overwrite local directory '/home/hadoop/data/tmp' row format delimited fields terminated by '\t' select * from emp;
1

运行结果：到/home/hadoop/data/tmp目录下查看

• 导出数据到HDFS

将表emp中的数据导出到hdfs的/data/hive目录下

insert overwrite directory '/data/hive' row format delimited fields terminated by '\t' select * from emp;
1

结果：在hdfs中查看

6.分区表

Hive 可以创建分区表，主要用于解决由于单个数据表数据量过大进而导致的性能问题 
Hive 中的分区表分为两种：静态分区和动态分区

(1).静态分区

静态分区由分为两种：单级分区和多级分区

单级分区

创建一种订单分区表

create table order_partition(
order_number string,
event_time string
)
partitioned by (event_month string)
row format delimited fields terminated by '\t';
1
2
3
4
5
6

order.txt文件中数据如下

10703007267488  2014-05-01 06:01:12.334+01
10101043505096  2014-05-01 07:28:12.342+01
10103043509747  2014-05-01 07:50:12.33+01
10103043501575  2014-05-01 09:27:12.33+01
10104043514061  2014-05-01 09:03:12.324+01
1
2
3
4
5

将order.txt 文件中的数据加载到order_partition表中

• 方式一：使用load加载数据

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_partition partition (event_month='2014-05');
1

• 方式二：使用hadoop shell 加载数据

$ hadoop fs -mkdir -p /user/hive/warehouse/db_hive.db/order_partition/event_month=2014-06
$ hadoop fs -put /home/hadoop/data/order.txt /user/hive/warehouse/db_hive.db/order_partition/event_month=2014-06
1
2

上传完成后查看表order_partition

上图中可以看到并没有看到我们刚刚通过hdfs上传后的数据，原因是我们将文件上传到了hdfs，hdfs是有了数据，但hive中的元数据中还没有，执行如下命令更新后，再次查看数据

msck repair table order_partition;
1

对于分区表，不建议直接使用select * 查询，性能低，建议查询时加上条件，如果加上条件后它会直接从指定的分区中查找数据

select * from order_partition where event_month='2014-06';
1

多级分区：

创建表order_multi_partition

create table order_multi_partition(
order_number string,
event_time string
)
partitioned by (event_month string, step string)
row format delimited fields terminated by '\t';
1
2
3
4
5
6

加载数据到表order_multi_partition

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=1);
1

把step修改为2，再次加载数据

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=2);
1

查看hdfs中的目录结构

$ hadoop fs -ls /user/hive/warehouse/db_hive.db/order_multi_partition/event_month=2014-05
1

单机分区和多级分区唯一的区别就是多级分区在hdfs中的目录为多级

(2).动态分区

hive 中默认是静态分区，想要使用动态分区，需要设置如下参数，笔者使用的是临时设置，你也可以写在配置文件（hive-site.xml）里，永久生效。临时配置如下

开启动态分区（默认为false，不开启）

set hive.exec.dynamic.partition=true;
1

指定动态分区模式，默认为strict，即必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。

set hive.exec.dynamic.partition.mode=nonstrict;
1

创建表student

create table student(
id int,
name string,
tel string,
age int
)
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7

student.txt文件内容如下

1001    zhangsan        18310982765     18
1002    lisi    15028761946     20
1003    wangwu  13018909873     25
1
2
3

将文件student.txt中的内容加载到student表中

load data local inpath '/home/hadoop/data/student.txt' overwrite into table student;
1

创建分区表stu_age_partition

create table stu_age_partition(
id int,
name string,
tel string
)
partitioned by (age int)
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7

将student表的数据以age为分区插入到stu_age_partition表，试想如果student表中的数据很多，使用insert一条一条插入数据，很不方便，所以这个时候可以使用hive的动态分区来实现

insert into table stu_age_partition partition (age) select id,name,tel,age from student;
1

查询时，建议加上分区条件，性能高

select * from stu_age_partition;
select * from stu_age_partition where age > 20;
1
2

四、复杂数据类型操作

之前的操作都是在hive1中做的，笔者安装的hive版本为2.3.0，即支持hive1，也支持hive2，下面的我们来使用hive2进行操作，hive1和hive2的区别就是hive2中解决了hive1中的单点故障，可以搭建高可用的hive集群，hive2界面显示格式要比hive1直观、好看（类似于mysql中的shell界面）

要想使用hive2，需要先执行如下命令开启hive2服务

hiveserver2
1

开启后使用如下命令连接hive2，就能进行操作了

beeline -u jdbc:hive2://
1

1.Array

• 创建一张带有数组的表tb_array

create table tb_array(
name string,
work_locations array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';
1
2
3
4
5
6

• 加载数据到表tb_array

hive_array.txt文件内容如下

zhangsan        beijing,shanghai,tianjin,hangzhou
lisi    changchu,chengdu,wuhan
1
2

load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table tb_array;
1

• 查询张三的第一个工作地点（数组下标从0开始）

select name,work_locations[0] from tb_array where name='zhangsan';
1

• 查询每个人工作地点的数量（size）

select name,size(work_locations) from tb_array;
1

2.Map

• 创建一个带有map类型的表tb_map

create table tb_map(
name string,
scores map<string,int>
)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
1
2
3
4
5
6
7

• 加载数据到表tb_map

hive_map.txt文件内容如下

zhangsan        math:80,chinese:89,english:95
lisi    chinese:60,math:80,english:99
1
2

load data local inpath '/home/hadoop/data/hive_map.txt' overwrite into table tb_map;
1

• 查询所有学生的英语成绩

select name,scores['english'] from tb_map;
1

• 查询所有学生的英语和数学成绩

select name,scores['english'],scores['math'] from tb_map;
1

3.Struct

• 创建一张带有结构体的表

create table tb_struct(
ip string,
userinfo struct<name:string,age:int>
)
row format delimited fields terminated by '#'
collection items terminated by ':';
1
2
3
4
5
6

• 加载文件hive_struct.txt中的数据到表tb_struct

hive_struct.txt文件内容如下

192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70
1
2
3
4

load data local inpath '/home/hadoop/data/hive_struct.txt' overwrite into table tb_struct;
1

• 查询姓名及年龄

select userinfo.name,userinfo.age from tb_struct;
1