微信搜索bigdata029 | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 订阅本站 | 赞助作者:赞助作者

[一起学Hive]之十一-Hive中Join的类型和用法

Hive lxw1234@qq.com 7271℃ 0评论

关键字:Hive Join、Hive LEFT|RIGTH|FULL OUTER JOIN、Hive LEFT SEMI JOIN、Hive Cross Join

Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联,还支持LEFT SEMI JOIN和CROSS JOIN,但这两种JOIN类型也可以用前面的代替。

注意:Hive中Join的关联键必须在ON ()中指定,不能在Where中指定,否则就会先做笛卡尔积,再过滤。

数据准备:

hive> desc lxw1234_a;
OK
id                      string                                      
name                    string                                      
Time taken: 0.094 seconds, Fetched: 2 row(s)
hive> select * from lxw1234_a;
OK
1       zhangsan
2       lisi
3       wangwu
Time taken: 0.116 seconds, Fetched: 3 row(s)
hive> desc lxw1234_b;
OK
id                      string                                      
age                     int                                         
Time taken: 0.159 seconds, Fetched: 2 row(s)
hive> select * from lxw1234_b;
OK
1       30
2       29
4       21
Time taken: 0.09 seconds, Fetched: 3 row(s)

10.1 内关联(JOIN)

只返回能关联上的结果。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
join lxw1234_b b 
ON (a.id = b.id);

--执行结果

1       zhangsan        30
2       lisi    	29

10.2 左外关联(LEFT [OUTER] JOIN)

以LEFT [OUTER] JOIN关键字前面的表作为主表,和其他表进行关联,返回记录和主表的记录数一致,关联不上的字段置为NULL。

是否指定OUTER关键字,貌似对查询结果无影响。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
left join lxw1234_b b 
ON (a.id = b.id);

--执行结果:
1   zhangsan   30
2   lisi        29
3   wangwu    NULL

10.3 右外关联(RIGHT [OUTER] JOIN)

和左外关联相反,以RIGTH [OUTER] JOIN关键词后面的表作为主表,和前面的表做关联,返回记录数和主表一致,关联不上的字段为NULL。

是否指定OUTER关键字,貌似对查询结果无影响。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
RIGHT OUTER JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果:
1          zhangsan    	   30
2          lisi    	   29
NULL       NULL    	   21

10.4 全外关联(FULL [OUTER] JOIN)

以两个表的记录为基准,返回两个表的记录去重之和,关联不上的字段为NULL。

是否指定OUTER关键字,貌似对查询结果无影响。

注意:FULL JOIN时候,Hive不会使用MapJoin来优化。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
FULL OUTER JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果:
1       zhangsan        	30
2       lisi    		29
3       wangwu  		NULL
NULL    NULL    		21

10.5 LEFT SEMI JOIN

以LEFT SEMI JOIN关键字前面的表为主表,返回主表的KEY也在副表中的记录。

SELECT a.id,
a.name 
FROM lxw1234_a a 
LEFT SEMI JOIN lxw1234_b b 
ON (a.id = b.id);

--执行结果:
1       zhangsan
2       lisi

--等价于:
SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE a.id IN (SELECT id FROM lxw1234_b);


--也等价于:
SELECT a.id,
a.name 
FROM lxw1234_a a 
join lxw1234_b b 
ON (a.id = b.id);

--也等价于:
SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE EXISTS (SELECT 1 FROM lxw1234_b b WHERE a.id = b.id);

10.6 笛卡尔积关联(CROSS JOIN)

返回两个表的笛卡尔积结果,不需要指定关联键。

SELECT a.id,
a.name,
b.age 
FROM lxw1234_a a 
CROSS JOIN lxw1234_b b;

--执行结果:
1       zhangsan        30
1       zhangsan        29
1       zhangsan        21
2       lisi    30
2       lisi    29
2       lisi    21
3       wangwu  30
3       wangwu  29
3	 wangwu  21

Hive中的JOIN类型基本就是上面这些,至于JOIN时候使用哪一种,完全得根据实际的业务需求来定,但起码你要搞清楚这几种关联类型会返回什么样的结果。

除非特殊需求,并且数据量不是特别大的情况下,才可以慎用CROSS JOIN,否则,很难跑出正确的结果,或者JOB压根不能执行完。

经验告诉我,Hive中只要是涉及到两个表关联,首先得了解一下数据,看是否存在多对多的关联。

Hive相关文章(持续更新)

一起学Hive系列

—-Hive概述,Hive是什么

—-Hive函数大全-完整版

—-Hive中的数据库(Database)和表(Table)

—-Hive的安装配置

—-Hive的视图和分区

—-Hive的动态分区

—-向Hive表中加载数据

—-使用Hive命令行

—-Hive的查询语句SELECT

—-Hive中Join的原理和机制

Hive分析函数系列

Hive索引

hive优化之——控制hive任务中的map数和reduce数

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » [一起学Hive]之十一-Hive中Join的类型和用法

喜欢 (5)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址