(转)Hive中JOIN的用法以及一些注意事项总结。
原文:https://www.dandelioncloud.cn/article/details/1529381803362369537
Hive表连接的语法支持如下:
- join_table:
- table_reference JOIN table_factor [join_condition]
- | table_reference { LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
- | table_reference LEFT SEMI JOIN table_reference join_condition
- | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
- table_reference:
- table_factor
- | join_table
- table_factor:
- tbl_name [alias]
- | table_subquery alias
- | ( table_references )
- join_condition:
- ON equality_expression ( AND equality_expression )*
- equality_expression:
-
expression = expression
hive只支持等连接,外连接,左半连接。hive不支持非相等的join条件(可以通过其他方式实现),因为它很难在map/reduce job实现这样的条件。而且,hive可以join两个以上的表。
例子
写join查询时,有几个典型的点要考虑,如下:
等链接
只有等链接是允许的:
- SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
这个非等值链接是不合法的:
- SELECT a.* FROM a JOIN b ON (a.id < b.id)
但是可以用如下方式实现:
- SELECT a.* FROM a JOIN b ON (true) WHERE a.id < b.id
多表连接
同个查询,可以join两个以上的表:
- SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
需要注意的是,多个表join时,最好按照表由小到大的顺序join。虽然实际原因要考虑数据倾斜等问题,此处不展开叙述。
join的缓存和任务转换
hive转换多表join时,如果每个表在join字句中,使用的都是同一个列,只会转换为一个单独的map/reduce。
例如:
这个会转换为单独的map/reduce任务,只有b表的key1列在join被调用。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
而这个链接:
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
被转换为两个map/reduce任务,因为b的key1列在第一个join条件使用,而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c
join的结果
LEFT,RIGHT,FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如,这个查询:
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
由于链接的字段是key,不存在一对多,所以将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配,输出的行将是 a.val,NULL。如果b的行没有和a.key匹配上,将被抛弃。语法”FROM a LEFT OUTER JOIN b”必须写在一行,为了理解它如何工作——这个查询,a是b的左边,a的所有行会被保持;RIGHT OUTER JOIN将保持b的所有行, FULL OUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。
join的过滤
Joins发生在where字句前,所以,如果要限制join的输出,需要写在where字句,否则写在JOIN字句。现在讨论的一个混乱的大点,就是分区表
Sql代码
- SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
- WHERE a.ds=’2000-01-01’ AND b.ds=’2000-01-01’
将会连接a和b,产生a.val和b.val的列表。WHERE字句,也可以引用join的输出列,然后过滤他们。
但是,无论何时JOIN的行找到a的key,但是找不到b的key时,b的所有列会置成NULL,包括ds列。这就是说,将过滤join输出的所有行,包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。
也就是说,如果你在WHERE字句引用b的任何列,LEFT OUTER的部分join结果是不相关的。所以,当外连接时,使用这个语句
Sql代码
- SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds=’2000-01-01’ AND a.ds=’2000-01-01’
join的输出会预先过滤,然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2017-11-11 (转)Shell——基本运算符
2017-11-11 (转)shell脚本之文件测试操作符及整数比较符
2017-11-11 (转)2017年最新企业面试题之shell(一,二)
2017-11-11 (转)企业Shell实战-MySQL分库分表备份脚本
2017-11-11 (转)合格linux运维人员必会的30道shell编程面试题及讲解
2017-11-11 (转)Linux运维MySQL必会面试题100道
2017-11-11 (转)Linux企业运维人员常用的150个命令分享