OuterJoinBehavior

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

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.dt 为 u 表的分区列，按照该 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 后，成功进行了谓词下推，解决了该问题。

如，以下两种写法都进行了谓词下推：

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