Hive HQL 之 DDL 数据表操作

发表于 更新于 分类于 阅读次数： 本文字数： 1.7k 阅读时长 ≈ 6 分钟

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;