hive谓词下放

保留表(Preserved Row table)

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

Null Supplying table

在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之后的谓词

谓词下放

谓词：谓词是一个属性或是一个表示“持有”或“不持有”的表达式，换句话说，也就是取值为 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,a
1,b
2,a
2,b
3,a
3,b
4,b

t2里的数据如下：

1,aa
4,dd

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

demo1:

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就等同于

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

造成这样的结果是因为hive中outer join谓词下放的规则,sql中的t2.name='aa'是join之后的谓词，不会被下放到t2中(t2是Null Supplying table)，而是对t1和t2 join的结果进行filter，filter的条件是t2.name='aa'，于是就出现了1 a aa结果，因为其余数据的t2.name的值都为NULL</p>
首先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的执行计划：</p>

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