Spark SQL如何基于日期列单行生成当月多行并并行处理9亿条数据

• 现有业务表结构示例：

EmpUser   UserDate Empname .....
User123   20220730 Rajesh      (7月共30条记录)
3434Use   20220625 Gopi ....   (6月共25条记录)

• 核心需求：根据每行记录的UserDate字段取值，为单条记录生成其所属自然月对应的全月多条行数据
• 数据规模：待处理数据总量9亿条，需编写支持并行执行的Spark SQL语句，最大化处理效率

直接用Spark内置算子实现，避免不必要的shuffle和计算开销，配合自适应执行参数充分利用集群并行能力，SQL如下：

-- 基础性能参数配置
SET spark.sql.adaptive.enabled=true;
SET spark.sql.adaptive.coalescePartitions.enabled=true;
SET spark.sql.adaptive.skewJoin.enabled=true;
-- 9亿数据规模建议设置shuffle分区数在1500~3000区间，可根据集群核数按单task处理128~256M数据的标准调整
SET spark.sql.shuffle.partitions=2000;

WITH base_data AS (
    SELECT 
        *,
        -- 按UserDate实际类型调整转换逻辑，字符串/数值类型统一转为标准日期格式
        to_date(cast(UserDate AS string), 'yyyyMMdd') AS std_user_date,
        -- 计算记录所属自然月第一天
        trunc(to_date(cast(UserDate AS string), 'yyyyMMdd'), 'MM') AS month_start,
        -- 计算记录所属自然月总天数
        dayofmonth(last_day(to_date(cast(UserDate AS string), 'yyyyMMdd'))) AS month_total_days
    FROM source_business_table
    -- 源表如果按UserDate做分区，此处加分区裁剪条件，避免扫描无效数据
),
expand_by_month AS (
    SELECT
        *,
        -- 本地生成当月所有日期序列并展开，无跨节点shuffle开销
        explode(sequence(month_start, date_add(month_start, month_total_days - 1), interval 1 day)) AS month_date
    FROM base_data
)
SELECT
    EmpUser,
    UserDate,
    Empname,
    date_format(month_date, 'yyyyMMdd') AS expand_date
    -- 其余需要保留的业务字段直接在此处添加
FROM expand_by_month;

优化注意事项 #

• 不要使用自定义UDF实现日期展开，全部采用Spark内置函数，SQL优化器可以自动下推算子，计算效率远高于UDF
• 不要单独构建日历维表做笛卡尔关联：9亿数据和维表join会产生海量shuffle数据，sequence+explode的实现方式会在数据分片本地完成行展开，没有跨节点网络传输开销，性能比join方案高一个数量级
• 务必开启自适应查询执行（AQE）相关参数：自动处理数据倾斜、合并过小分区、拆分过大分区，不需要手动针对倾斜数据做单独处理
• 读取源表阶段尽可能提前过滤无效数据、利用分区裁剪，减少后续需要处理的数据量

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