hive join left 过滤 hive left join or

转载

字节墨海星 2023-07-20 21:55:34

文章标签 hive join left 过滤 hive join 官网理解实践验证 文章分类 Hive 大数据

标题说明：【】括号中代表此篇文章会涉及的方面，例如【官网理解】则代表对官网的讲解

目前进度：50%

该文目前完成【官网理解】部分，【实践验证】待更新，望批评指正，多多交流

正文：

版本功能更新：

hive join left 过滤 hive left join or_实践验证

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

基本功能分类说明：

1、JOIN：例如a JOIN b ON a.key = b.key，只有a的key和b的key匹配上的行才会被保留下来。

2、LEFT OUTER JOIN：例如a LEFT OUTER JOIN b ON a.key = b.key，a的所有行都会被保留下来，b中跟a.key匹配的行会显示正常的b的各列的值，b中没有跟a.key匹配的行则全部为null。

3、 RIGHT OUTER JOIN：与LEFT OUTER JOIN相反的效果。

4、FULL OUTER JOIN：例如a FULL OUTER JOIN b ON a.key = b.key，a和b的所有行都会被保留下来，key匹配上的行正常显示，a、b表相互不匹配的行的字段显示为null。

5、LEFT SEMI JOIN：以更高效的形式实现了IN/EXISTS操作，但注意的是，使用这个JOIN操作，SELECT子句中只能出现左边表的字段，也就是说，作为最终结果，不能SELECT join操作右边表的字段。

JOIN写法细节分析：

1、会产生map/reduce过程的：

hive join left 过滤 hive left join or_官网理解_02

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

当有多个表连起来进行join操作时，如果在不同的join子句中，同一张表用来join的key不是同一列，那么有几个join操作，hive就会生成几个map/reduce任务，并且顺序执行，即上一个join产生的结果，用来跟下一张表进行join操作。

hive join left 过滤 hive left join or_hive_03

在每一个join的map/reduce阶段，在reducer操作的时候，总会有一个表的数据是被流式地读取，其他表的被用来join的列的值，会被先缓存起来，然后流式读取的每一行，被拿来进行计算。默认情况下，在一个map/reduce过程中，最右边的表会被流式读入，而其他表用来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);

hive join left 过滤 hive left join or_hive join left 过滤_04

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

SELECT a.val, b.val1, b.val2 FROM a LEFT OUTER JOIN b ON a.key = b.key;

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

当我们要的结果是，选取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');

hive join left 过滤 hive left join or_join_05

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，大家可自行分析下。

2、只会产生单独的map过程的：

hive join left 过滤 hive left join or_实践验证_06