hive的JOIN和SQL执行计划解读

测试数据准备1：

echo -e '1\tzhangsan\n2\tlisi\n3\twangwu'>/tmp/join_a.txt
echo -e '1\t30\n2\t29\n4\t21'>/tmp/join_b.txt

beeline -u "jdbc:hive2://127.0.0.1:10000" -n hadoop -p hadoop
use test1;
create table id_name(
id int, 
name string)
row format delimited fields terminated by '\t';
create table id_age(
id int, 
age int)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/join_a.txt' overwrite into table id_name;
load data local inpath '/tmp/join_b.txt' overwrite into table id_age;

JOIN的几种SQL：

-- 内连接
select * from id_name join id_age on id_name.id=id_age.id;

-- 外连接
select * from id_name left  join id_age on id_name.id=id_age.id;
select * from id_name right join id_age on id_name.id=id_age.id;
select * from id_name full  join id_age on id_name.id=id_age.id;

-- 笛卡尔积
select * from id_name cross join id_age;
select * from id_name cross join id_age    on id_name.id=id_age.id;
select * from id_name cross join id_age where id_name.id=id_age.id;
-- 笛卡尔积基础上增加链接条件，其实就是内连接

测试数据准备2：

use test1;
create table emp(
empno int, 
ename string, 
job string, 
mgr int, 
hiredate string, 
sal double, 
comm double, 
deptno int)
row format delimited fields terminated by '\t';
create table dept(
deptno int,
dname  string,
loc    string)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/emp' overwrite into table emp;
load data local inpath '/tmp/dept' overwrite into table dept;

hive的join相关分析：

hive常用的join有两大类：

• common join/reduce join/shuffle join 一般的join
• mapjoin 优化器优化后的join

hive默认使用的join：

• 当 hive.auto.convert.join = true时，优化器默认将common join转化成mapjoin
• 当 hive.auto.convert.join = false时，默认使用 common join

测试SQL：

select e.empno, e.ename, e.deptno, d.dname 
  from emp e join dept d on e.deptno=d.deptno;
-- shuffle：将相同的deptno分到一个reduce上去
-- emp表所需列 ：<deptno,(empno,ename)>
-- dept表所需列：<deptno,(dname)>
-- 正常的操作如以上分析，分别扫表取出emp表和dept表对应的列
-- 然后将列deptno相同的数据分配到一个reduce上去，查出数据

该SQL的common join执行计划解读：

set hive.auto.convert.join = false;
explain
select e.empno, e.ename, e.deptno, d.dname 
  from emp e join dept d on e.deptno=d.deptno;
-- 设置优化器参数为false，使用explain关键字查看执行计划

可以看出 common join 执行了两步，第一步是map+reduce，第二部是展示数据，
第一步中，map操作分别对两表进行扫描，根据deptno分组，查出需要的列数据，传递给reduce，
然后在reduce操作中进行join操作，最终得出结果数据集。

该SQL的mapjoin执行计划解读：

set hive.auto.convert.join = true;
explain
select e.empno, e.ename, e.deptno, d.dname 
  from emp e join dept d on e.deptno=d.deptno;

可以看出，mapjoin比common join多了一步，首先启动了一个本地的Map Reduce作业，读d表，
然后启动了一个非本地的Map Reduce作业，是一个真实的Map操作，读e表，
然后并没有启动真实的Reduce操作，而直接在Map端进行了join操作，最后展示数据。
使用优化器将commmon join 优化成mapjoin，省掉了Reduce操作，效率更高。

两种join的进一步分析：

两表进行common join，需要对两表分别启动一组map作业，将数据根据join的条件进行排序，
经过网络shuffle后传输到同一个reduce作业，然后启动该reduce作业，进行join，然后查出数据。
这中join性能是较差的，因为两表的数据map之后需要经过shuffle进行网络传输。

两表进行mapjoin，首先启动一个本地的MR Local Task，会去读小表（根据表的元数据中的统计信息确定），将小表的数据读入之后生成一个HashTable文件，将该文件存入hadoop的分布式缓存中；
然后启动一个Map任务，将另外一个表的数据读入之后和上一步存入到入hadoop的分布式缓存中的HashTable文件进行join操作，查出数据。
这种join是没有shuffle进行网络传输的，是性能比较高的join方法。

从hive执行的日志分析：

第一个红框看到：启动一个本地任务，Dump the side-table for tag 生成了hashtable，Uploaded 1 File to 将该hashtable上传到了分布式缓存中；
第二个红框看到：number of mappers: 1; number of reducers: 0，即一个map操作，没有reducer操作，map取得数据之后直接和分布式缓存中的hashtable进行join，没有shuffle操作，执行计划比较高效。

先处理一张表，生成hashtable放入分布式缓存，第二张表一遍map一遍和缓存做join，不需要shuffle不需要reduce。

[TOC]