BigQuery构建树形层级视图报执行资源超出错误的解决方法

错误根因 #

你当前使用的硬编码逐层CTE关联写法，每新增一层层级就多一个子查询和关联操作，层级较多时查询规划器需要处理的逻辑量指数级上升，就会触发Resources exceeded during query execution类报错。

优化方案 #

使用递归CTE（Recursive CTE） 替代硬编码逐层关联，既能自动遍历所有层级，又能大幅降低查询复杂度，避免资源超出问题，适配任意层级的树形结构查询。

WITH RECURSIVE hierarchy AS (
  -- 锚点节点：取最底层的初始对象数据
  SELECT 
    room,
    object,
    superia,
    1 AS level,
    -- 初始化父级路径数组，后续递归会把更高层级的父级插入到数组头部
    [superia] AS superia_path
  FROM edc_sap.v_eq_fl 
  WHERE type_table = 'EQ'

  UNION ALL

  -- 递归逻辑：逐层向上查找父级
  SELECT 
    h.room,
    h.object,
    t.superia,
    h.level + 1 AS level,
    ARRAY_INSERT(h.superia_path, 0, t.superia)
  FROM hierarchy h
  LEFT JOIN edc_sap.v_eq_fl t 
    ON t.object = h.superia
  -- 递归终止条件：找不到更高层级的父级就停止
  WHERE t.superia IS NOT NULL
),
-- 取每个对象的完整最长层级路径
full_path AS (
  SELECT 
    * EXCEPT(level, superia)
  FROM hierarchy
  QUALIFY MAX(level) OVER(PARTITION BY room, object) = level
)
-- 按要求输出列，层级从高到低排列
SELECT 
  room,
  superia_path[SAFE_OFFSET(0)] AS superia6,
  superia_path[SAFE_OFFSET(1)] AS superia5,
  superia_path[SAFE_OFFSET(2)] AS superia4,
  superia_path[SAFE_OFFSET(3)] AS superia3,
  superia_path[SAFE_OFFSET(4)] AS superia2,
  superia_path[SAFE_OFFSET(5)] AS superia,
  object
FROM full_path

方案说明 #

• 递归逻辑只会扫描源表2次（锚点1次、递归遍历1次），查询复杂度远低于逐层关联写法，不会触发资源超限错误
• 如果实际业务层级超过6层，只需在最终SELECT语句中增加对应superia_path[SAFE_OFFSET(N)]的列即可，不需要修改递归逻辑
• 用SAFE_OFFSET取数组元素，索引不存在时自动返回NULL，兼容不同深度的层级数据

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