一起学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 '\n';

dept部门表：

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

薪水表:

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

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 左半开连接