hive多个表join_Hive Join详解

Join原理

Hive执行引擎会将HQL“翻译”成为MapReduce任务，如果多张表使用同一列做Join,将被“翻译”成一个MapReduce任务，否则会被“翻译”成多个MapReduce任务

例如：以下将被“翻译”成1个MapReduce任务

SELECT talble1.val,table2.val,table3.val from table1 JOIN table2 ON (table1.key=table2.key1) JOIN table3 ON(table3.key = table2.key1)

以下将被“翻译”成2个MapReduce任务

SElECT table1.val,table2.val,table3.val from table1 JOIN table2 ON(table1.key=table2.key1) JOIN table3 ON(table3.key = table2.key2)；

很好理解，一般来说(map side join除外)Map过程负责分发数据，具体JOIN操作在Reduce完成，因此，如果多表基于不同的列做JOIN，则无法在一轮MapReduce任务中将所有相关数据Shuffle到同一个Reduce

对于多表JOIN，Hive会将前面的表缓存在Reduce内存中，然后后面的表会流式的进入Reduce和Reduce内存中其他的表做JOIN。例如：

SELECT table1.val,table2.val,table3.val from a JOIN b on (table1.key = table2.key1) JOIN c ON(table3.key = table2.key1)

在Reduce中，table1、table2表等待JOIN的数据会放在内存中，这会引发一些问题，如果Reduce个数不足或者table1,table2表数据过大，可能导致Out of Memory”

因此，需要将数据量最大的表放到最后，或者通过“STREAMTABLE”显示指定Reduce流式读入的表。例如：

SELECT STREAMTABLE(table1) table1.val,table2.val,table3.val from table1 JOIN table2 on (table1.key = table2.key1) JOIN table3 ON(table3.key = table2.key1)

此时，table2、table3表数据在Reduce将放在内存中

外部Join

外部JOIN包括LEFT、RIGHT、FULL OUTER JOIN，其目的是针对不匹配的情况做一些控制。

LEFT OUTER JOIN操作如下：

SELECT table1.val,table2.val from table1 LEFT OUTER JOIN table2 ON (table1.key = table2.key)

如果table1.key中找不到对应的table2.key,则输出“table1.val,NULL”

LEFT OUTER JOIN可以用来代替NOT IN操作，NOT IN在Hive0.8才支持

例如：以下是过滤table2.key为NULL的值

SELECT table1.key from table1 LEFT OUTER JOIN table2 ON(table1.key = table2.key) where table2.key1 IS NOT NULL;

注意：外部JOIN与传统型的数据库相似，可参照mysql进行理解

map端join

假如JOIN两张表，其中有一张表特别小(可以放在内存中),可以使用Map-side JOIN。

Map-Side JOIN是在Mapper中做JOIN,原理是将其中一张JOIN表放到每个Mapper任务的内存中，从而不用Reduce任务，在Mapper中就完成JOIN。

Map-SIde JOIN不适合FULL/RIGHT OUTER JOIN。

示例如下：

SELECT MAPJOIN(b) table1.key,table1.value from a join b on table1.key = table2.key;

join中处理null值的语义区别

SQL标准中，任何对NULL的操作(如数值比较，字符串操作等)结果都为NULL.Hive对NULL值处理的逻辑和标准基本一致，除了JOIN时的特殊逻辑

这里的特殊逻辑是指,Hive的JOIN中作为JOIN Key的字段比较，NULL=NULL是有意义的，且返回值为True。例如：

SELECT user.uid,count(user.uid) from user JOIN class ON(class.uid = user.uid) Group by user.uid;

查询中，user表中为空的记录将和class表中uid为空的记录连接，即以下成立

class.uid = user.uid = NULL

如果需要与标准一致的语义，需要改写查询手动过滤NULL值情况，操作如下

SELECT user.uid,count(user.uid) FROM class JOIN user ON(class.uid = user.uid and class.uid IS NOT NULL and user.uid IS NOT NULL) GROUP BY user.uid

实践中，这一语义区别也是经常导致数据倾斜的原因之一