join
- join分类
- [inner] join：内连接，两边都有，结果才有
- left [outer] join ：左外连接，左表有，结果就有
- left semi join：左半连接，IN/EXISTS 子查询的一种更高效的实现
- right [outer] join ：右外连接，右表有，结果就有
- full join：全连接，两张表任意一边有，结果就有
- join实现
- ==Map Join==：适合于小表join大表
- 使用场景--小表join大表
- 使用场景--不等值连接
- ==Bucket-MapJoin==
- 作用
- 原理
- 条件
- ==Reduce Join==：适合于大表join大表
- ==SMB Join==：适合于大表join大表
- join的条数范围
- join的mr流程
- 普通join
- mapjoin
- 参考
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，就有多少个结果文件。
