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中的坑,主要是对谓词下放的规则不熟悉,导致理解的不够全面,造成一些坑。
谓词下放的规则有两个,分别为
- Join中谓词如果是保留表的,则不会下放。
- 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 | 1,a |
t2里的数据如下:
1 | 1,aa |
在执行例子之前,避免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 | select t.*,t2.name as name_t2 |
其结果为1 a aa
,可能你的期望结果是
1 | 1 a aa |
造成这样的结果是因为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 | STAGE DEPENDENCIES: |
从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 | 1 a aa |
其中t2.name是join中谓词,t1.name是join之后谓词,根据规则join中谓词能下放到t2中,join之后的谓词能下放到t1中,则上面sql的意思是将t1中name为a的数据与t2中name为aa的数据进行join,t1和t2都会在table scan时进行数据过滤。sql的执行计划如下:
1 | STAGE DEPENDENCIES: |
从执行计划上可以看出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 | STAGE DEPENDENCIES: |
上面是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 | 1 b NULL |
这里需要说明下,t1是保留表,join之后要返回其表中所有的数据,因为在join之后没有where对其结果进行过滤,所以此处显示的是t1表的所有数据,上面的case之所以没有返回t1的所有数据是因为他们要么在table scan时进行了过滤要么就是在join之后通过where进行了过滤。
为了验证下t1中只有那么为a的数据与t2进行了join,我对t2中的数据进行了修改,添加一条数据4,aa
,其执行结果按照上面的逻辑应该不会发生变化,再次执行case4,看下返回的结果是否一致。其结果如下
1 | 1 b NULL |
与上面的结果一致。
此sql的执行计划如下:
1 | STAGE DEPENDENCIES: |
这四个case介绍了left outer join的情况,right outer join与left类似,只是保留表和Null Supplying table换下位置,比较特殊的是full outer join,在full outer join中join中谓词和join之后谓词都不会被下放,(这里说不会下放可能不太准备,会下放,但是下放的位置不对,下放前和下放后的位置是一样的,开启log可以查看。)
下放前的与下放后的结果如下:
1 | TS[0]-RS[2]-JOIN[4]-FIL[5]-SEL[6]-FS[7] |
因为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 | 1 a aa |
其sql的执行计划如下:
1 | STAGE PLANS: |
从执行计划中可以看出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 | select t.*,t2.name as name_t2 |
总结
这篇文章其实想说的有两点,
- outer join中谓词的下放规则
- 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的方式:
- 启动hive cli时,执行
hive --hiveconf hive.root.logger=DEBUG,console
- 修改hive的log4j文件,在${HIVE_HOME}/conf/hive-log4j.properties文件中找到hive.root.logger属性,并将其修改为
hive.root.logger=DEBUG,console