join是sql中常用的关键字，同样在hive中，join语句也经常被用到，尤其是一些outer join语句，但在使用这样outer join语句时，不小心就会踩到坑里，使结果与预期的不一样，或者使sql执行不够高效。

这里就详细介绍下hive outer join。

下面先看几个概念：

  • 保留表(Preserved Row table)

在outer join中需要_返回所有数据的表_叫做保留表，也就是说在left outer join中，左表需要返回所有数据，则左表是保留表；right outer join中右表则是保留表；在full outer join中左表和右表都要返回所有数据，则左右表都是保留表。

  • Null Supplying table(这个怎么翻译？ 支持null的表？？？？)

在outer join中对于没有匹配到的行需要用null来填充的表称为Null Supplying table。在left outer join中，左表的数据全返回，对于左表在右表中无法匹配的数据的相应列用null表示，则此时右表是Null Supplying table，相应的如果是right outer join的话，左表是Null Supplying table。_但是在full outer join中左表和右表都是Null Supplying table_，因为左表和右表都会用null来填充无法匹配的数据。

此时你会发现full outer join就是个矛盾体，因为此时的左表和右表即使保留表又是Null Supplying table，这就导致在outer join中谓词下放时有一些问题。

  • Join中的谓词

Join中的谓词是指 Join On语句中的谓词。如：’R1 join R2 on R1.x = 5’ the predicate ‘R1.x = 5’是Join中的谓词

  • Join之后的谓词

where语句中的谓词称之为Join之后的谓词

最好使用子查询的方式进行join

谓词下放

谓词：谓词是一个属性或是一个表示“持有”或“不持有”的表达式，换句话说，也就是取值为 TRUE、FALSE 或 UNKNOWN 的表达式。谓词用于 WHERE 子句和 HAVING 子句的搜索条件中，还用于 FROM 子句的联接条件以及需要布尔值的其他构造中。

对于outer join中的坑，主要是对谓词下放的规则不熟悉，导致理解的不够全面，造成一些坑。
谓词下放的规则有两个，分别为

  1. Join中谓词如果是保留表的，则不会下放。
  2. Join之后的谓词如果是Null Supplying tables的，则不会下放。

下面来看写例子来加深理解。

栗子

创建两个表

create table t1 (id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY “,”;
create table t2 (id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY “,”;

t1里的数据如下：

1
2
3
4
5
6
7
1,a
1,b
2,a
2,b
3,a
3,b
4,b

t2里的数据如下：

1
2
1,aa
4,dd

在执行例子之前，避免map join影响到各个例子的执行计划，先关闭map join，set hive.auto.convert.join=false;

  • case 1，sql如下：
    select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id) where t1.name='a' and t2.name='aa';
    此sql中，保留表为t1，Null Supplying table为t2，join中谓词在此sql中没有，where中的两个谓词都是join之后的谓词。
    则根据谓词下放规则，t1.name='a'是保留表的字段，则被下放，而t2.name='aa'是Null Supplying table的，Null Supplying table不支持join之后的谓词下放。
    那么此时的sql就等同于
1
2
3
4
5
select t.*,t2.name as name_t2 
from (
	select id, name from t1 where t1.name='a'
) as t left outer join t2 on
(t.id=t2.id) where t2.name='aa';

其结果为1 a aa，可能你的期望结果是

1
2
3
1	a	aa
2	a	NULL
3	a	NULL

造成这样的结果是因为hive中outer join谓词下放的规则。
case1 sql中的t2.name='aa'是join之后的谓词，不会被下放到t2中(t2是Null Supplying table)，而是对t1和t2 join的结果进行filter，filter的条件是t2.name='aa'，于是就出现了1 a aa结果，因为其余数据的t2.name的值都为NULL。

现在来解析下case1的执行过程。
首先sql中有两个join之后的谓词，分别为t1.name='a' and t2.name='aa'，其中t1.name是保留表t1的，会谓词下放，在scan t1表时，对t1中的数据进行过滤，t2.name是t2的，t2不是保留表，谓词无法下放，而where又是在join之后执行的(Joins occur BEFORE WHERE CLAUSES)。看下sql的执行计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 5 Data size: 28 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (name = 'a') (type: boolean)
              Statistics: Num rows: 2 Data size: 11 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 2 Data size: 11 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col6
          Statistics: Num rows: 2 Data size: 12 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: (_col6 = 'aa') (type: boolean)
            Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: int), 'a' (type: string), 'aa' (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从sql的执行计划上也可以看出，TableScan t1时使用了predicate: (name = 'a') (type: boolean)对数据进行过滤，(则进入mr的t1数据不是全表数据，而是过滤过的数据)。而where对应的filter operator是在join之后才执行的。

  • case 2，sql如下：
    select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t2.name='aa') where t1.name='a';
    执行结果为
