在之前的笔记中,介绍了Hive的基本架构,以及常见的SQL操作如何转化为MapReduce任务。
本文介绍Hive中的一些值得留意的SQL,如mapjoin、left semi join、不同的排序等,了解这些SQL的原理和使用,在适合的场景中使用能大大提高查询效率。
1. 连接
Hive支持的连接操作是等值连接,非等值连接由于难以转化为MapReduce任务暂时不被Hive支持。
对于连续的连接操作,Hive中有几点特性——
- join on使用的key有几组就会被转化为几个MR任务
- 如连续的连接操作,但使用相同的key来连接,则只会被转化为1个MR任务
- reducer会缓存join序列中除了最后一个表的所有表的记录,然后每次取到最后一个表的一条记录就计算结果,因此需要把最大的表放在最后,减少内存使用
左连接、右连接等外连接操作Hive也同样支持,这里再介绍下Hive中的left semi join和mapjoin。
1.1 left semi join
Hive不支持 IN/EXISTS 子查询,左半连接是Hive对于 IN/EXISTS 子查询的一种更高效的实现。在下面的博客中有具体例子的介绍。
hive 的 left semi join 讲解blog.csdn.net
IN/EXISTS 子查询的使用场景如下:
SELECT A.value1, A.value2
FROM A
WHERE A.value1 IN (SELECT B.value1
FROM B);
在Hive中,使用如下SQL更高效地实现:
SELECT A.value1, A.value2
FROM A LEFT SEMI JOIN B ON A.value1=B.value1;
对于左半连接,需要注意:
- 左半连接只传递右表用于比较的key,因此最后的结果只会有左表的数据
- 右表只能在on子句中设置过滤条件,在where等子句中不能过滤,原因与前面相同
- 遇到右表重复记录,左表会跳过,而内连接会一直遍历,因此在右表有重复记录时,左半连接仅生成一条记录,与IN相同
1.2 mapjoin
在Hive中,表之间的连接操作通常会转化为MR任务,效率较低。
对于某些特殊的情况,即小表与大表的连接,这种情况下Hive提供了mapjoin功能,通过将连接操作全部在Map任务中完成,大大提高效率——
- 没有Reduce任务,避免产生数据倾斜
- 没有Map、Reduce任务中间的shuffle操作,减少网络传输
Mapjoin的实现原理如下:
- 分布式缓存中,每个节点都有小表的完整副本
- 然后启动MR任务处理大表,在map阶段,依据每一条记录去与缓存中小表对应的hashtable连接得到结果
对于mapjoin的使用,需要注意:
- 小表必须是从表,即连接时的右表(右半连接则是左表)
- 关于小表的定义,默认阈值是25M,可以通过
hive.mapjoin.smalltable.filesize
参数来修改大小 - Hive默认会自动将可以转化为mapjoin的任务进行转化,当然也可以按照下列语法显式指定使用
SELECT /*+MAPJOIN(B)*/ A.value1, A.value2, B.value2
FROM A JOIN B ON A.value1=B.value1;
2. 排序
Hive中的排序通常涉及到Order By、Sort By、Distribute By、Cluster By,各自的意义不相同,这里简单介绍。
2.1 Order By
Hive的Order By与其他的SQL一样,对所有数据进行排序。
Hive通常用于大表的处理,而Order by的全局排序使得转化的MR任务中只有一个Reduce任务,效率低。
在Hive的严格模式(set hive.mapred.mode=strict
)下,执行Order by必须加Limit子句。
2.2 Sort By 和 Distribute By
针对Order By的缺点,Hive提供了部分排序的两个功能,Sort By 和 Distribute By。
Hive的Sort by是对每个Reduce任务中的数据进行排序,是部分的有序。
Hive的Distribute by是指定Shuffle阶段按照某个key把数据分配到Reduce任务中,且相同key的数据保证在同一个Reduce任务中。
Sort By 和 Distribute By可以结合在某些场景下使用,如按照日期、类型等进行Distribute By,再使用Sort By按照期望的顺序排序。
2.3 Cluster By
Cluster By 相当于 Sort By 和 Distribute By 的结合。当 Sort By 和 Distribute By 指定的列相同时,可以直接使用 Cluster By 代替,如下两种表达相同。
1. SELECT * from A CLUSTER BY A.value1;
2. SELECT * from A DISTRIBUTE BY A.value1 SORT BY A.value1;
更详细的内容可以参考下面的笔记,里面举了一些例子。
hive中order by,sort by,distribute by,cluster by作用和用法blog.csdn.net
Reference
- 《Hive用户指南》
hive 的 left semi join 讲解blog.csdn.net
Hive调优策略--Fetch抓取 & 表的各种优化策略(mapjoin原理)blog.csdn.net
hive显式使用mapjoin - 大数据-大道至简 - 博客园www.cnblogs.com hive中order by,sort by,distribute by,cluster by作用和用法blog.csdn.net