《Hive用户指南》- Hive的连接join与排序

《Hive用户指南》- Hive的连接join与排序

在之前的笔记中，介绍了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 讲解 有具体例子的介绍。

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的实现原理如下：

• 将小表的数据，先转化成hashtable格式，再上传到分布式缓存中，每个节点都有小表的完整副本

• 然后启动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作用和用法，里面举了一些例子。

Reference

1. 《Hive用户指南》

2. hive 的 left semi join 讲解

3. Hive调优策略——Fetch抓取 & 表的各种优化策略（mapjoin原理）

4. hive显式使用mapjoin

5. hive中order by,sort by,distribute by,cluster by作用和用法