HQL 的特点:

  • 并不是所有的 HQL 都会被 Hive 转换成 MR 作业执行,对于简单不需要聚合的操作,如 SELECT .. FROM xx LIMIT n,直接通过 FetchTask 获取数据

  • HQL 是一种 SQL 方案,支持绝大部分的 SQL-92 标准

DML

数据导入

从本地或 HDFS 中装载数据

  • LOCAL:
    • LOAD DATA LOCAL …:从本地文件系统加载数据到 Hive 表中
    • LOAD DATA …:从HDFS加载数据到 Hive 表中
  • INPATH:加载数据的路径
  • OVERWRITE:覆盖表中已有数据,否则表示追加数据,用于幂等
  • PARTITION:将数据加载到指定的分区

一旦该表存在分区,那么在数据在加载时必须加载进入指定分区中,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 加载数据并指定分区,HDFS 文件,已经被转移
LOAD DATA INPATH '/user/hadoop/data' INTO student_info
PARTITION(province='sichuan', city='chengdu');

-- 加载数据覆盖表中已有数据
LOAD DATA INPATH 'data/sourceA.txt' OVERWRITE INTO TABLE tabA;

-- 加载数据,覆盖表的数据,到指定的分区
LOAD DATA INPATH '/user/hadoop/o' OVERWRITE INTO TABLE test3
PARTITION (part = "a");

-- 添加分区并指定存储位置
ALTER TABLE test ADD PARTITION (x = x1, y = y2)
SET LOCATION '/user/test/x1/y1';

插入数据

1
2
3
4
5
-- 插入数据
insert into table tabC partition(month='202001') values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan', 'TJ');

-- 插入查询的结果数据到指定分区中
insert into table tabC partition(month='202002') select id, name, area from tabC where month='202001';

创建表并插入数据

1
create table if not exists tabD as select * from tabC;

多表(多分区)插入模式

一次查询,产生多个不相交的输出

可以通过一次查询,产生多个不相交的输出。

这样只通过对 source 表的一次查询,就将符合条件的数据插入 test 表的各个分区,非常方便

1
2
3
4
5
6
7
8
9
10
-- 多个查询差生多个不相交的输出
FROM source
INSERT OVERWRITE TABLE test PARTITION (part = 'a')
SELECT id, name WHERE id >= 0 AND id < 100

INSERT OVERWRITE TABLE test PARTITION (part = 'b')
SELECT id, name WHERE id >= 100 AND id < 200

INSERT OVERWRITE TABLE test PARTITION (part = 'c')
SELECT id, name WHERE id >= 200 AND id < 300

import 导入数据

1
import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

数据导出

  • 将结果导出到本地
  • 将查询结果格式化到本地
  • 将结果导出到 HDFS
  • 通过 DataX、Sqoop 等工具将结果导出到 HBase、MySQL 等其他地方

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 修改列
alter table course_common1 change column id cid int;

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

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

-- 更改序列化的编码格式
ALTER TABLE person SET SERDEPROPERTIES ('serialization.encoding'='GBK');

-- 更改序列化反序列化的属性分割
alter table test.tmp1 set serdeproperties('field.delim'=',');

-- 更改编码
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

-- 创建了分区的话就要再执行两条命令
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

DDL

DB 与 Table

http://img.janhen.com/202103030859484G2IXM.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查看默认 hive 存放目录下的数据目录
dfs -ls /user/hive/warehouse;

-- 创建数据库并指定存放位置
create database if not exit comment 'test comment'
location '/usr/hive/mydb2.dbe';

desc database extended mydb2;

-- 连同表一起删除
DROP DATABASE IF EXISTS test CASCADE;
DESC EXTENDED student;
DESC FORMATTED student;
-- 根据其他表创建新的表
CREATE TABLE IF NOT EXISTS test.student2 LIKE test.student;

-- 表的情况查看,文件的大小,文件个数,是否分区,lastAccessTime,lastUpdateTime
show table extended like 'dwd_start_log';
-- 查看指定分区的情况
show table extended like 'dwd_start_log' parititon(dt='2020-07-22');

--
desc formatted dwd_start_log;
desc formatted dwd_start_log partition (dt='2020-07-22');

Table

建表的方式: 直接建表、查询建表、LIKE 建表

