Hive中的join

join

等连接

只有等连接才允许。

select a.* from a join b on a.id = b.id
select a.* from a join b on (a.id = b.id and a.department = b.department)

不等连接是不允许的。

select a.* from a join b on a.id <> b.id

在使用join写查询的时候有一个原则：应该将条目少的表或者子查询放在join操作符的左边。原因是在join的操做的reduce阶段，位于join操作符左边的表的内容会被加载进入内存，将条目少的表放在左边，可以有效的减少发生OOM错误的几率。

join的任务转化

hive执行引擎会将HQL“翻译”成为map-reduce任务，如果多张表使用同一列做join则将被翻译成一个reduce，否则将被翻译成多个map-reduce任务。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)将被翻译成1个map-reduce任务。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)将被翻译成2个map-reduce任务。

这个很好理解，一般来说（map side join除外，后面会介绍），map过程负责分发数据，具体的join操作在reduce完成，因此，如果多表基于不同的列做join，则无法在一轮map-reduce任务中将所有相关数据shuffle到同一个reducer。对于多表join，hive会将前面的表缓存在reducer的内存中，然后后面的表会流式的进入reducer和reducer内存中其它的表做join。

left semi join

hive之前不支持in/exists，left semi join是in/exists更有效率的实现。

SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

可以使用如下语句代替：SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

join的顺序

join是不可替换的，连接是从左到右，不管是LEFT或RIGHT join。

SELECT a.val1, a.val2, b.val, c.val  
FROM a  
JOIN b ON (a.key = b.key)  
LEFT OUTER JOIN c ON (a.key = c.key) 

首先，连接a和b，扔掉a和b中没有匹配的key的行。结果表再连接c。这提供了直观的结果，如果有一个键都存在于A和C，但不是B：完整行(包括 a.val1,a.val2,a.key)会在"a jOIN b"步骤，被丢弃，因为它不在b中。结果没有a.key，所以当它和c做LEFT OUTER JOIN,c.val也无法做到，因为没有c.key匹配a.key(因为a的行都被移除了)。类似的，RIGHT OUTER JOIN(替换为LEFT),我们最终会更怪的效果,NULL, NULL, NULL, c.val。因为尽管指定了join key是a.key=c.key，我们已经在第一个JOIN丢弃了不匹配的a的所有行。

reducer个数

程序中，个数的设定极大影响执行效率，这使得怎样决定个数成为一个关键问题。遗憾的是Hive的估计机制很弱，不指定reducer个数的情况下，Hive会猜测确定一个reducer个数，基于以下两个设定：

1. hive.exec.reducers.bytes.per.reducer（默认为1000^3）

2. hive.exec.reducers.max（默认为999）

计算reducer数的公式很简单：N=min(参数2，总输入数据量/参数1)。

通常情况下，有必要手动指定reducer个数。考虑到map阶段的输出数据量通常会比输入有大幅减少，因此即使不设定reducer个数，重设参数2还是必要的。依据Hadoop的经验，可以将参数2设定为0.95*(集群中TaskTracker个数)。

MapJoin

假如join两张表，其中有一张表特别小(可以放到内存中)，那么可以使用Map-side join。

Map side join是在mapper中做join，原理是将其中一张条目数小的join表放到每个mapper任务的内存中，从而不用reducer任务，在mapper中就完成join。

示例：SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key。

不需要reducer。

resource:http://itindex.net/detail/48520-hive-join