BigQuery查询运行1-2小时报错60909208，求错误原因及优化方案

我尝试运行如下查询，任务持续执行1到2小时后始终抛出以下错误：

发生内部错误，无法完成请求。错误代码：60909208

WITH
Liveramp AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_Liveramp_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

Neustar AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_Neustar_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

PT AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_PT_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

liveramp_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM Liveramp
),

PT_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM PT
),

Neustar_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM Neustar
),
Liveramp_unnested AS(
    SELECT DISTINCT maid,segments AS segment_id, device_type, date 
    FROM liveramp_split , UNNEST(segment_id) AS segments
),
Neustar_unnested AS(
    SELECT DISTINCT maid, segments AS segment_id, device_type, date 
    FROM Neustar_split , UNNEST(segment_id) AS segments
),
PT_unnested AS(
    SELECT DISTINCT maid, segments AS segment_id, device_type, date 
    FROM PT_split , UNNEST(segment_id) AS segments
),

Taxonomy AS(
    SELECT segment_id, CAST(skydeo_segment_id AS STRING) AS skydeo_segment_id
    FROM `mikesky.Skydeo_Taxonomy.Segments_IDs_Mappings`
),

Liveramp_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM Liveramp_unnested  JOIN Taxonomy ON Liveramp_unnested.segment_id = Taxonomy.segment_id 
),

Neustar_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM Neustar_unnested JOIN Taxonomy ON Neustar_unnested.segment_id = Taxonomy.segment_id 
),

PT_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM PT_unnested JOIN Taxonomy ON PT_unnested.segment_id = Taxonomy.segment_id 
),

Liveramp_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM Liveramp_Taxonomy
    GROUP BY maid, device_type, date
),

Neustar_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM Neustar_Taxonomy
    GROUP BY maid, device_type, date
),

PT_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM PT_Taxonomy
    GROUP BY maid, device_type, date
),

Super_set AS(
SELECT * FROM Liveramp_agg

UNION ALL

SELECT * FROM Neustar_agg

UNION ALL

SELECT * FROM PT_agg

)

SELECT *
FROM Super_set

该查询的预期逻辑是合并Liveramp_Raw、PT_Raw、Neustar_Raw三张表的数据，关联Segments_IDs_Mappings表后对结果进行聚合。
最初我运行该查询的另一版本时抛出如下错误：

查询执行期间资源超出限制：你的项目或组织超出了shuffle操作可用的最大磁盘和内存限制。建议申请更多槽位、降低查询并发度，或优化任务逻辑提升效率。

我已尽可能对查询逻辑做了优化，想请教本次出现的60909208错误的具体含义，以及触发该错误的原因是什么？

解答 #

错误含义与触发原因 #

• 错误代码60909208是BigQuery未对外公开的内部错误码，本质是前序shuffle资源超限问题的衍生错误：你之前遇到的资源不足问题没有被完全解决，查询长时间运行持续耗尽分配给该任务的内存、磁盘资源，导致后台执行节点崩溃、任务被强制中断，最终抛出该内部错误。
• 触发该错误的核心原因是当前查询逻辑仍存在大量冗余计算，远超你持有的BigQuery槽位可承载的负载：
  1. 对三张结构完全一致的来源表重复执行了三次完全相同的拆分、打平、关联、聚合逻辑，产生了三倍的不必要计算开销
  2. 拆分segment_id后提前去重的逻辑存在冗余，可在关联映射表、聚合阶段再去重，大幅降低中间数据量
  3. 没有下推过滤条件，映射表中的无效segment_id未提前过滤，导致后续产生大量无意义的关联计算

优化方案 #

你可以直接将查询逻辑重构为如下结构，可减少至少70%的计算量，大概率解决资源超限问题：

WITH
all_source AS(
 -- 先合并三张表，仅执行一次全表扫描
 SELECT maid, segment_id, device_type, date
 FROM `mikesky.Segments.Segment_Liveramp_Raw`
 WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
 UNION ALL
 SELECT maid, segment_id, device_type, date
 FROM `mikesky.Segments.Segment_Neustar_Raw`
 WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
 UNION ALL
 SELECT maid, segment_id, device_type, date
 FROM `mikesky.Segments.Segment_PT_Raw`
 WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),
split_unnested AS(
 -- 统一拆分打平，仅执行一次UNNEST操作
 SELECT DISTINCT maid, segments AS segment_id, device_type, date 
 FROM all_source, UNNEST(SPLIT(segment_id)) AS segments
),
Taxonomy AS(
 SELECT segment_id, CAST(skydeo_segment_id AS STRING) AS skydeo_segment_id
 FROM `mikesky.Skydeo_Taxonomy.Segments_IDs_Mappings`
 -- 可在此处添加过滤条件，提前剔除无用的segment_id减少后续关联量
),
joined AS(
 -- 统一关联映射表，仅执行一次JOIN操作
 SELECT maid, skydeo_segment_id, device_type, date
 FROM split_unnested JOIN Taxonomy ON split_unnested.segment_id = Taxonomy.segment_id 
)
-- 统一聚合，仅执行一次GROUP BY操作
SELECT maid, ARRAY_AGG(DISTINCT skydeo_segment_id) AS skydeo_segment_id, device_type, date 
FROM joined
GROUP BY maid, device_type, date

如果优化后仍存在资源超限问题，可再做如下调整：

• 缩小单次查询的时间范围，分批次计算后合并结果
• 申请更多BigQuery计算槽位，提升任务可使用的资源上限
• 降低当前项目同时运行的查询并发数，避免资源被其他任务占用

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