hive dim 维度 关联 hive关键词

一、背景

谓词，是指用来描述或判断客体性质、特征或客体之间关系的词项。在SQL中即返回值为布尔值的函数。

谓词下推，在Hive中叫Predicate Pushdown，含义是指在不影响结果的前提下，尽量将过滤条件提前执行，使得最后参与join的表的数据量更小。谓词下推后，过滤条件将在map端提前执行，减少map端输出，降低了数据传输IO，节约资源，提升性能。在Hive中通过配置hive.optimize.ppd参数为true，开启谓词下推，默认为开启状态。

二、表的分类

首先定义一下参与join的表的类型，共分4类。

2.1 Preserved Row table：保留表

a left (outer) join b 中的 a 表;

a right (outer) join b 中的 b 表;

a full outer join b a 和 b 表都是 保留表。

2.2 Null Supplying table：提供null值的表

也就是 非保留表，在join中如果不能匹配上的使用null填充的表。

a left (outer) join b 中的 b 表;

a right (outer) join b 中的 a 表，

a full outer join b a 和 b 表都是 null值保留表

2.3 During Join predicate：join中谓词

就是on后面的条件。

R1 join R2 on R1.x = 5 --> R1.x = 5就是join中谓词

2.4 After Join predicate：join后谓词

就是where后面的条件。

a left join b where a.t=1 --> a.t=1 就是join后谓词

三、关联场景分析

备注：关闭 cbo 优化：set hive.cbo.enable=false

链接：

关联类型	测试场景	测试sql	测试结果
Left outer Join & Right outer Join	过滤条件写在 where, 且是 保留表的字段	select o.id from bigtable b left join bigtable o  where b.id <= 10;	可以
	过滤条件写在 where, 且是 非保留表的字段	select b.id,o.id from bigtable b left join bigtable o where o.id <= 10;	不可以
	过滤条件写在 on, 且是 保留表的字段	select o.id from bigtable b left join bigtable o on b.id <= 10;	不可以
	过滤条件写在 on, 且是 非保留表的字段	select o.id from bigtable b left join bigtable o on o.id <= 10;	可以
Full outer Join	过滤条件写在 where, 且是 保留表的字段	select o.id from bigtable b full join bigtable o  where b.id <= 10;	可以
	过滤条件写在 where, 且是 非保留表的字段	select b.id,o.id from bigtable b full join bigtable o where o.id <= 10;	可以
	过滤条件写在 on, 且是 保留表的字段	select o.id from bigtable b full join bigtable o on b.id <= 10;	不可以
	过滤条件写在 on, 且是 非保留表的字段	select o.id from bigtable b full join bigtable o on o.id <= 10;	不可以
Inner Join	过滤条件写在 where, 且是 保留表的字段	select o.id from bigtable b join bigtable o  where b.id <= 10;	可以
	过滤条件写在 where, 且是 非保留表的字段	select b.id,o.id from bigtable b join bigtable o where o.id <= 10;	可以
	过滤条件写在 on, 且是 保留表的字段	select o.id from bigtable b join bigtable o on b.id <= 10;	可以
	过滤条件写在 on, 且是 非保留表的字段	select o.id from bigtable b join bigtable o on o.id <= 10;	可以

四、总结

4.1 Left outer Join & Right outer Join

	保留表字段（left的左表）	非保留表字段 （left的右表）
on	不可以	可以
where	可以	不可以（开启cbo，可以）

1、对于 Left outer Join ，右侧的表写在 on后面、左侧的表写在 where后面，性能上有提高；

2、对于 Right outer Join，左侧的表写在 on后面、右侧的表写在 where后面，性能上有提高；

4.2 Full outer Join

1. 如果不开启 cbo，写在 on后面，还是 where后面，都不会谓词下推

2. 如果开启了 cbo，写在 where 可以 谓词下推， 写在 on 不可以 谓词下推

4.3 Inner Join

Inner Join 不管有没有开启cbo，不管写在 on后面 还是 where后面，都会进行谓词下推。

4.4 最终总结

	inner join		left join		right join		outer join
	左表	右表	左表	右表	左表	右表	左表	右表
join中的谓词	Pushed	Pushed	Not Pushed	Pushed	Pushed	Not Pushed	Not Pushed	Not Pushed
join后的谓词	Pushed	Pushed	Pushed	Pushed	Pushed	Pushed	Pushed	Pushed