Databricks Spark SQL查询优化：避免重复读取表A与表B

以下几种方法可以让你只读取一次表A和表B，后续操作复用内存/磁盘中的数据，避免重复扫描原表：

1. 缓存表到内存/磁盘 #

在执行业务查询前，先把表A和表B缓存起来，Spark会将数据持久化到内存（内存不足时自动写入磁盘），后续所有对这两张表的操作都会直接调用缓存数据，不再读取原Hive表。

执行缓存的SQL命令：

-- 缓存表A，存储级别设为MEMORY_AND_DISK（兼顾性能与容错）
CACHE TABLE table_a OPTIONS ('storageLevel' 'MEMORY_AND_DISK');
-- 同理缓存表B
CACHE TABLE table_b OPTIONS ('storageLevel' 'MEMORY_AND_DISK');

完成查询后，可手动清理缓存释放资源：

UNCACHE TABLE table_a;
UNCACHE TABLE table_b;

2. 用物化CTE复用基础数据集 #

如果你的查询中多次引用表A和表B（带或不带过滤/转换逻辑），可以用MATERIALIZED关键字定义CTE，强制Spark将CTE的结果物化存储，后续所有引用该CTE的地方都会复用结果，无需重复扫描原表。

示例：

WITH 
  materialized base_a AS (
    -- 这里定义基础过滤/转换逻辑，仅执行一次
    SELECT id, col1, col2 FROM table_a WHERE dt = '2024-05-20'
  ),
  materialized base_b AS (
    SELECT id, col3 FROM table_b WHERE dt = '2024-05-20'
  )
-- 后续所有操作基于base_a和base_b
SELECT * FROM base_a WHERE col1 = 'value1'
UNION ALL
SELECT a.*, b.col3 FROM base_a a JOIN base_b b ON a.id = b.id
WHERE a.col2 > 100;

注：Spark 3.0及以上版本支持MATERIALIZED关键字，低版本可省略，但加上能确保结果被物化。

3. 创建临时视图复用数据集 #

如果需要在多个查询中复用表A和表B的过滤后数据，可以创建临时视图，视图的结果会被Spark自动缓存，后续引用视图时不会重复读取原表。

示例：

-- 创建表A的临时视图，包含基础过滤逻辑
CREATE OR REPLACE TEMP VIEW view_a AS 
SELECT id, col1, col2 FROM table_a WHERE dt = '2024-05-20';

-- 创建表B的临时视图
CREATE OR REPLACE TEMP VIEW view_b AS 
SELECT id, col3 FROM table_b WHERE dt = '2024-05-20';

-- 后续查询直接调用视图
SELECT * FROM view_a WHERE col1 = 'value1';
SELECT a.*, b.col3 FROM view_a a JOIN view_b b ON a.id = b.id;

4. 开启Spark自动优化配置 #

开启Spark的子查询复用和自适应执行优化，让优化器自动识别重复的表扫描并复用结果，无需手动修改查询逻辑：

在Databricks中可在查询前执行以下配置：

SET spark.sql.optimizer.reuseSubquery = true;
SET spark.sql.adaptive.enabled = true;

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