Poison

关于 Hive 的谓词下推

业务方反馈有一条 SQL 在 Hive on Spark 上执行会触发 OOM,原 SQL 经简化后如下:

1
SELECT c.name, COUNT(DISTINCT u.user_id) AS `uv`, COUNT(*) AS `pv` FROM u LEFT JOIN w ON get_json_object(u.param, '$.id') = w.id LEFT JOIN c ON w.cat_id = c.id WHERE u.page = 'xxx.htm' AND c.name LIKE '%筛选%' AND u.dt >= '20210715' GROUP BY c.name ORDER BY `pv` DESC LIMIT 10;

其中 u.dtu 表的分区列,按照该 SQL 的含义,即只需要扫描大于等于 20210715 的分区数据,但是通过检查 SQL 执行计划可视化图及查看逻辑计划,发现 u.dt 的筛选条件并没有下推至 u 表,而是在将 u 表与 w 表进行 LEFT JOIN 后再进行了过滤,导致扫描了整张 u 表,而又因为 u 表相当大,最后触发了 OOM,随后查询 Hive 关于外连接 JOIN 行为的文档:OuterJoinBehavior - Apache Hive - Apache Software Foundation,该文档详细描述了什么时候可以执行谓词下推,什么时候不能执行谓词下推,但是,所有的例子都是两张表时的示例,并没有解释多张表时的谓词下推规则,根据该文档的描述,我们把 SQL 调整为先 LEFT JOIN 其中两张表为临时表再进行一次 LEFT JOIN 后,成功进行了谓词下推,解决了该问题。

如,以下两种写法都进行了谓词下推:

1
2
3
SELECT c.name, COUNT(DISTINCT u.user_id) `uv`, COUNT(*) AS `pv` FROM (SELECT u.user_id, w.cat_id FROM u LEFT JOIN w ON get_json_object(u.param, '$.id') = w.id WHERE u.dt >= '20210715' AND u.page = 'xxx.htm') LEFT JOIN c ON w.cat_id = c.id AND c.name LIKE '%筛选%' GROUP BY c.name ORDER BY `pv` DESC LIMIT 10;

SELECT t.name, COUNT(DISTINCT u.user_id) AS `uv` , COUNT(*) AS `pv` FROM u LEFT JOIN (SELECT w.id, c.name FROM w w LEFT JOIN cat c ON w.cat_id = c.id WHERE c.name LIKE '%筛选%') t ON get_json_object(u.param, '$.id') = t.id WHERE u.dt >= '20210715' AND u.page = 'xxx.htm' GROUP BY t.name LIMIT 10;
2021-12-02

今天阅读 《Spark SQL内核剖析》 时发现在第 11 章的 “SQL 写法的陷阱” 第三小节中举的例子即为以上引用到的链接中的内容:OuterJoinBehavior - Apache Hive - Apache Software Foundation

Reference

《Hive 编程指南》
Understanding Spark’s Logical and Physical Plan in layman’s term