hive中的join

 hive只支持等连接，外连接，左半连接。hive不支持非相等的join条件（通过其他方式实现，如left outer join），因为它很难在map/reduce job实现这样的条件。而且，hive可以join两个以上的表。

 

同个查询，可以join两个以上的表

Sql代码  

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

 

join的缓存和任务转换

 

hive转换多表join时，如果每个表在join字句中，使用的都是同一个列，只会转换为一个单独的map/reduce。

Sql代码  

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

这个会转换为单独的map/reduce任务，只有b表的key1列在join被调用。

 

另一方面

Sql代码  

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

被转换为两个map/reduce任务，因为b的key1列在第一个join条件使用，而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c。

 

 

表连接顺序优化

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

1	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操作，这指定了将哪个表作为大表，从而得到优化。例如：

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

上述JOIN语句中，a表被视为大表，则首先会对表b和c进行JOIN，然后再将得到的结果与表a进行JOIN。

 

 

 

基于条件的LEFT OUTER JOIN优化

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

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

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

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

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

 

Map SideJOIN

Map Side JOIN优化的出发点是，Map任务输出后，不需要将数据拷贝到Reducer节点，降低的数据在网络节点之间传输的开销。
多表连接，如果只有一个表比较大，其他表都很小，则JOIN操作会转换成一个只包含Map的Job，例如：

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

 

 

原理是将其中一张join表放到每个mapper任务的内存中，从而不用reducer任务，在mapper中就完成join。Map side join不适合FULL/RIGHT OUTER JOIN，理由大家思考下。
示例：
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b ona.key = b.key