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

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

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。



    用心写好每篇技术文章
-----------------------------------------------------------------

    关注本人的微信公众号获取更多关于大数据机器学习方面的技术文章。