优化Spark SQL关联聚合查询：如何避免冗余Shuffle？

你的问题核心是两次Shuffle的冗余：先按(campaign_id, item_id)聚合Shuffle，再按campaign_id做DISTRIBUTE BY Shuffle。可以通过调整聚合的分区策略，让一次Shuffle同时完成聚合和分区需求，具体有两种可行方式：

方式一：使用REPARTITION提示指定聚合分区键 #

在查询中添加REPARTITION(campaign_id)提示，让Spark在执行聚合前直接按campaign_id分区，这样聚合操作可以在每个campaign_id分区内对item_id做本地聚合，只触发一次Shuffle：

select  /*+ BROADCAST(campaigns), REPARTITION(campaign_id) */
 campaign_id,
 items.item_id,
 SUM(clicks) AS clicks,
 SUM(recs) AS recs, 
 MAX(data_timestamp) AS data_timestamp 
FROM items 
JOIN campaigns
ON items.item_id = campaigns.item_id
GROUP BY campaign_id, items.item_id

方式二：合并GROUP BY与DISTRIBUTE BY #

将DISTRIBUTE BY campaign_id与GROUP BY语句合并，利用Spark的优化逻辑：当DISTRIBUTE BY的键是GROUP BY键的子集时，Spark会自动将聚合的Shuffle分区与最终分区合并，只执行一次Shuffle：

select  /*+ BROADCAST(campaigns) */
 campaign_id,
 items.item_id,
 SUM(clicks) AS clicks,
 SUM(recs) AS recs, 
 MAX(data_timestamp) AS data_timestamp 
FROM items 
JOIN campaigns
ON items.item_id = campaigns.item_id
GROUP BY campaign_id, items.item_id
DISTRIBUTE BY campaign_id

这种写法下，Spark会识别到campaign_id是GROUP BY的前缀键，直接按campaign_id分区后完成聚合，不会触发第二次Shuffle。

原理说明 #

两种方式本质都是让聚合阶段的Shuffle分区直接以campaign_id为基准，避免先按(campaign_id, item_id)分区聚合、再重新按campaign_id分区的冗余操作。聚合时每个campaign_id分区内的item_id数据可以本地完成分组计算，一次Shuffle同时满足聚合和最终分区的需求。

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