Hive HQL 之 DDL 数据表操作

Hive 数据类型

普通数据类型

Hive 类似和 Java 语言中一样,会支持多种不同长度的整型和浮点类型数据,同时也支持布尔类型、字符串类型,时间截数据类型以及二进制数组数据类型等。具体的如下表:

大类类型
Integers (整型)TINYINT – 1 字节的有符号整型
SAMLINT – 2 字节的有符号整型
INT – 4 字节的有符号整型
BIGINT – 8 字节的有符号整型
Floating point numbers (浮点数)FLOAT – 单精度浮点数
DOUBLE – 双精度浮点数
Fixed point numbers (定点数)DECIMAL – 17 字节,任意精度数字。通常用户自定义 decimal (12,6)
String (字符串)STRING – 可指定字符集的不定长字符
VARCHAR – 1-65535 长度的不定长字符串
CHAR – 1-255 定长字符串
Datetime (时间日期类型)STRTIMESTAMP – 时间戳 (纳秒)
DATE – 时间日期类型
Boolean (布尔类型)BOOLEAN – true/false
Binary types (二进制类型)BINARY – 二进制字节序列

集合数据类型

Hive 支持集合数据类型,包括 array、map、struct、union

类型描述字面量示例
ARRAY有序的相同数据类型的集合array(1,2)
MAPkey-value 对。key 必须是基本数据类型,value 不限map(‘a’,1, ‘b’, 2)
STRUCT不同类型字段的集合。类似于 C 语言的结构 体struct(‘1’,1,1.0),
named_struct(‘col1’,’1’,’col2’,1,’clo3’, 1.0)
UNION不同类型的元素存储在同一字段的不同行create_union(1,’a’,63)

内部表 & 外部表

在创建表的时候,可指定表的类型。表有两种类型,分别是内部表(管理表)、外部表。

  • 默认情况下,创建内部表。如果要创建外部表,需要使用关键字 external
  • 在删除内部表时,表的定义(元数据)和数据同时被删除
  • 在删除外部表时,仅删除表的定义,数据被保留
  • 在生产环境中,多使用外部表

内部表

t1.dat 文件内容

1
2
3
4
ID 姓名 爱好 地址
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

基于上面数据创建表 HQL

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
-- 创建内部表
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 因为默认的分隔符号是 ^A ^B ^C 所以我们要指定分隔符号

-- 显示表的定义,信息较少
desc t1;

-- 显示表的定义,详细
desc formatted t1;

-- 加载数据
load data local inpath '/root/t1.dat' into table t1;

-- 查询数据
select * from t1;

-- 查看数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;
dfs -cat /user/hive/warehouse/mydb.db/t1/t1.dat;

-- 删除表,文件也一并删除
drop table t1;

外部表

t2.dat 文件内容

1
2
3
4
ID 姓名 爱好 地址
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

基于上面数据创建表 HQL

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
-- 创建内部表
create external table t2(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 因为默认的分隔符号是 ^A ^B ^C 所以我们要指定分隔符号

-- 显示表的定义,信息较少
desc t2;

-- 显示表的定义,详细
desc formatted t2;

-- 加载数据
load data local inpath '/root/t2.dat' into table t2;

-- 查询数据
select * from t2;

-- 查看数据文件
dfs -ls /user/hive/warehouse/mydb.db/t2;
dfs -cat /user/hive/warehouse/mydb.db/t2/t2.dat;

-- 删除表,文件不会被删除
drop table t2;

-- 再次查询数据文件,仍然存在

内部表与外部表的转换

1
2
3
4
5
6
7
-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查看表信息,是否转换成功
desc formatted t1;

-- 外部表转内部表
alter table t1 set tblproperties('EXTERNAL'='FALSE');

分区表

Hive 在执行查询时,一般会扫描整个表的数据。由于表的数据量大,全表扫描消耗时间长、效率低。
而有时候,查询只需要扫描表中的一部分数据即可,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
-- 创建通过 日期字符串 分区的表
create external table t3(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
partitioned by (dt string)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 加载数据
load data local inpath '/root/t1.dat' into table t3 partition(dt="2021-11-16");
load data local inpath '/root/t1.dat' into table t3 partition(dt="2021-11-17");

-- 查看分区
show partitions t3;

-- 增加分区
alter table t3 add partition(dt="2021-11-18") partition(dt="2021-11-19");

-- 删除分区
alter table t3 drop partition(dt='2021-11-18');

备注:分区字段不是表中已经存在的数据,可以将分区字段看成伪列

分桶表

当单个的分区或者表的数据量过大,分区不能更细粒度的划分数据,就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去,即将数据按照字段进行划分,数据按照字段划分到多个文件当中去。
分桶的原理:

  • MR 中:key.hashCode % reductTask
  • Hive 中:分桶字段.hashCode % 分桶个数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 测试数据
1 java 90
1 C 78
1 python 91
1 hadoop 80
2 java 75
2 C 76
2 python 80
2 hadoop 93
3 java 98
3 C 74
3 python 89
3 hadoop 91
5 java 93
6 c 76
7 python 87
8 hadoop 88
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
-- 创建分桶表
create table course(
id int,
name string,
score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "\t";

-- 创建普通表
create table course_common(
id int,
name string,
score int
)
row format delimited fields terminated by "\t";

-- 给普通表增加数据
load data local inpath '/root/course.dat' into table course_common;

-- 需要通过 insert ... select 给桶加数据
insert into table course select * from course_common;

-- 查看分桶数据 数据按照:(分区字段.hashCode)%(分桶数)进行分区
desc formatted course;
dfs -ls /user/hive/warehouse/mydb.db/course;

image

备注:

  1. 分桶规则:分桶字段.hashCode% 分桶数
  2. 分桶表加载数据时,使用 insert…select… 方式进行
  3. 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true, 那是 Hive1.x 以前的版本;
    Hive2.x 中,删除了该参数,始终可以分桶;

修改表 & 删除表

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
-- 修改表名。rename
alter table course_common
rename to course_commonl;

-- 修改列名。change column
alter table course_common1
change column id cid int;

--修改字段类型。change column
alter table course_common1
change column cid cid string;

-- The following columns have types incompatible with the existing columns in their
respective positions
--修改字段数据类型时,要满足数据类型转换的要求。如int可以转为string,但是string不能转为int

--增加字段。add columns
alter table course_common1
add columns (common string);

--删除字段:replace columns
--这里仅仅只是在元数据中删除了字段,并没有改动hdfs上的数据文件
alter table course_common1
replace columns(
id string, cname string, score int);

--删除表
drop table course_common1;