Apache Spark SQL中with as(CTE)与cache对比及CTE缓存问题问询

首先明确结论：默认情况下，Spark SQL的CTE和你了解的传统SQL行为一致，不会自动缓存结果集，每一处引用myCTE的位置，都会触发对应的子查询重新执行，也就是会重新读取源表someTable。

具体行为细节 #

• 如果你的SQL里多次引用同一个CTE，比如下面这种写法：

with myCTE as (select * from someTable where age > 18)
select * from myCTE a join myCTE b on a.id = b.parent_id

上述语句执行时，myCTE对应的子查询会执行两次，等价于直接把myCTE的定义替换到两个引用位置，会两次读取someTable表数据。

• 如果要避免重复执行，需要主动缓存CTE对应的结果，有两种常用实现方式：
  • 先执行cache table 缓存表名 as <CTE对应的查询语句>，后续SQL直接引用这个缓存表，用完后可以用uncache table 缓存表名释放资源
  • 如果是用DataFrame API的话，可以把CTE对应的查询结果转成DataFrame后调用cache()或者persist()方法手动缓存

特殊情况说明 #

Spark 3.x之后的版本提供了spark.sql.optimizer.cachePlanForCommonSubexpressions配置项，开启后（默认是关闭状态），Spark会自动识别重复的子查询计划并缓存结果，这时候多次引用的CTE就不会重复执行了，但这个特性默认不开启，且只适用于同一个SQL语句内的重复子查询。

内容的提问来源于stack exchange，提问作者MiamiBeach