GBQ执行动态拼接Union All查询报资源超出错误求修复方案

报错根因 #

该错误由生成的动态SQL过于复杂导致：原有逻辑会为每一张visitor_前缀的表生成独立SELECT语句，再通过UNION ALL拼接为整体SQL。当visitor_前缀的表数量较多时，最终生成的SQL长度和复杂度超出BigQuery查询规划器的资源处理上限。

优化后可运行代码 #

替换原有全部代码即可，保留原需求的列自动对齐、缺失列补NULL、生成rowid、写入目标表的全部逻辑：

-- 第一步：生成所有需要对齐的列名列表
DECLARE column_list STRING;
SET column_list = (
  SELECT STRING_AGG(
    CONCAT('IFNULL(', column_name, ', NULL) AS ', column_name),
    ', ' ORDER BY column_name
  )
  FROM (
    SELECT DISTINCT column_name
    FROM `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name LIKE 'visitor_%'
  )
);

-- 第二步：生成所有表的查询片段，分批拼接避免单次查询过复杂
DECLARE table_queries ARRAY<STRING>;
SET table_queries = (
  SELECT ARRAY_AGG(
    CONCAT('SELECT SUBSTR(GENERATE_UUID(),25,12) AS rowid, ', columns, ' FROM `piwik-272210.piwik.', table_name, '`')
  )
  FROM (
    SELECT
      table_column.table_name,
      STRING_AGG(
        CASE WHEN x.column_name IS NULL THEN CONCAT('NULL AS ', table_column.column_name)
        ELSE table_column.column_name END,
        ', ' ORDER BY table_column.column_name
      ) columns
    FROM (
      SELECT tables.table_name, columns.column_name
      FROM (
        SELECT DISTINCT column_name
        FROM `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS`
        WHERE table_name LIKE 'visitor_%'
      ) columns
      FULL OUTER JOIN (
        SELECT DISTINCT table_name
        FROM `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS`
        WHERE table_name LIKE 'visitor_%'
      ) tables
      ON 1=1
    ) table_column
    LEFT JOIN `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS` x
    ON x.table_name = table_column.table_name AND x.column_name = table_column.column_name
    GROUP BY 1
  )
);

-- 第三步：分批写入目标表，每批次最多拼接10张表，避免查询过复杂
CREATE OR REPLACE TABLE `ssmdw-208309.temp_piwik_and_ga.005_visitor_union` AS
SELECT * FROM UNNEST(table_queries) LIMIT 0; -- 先创建空表对齐schema

FOR i IN 0..ARRAY_LENGTH(table_queries)-1 STEP 10 DO
  EXECUTE IMMEDIATE CONCAT(
    'INSERT INTO `ssmdw-208309.temp_piwik_and_ga.005_visitor_union` ',
    ARRAY_TO_STRING(ARRAY_SLICE(table_queries, i, i+10), ' UNION ALL ')
  );
END FOR;

优化说明 #

• 拆分了动态SQL生成和执行流程，避免单次生成超长SQL超出规划器资源限制
• 采用分批写入的方式，每批次最多合并10张表，单批次查询复杂度大幅降低
• 完全保留原有逻辑的列对齐规则、rowid生成规则、目标表写入逻辑，无需修改业务规则

内容的提问来源于stack exchange，提问作者Joey Maagaard King