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 文件内容
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
-- 创建内部表
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 文件内容
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
-- 创建内部表
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;
-- 再次查询数据文件，仍然存在
内部表与外部表的转换
-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查看表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表
alter table t1 set tblproperties('EXTERNAL'='FALSE');
分区表
Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。
而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。
在实际中，通常根据时间、地区等信息进行分区。
-- 创建通过 日期字符串 分区的表
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 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
-- 创建分桶表
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;
备注：
- 分桶规则：分桶字段.hashCode%分桶数
- 分桶表加载数据时，使用insert...select...方式进行
- 网上有资料说要使用分区表需要设置hive.enforce.bucketing=true,那是Hive1.x以前的版本；
Hive2.x中，删除了该参数，始终可以分桶；
修改表 & 删除表
-- 修改表名。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;
