Hive--join

join分类

[inner] join：内连接，两边都有，结果才有

select a.empno
	,a.ename
	,a.deptno
	,b.deptno
	,b.dname 
from tb_emp a 
join tb_dept b 
	on a.deptno = b.deptno;

left [outer] join ：左外连接，左表有，结果就有

select a.empno
	,a.ename
	,a.deptno
	,b.deptno
	,b.dname 
from tb_emp a 
left join tb_dept b 
	on a.deptno = b.deptno;

left semi join：左半连接，IN/EXISTS 子查询的一种更高效的实现

• 右表关联key有重复也不会数据膨胀
• 右表字段是拿不出来的
• 详细可参考这篇

select a.empno
	,a.ename
	,a.deptno
	,b.deptno
	-- ,b.dname 
from tb_emp a 
left semi join tb_dept b 
on a.deptno = b.deptno;

right [outer] join ：右外连接，右表有，结果就有

select a.empno
	,a.ename
	,a.deptno
	,b.deptno
	,b.dname 
from tb_emp a 
right join tb_dept b 
on a.deptno = b.deptno;

full join：全连接，两张表任意一边有，结果就有

select a.empno
	,a.ename
	,a.deptno
	,b.deptno
	,b.dname 
from tb_emp a 
full join tb_dept b 
on a.deptno = b.deptno;

join实现

Map Join：适合于小表join大表

• 本质：将小表放入分布式缓存，与大表的每个部分进行Join
• 好处：在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输
• 开启及设置MapJoin的阈值会自动判断是否走Map Join

set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=512000000;

使用场景–小表join大表

如果不使用MAPJION，可能会产生数据倾斜问题。
MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map时进行了join操作，省去了reduce运行的效率会高很多。

select f.a
	,f.b 
from A t 
join B f  
	on ( f.a=t.a and f.ftime=20110802)  

该语句中B表有30亿行记录，A表只有100行记录，而且B表中数据倾斜特别严重，有一个key上有15亿行记录，在运行过程中特别的慢，而且在reduece的过程中遇到执行时间过长或者内存不够的问题。

如果开启了mapjoin，在map时进行了join操作，省去了reduce，就不会由于数据倾斜导致某个reduce上落数据太多而失败。

使用场景–不等值连接

如果将不等条件写在where中，那么mapreduce过程中会进行笛卡尔积，运行效率特别低，如果使用mapjoin操作，在map的过程中就完成了不等值的join操作，效率会高很多。

Bucket-MapJoin

作用

两个表join的时候，小表不足以放到内存中，但是又想用map side join这个时候就要用到bucket Map join。

原理

两个join表在join key上都做hash bucket，并且把你打算复制的那个（相对）小表的bucket数设置为大表的倍数。这样数据就会按照key join，做hash bucket。小表依然复制到所有节点，Map join的时候，小表的每一组bucket加载成hashtable，与对应的一个大表bucket做局部join，这样每次只需要加载部分hashtable就可以了。

条件

• set hive.optimize.bucketmapjoin = true;
• 一个表的bucket数是另一个表bucket数的整数倍
• bucket列 == join列
• 必须是应用在map join的场景中

tips:如果表不是bucket的，则只是做普通join。

Reduce Join：适合于大表join大表

• Hive中默认不走MapJoin，就走ReduceJoin
• 必须通过shuffle将两份数据进行全局的分组实现关联
• 比较的次数类似于笛卡尔积
  • A表：1亿条
  • B表：1亿条
  • reducejoin时：A表的每一条都需要与B表的每一条进行比较，然后关联。比较过程类似于笛卡尔积

SMB Join：适合于大表join大表

全称Sort Merge Bucket Join
SMB Join基于bucket-mapjoin的有序bucket，可实现在map端完成join操作，可以有效地减少或避免shuffle的数据量。

• 要求两张表：都必须为桶表
• B表的桶的个数必须为A表桶的个数的倍数

bucket mapjoin	SMB join
set hive.optimize.bucketmapjoin = true;	set hive.optimize.bucketmapjoin = true;
	set hive.auto.convert.sortmerge.join=true;
	set hive.optimize.bucketmapjoin.sortedmerge = true;
	set hive.auto.convert.sortmerge.join.noconditionaltask=true;
一个表的bucket数是另一个表bucket数的整数倍	小表的bucket数=大表bucket数
bucket列 == join列	Bucket 列 == Join 列 == sort 列
必须是应用在map join的场景中	必须是应用在bucket mapjoin 的场景中

hive并不检查两个join的表是否已经做好bucket且sorted，需要用户自己去保证join的表数据sorted，否则可能数据不正确。

有两个办法：

1）hive.enforce.sorting 设置为 true。开启强制排序时，插数据到表中会进行强制排序，默认false。

2）插入数据时通过在sql中用distributed c1 sort by c1 或者 cluster by c1

另外，表创建时必须是CLUSTERED且SORTED，如下：

create table test_smb_2(
	mid string,
	age_id string)
CLUSTERED BY(mid) 
SORTED BY(mid) INTO 500 BUCKETS;

综上，涉及到分桶表操作的齐全配置为：

--写入数据强制分桶

set hive.enforce.bucketing=true;

--写入数据强制排序

set hive.enforce.sorting=true;

--开启bucketmapjoin

set hive.optimize.bucketmapjoin = true;

--开启SMB Join

set hive.auto.convert.sortmerge.join=true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

开启MapJoin的配置（hive.auto.convert.join和hive.auto.convert.join.noconditionaltask.size），还有限制对桶表进行load操作（hive.strict.checks.bucketing）可以直接设置在hive的配置项中，无需在sql中声明。

自动尝试SMB联接（hive.optimize.bucketmapjoin.sortedmerge）也可以在设置中进行提前配置。

join的条数范围

• 假如a表800条数据，b表1000条数据，那么join的时候条数范围是多少？
  • 800～800*1000
    • 800的话不难理解
    • 800*1000怎么理解呢
      • 假如极端情况，两表关联id，但是就一个id值，产生笛卡尔积

join的mr流程

普通join

SELECT
a.id,a.dept,b.age
FROM a join b
ON (a.id = b.id);

mapjoin

• 首先是Task A，它是一个Local Task（在客户端本地执行的Task），负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache中，该HashTable的数据结构可以抽象为：

key	value
1	26
2	34

• 执行Local Task的信息:
  
• 接下来是Task B，该任务是一个没有Reduce的MR，启动MapTasks扫描大表a,在Map阶段，根据 a 的每一条记录去和DistributeCache中 b 表对应的 HashTable 关联，并直接输出结果。
• 由于 MapJoin 没有Reduce，所以由 Map直接输出结果文件，有多少个Map Task，就有多少个结果文件。

参考

1.http://www.openkb.info/2014/11/understanding-hive-joins-in-explain.html
2.https://cwiki.apache.org/confluence/download/attachments/27362054/Hive+Summit+2011-join.pdf
3.https://stackoverflow.com/questions/20199077/hive-efficient-join-of-two-tables