Hive 的join

直接就总结了

 

1. join 中不能包含不等的操作( 大于，小于，不等)

 

select * from a 
inner join b
on a.a>b.a

2.Join 中也不能有 OR 这种谓词

 

3.小表在前，大表在后

4.但是也可以颠倒，但是要用 STREAMTABLE来指定那个是大表

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)

5.join比where的优先级高，

 

-- post-filtered
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'


-- pre-filtered
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')

  

 

5.hive 会把多表join中用到相同key的SQL合并成一个MR

 

--会合并成一个MR
SELECT a.val, b.val, c.val 
FROM a JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key1)

--不会合并
SELECT a.val, b.val, c.val 
FROM a JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key2)
--结合第四点，a是大表，所以会把b,c放进reducers的内存中，然后和 a 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)

 

6.NOT IN，IN 这种语句hive 中不能执行，改用 LEFT SEMI JOIN

 

---不合法的
SELECT a.key, a.value
FROM a
WHERE a.key in
 (SELECT b.key
  FROM B);


--应该这么写
--但是b的列不能出现在select 中
SELECT a.key, a.val
FROM a 
LEFT SEMI JOIN b on (a.key = b.key)

7.很小的表可以放进内存，就用MapJoin 但是会有很多的限制.不是加了hint 就能用的

• Mapjoin 的SQL 只有map 没有reduce 和 STREAMTABLE 是有本质区别的

--MapJoin 只有map端 所以所有的reduce端做的操作都是不能做的
--如 Group By/Join/Sort By/Cluster By/Distribute By

--摘录官方blogs
--The following is not supported.
--Union Followed by a MapJoin
--Lateral View Followed by a MapJoin
--Reduce Sink (Group By/Join/Sort By/Cluster By/Distribute By) Followed by MapJoin
--MapJoin Followed by Union
--MapJoin Followed by Join
--MapJoin Followed by MapJoin

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

------------------------------------------------------------分割线
-- /*+ MAPJOIN(b) */  指定的是小表 （b） 
-- /*+ STREAMTABLE(a) */ 指定的是大表 （a）

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

•  如果不使用hint在0.7版本以后，可以使用  hive.auto.convert.join 属性

--0.11默认是false,0.12默认变成了true
set  hive.auto.convert.join =ture;
--默认是25m 单位是字节
set hive.,mapjoin.smalltable.filesize=25000000

•  在多个mapjoin上使用不同的key

select /*+MAPJOIN(smallTableTwo)*/ 
idOne, idTwo, value 
FROM
  ( select /*+MAPJOIN(smallTableOne)*/ idOne, idTwo, value FROM
    bigTable JOIN smallTableOne on (bigTable.idOne = smallTableOne.idOne)          )firstjoin                                                            
  JOIN                                                                 
  smallTableTwo on (firstjoin.idTwo = smallTableTwo.idTwo)

--上面的SQL不能变成一个map job
--如果不用Hint,这个SQL 会被当做2个map-only的job执行
--如果用户事先知道，输入是足够小，适合在内存中，以下配置参数用来确保查询在单个map-reduce job中执行
--hive.auto.convert.join.noconditionaltask 就是个开关
--hive.auto.convert.join.noconditionaltask.size 表的大小小于这个size直接变成mapjoin，默认是10m

•  Mapjoin的步骤

 

Local work:

--read records via standard table scan (including filters and projections) from source on local machine

--build hashtable in memory

--write hashtable to local disk

--upload hashtable to dfs

--add hashtable to distributed cache

Map task

--read hashtable from local disk (distributed cache) into memory

--match records' keys against hashtable

--combine matches and write to output

No reduce task

 

MapJoin总结下

a)一个mapjoin只能处理一次一个key，它可以执行的多表连接，但只有当所有的表都加入了相同的key。（典型的星型连接不属于这一类，但是上面不支持的例子就是属于这一类）

b)就算加了hint也未必，真的是用mapjoin

c)一连串的mapjoins不会合并成一个单一的map job，除非查询写成一个级联的mapjoin（mapjoin(table, subquery(mapjoin(table, subquery....).自动转换后的也不会变成一个单一的map job。还是上面这个例子，打开了所有的开关，会把SQL 变成2个单一的map-only job 来跑

d)mapjoin 中用到的哈希表，每个子QUERY运行都会生成，先下载，再分发给map