Hive---Join 优化

1、连接顺序优化

多表连接，会转换成多个MR Job，每一个MR Job在Hive中称为JOIN阶段（Stage）。在每一个Stage，按照JOIN顺序中的最后一个表应该尽量是大表，因为JOIN前一阶段生成的数据会存在于Reducer的buffer中，通过stream最后面的表，直接从Reducer的buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是JOIN顺序中，前面表连接的结果的Key，数据量相对较小，内存开销就小），这样，与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。例如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

这个JOIN语句，会生成一个MR Job，在选择JOIN顺序的时候，数据量相比应该是b < c，表a和b基于a.key = b.key1进行连接，得到的结果（基于a和b进行连接的Key）会在Reducer上缓存在buffer中，在与c进行连接时，从buffer中读取Key（a.key=b.key1）来与表c的c.key进行连接。

另外，也可以通过给出一些Hint信息来启发JOIN操作，这指定了将哪个表作为大表，从而得到优化。例如：

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

2、基于条件的LEFT OUTER JOIN优化

左连接时，左表中出现的JOIN字段都保留，右表没有连接上的都为空。对于带WHERE条件的JOIN语句，例如：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

执行顺序是，首先完成2表JOIN，然后再通过WHERE条件进行过滤，这样在JOIN过程中可能会输出大量结果，再对这些结果进行过滤，比较耗时。可以进行优化，将WHERE条件放在ON里面，例如：

SELECT a.val, b.val FROM a LEFT OUTER JOIN bON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

这样，在JOIN的过程中，就对不满足条件的记录进行了预先过滤，可能会有更好的表现。

3、左半连接（LEFT SEMI JOIN）

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。
Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

SQL中的子查询：select a.key,a.value from a where a.key in (select b.key from b)Hive中的左半连接:select a.key, a.val from a left semi join b on (a.key = b.key)

4、Map Side JOIN

当连接的两个表是一个比较小的表和一个特别大的表的时候，我们把比较小的table直接放到内存中去，然后再对比较大的表格进行map操作。join就发生在map操作的时候，每当扫描一个大的table中的数据，就要去去查看小表的数据，哪条与之相符，继而进行连接。这里的join并不会涉及reduce操作。map端join的优势就是在于没有shuffle。
多表连接，如果只有一个表比较大，其他表都很小，则JOIN操作会转换成一个只包含Map的Job，例如：

这里b为小表SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a JOIN b ON a.key = b.key

5、BUCKET Map Side JOIN

我们先看两个表a和b的DDL，表a为：

CREATE TABLE a(key INT, othera STRING)CLUSTERED BY(key) INTO 4 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY '\001'COLLECTION ITEMS TERMINATED BY '\002'MAP KEYS TERMINATED BY '\003'STORED AS SEQUENCEFILE;

表b为：

CREATE TABLE b(key INT, otherb STRING)CLUSTERED BY(key) INTO 32 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY '\001'COLLECTION ITEMS TERMINATED BY '\002'MAP KEYS TERMINATED BY '\003'STORED AS SEQUENCEFILE;

现在要基于a.key和b.key进行JOIN操作，此时JOIN列同时也是BUCKET列，JOIN语句如下：

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key

并且表a有4个BUCKET，表b有32个BUCKET，默认情况下，对于表a的每一个BUCKET，都会去获取表b中的每一个BUCKET来进行JOIN，这回造成一定的开销，因为只有表b中满足JOIN条件的BUCKET才会真正与表a的BUCKET进行连接。
这种默认行为可以进行优化，通过改变默认JOIN行为，只需要设置变量：

set hive.optimize.bucketmapjoin = true

这样，JOIN的过程是，表a的BUCKET 1只会与表b中的BUCKET 1进行JOIN，而不再考虑表b中的其他BUCKET 2~32。
如果上述表具有相同的BUCKET，如都是32个，而且还是排序的，亦即，在表定义中在CLUSTERED BY(key)后面增加如下约束：

SORTED BY(key)

则上述JOIN语句会执行一个Sort-Merge-Bucket (SMB) JOIN，同样需要设置如下参数来改变默认行为，优化JOIN时只遍历相关的BUCKET即可：

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;set hive.optimize.bucketmapjoin = true;set hive.optimize.bucketmapjoin.sortedmerge = true;