1
2
3
1	a	aa
2	a	NULL
3	a	NULL

其中t2.name是join中谓词，t1.name是join之后谓词，根据规则join中谓词能下放到t2中，join之后的谓词能下放到t1中，则上面sql的意思是将t1中name为a的数据与t2中name为aa的数据进行join，t1和t2都会在table scan时进行数据过滤。sql的执行计划如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 5 Data size: 28 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (name = 'a') (type: boolean)
              Statistics: Num rows: 2 Data size: 11 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 2 Data size: 11 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (name = 'aa') (type: boolean)
              Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
                value expressions: name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col6
          Statistics: Num rows: 2 Data size: 12 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: int), 'a' (type: string), _col6 (type: string)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 2 Data size: 12 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 2 Data size: 12 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从执行计划上可以看出t1和t2的TableScan中都存在Filter Operator操作，对数据进行过滤。

  • case 3，sql如下：
    select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t1.name='a' ) where t2.name='aa';
    此sql中有两个谓词，一个是join中谓词t1.name='a'和join之后谓词t2.name='aa'。
    这里需要明白一点，join中谓词决定了进行join的数据，join之后谓词决定了最终要呈现的数据。也就是说join中谓词t1.name='a'决定了t1表中name为a的数据才去和t2进行join，而join之后谓词t2.name='aa'决定了最终的结果。
    执行结果为：1 a aa。

这里的结果随便和case 1的结果一样，但是执行的内部逻辑是不一样的，case3在执行where filter之前返回的数据是t1全表的数据，而case1在执行where filter之前返回的数据是t1中name为a的数据，因为case3中join中谓词t1.name='a'并不能对t1进行下放，这里的语义是对t1进行全表扫描，只拿name为a的数据和t2进行join，id匹配成功则为t2.name的值，匹配不成功和name不为a的数据中t2.name的值都为NULL，join结束之后由where filter过滤t2.name='aa'，因此就出现了上面的结果(因为没有匹配成功的和没有进行匹配的数据都是NULL，被过滤)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          filter predicates:
            0 {(VALUE._col0 = 'a')}
            1
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col1, _col6
          Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: (_col6 = 'aa') (type: boolean)
            Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: int), _col1 (type: string), 'aa' (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

上面是case3的执行计划，可以看出两个表在TableScan时都是全表扫描，没有进行filter，在join时有个filterfilter predicates，join结束之后又有个Filter Operator对join的结果进行过滤。

  • case 4，sql如下:
    select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t1.name='a' and t2.name='aa');
    此sql中只有join中谓词，根据规则t1.name=’a’不能下放到t1中，而t2.name=’aa’能够下放到t2中，那就是说扫t1的全表数据，然后拿t1.name为a的数据去和t2中t2.name为aa的数据(t2在table scan时会过滤name为aa的数据)进行join，那么t1中name不为a的数据不和t2进行join，直接将t2.name置为NULL，而t1中name为a却没有和t2进行join成功的数据也为NULL，所以其结果如下：
1
2
3
4
5
6
7
1	b	NULL
1	a	aa
2	b	NULL
2	a	NULL
3	b	NULL
3	a	NULL
4	b	NULL

这里需要说明下，t1是保留表，join之后要返回其表中所有的数据，因为在join之后没有where对其结果进行过滤，所以此处显示的是t1表的所有数据，上面的case之所以没有返回t1的所有数据是因为他们要么在table scan时进行了过滤要么就是在join之后通过where进行了过滤。

