轻松玩转hive中各种join之间的关系以及使用

      hive编程是整个数据仓库操作的核心,而各种业务之间的join是hive的核心,所以熟练明白滴掌握hive中的各种join是数据仓库开发工程师必备的技能。   

    hive中的join只支持等值join,也就是说join on中的on里面表之间连接条件只能是=,不能是<,>等符号。此外,on中的等值连接之间只能是and,不能是or. (如果在on 里添加非表之间的条件可以是非等号,下面有演示)

     为了演示各种join之间的关系以及使用注意事项,准备如下三个表,以及表中数据。

表1:CREATE  TABLE `FDM_SOR.mytest_department`(
      `dept_no` int, 
     `dept_name` string) row format delimited fields terminated by ','

 表2:CREATE  TABLE `FDM_SOR.mytest_staffinfo`(
         `id` int, 
         `name` string,
          sex   string,
          dept_no int 
      )row format delimited fields terminated by ','

  表3:CREATE  TABLE `FDM_SOR.mytest_deptaddr`(
      `dept_no` int, 
      `addr` string, 
      `tel` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
如下对应的各表中load的数据
`FDM_SOR.mytest_staffinfo
001,'tom','man',101
002,'jerry','man',103
003,'marry','woman',101
004,'tom','man',104
005,'jebby','man',102
006,'smiiss','man',101
007,'dosos','man',102
008,'tiny','woman',102
009,'feyy','woman',104
010,'feyy','woman',103
011,'cake','man',101
012,'dogy','man',102
013,'gaayy','man',105
014,'kety','man',
015,'caggyy','man',104
016,'addy','man',106
017,'nancy','man',104
018,'tom','man',101

FDM_SOR.mytest_department
101,'it'
102,'finance'
103,'mannager'
104,'dining'
105,'boss-office'
107,'enjoy'
108,'fun'

FDM_SOR.mytest_deptadd
109,'new york109','931232323'
108,'new york108','431232323'
107,'new york107','531232323'
106,'new york106','531232323'
101,'new york101','431232323'
104,'new york104','131232323'
103,'new york103','131232323'
102,'new york102','131232323'
105,'new york105','131232323'
1.inner join(内连接,只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来) 

   内连接是最常见的一种连接,它页被称为普通连接,而E.FCodd最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法

select a.id ,a.name,b.dept_no,b.dept_name
  from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
  on a.dept_no = b.dept_no
       等价于 inner join内连接的另外一种写法,效果一样。
  select a.id ,a.name,b.dept_no,b.dept_name
  from FDM_SOR.mytest_staffinfo a  ,FDM_SOR.mytest_department  b
  where a.dept_no = b.dept_no
        1	'tom'	101	'it'
        2	'jerry'	103	'mannager'
        3	'marry'	101	'it'
        4	'tom'	104	'dining'
        5	'jebby'	102	'finance'
        6	'smiiss'101	'it'
        7	'dosos'	102	'finance'
        8	'tiny'	102	'finance'
        9	'feyy'	104	'dining'
        10	'feyy'	103	'mannager'
        11	'cake'	101	'it'
        12	'dogy'	102	'finance'
        13	'gaayy'	105	'boss-office'
        15	'caggyy'104	'dining'
        17	'nancy'	104	'dining'
        18	'tom'	101	'it'
   如上,表1中工号16的员工,所在106部门,因为department中没有,所以其没有留下,同理14号员工因为没有部门,也没有存在。 同理department中部分编号107,108也没有留下,因为其在表staffinfo中没有,故没有留下。
------------------------------------------------------------------------------------------------------------------------------
2.left join 是左外连接(Left Outer Jion),其中outer可以省略,left outer join是早期的写法。
     A left join  B 是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
     意思说,左表(A)的记录将会全部表示出来(不管右边的表中是否存在与它们匹配的行),而右表(B)只会显示符合搜索条件的记录,比如符合on,where中的条件。B表记录不足的地方均为NULL.    A  left   join   B   等价B   right   join   A  
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no
1	'tom'	101	'it'
2	'jerry'	103	'mannager'
3	'marry'	101	'it'
4	'tom'	104	'dining'
5	'jebby'	102	'finance'
6	'smiiss'101	'it'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
9	'feyy'	104	'dining'
10	'feyy'	103	'mannager'
11	'cake'	101	'it'
12	'dogy'	102	'finance'
13	'gaayy'	105	'boss-office'
14	'kety'	NULL	NULL
15	'caggyy'104	'dining'
16	'addy'	NULL	NULL
17	'nancy'	104	'dining'
18	'tom'	101	'it'
3.right join ,同理和left join相反,A right join B ,则显示B表中所有的记录,A表不足的用null填充
  同样 right outer join = right join ,outer可以省略。
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  right outer  join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no
1	'tom'	101	'it'
3	'marry'	101	'it'
6	'smiiss'101	'it'
11	'cake'	101	'it'
18	'tom'	101	'it'
5	'jebby'	102	'finance'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
12	'dogy'	102	'finance'
2	'jerry'	103	'mannager'
10	'feyy'	103	'mannager'
4	'tom'	104	'dining'
9	'feyy'	104	'dining'
15	'caggyy'104	'dining'
17	'nancy'	104	'dining'
13	'gaayy'	105	'boss-office'
NULL	NULL	107	'enjoy'
NULL	NULL	108	'fun'

4. full outer join 等价 full join  ,全外连接

   包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行  在功能上,它等价于对这两个数据集合分别进行左外连接和右外连接,然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  full  join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no
order by a.id 
NULL	NULL	107	'enjoy'
NULL	NULL	108	'fun'
1	'tom'	101	'it'
2	'jerry'	103	'mannager'
3	'marry'	101	'it'
4	'tom'	104	'dining'
5	'jebby'	102	'finance'
6	'smiiss'	101	'it'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
9	'feyy'	104	'dining'
10	'feyy'	103	'mannager'
11	'cake'	101	'it'
12	'dogy'	102	'finance'
13	'gaayy'	105	'boss-office'
14	'kety'	NULL	NULL
15	'caggyy'	104	'dining'
16	'addy'	NULL	NULL
17	'nancy'	104	'dining'
18	'tom'	101	'it'
5.自连接是自身连接,指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系(也称作递归关系)中抽取数据
6.外连接与条件配合使用

当在内连接(join或者inner join)查询中加入条件时,无论是将它加入到join子句,还是加入到where子句,其效果是完全一样的,比如:
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no and a.id > 5
等价于
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  inner join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no  
where  a.id > 5
6	'smiiss'	101	'it'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
9	'feyy'	104	'dining'
10	'feyy'	103	'mannager'
11	'cake'	101	'it'
12	'dogy'	102	'finance'
13	'gaayy'	105	'boss-office'
15	'caggyy'	104	'dining'
17	'nancy'	104	'dining'
18	'tom'	101	'it'
但对于外连接情况就不同了。加入的条件在join子句中和在where子句中效果完全不一样。
当把条件加入到 join子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。

如果将条件放到where子句中,SQL Server将会首先进行连接操作,然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响:

select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no and  a.id > 5 
1	'tom'	NULL	NULL
2	'jerry'	NULL	NULL
3	'marry'	NULL	NULL
4	'tom'	NULL	NULL
5	'jebby'	NULL	NULL
6	'smiiss'	101	'it'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
9	'feyy'	104	'dining'
10	'feyy'	103	'mannager'
11	'cake'	101	'it'
12	'dogy'	102	'finance'
13	'gaayy'	105	'boss-office'
14	'kety'	NULL	NULL
15	'caggyy'	104	'dining'
16	'addy'	NULL	NULL
17	'nancy'	104	'dining'
18	'tom'	101	'it'
如上,当将条件加入到where 子句后
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a  left join  FDM_SOR.mytest_department  b
on a.dept_no = b.dept_no 
where  a.id > 5
6	'smiiss'	101	'it'
7	'dosos'	102	'finance'
8	'tiny'	102	'finance'
9	'feyy'	104	'dining'
10	'feyy'	103	'mannager'
11	'cake'	101	'it'
12	'dogy'	102	'finance'
13	'gaayy'	105	'boss-office'
14	'kety'	NULL	NULL
15	'caggyy'	104	'dining'
16	'addy'	NULL	NULL
17	'nancy'	104	'dining'
18	'tom'	101	'it'
7.多表之间进行join时注意事项
select a.id ,a.name,b.dept_no,b.dept_name,c.addr,c.tel
          from FDM_SOR.mytest_staffinfo a  
          inner join  FDM_SOR.mytest_department  b  on a.dept_no = b.dept_no
          inner join  FDM_SOR.mytest_deptaddr    c  on a.dept_no = c.dept_no ;
1	'tom'	101	'it'	    ‘new york101'	'431232323'
2	'jerry'	103	'mannager'	'new york106'	'131232323'
3	'marry'	101	'it'	    'new york101'	'431232323'
4	'tom'	104	'dining'	'new york101'	'131232323'
5	'jebby'	102	'finance'	'new york102'	'131232323'
6	'smiiss'101	'it'	    'new york101'	'431232323'
7	'dosos'	102	'finance'	'new york102'	'131232323'
8	'tiny'	102	'finance'	'new york102'	'131232323'
9	'feyy'	104	'dining'	'new york101'	'131232323'
10	'feyy'	103	'mannager'	'new york106'	'131232323'
11	'cake'	101	'it'	    'new york101'	'431232323'
12	'dogy'	102	'finance'	'new york102'	'131232323'
13	'gaayy'	105	'boss-office''new york106'	'131232323'
15	'caggyy'104	'dining'	'new york101'	'131232323'
17	'nancy'	104	'dining'	'new york101'	'131232323'
18	'tom'	101	'it'	   'new york101'	'431232323
注意:一般情况下,hive会给每个join对象启动一个mapreduce job进行执行,如上,一般会先启动一个mr job 进行a,b表的join,然后再启动一个mr job进行上面job产生的临时表与c表的join,但是对于3个及以上的多表join,join有所优化,如果每个join的子句on里的连接键一样,如上都是dept_no,那么这样的话,mr会启动一个job完成所有表的join.此外,多表进行join时,一般要把大表放到最后面,这样可以提高效率。
8.left semi join  左半开连接 ,会显示左半边表中记录,前提是其记录对于右半边表满足于on语句中判定条件。
   left semi join 效果和inner join 效果差不多,比inner join 效率更高。
select a.id ,a.name,a.dept_no
 from FDM_SOR.mytest_staffinfo a  
 where a.dept_no in (
 select dept_no  from FDM_SOR.mytest_department
 where dept_no > 102
 )
2	'jerry'	103
4	'tom'	104
9	'feyy'	104
10	'feyy'	103
13	'gaayy'	105
15	'caggyy'104
17	'nancy'	104
等价于下面的查询
select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
on a.dept_no = b.dept_no   and b.dept_no >102
 
2	'jerry'	103
4	'tom'	104
9	'feyy'	104
10	'feyy'	103
13	'gaayy'	105
15	'caggyy'104
17	'nancy'	104

select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
on a.dept_no = b.dept_no   
where a.dept_no > 102
 
 2	'jerry'	103
4	'tom'	104
9	'feyy'	104
10	'feyy'	103
13	'gaayy'	105
15	'caggyy'104
17	'nancy'	104

select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a  left semi join FDM_SOR.mytest_department  b  
on a.dept_no = b.dept_no   
 where b.dept_no > 102   -----报错。select 和where子句中不能引用右边表中的字段。
最后附上hive中各种join之间的关系一览图
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

涤生大数据

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值