Hive中JOIN的用法以及一些注意事项总结。

野性酷女 2022-05-25 08:40 479阅读 0赞

# 常见用法 #

Hive表连接的语法支持如下：![收藏代码][icon_star.png]

1.  join\_table: 
2.   table\_reference JOIN table\_factor \[join\_condition\] 
3.   | table\_reference \{ LEFT|RIGHT|**FULL**\} \[OUTER\] JOIN table\_reference join\_condition 
4.   | table\_reference LEFT SEMI JOIN table\_reference join\_condition 
5.   | table\_reference CROSS JOIN table\_reference \[join\_condition\] (**as** **of** Hive 0.10) 
6.  
7.  table\_reference: 
8.   table\_factor 
9.   | join\_table 
10. 
11. table\_factor: 
12.  tbl\_name \[alias\] 
13.  | table\_subquery alias 
14.  | ( table\_references ) 
15. 
16. join\_condition: 
17. **ON** equality\_expression ( AND equality\_expression )\* 
18. 
19. equality\_expression: 
20.  expression = expression

hive只支持等连接，外连接，左半连接。**hive不支持非相等的join条件**（可以通过其他方式实现），因为它很难在map/reduce job实现这样的条件。而且，**hive可以join两个以上的表。**

例子

写join查询时，有几个典型的点要考虑，如下：

**等链接**

只有等链接是允许的：

1.  **SELECT** a.\* **FROM** a JOIN b **ON** (a.id = b.id AND a.department = b.department)

这个非等值链接是不合法的：

1.  **SELECT** a.\* **FROM** a JOIN b **ON** (a.id < b.id)

但是可以用如下方式实现：

1.  **SELECT** a.\* **FROM** a JOIN b **ON** (true) **WHERE** a.id < b.id

多表连接

同个查询，可以join两个以上的表：

1.  **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代码 ![收藏代码][icon_star.png]

1.  **SELECT** a.val, b.val **FROM** a LEFT OUTER JOIN b **ON** (a.**key**=b.**key**) 
2.  **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代码 ![收藏代码][icon_star.png]

1.  **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也是一样的逻辑

[icon_star.png]: /images/20220525/baadd84ad2e8447b80f1e40a94941dcd.png