分享

HIVE中Join的专题---Join详解

pig2 2014-4-15 18:53:18 发表于 介绍解说 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 6 75509
1.什么是等值连接?
2.hive转换多表join时,如果每个表在join字句中,使用的都是同一个列,该如何处理?
3.LEFT,RIGHT,FULL OUTER连接的作用是什么?
4.LEFT或RIGHT join是连接从左边还有右边?




Hive表连接的语法支持如下:
Sql代码  
join_table:  
    table_reference JOIN table_factor [join_condition]  
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition  
  | table_reference LEFT SEMI JOIN table_reference join_condition  
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)  
  
table_reference:  
    table_factor  
  | join_table  
  
table_factor:  
    tbl_name [alias]  
  | table_subquery alias  
  | ( table_references )  
  
join_condition:  
    ON equality_expression ( AND equality_expression )*  
  
equality_expression:  
    expression = expression  
hive只支持等连接,外连接,左半连接。hive不支持非相等的join条件(通过其他方式实现,如left outer join),因为它很难在map/reducejob实现这样的条件。而且,hive可以join两个以上的表。


例子
写join查询时,有几个典型的点要考虑,如下:


等连接
只有等连接才允许
Sql代码  
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)  
这两个是合法的连接
Sql代码  
SELECT a.* FROM a JOIN b ON (a.id <> b.id)  
这个是不允许的。


多表连接
同个查询,可以join两个以上的表
Sql代码  
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代码  
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代码  
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。
在join的每个map/reduce阶段,序列中的最后一个表,当其他被缓存时,它会流到reducers。所以,reducers需要缓存join关键字的特定值组成的行,通过组织最大的表出现在序列的最后,有助于减少reducers的内存。
Sql代码  
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任务做join。a和b的key对应的特定值组成的行,会缓存在reducers的内存。然后reducers接受c的每一行,和缓存的每一行做join计算。
类似
Sql代码  
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任务在join计算被调用。第一个是a和b做join,然后reducers缓存a的值,另一边,从流接收b的值。第二个阶段,reducers缓存第一个join的结果,另一边从流接收c的值。
在join的每个map/reduce阶段,通过关键字,可以指定哪个表从流接收。
Sql代码  
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)  
三个表的连接,会转换为一个map/reduce任务,reducer会把b和c的key的特定值缓存在内存里,然后从流接收a的每一行,和缓存的行做join。


join的结果
LEFT,RIGHT,FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如,这个查询:
Sql代码  
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)  
将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配,输出的行将是a.val,NULL。如果b的行没有和a.key匹配上,将被抛弃。语法"FROM a LEFT OUTER JOIN b"必须写在一行,为了理解它如何工作——这个查询,a是b的左边,a的所有行会被保持;RIGHT OUTER JOIN将保持b的所有行, FULLOUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。


join的过滤
Joins发生在where字句前,所以,如果要限制join的输出,需要写在where字句,否则写在JOIN字句。现在讨论的一个混乱的大点,就是分区表
Sql代码  
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'  
将会连接a和b,产生a.val和b.val的列表。WHERE字句,也可以引用join的输出列,然后过滤他们。
但是,无论何时JOIN的行找到a的key,但是找不到b的key时,b的所有列会置成NULL,包括ds列。这就是说,将过滤join输出的所有行,包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。
也就是说,如果你在WHERE字句引用b的任何列,LEFTOUTER的部分join结果是不相关的。所以,当外连接时,使用这个语句
Sql代码  
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07'  
join的输出会预先过滤,然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑。


join的顺序
join是不可替换的,连接是从左到右,不管是LEFT或RIGHT join。
Sql代码  
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 jOINb"步骤,被丢弃,因为它不在b中。结果没有a.key,所以当它和c做LEFT OUTER JOIN,c.val也无法做到,因为没有c.key匹配a.key(因为a的行都被移除了)。类似的,RIGHTOUTER JOIN(替换为LEFT),我们最终会更怪的效果,NULL, NULL, NULL, c.val。因为尽管指定了join key是a.key=c.key,我们已经在第一个JOIN丢弃了不匹配的a的所有行。
为了达到更直观的效果,相反,我们应该从
Sql代码  
FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key).  
LEFT SEMI JOIN实现了相关的IN / EXISTS的子查询语义的有效途径。由于Hive目前不支持IN / EXISTS的子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMIJOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
Sql代码  
SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);  
可以重写为
Sql代码  
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)  


mapjoin
但如果所有被连接的表是小表,join可以被转换为只有一个map任务。查询是
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a join b ona.key = b.key
不需要reducer。对于每一个mapper,A和B已经被完全读出。限制是a FULL/RIGHTOUTER JOIN b不能使用。
如果表在join的列已经分桶了,其中一张表的桶的数量,是另一个表的桶的数量的整倍,那么两者可以做桶的连接。如果A有4个桶,表B有4个桶,下面的连接:
Sql代码  
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key  
只能在mapper工作。为了为A的每个mapper完整抽取B。对于上面的查询,mapper处理A的桶1,只会抽取B的桶1,这不是默认行为,要使用以下参数:
Sql代码  
set hive.optimize.bucketmapjoin = true;  
如果表在join的列经过排序,分桶,而且他们有相同数量的桶,可以使用排序-合并 join。每个mapper,相关的桶会做连接。如果A和B有4个桶,
Sql代码  
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM A a join B b on a.key = b.key  
只能在mapper使用。使用A的桶的mapper,也会遍历B相关的桶。这个不是默认行为,需要配置以下参数:
Sql代码  
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;  
set hive.optimize.bucketmapjoin = true;  

set hive.optimize.bucketmapjoin.sortedmerge = true;

已有(6)人评论

跳转到指定楼层
june_fu 发表于 2015-3-8 21:19:15
有几个问题不太明白,请教下:
1、“通过组织最大的表出现在序列的最后,有助于减少reducers的内存。”
放在最后意思是说:SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  和下面一句
SELECT /*+ STREAMTABLE(c) */ a.val, b.val, c.val FROM a JOIN c ON (a.key = c.key1) JOIN b ON (b.key = c.key1)  是等价的,我理解没错吧?
2、“LEFT SEMI JOIN实现了相关的IN / EXISTS的子查询语义的有效途径“ 这个能否再详细说明下呢?
你举得例子用内连接也是可以的嘛。
3、mapjoin是不是就算涉及优化的问题了?用mapjoin的话就不会产生reducer?
谢谢版主先。
回复

使用道具 举报

june_fu 发表于 2015-3-8 22:25:48
回复

使用道具 举报

benhui 发表于 2015-7-22 18:07:59
总结得很好,感谢楼主的分享
回复

使用道具 举报

benhui 发表于 2015-7-22 18:10:32
感谢楼主,总结得很好
回复

使用道具 举报

地球仪 发表于 2016-9-8 10:09:05
不错,分析的很到位!
回复

使用道具 举报

sunbrillant 发表于 2017-8-16 22:38:22
总结的很号,但是还有几个地方不懂,问问楼主
mapJoin后分桶,怎么判断两桶是相关的???
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /2 下一条