建表的语法:

  • CREATE TABLE [IF NOT EXISTS]:创建表
  • EXTERNAL: 外部表创建,生产中一般创建的都是外部表,删除表不删除数据
  • comment: 表注释
  • partition by: 对表中数据进行分区
  • clustered by: 建立分桶表
  • sorted by: 对表中的一个或多个字段进行排序,较少使用
  • 存储子句: 可指定 SerDe, 默认没有使用 ROW FORMAT 或者 ROW FORMAT DELIMITED,会默认使用 SerDe。建表时需要为表指定列在指定列的同时也会指定自定义的 SerDe。hive使用 Serde 进行行对象的序列与反序列化。
  • stored as SEQUENCEFILE|TEXTFILE|RCFILE
  • LOCATION: 表在 HDFS 上的位置
  • TBLPROPERTIES:定义表的属性
  • AS: 接查询语句,根据查询结果建表
  • LIKE: 复制现有的表结构,不会复制数据
1
2
3
4
5
6
7
ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] |
SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]

使用 JSONSerDe 进行建表

1
2
3
4
5
6
7
8
{"id": 1,"ids": [101,102,103],"total_number": 3}
{"id": 2,"ids": [201,202,203,204],"total_number": 4}
{"id": 3,"ids": [301,302,303,304,305],"total_number": 5}
create table jsont2 (
id int,
ids array<string>,
total_number int
) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';

内外部表

表的类型有两种,分别是内部表、外部表

  • 默认情况下,创建内部表
  • 删除内部表,表的元数据和数据一起删除
  • 删除外部表,删除表的定义,数据保留
  • 生产环境中,多使用外部表
  • 外部表不能执行 TRUNCATE

表类型转换

1
2
3
4
5
-- 内部表转外部表
ALTER TABLE t1 SET tblproperties('EXTERNAL'='TRUE');

-- 外部表转内部表。EXTERNAL 大写,false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');

分区表

按照分区字段将表中的数据放置在不同的目录中,提高SQL查询的性能

Hive 没有索引,分区的作用和索引非常类似,可将其看做一种简易索引。对于直接命中分区的查询,Hive 不会执行 MapReduce 作业。

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

多分区表,在表的目录下有多少分区就有多少级子目录。

分区查看

1
2
3
4
5
-- 查看分区
SHOW PARTITIONS student_info;
-- partition count
hive --silent -e "show partitions <dbName>.<tableName>;" | wc -l
select count(distinct <partition key>) from <TableName>;

新增分区,加载数据

1
2
3
4
5
6
7
8
9
10
11
12
-- 增加分区,不加载数据
alter table t3 add partition(dt='2020-06-03');

-- 增加多个分区
alter table t3 add partition(dt='2020-06-05') partition(dt='2020-06-06');

-- 增加分区,加载数据
alter table t3 add partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';

-- 单独为外部表的分区键指定值和存储位置:
ALTER TABLE student _info ADD PARTITION (province = sichuan, city = chengdu) LOCATION 'hdfs://master:9000/student/sichuan/chengdu';

修改分区的hdfs路径

1
2
alter table t3 partition(dt='2020-06-01') 
set location '/user/hive/warehouse/t3/dt=2020-06-03';

删除分区

1
alter table t3 drop partition(dt='2020-06-03'), partition(dt='2020-06-04');

动态分区

Hive 会根据 SELECT 语句中的最后一个查询字段作为动态分区的依据,而不是根据字段名来选择。如果指定了 n 个动态分区的字段,Hive 会将 select 语句中最后 n 个字段作为动态分区的依据。 Hive 默认没有开启动态分区。

1
2
3
4
-- 开启自动分区
set hive.exec.dynamic.partition = true;
INSERT OVERWRITE TABLE test PARTITION(time)
SELECT id, modify_time FROM source;

分桶表

分区不能更细粒度的划分数据,就需要使用分桶技术将数据划分成更细的粒度。

使用 cluster by <col-name> into <num> buckets 建表的时候指定。

分桶的原理

  • MR 中: key.hashCode % reduceTask
  • Hive 中: 分桶字段.hashCode % 分桶个数

分桶表创建

1
2
3
4
5
6
create table course( 
id int,
name string,
score int )
clustered by (id) into 3 buckets
row format delimited fields terminated by "\\t";

分桶表加载数据

1
2
3
4
5
-- 普通表加载数据 
load data local inpath '/home/hadoop/data/course.dat' into table course_common;

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

分区表和分桶表的区别

存储格式: 分区表和分桶表的存储格式不同,分区表在多个分区时会有多级目录,分桶表按照字段散列分布。

作用: 分区表细化数据管理,缩小 MR 扫描的数据量;分桶表提高 join 查询效率,在一份数据被经常用作连接 hash 的时候建立分桶,分桶字段为连接字段,提高采样的效率。

数据粒度划分: 分桶表相对分区进行更细粒度的划分。

依据的列: 分桶表指定的分桶依据不是伪列,而是数据表中真实存在的列。

大表 JOIN 分桶的原因

DQL

where

正则匹配过滤

1
2
3
4
-- 正则匹配,使用 rlike。正则表达式,名字以A或S开头
select ename,
salfrom emp
where ename rlike '^(A|S).*';