为了验证下t1中只有那么为a的数据与t2进行了join，我对t2中的数据进行了修改，添加一条数据4,aa，其执行结果按照上面的逻辑应该不会发生变化，再次执行case4，看下返回的结果是否一致。其结果如下

1
2
3
4
5
6
7
1	b	NULL
1	a	aa
2	b	NULL
2	a	NULL
3	b	NULL
3	a	NULL
4	b	NULL

与上面的结果一致。
此sql的执行计划如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (name = 'aa') (type: boolean)
              Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: NONE
                value expressions: name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          filter predicates:
            0 {(VALUE._col0 = 'a')}
            1
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col1, _col6
          Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: int), _col1 (type: string), _col6 (type: string)
            outputColumnNames: _col0, _col1, _col2
            Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

这四个case介绍了left outer join的情况，right outer join与left类似，只是保留表和Null Supplying table换下位置，比较特殊的是full outer join，在full outer join中join中谓词和join之后谓词都不会被下放，(这里说不会下放可能不太准备，会下放，但是下放的位置不对，下放前和下放后的位置是一样的，开启log可以查看。)

下放前的与下放后的结果如下：

1
2
3
4
5
TS[0]-RS[2]-JOIN[4]-FIL[5]-SEL[6]-FS[7]
TS[1]-RS[3]-JOIN[4]

TS[0]-RS[2]-JOIN[4]-FIL[8]-SEL[6]-FS[7]
TS[1]-RS[3]-JOIN[4]

因为full outer join中的两个表既是保留表也是Null Supplying table。来看个full outer join的例子，
select t1.*,t2.name as name_t2 from t1 full outer join t2 on (t1.id=t2.id and t2.name='aa') where t1.name='a';
其结果为

1
2
3
1	a	aa
2	a	NULL
3	a	NULL

其sql的执行计划如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 28 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 15 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: int)
              sort order: +
              Map-reduce partition columns: id (type: int)
              Statistics: Num rows: 1 Data size: 15 Basic stats: COMPLETE Column stats: NONE
              value expressions: name (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Outer Join 0 to 1
          filter predicates:
            0
            1 {(VALUE._col0 = 'aa')}
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col1, _col6
          Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: (_col1 = 'a') (type: boolean)
            Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: int), 'a' (type: string), _col6 (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 1 Data size: 30 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从执行计划中可以看出t1和t2在scan时都没有进行filter过滤，也就是说join中谓词和join之后的谓词都没有进行下放，join中谓词是在Join Operator时通过filter predicates进行过滤join，而join之后谓词是在join之后通过Filter Operator进行过滤的。

如果t1和t2某个表是分区表，此时想只full outer join某个分区的数据，应该怎么过滤更高效呢？
此时应该使用子查询，sql为select t1.*,t2.name as name_t2 from t1 full outer join t2 on (t1.id=t2.id and t2.name='aa')

1
2
3
4
5
select t.*,t2.name as name_t2 
from
    （select * from t1 where day='20170411') t
     full outer join  t2
     on t.id=t2.id

总结

这篇文章其实想说的有两点，

  1. outer join中谓词的下放规则
  2. join on和where的区别，以及执行顺序

根据上面的四个case总结下outer join中谓词的下放规则
| | 保留表 | Null Supplying table |
|———-|———-|——–|
| join中谓词 | case3和case4不下放 | case2和case4下放 |
| join之后谓词 | case1和case2下放 | case1和case3不下放 |

join on和where的区别是join on决定的是什么数据去进行join操作，符合条件的数据才会进行join，不符合条件的数据直接赋值为NULL，而where是对join之后的结果进行过滤，决定最终展示的内容。
where中的谓词如果没有被下放则在join之后执行。

参考

Hive Outer Join Behavior
Understanding Hive Outer Join Behavior

附加

hive开启debug的方式：

  1. 启动hive cli时，执行hive --hiveconf hive.root.logger=DEBUG,console
  2. 修改hive的log4j文件，在${HIVE_HOME}/conf/hive-log4j.properties文件中找到hive.root.logger属性，并将其修改为hive.root.logger=DEBUG,console