【官网理解】【实践验证】Hive之join操作

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

目前进度：50%

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

正文：

版本功能更新：

    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过程的：

    

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

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

    

    在每一个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);

    

    正如前面所说，当使用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');

    

    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过程的：

    

    1）在join的独立的一个map/reduce过程中，如果几张表中，有一张表很小，那么可以通过在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操作。这种操作需要对三个参数进行设置。

    set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat

    set hive.optimize.bucketmapjoin=true

    set hive.optimize.bucketmapjoin.sortedmerge=true

    

    4）上述三种只有map操作过程的分类，除了第一种，另外两种都需要进行hive参数的配置。官方建议，将

            hive.auto.convert.join参数设置为true，则第一种无需进行hive参数设置的情况，无需自己写暗示语法，hive会在运行时，如果可能，则会自动转换为mapjoin操作。另外两种，则需要使用暗示语法去进行人为控制。