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) |
MAP | key-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
4ID 姓名 爱好 地址
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin
基于上面数据创建表 HQL1
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
4ID 姓名 爱好 地址
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin
基于上面数据创建表 HQL1
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 | -- 内部表转外部表 |
分区表
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 | -- 测试数据 |
1 | -- 创建分桶表 |
备注:
- 分桶规则:分桶字段.hashCode% 分桶数
- 分桶表加载数据时,使用 insert…select… 方式进行
- 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true, 那是 Hive1.x 以前的版本;
Hive2.x 中,删除了该参数,始终可以分桶;
修改表 & 删除表
1 | -- 修改表名。rename |