一起学Hive——总结各种Join连接的用法

Hive支持常用的SQL join语句，例如内连接、左外连接、右外连接以及HiVe独有的map端连接。其中map端连接是用于优化Hive连接查询的一个重要技巧。

在介绍各种连接之前，先准备好表和数据。

employee员工表：

create table if not exists employee(
user_id int,
username string,
dept_id int)
row format delimited fields terminated by ' '  
lines terminated by '
';

dept部门表：

create table if not exists dept(
dept_id int,
dept_name string
)
row format delimited fields terminated by ' '  
lines terminated by '
';

薪水表:

create table if not exists salary(
userid int,
dept_id int,
salarys double
)
row format delimited fields terminated by ' '  
lines terminated by '
';

employee员工表的数据如下：

1 zhangsas 1
2 lisi 2
3 wangwu 3
4 tom 1
5 lily 2
6 amy 3
7 lilei 1
8 hanmeimei 2
9 poly 3

dept部门表的数据如下：

1 Technical
2 sales
3 HR
4 marketing

薪水表的数据如下：

1 1 20000
2 2 16000
3 3 20000
4 1 50000
5 2 18900
6 3 12098
7 1 21900

INNER JOIN内连接

多张表进行内连接操作时，只有所有表中与on条件中相匹配的数据才会显示。例如下面的SQL实现了每个员工所在的部门，employee表和dept表连接，on条件是dept_id，只有dept_id一样的数据才会匹配并显示出来。

select e.username,e.dept_id,d.dept_name,d.dept_id from employee e join dept d on e.dept_id = d.dept_id 

结果为：
zhangsas        Technical
lisi    sales
wangwu  HR
tom     Technical
lily    sales
amy     HR
lilei   Technical
hanmeimei       sales
poly    HR

Hive SQL和标准SQL还有些差别，Hive SQL值支持等值连接，以及在on子句中只支持and，不支持or。下面的SQL在Hive中是无法运行的：

select e.username,e.dept_id,d.dept_name,d.dept_id from employee e join dept d on e.dept_id <= d.dept_id 

select e.username,e.dept_id,d.dept_name,d.dept_id from employee e join dept d on e.dept_id = d.dept_id or d.dept_id =1 ;

我们可以对两张以上的表进行连接操作，下面的SQL语句查询员工的名字、部门名字及其的薪水：

select e.username,d.dept_name,s.salarys from employee e join dept d on e.dept_id = d.dept_id join salary s on e.user_id = s.userid 

一般情况下，一个join连接会生成一个MapReduce job任务，如果join连接超过2张表时，Hive会从左到右的顺序对表进行关联操作，上面的SQL，先启动一个MapReduce job任务对表employee和dept进行连接操作，然后在启动第二个MapReduce job对第一个MapReduce job输出的结果和表salary进行连接操作。这和标准SQL刚好相反，标准SQL是从右向左的顺序进行Join操作的。因此在Hive SQL中，我们都是把小表写在左边，这样可以提高执行效率。

Hive支持使用/* +STREAMTALBE /语法指定哪张表是大表，例如下面的SQL，指定dept为大表。如果不使用/+STREAMTALBE*/语法，Hive认为最右边的表是大表。

select /*+STREAMTABlE(d)*/ e.username,e.dept_id,d.dept_name,d.dept_id from employee e join dept d on e.dept_id = d.dept_id 

一般情况下有多少张表进行join连接操作，就会启动多少个MapReduce任务，但是如果on条件的连接键都是一样的，那么则只会启动一个MapReduce任务。

LEFT OUTER JOIN 左外连接

左外连接，和标准SQL一样，以左边表为基准，如果右边表和on条件匹配的数据则显示出来，否则显示NULL：

select e.user_id,e.username,s.salarys from employee e left outer join salary s on e.user_id = s.userid;

结果为：
1       zhangsas        20000.0
2       lisi    16000.0
3       wangwu  20000.0
4       tom     50000.0
5       lily    18900.0
6       amy     12098.0
7       lilei   21900.0
8       hanmeimei       NULL
9       poly    NULL

从上面的结果可以看到左外连接这种连接方式，左边employee员工表的记录都全部显示，右边salary薪水表符合on条件的数据也显示出来，不符合条件的数据显示NULL。

RIGHT OUTER JOIN 右外连接

右外连接和左外连接正好相反，右外连接以右边的表为基准，如果左边表和on条件匹配的数据则显示出现，不匹配的数据显示NULL。

Hive是处理大数据的组件，经常用于处理几百G设置以T为单位的数据，因此在编写SQL时尽量用where条件过滤掉不符合条件的数据。但是对于左外连接和右外连接，where条件是在on条件执行之后才会执行，因此为了优化Hive SQL执行的效率，在需要使用外链接的场景，尽量使用子查询，然后在子查询中使用where条件过滤掉不符合条件的数据：

select e1.user_id,e1.username,s.salarys from (select e.* from employee e where e.user_id < 8) e1 left outer join salary s on e1.user_id = s.userid;

上面的SQL就是通过子查询将user_id>=8的数据给过滤掉。

FULL OUTER JOIN 全外连接

全外连接返回所有表中满足where条件的数据，不满足条件的数据以NULL代替：

select e.user_id,e.username,s.salarys from employee e full outer join salary s on e.user_id = s.userid where e.user_id > 0;

结果为：
1       zhangsas        20000.0
2       lisi    16000.0
3       wangwu  20000.0
4       tom     50000.0
5       lily    18900.0
6       amy     12098.0
7       lilei   21900.0
8       hanmeimei       NULL
9       poly    NULL

全外连接和左外连接的结果是一致的。

LEFT SEMI JOIN 左半开连接

顾名思义，只查询出满足左边表的数据：

select e.* from employee e LEFT SEMI-JOIN salary s on e.user_id=s.userid;

结果：
1       zhangsas        1
2       lisi    2
3       wangwu  3
4       tom     1
5       lily    2
6       amy     3
7       lilei   1

左半开连接时内连接的优化，当左边表的一条数据，在右边表中存在时，Hive就停止扫描。因此效率比join高，但是左半开连接的select和where关键字后面只能出现左边表的字段，不能出现右边表的字段。

Hive不支持右半开连接。

笛卡尔JOIN

笛卡尔积连接的结果是将左边表的数据乘以右边表的数据：

select e.user_id,e.username,s.salarys from employee e join salary s;

上面SQL执行的结果就是employee表的记录乘以salary表的记录。

map-side JOIN连接

map端连接，按道理来说不算是Hive连接的一种，它是对Hive SQL的优化，Hive是将SQL转化为MpaReduce job，因此Map端连接对应的就是Hadoop Join连接中的Map端连接，将小表加载到内存中，以提高hive sql的执行速度。
可以通过下面两种方式使用Hive SQL map 端join连接：

1. 使用/* + MAPJOIN*/标记：

select /*+ MAPJOIN*(d)*/ e.username,e.dept_id,d.dept_name,d.dept_id from employee e join dept d on e.dept_id = d.dept_id; 

2. 设置hive.auto,convert.JOIN的值为true。

总结：
1、本文总结了Hive SQL中各种join连接的用法和使用场景。
2、使用inner join内连接时如何减少MapReduce的个数。
3、如何在Hive SQL中使用Map端连接。
4、如何使用嵌套查询优化SQL。