《Hive官方文档》Hive - Joins 学习笔记

官网链接：Hive官方文档

hive 0.13.0以后的版本，支持了隐式join标记。

例如

SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535'

这样的写法，等同于

SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id AND t1.zipcode = '02535' JOIN table3 ON t2.id = t3.id

一、基本功能说明

• JOIN
  • 例如a JOIN b ON a.key = b.key，只有a的key和b的key匹配上的行才会被保留下来。
• LEFT [OUTER] JOIN
  • 例如a LEFT JOIN b ON a.key = b.key，a的所有行都会被保留下来，b中跟a.key匹配的行会显示正常的b的各列的值，b中没有跟a.key匹配的行则全部为null。
• RIGHT [OUTER] JOIN
  • 与LEFT OUTER JOIN相反的效果。
• FULL [OUTER] JOIN
  • 例如a FULL OUTER JOIN b ON a.key = b.key，a和b的所有行都会被保留下来，key匹配上的行正常显示，a、b表相互不匹配的行的字段显示为null。
• LEFT SEMI JOIN
  • 以更高效的形式实现了IN/EXISTS操作，但注意的是，使用这个JOIN操作，SELECT子句中只能出现左边表的字段，也就是说，作为最终结果，不能SELECT join操作右边表的字段。

二、Join细节分析

1、两张以上的hive表join，会产生会几个map/reduce

1）当有多个表连起来进行join操作时，如果在不同的join子句中，每张表用来join的key都是同一列，那么hive会将所有的join操作合起来，作为一个map/reduce任务。

如：

SELECT 
    a.val, b.val, c.val 
FROM a 
JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key1)

由于Join中仅涉及 b 的 key1 列，因此被转换为单个 map/reduce 作业。

2）当有多个表连起来进行join操作时，如果在不同的join子句中，同一张表用来join的key不是同一列，那么有几个join操作，hive就会生成几个map/reduce任务，并且顺序执行，即上一个join产生的结果，用来跟下一张表进行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条件中使用了 b 中的 key1 列，而在第二个join条件中使用了 b 中的 key2 列。第一个 map/reduce 作业将 a 与 b 进行join，然后将结果与 c join到第二个 map/reduce 作业中。

2、多表Join，它们的先后顺序有什么讲究

在每一个join的map/reduce阶段，在reducer操作的时候，总会有一个表的数据是被流式地读取，其他表被用来join的列的值，会被先缓存起来，然后流式读取的每一行，被拿来进行计算。默认情况下，在一个map/reduce过程中，最右边的表会被流式读入，而其他表用来join的列的值会被缓存起来。为了降低在reducer中需要的内存，可以将最大的表放最后，作为流式读入的表，其他相对较小的表，就可以被缓存。

如：

SELECT 
    a.val, b.val, c.val 
FROM a 
JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key1)

这三个表都在一个 map/reduce 作业中join，并且表 a 和 b 的key的值被缓存在reduce的内存中。然后，从 c 中流式读取每一行，再使用缓存中的行来进行join。

同样的：

SELECT 
    a.val, b.val, c.val 
FROM a 
JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key2)

该SQL涉及两个 map/reduce 作业。其中的第一个将 a 与 b 连接起来，并缓冲 a 的值，同时在reduce 中流式读取 b 的值，join完之后将join结果缓存在内存中。第二个MR作业流式的读取 c 表的值，与内存中的第一个join的结果进行join。

所以：为了降低在reducer中需要的内存，可以将最大的表放最后，作为流式读入的表，其他相对较小的表，就可以被缓存。

如果不方便放最后，该怎么办呢？

可以指定要流式读取的表。例如：

SELECT 
/*+ STREAMTABLE(a) */ a.val, b.val, c.val 
FROM a 
JOIN b ON (a.key = b.key1) 
JOIN c ON (c.key = b.key1)

这三个表都在一个 map/reduce 作业中join，并且 b 和 c 的key的值被缓冲在reduce的内存中。然后，从 a 中读取每一行，使用缓存中的数据进行join。

如果省略 STREAMTABLE 提示，则 Hive 将流化 join中 最右边的表。

3、Left join 连接后筛选问题

当使用LEFT OUTER JOIN 的时候，b中跟a中不匹配的key的行，b的列全部为null，也就是说：

SELECT 
a.val, b.val 
FROM a 
LEFT OUTER JOIN b ON (a.key=b.key)

上面这条语句中的b.val1，b.val2以及可能出现的b的其他列，都可能为null。所以当后续的where子句中，出现b表的任何筛选字段，作用在join之后的结果上，只要是行中b的列为null的，都会被过滤掉，这样会导致很多a的行也被过滤掉，可能不是我们要的结果；例如：

SELECT 
a.val, b.val 
FROM a 
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

当我们要的结果是，选取b中某列为某值的数据，和a进行key的匹配，这样的效果时，则应该选择在LEFT OUTER JOIN ON 的on子句中进行提前过滤（也就是说不是在join之后的结果上进行过滤）。例如：

SELECT 
a.val, b.val 
FROM a 
LEFT OUTER JOIN b ON (a.key = b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07');

4、Joins操作不是可交换的，是左连接的。(重点关注)

例如：

SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)

在上述这个句子中，可能有些人会认为，整个过程会是a、b、c三张表，全表进行了两个join操作后，最终的结果选取a.val1, a.val2, b.val, c.val；

但是事实上，因为a.val1, a.val2, b.val 这三个值，在第一个join后的结果中，已经可以拿到，所以当进行第二个LEFT OUTER JOIN操作时，是拿a.val1, a.val2, b.val这三个列的结果来进行join操作的，所以并不存在a.key，LEFT OUTER JOIN又是保留左边的结果的全部行，所以最终的结果是c.val根本不会存在在最终结果。反之，如果上面这个语句的LEFT OUTER JOIN换成RIGHT OUTER JOIN的话，会出现更奇怪的结果，就是null, null, null, c.val，大家可自行分析下。

三、MapJoin(只会产生map操作的join)

1）如果除一个要连接的表之外的所有表都很小，那么可以通过在hql中写暗示的方式，实现只有map过程，没有reduce过程。假设小的表为b，例如：

SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a 
JOIN b ON a.key = b.key

但是注意，FULL/RIGHT OUTER JOIN 不支持这样的操作（但似乎只有当小表是右边的表才不支持，待验证）

2）如果所有的表都依据join的列值进行分桶，并且每个表的桶的数量刚好是其他表的桶的数量的倍数，那么每个表的桶可以一一对应地去进行join操作，同样只有map操作，没有reduce操作。同样需要在代码中进行暗示方式的编写。不同于上一点的是，跟桶有关的这种只有map的操作，需要设置下面这个参数

set hive.optimize.bucketmapjoin = true

3）如果join操作中所有的表在进行join操作所用的列上进行分桶，并且进行了排序，同时他们有相同数量的桶，那么会应用到sort-merge join这种操作。一致的桶相互之间会进行join操作，并且只会有map操作。这种操作需要对三个参数进行设置。

如果 A 和 B 都有 4 个存储桶：

SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM A a JOIN B b ON a.key = b.key

只能在 Map 器上完成。 A 的存储桶的 Map 器将遍历 B 的对应存储桶。这不是默认行为，需要设置以下参数：

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;

总结：

述三种只有map操作过程的分类，除了第一种，另外两种都需要进行hive参数的配置。官方建议，将 hive.auto.convert.join 参数设置为true，则第一种无需进行hive参数设置的情况，无需自己写暗示语法，hive会在运行时，如果可能，则会自动转换为mapjoin操作。另外两种，则需要使用暗示语法去进行人为控制。