lateral view

lateral view 首先将表生成函数 UDTF 应用于基础表的每一行,然后将结果输出行与输入行连接起来以形成具有提供的表别名的虚拟表。

语法

  • 从 0.12.0 开始列别名可省略,从 UTDF 返回的 StructObjectInspector 的字段名称继承
1
2
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

使用案例

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
CREATE TABLE IF NOT EXISTS pageAds(
pageid string,
adid_list Array<int>
);
pageid adid_list
front_page [1,2,3]
contact_page [3,4,5]
-- 页面对应的广告
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
-- 查看特定广告的展示次数
SELECT adid, count(1) AS adcnt
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
pageid(string) adid(int)
"front_page" 1
"front_page" 2
"front_page" 3
"contact_page" 3
"contact_page" 4
"contact_page" 5

adid adcnt
1 1
2 1
3 2
4 1
5 1

多个 lateral view

  • from clause 可有多个 lateral view
  • 后续的 LATERAL VIEWS 可以引用 LATERAL VIEW 左侧出现的任何表中的列。

原始数据 ⇒ 建表语句 ⇒ 多个 lateral view 使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
use1;18;male;{"id": 1,"ids": [101,102,103],"total_number": 3}
use2;20;female;{"id": 2,"ids": [201,202,203,204],"total_number": 4}
use3;23;male;{"id": 3,"ids": [301,302,303,304,305],"total_number": 5}
CREATE TABLE IF NOT EXISTS jsont1 (
username string,
age int,
sex string,
json string
) row format delimited fields terminated by ';';
-- 解析json串中的数组,并展开
select username,
age,
sex,
ids1,
id,
num
from jsont1 lateral view explode(janhen_json_array(json, "ids")) t1 as ids1
lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;

join 子句

1
2
3
4
5
6
-- 内连接 
select * from u1 join u2 on u1.id = u2.id;
-- 左外连接
select * from u1 left join u2 on u1.id = u2.id;
-- 全外连接
select * from u1 full join u2 on u1.id = u2.id;

多表连接

Hive 总是按照从左到右的顺序执行,Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。

会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作;然后再 启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作; 然后再继续直到全部操作;

1
2
3
4
5
select *
from techer t
left join course c on t.t_id = c.t_id
left join score s on s.c_id = c.c_id
left join student stu on s.s_id = stu.s_id;

笛卡尔积

满足下列条件

  • 没有连接条件
  • 连接条件无效
  • 所有表中的所有行互相连接

Hive 默认不支持笛卡尔积

1
2
set hive.strict.checks.cartesian.product=false;
select * from u1, u2;

union 子句

union all 不会对合并的数据进行去重, union 会进行去重

可以使用 union all + group by 进行数据去重

使用 union all 要保证两个子查询得到的字段名称一致

排序子句

MR 全局排序

  • 排序字段需要出现在 select 字段中
  • ORDER BY 执行全局排序,只有一个 reduce
  • 输出规模较大时,耗时高
1
2
3
4
5
6
7
8
-- 多列排序 
select empno,
ename,
job, mgr,
sal + nvl(comm, 0) salcomm,
deptno
from emp
order by deptno, salcomm desc;

MR 的内部排序(sort by)

sort by 为每个 reduce 产生排序文件,在 reduce 内部进行排序,得到局部有序的结果,保证每个 reducer 的结果有序

1
2
3
4
5
6
-- 设置reduce个数
set mapreduce.job.reduces=2;
-- 按照工资降序查看员工信息
select * from emp sort by sal desc;
-- 将查询结果导入到文件中(按照工资降序)。生成两个输出文件,每个文件内部数据按 工资降序排列
insert overwrite local directory '/home/hadoop/output/sortsal' select * from emp sort by sal desc;

MR 分区排序(distribute by)

  • 将特定的行发送到特定的 reducer 中
  • distribute by 要写在 sort by 之前
  • 可结合 sort by 操作,使分区数据有序
  • 类似于 MR 中的分区操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 先按 deptno 分区,在分区内按照 sal + comm 排序
set mapreduce.job.reduces=3;
SELECT
empno,
ename,
job,
deptno,
sal + nvl(comm, 0) salcomm
FROM emp
DISTRIBUTE BY deptno
SORT BY sal comm DESC;
-- 将数据分到 3 个区中,每个分区都有数据
insert overwrite local directory '/home/hadoop/output/distBy1'
select empno,
ename,
job,
deptno,
sal + nvl(comm, 0) salcomm
from emp
distribute by deptno
sort by salcomm desc;

Cluster By

  • distribute by 与 sort by 为同一个字段时,使用 cluster by 简化语法
  • 只能是升序,不能指定排序规则

Ref