黑洞

这里藏着一些独特的想法

0%

Hive常用SQL之DQL

Hive发展至今,已支持大部分Mysql中的语法,基础语法在此不过多赘述。

本文重点记录一些特别的查询语句。

1
2
3
4
5
6
7
8
9
10
[WITH CommonTableExpression (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

分区查询(分区裁剪)

通常,SELECT查询将扫描整个表(所谓的全表扫描)。如果使用PARTITIONED BY子句创建的分区表,则在查询时可以指定分区查询,减少全表扫描,也叫做分区裁剪。

所谓分区裁剪指的是:对分区表进行查询时,会检查WHERE子句或JOIN中的ON子句中是否存在对分区字段的过滤,如果存在,则仅访问查询符合条件的分区,即裁剪掉没必要访问的分区。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--创建分区表
creat table t_1(
id int,
name string,
gender string,
addr string
) partitioned by (gender_p string)
row format delimited fields
terminated by '\001';

--动态加载分区表
set hive.exec.dynamic.partition.mode = nonstrict;

insert overwrite table t_1 partition (gender_p)
select ori.*, gender from t_ori as ori;

--分区表查询,顺利执行
select * from t_1 where t_1.gender_p = 'male';

--严格模式下不指定分区将报错,因为t_1是个分区表!
set hive.mapred.mode = strict
select * from t_1

分桶采样

对表进行分桶一般有两个目的,提高数据查询效率和抽样调查。在实际生产中,对于非常大的数据集,有时我们需要的是一个具有代表性的查询结果,而非全部结果。这时候就可以使用如下语句对分桶表进行数据采样。

1
select * from t_data tablesample(bucket x out of y on [column])
  • x:表示从第几个桶开始抽,最小序号为0。
  • y:表示抽取桶的比例(总桶数 / y = 抽多少个桶)。
  • column:表示按照哪个字段抽样(一般为分桶字段)。

y必须是t_data总桶数的倍数或因数,并且x必须小于等于y。

hive根据y的大小,决定抽样比例。
例如,t_data一共分为了10个bucket(桶)。当y=2,抽取5个桶的数据;当y=10,抽取1个桶的数据。

如果总桶数为6,tablesample(bucket 1 out of 2)则表示一共抽取3个桶的数据,从序号为1的桶开始,抽取第1(x)个桶,第3(x+y)个桶和第5(x+y+y)个桶的数据。

排序

order by

Hive SQL中的order by语法类似于SQL语言中的order by语法。会对输出的结果进行全局排序,因此底层使用MapReduce引擎执行的时候,只会有一个reduce task执行。也因此,如果输出的行数太大,会导致需要很长的时间才能完成全局排序。

默认排序顺序为ASC升序,也可以指定为DESC降序。

在Hive 2.1.0和更高版本中,支持在order by子句中为每个列指定null类型结果排序顺序。ASC顺序的默认空排序顺序为NULLS FIRST,而DESC顺序的默认空排序顺序为NULLS LAST。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
---order by
--根据字段进行排序
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths; -- null first

select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths desc; -- null last

--当 hive.mapred.mode 设置为 strict 严格模式时,使用不带 LIMIT 的 ORDER BY 时会引发异常。
select * from t_usa_covid19_p
where count_date = "2021-01-28" and state ="California"
order by deaths desc
limit 3;

cluster by

Hive SQL中的cluster by语法可以指定根据后面的字段将数据分组,每组内再根据这个字段正序排序(不允许指定排序规则),概括起来就是:根据同一个字段,分且排序。

分组规则:hash_func(col_name) % reduce task nums

分为几组取决于reduce task的个数。

1
2
3
4
5
6
7
8
9
10
11
12
--创建学生表
create table student(
sid int,
name string,
age int,
classId int
) row format delimited fields
terminated by '\001';

--手动设置 reduce task 个数,不设置由Hive在编译期间自动决定。
set mapreduce.job.reduces = 2;
select * from student cluster by sid;

cluster by 不要和 order by 一起使用,因为 order by 会将 reduce task 重置为1,如果需要分组后按其他字段排序请使用下面介绍的语句。

distribute by + sort by

如果说cluster by的功能是分且排序(同一个字段),那么distribute by + sort by就相当于把cluster by的功能一分为二:

distribute by负责分,sort by负责分组内排序,并且可以是不同的字段。

distribute by + sort by的字段一样时:

cluster by = distribute by + sort by

1
2
--查询学生表 按班级分组按年龄降序排序
select * from student distribute by classID sort by age desc;

distribute by 与 cluster by 使用的算法都是hash散列。

union 联合查询

union用于将来自多个select语句的结果合并为一个结果集。语法如下:

1
2
select_statement UNION [ALL | DISTINCT] select_statement UNION
[ALL | DISTINCT] select_statement ...

使用union distinct与只使用union效果一样,都会删除重复行。

使用all关键字,不会删除重复行,结果集包括所有select语句的匹配行(包括重复行)。

1.2.0之前的Hive版本仅支持union all,在这种情况下不会消除重复的行。

每个select_statement返回的列的数量和名称必须相同。

1
2
3
4
5
6
--如果要将 ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY 或 LIMIT 子句应用于整个 UNION 结果
--请将 ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY 或 LIMIT 放在最后一个之后。
select sno,sname from student_local
UNION
select sno,sname from student_hdfs
order by sno desc;

Common Table Expressions (CTE)

公用表表达式(CTE)是一个临时结果集,该结果集是从WITH子句中指定的简单查询派生而来的,该查询紧接在SELECTINSERT关键字之前。

CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECTINSERTCREATE TABLE AS SELECTCREATE VIEW AS SELECT语句中使用。

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
--基本用法
with q1 as (select sno,sname,sage from student where sno = 95002)
select * from q1;

-- from 风格
with q1 as (select sno,sname,sage from student where sno = 95002)
from q1
select *;

-- chaining CTEs 链式
with q1 as ( select * from student where sno = 95002),
q2 as ( select sno,sname,sage from q1)
select * from (select sno from q2) a;

-- union 案例
with q1 as (select * from student where sno = 95002),
q2 as (select * from student where sno = 95004)
select * from q1 union all select * from q2;

-- ctas
create table s2 as
with q1 as ( select * from student where sno = 95002)
select * from q1;

-- view
create view v1 as
with q1 as ( select * from student where sno = 95002)
select * from q1;

join 连接查询

Hive作为面向分析的数据仓库软件,为了更好的支持数据分析的功能丰富,也实现了join的语法,整体上来看和RDBMS中的join语法类似,只不过在某些点有自己的特色。需要特别注意。

  • 从Hive 0.13.0开始,支持隐式连接表示法。这允许FROM子句连接以逗号分隔的表列表,而省略JOIN关键字。
  • 从Hive 2.2.0开始,支持ON子句中的复杂表达式,支持非等值连接。

在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键。

在HiveSQL语法中,cross join后面可以跟where子句进行过滤,或者on条件过滤。但是!语句执行期间往往会先计算笛卡尔积返回数据,然后再执行where过滤。

因此,如果两个表太大,执行将会非常非常慢,不建议在笛卡尔积后使用where。

如果每个表在连接子句中使用相同的列,Hive将会把多个表连接为单个MR作业

1
2
3
4
5
6
7
8
9
10
11
SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1)
--由于联接中仅涉及b的key1列,因此被转换为1个MR作业来执行。

SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key2)
--会转换为两个MR作业,因为在第一个连接条件中使用了b中的key1列
--而在第二个连接条件中使用了b中的key2列
--第一个MR作业将a与b联接在一起,然后将结果与c联接到第二个MR作业中。

join时的最后一个表会通过reducer流式传输,并在其中缓冲之前的其他表,因此,将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1)
--由于联接中仅涉及b的key1列,因此被转换为1个MR作业来执行
--并且表a和b连接的结果被缓冲在reducer的内存中
--然后,对于从c中检索的每一行,将使用缓冲的行来计算联接。

SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key2)
--计算涉及两个MR作业。其中的第一个将a与b连接起来
--并缓冲a的值,同时在reducer中流式传输b
--在第二个MR作业中,将缓冲第一个连接的结果,同时将c的值通过reducer流式传输。

更多信息参阅官方文档 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!