Hive谓词下推

1、情景描述

在实际数仓开发中，我们经常会遇到多表关联，这个时候就会涉及到where与on的使用

Hive中的where与on在HQL中的区别为：

• 相同点
  • where与on都是用作筛选条件
• 不同点
  • on会显示所有匹配条件的值，不匹配条件的数据补NULL；where只显示满足条件的数据
  • on作用不同类型的多表连接时结果不同；where只起连接作用，不同类型的多表连接时结果相同

首先，来看一个例子：使用a表指定数据关联b表：

写法一：

select * from a left join b on a.id=b.id where a.id='1';

写法二：

select * from a left join b on a.id=b.id and a.id='1';

写法三：

select * from (select * from a where id='1') t left join b on t.id=b.id;

三种写法的结果是等同的，也没有问题。如果需要以b表作为主表，关联查询a表，只需要修改连接类型为右外连接就可以了

但三种写法存在效率上的差异！

写法一与写法三都为高效写法，Hive只会取指定的数据进行join

写法二则效率较低，Hive先会查出所有数据进行join，然后再去过滤指定的数据

这可以通过explain执行计划证明：

写法一和写法三：

写法二：

从执行计划可以得出，写法一和写法三的数据在一开始扫描时候就已经过滤了。而写法二会拿所有的数据先进行查询join，然后再进行过滤

这种将过滤表达式提前执行的过程我们称为谓词下推

2、Hive谓词下推

2.1、什么是谓词下推

谓词下推（Predicate Pushdown，PPD）是指将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简而言之，就是在合适的场景下，优先执行过滤条件

2.2、Hive谓词下推

在Hive生成物理执行计划中，有一个配置项用于管理谓词下推优化是否开启：

set hive.optimize.ppd=true;

需要注意的是，Hive的PPD控制参数默认开启

3、谓词下推规则

Hive官网谓词下推介绍：https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior

基本概念：

• 保留行表(Preserved Row table)： 外连接中的表必须返回所有行。对于左外连接，左表是保留行表；对于右外连接，右表是保留行表；对于全外连接，两个表都是保留行表
• 空供应表(Null Supplying table)： 该表的不匹配行中的列使用空值填充。对于左外连接，左表数据全部返回，左表在右表中无法匹配的数据的列用NULL表示；对于右外连接，刚好相反；对于全外连接，左表和右表都会用NULL来填充无法匹配的数据
• JOIN中的谓词(During Join predicate)： join on子句中的谓词。例如，在R1 join R2 on R1.x = 5中，谓词R1.x = 5是JOIN中的谓词
• JOIN后的谓词(After Join predicate)： where子句中的谓词。例如，在R1 join R2 on R1.m = R2.n where R1.x = 5中，谓词R1.x = 5是JOIN后的谓词

官网对谓词下推规则的概括如下：

• 在JOIN期间谓词不能被推过保留行表
• JOIN后的谓词不能被推过空供应表

总结来说就是：

• 保留行表的谓词写在join中不能下推，需要用where
• 空供应表的谓词写在join后不能下推，需要用on
• 在join关联情况下，过滤条件无论在join中还是where中谓词下推都生效
• 在full join关联情况下，过滤条件无论在join中还是where中谓词下推都不生效

因此，可以得到如下谓词下推规则表：

item	[inner] join		left [outer] join		right [outer] join		full [outer] join
	left table	right table	left table	right table	left table	right table	left table	right table
where	PPD	PPD	PPD	Not PPD	Not PPD	PPD	Not PPD	PPD
on	PPD	PPD	Not PPD	PPD	PPD	Not PPD	Not PPD	Not PPD

4、谓词下推场景分析

Hive谓词下推规则表中各场景分析案例详见文章：传送门