[大数据]Hive - 之谓词下推解析

Hive - 之谓词下推解析

Hive的谓词下推是在对数据做操作之前，扫描数据进内存的一种优化手段！

那么具体什么时候会自动进行谓词下推呢，或者换一句话说谓词下推的规则是怎样的呢 ？

-- 1  join 【对于join，不管谓词放在on 还是 放在join都会触发谓词下推】
select * from a join b on a.id = b.id and a.name = 'a' and b.name = 'b';  --all pushed 
select * from a join b on a.id = b.id where a.name = 'a'; 				  --all pushed
select * from a join b on a.id = b.id and a.name = 'a' where b.name = 'a';--all pushed
select * from a join b on a.id = b.id where a.name = 'a';

-- 2 left outer join 【where 过滤只能针对主表的过滤条件，on上的只针对辅表的过滤条件】
select * from a left join b on a.id = b.id and a.name = 'a';     -- not pushed 
select * from a left join b on a.id = b.id where a.name = 'a';   -- pushed 
select * from a left join b on a.id = b.id where b.name = 'b';   -- not pushed 
select * from a left join b on a.id = b.id and b.name = 'b';     -- pushed 


--3 right outer join 【where 过滤只能针对主表的过滤条件，on上的只针对辅表的过滤条件】
select * from a right join b on a.id = b.id and a.name = 'a';     -- pushed 
select * from a right join b on a.id = b.id where a.name = 'a';   -- not pushed 
select * from a right join b on a.id = b.id where b.name = 'b';   -- pushed 
select * from a right join b on a.id = b.id and b.name = 'b';     -- not pushed 

--4 full outer join 
--这种情况下无论如何都不会触发谓词下推

--还有一些情况下也不会触发谓词下推
	1、在过滤条件中用到了某些函数
	...