播面

SparkSQL 面试题：快递寄件多渠道订单及运费统计

题目背景

在智慧物流场景中，用户通过快递柜寄件有多种入口（如微信小程序、支付宝小程序、APP、开放API等）。为了评估各渠道的获客能力和营收贡献，现需要统计每个渠道的总订单量及总实付运费金额。

1. 示例数据

表 1：订单主表 t_express_order

记录用户在快递柜投递的所有寄件订单信息。

表 2：渠道维度表 t_channel_dict

记录渠道编码与中文名称的映射关系。

2. 面试题要求

编写一条 SparkSQL 语句，统计已支付成功（SUCCESS）的订单中，微信小程序、支付宝小程序、App 及开放 API 各渠道的：

1. 订单量（order_count）
2. 实付运费总金额（total_pay_amount，保留两位小数）

注意：过滤掉非上述四个渠道的数据（如示例中的 unknown），结果按实付运费总金额降序排列。

3. 正确 SQL 答案

SELECT 
    c.channel_name,
    COUNT(o.order_id) AS order_count,
    ROUND(SUM(o.pay_amount), 2) AS total_pay_amount
FROM 
    t_express_order o
JOIN 
    t_channel_dict c ON o.channel_code = c.channel_code
WHERE 
    o.order_status = 'SUCCESS'
    AND o.channel_code IN ('wechat_mini', 'alipay_mini', 'app', 'open_api')
GROUP BY 
    c.channel_name
ORDER BY 
    total_pay_amount DESC;

期望输出结果

4. SparkSQL 深度分析与面试应对指南

在 SparkSQL 面试中，仅仅写出上述 SQL 只能算及格。面试官通常会针对该 SQL 追问其在 Spark 底层的执行逻辑和优化手段。以下是应对面试的硬核解析：

解析一：执行计划与 Join 优化（Broadcast Hash Join）

• 面试官追问：“如果 t_express_order 是百亿级大表，而 t_channel_dict 只有几条数据，Spark 会如何执行这个 Join？你会怎么优化？”
• 通关回答：
  1. 默认机制：Spark 默认会尝试使用 Broadcast Hash Join (BHJ)，将小表（t_channel_dict）广播到所有的 Executor 节点上，在 Map 端直接进行 Join，从而避免了昂贵的 Shuffle 阶段。
  2. 调优参数：可以通过 spark.sql.autoBroadcastJoinThreshold（默认 10MB）来控制自动广播的大小的阈值。
  3. 显式 Hint：在 SQL 中可以显式声明广播提示，确保 Spark 强制执行广播关联，避免因元数据估算不准退化为 SortMergeJoin：
     sql

     SELECT /*+ BROADCAST(c) */ c.channel_name, ...

解析二：避免数据倾斜（Data Skew）

• 面试官追问：“如果微信小程序的寄件量占了 90% 以上，在执行 GROUP BY 时出现了数据倾斜（某个 Task 运行极慢），你怎么解决？”
• 通关回答：
  1. 两阶段聚合（放盐法/两阶段聚合）：
     • 第一阶段：给 Group By 的 Key 加上随机前缀（如 concat(channel_name, '_', cast(rand() * 10 as int))），将倾斜的数据分散到多个 Task 中进行局部聚合。
     • 第二阶段：去掉随机前缀，进行全局聚合。
  2. 启用 Spark 3.x AQE（自适应查询执行）：
     • 开启 spark.sql.adaptive.skewJoin.enabled = true，Spark 会在运行时检测倾斜的分区，并自动将其拆分成多个子分区分别与另一端关联，最后合并结果。

解析三：过滤下推与投影列裁剪（Filter Pushdown & Project Column Pruning）

• 面试官追问：“SparkSQL 在读取这两个表的数据时，做了哪些优化？”
• 通关回答：
  1. 谓词下推（Predicate Pushdown）：Spark 优化器（Catalyst）会将 o.order_status = 'SUCCESS' 和 o.channel_code IN (...) 这些过滤条件下推到数据源端（如 Parquet/ORC 文件的读取阶段）。在读取时就过滤掉不符合条件的数据，减少了传输到内存的数据量。
  2. 列裁剪（Column Pruning）：Spark 只会读取 SQL 中用到的列（如 order_id, channel_code, pay_amount, order_status），而不会读取订单表中的 create_time, user_id 等无关列，极大地节省了 I/O 带宽。