如何通过BigQuery Standard SQL跨多项目查询创建超90天的表

• #2报错原因：你将动态SQL内的别名proj写到了字符串拼接逻辑外，SQL解析时无法识别该变量，同时存在拼写错误（UNNSET应为UNNEST）、未定义的变量iter（你实际使用的循环变量是i）
• #3报错原因：dt_list是数组类型，你直接把拼接好的SQL字符串赋值给数组触发了类型不匹配错误，动态SQL需要通过EXECUTE IMMEDIATE执行后才能拿到数组结果

前置依赖：运行查询的账号需要拥有所有目标项目的BigQuery 元数据查看者（roles/bigquery.metadataViewer）权限，否则会触发权限报错。
完整可运行代码如下：

-- 配置需要遍历的目标项目列表
DECLARE projects ARRAY<STRING> DEFAULT ['my-project-1', 'my-project-2', 'my-project-n'];
DECLARE project_idx INT64 DEFAULT 0;
DECLARE current_project STRING;
DECLARE datasets ARRAY<STRING>;
DECLARE dataset_idx INT64;
DECLARE current_dataset STRING;

-- 临时表统一存储所有符合条件的表结果
CREATE TEMP TABLE IF NOT EXISTS long_term_storage_tables (
  project_id STRING,
  dataset_id STRING,
  table_id STRING,
  size_gb FLOAT64,
  creation_time TIMESTAMP,
  last_modified_time TIMESTAMP,
  row_count INT64,
  table_type STRING
);

-- 遍历所有目标项目
WHILE project_idx < ARRAY_LENGTH(projects) DO
  SET current_project = projects[OFFSET(project_idx)];
  SET dataset_idx = 0;

  -- 动态查询获取当前项目下的所有数据集
  EXECUTE IMMEDIATE FORMAT("""
    SELECT ARRAY_AGG(schema_name) FROM `%s.INFORMATION_SCHEMA.SCHEMATA`
  """, current_project) INTO datasets;

  -- 遍历当前项目下的所有数据集
  WHILE dataset_idx < ARRAY_LENGTH(datasets) DO
    SET current_dataset = datasets[OFFSET(dataset_idx)];

    -- 过滤最后修改时间超过90天的表，写入临时表
    EXECUTE IMMEDIATE FORMAT("""
      INSERT INTO long_term_storage_tables
      SELECT
        @project_id AS project_id,
        dataset_id,
        table_id,
        ROUND(size_bytes/POW(10,9),2) AS size_gb,
        TIMESTAMP_MILLIS(creation_time) AS creation_time,
        TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
        row_count,
        type AS table_type
      FROM `%s.%s.__TABLES__`
      WHERE TIMESTAMP_MILLIS(last_modified_time) < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    """, current_project, current_dataset) USING current_project AS project_id;

    SET dataset_idx = dataset_idx + 1;
  END WHILE;

  SET project_idx = project_idx + 1;
END WHILE;

-- 输出最终查询结果
SELECT * FROM long_term_storage_tables ORDER BY project_id, dataset_id, size_gb DESC;

• 代码使用FORMAT函数拼接动态SQL，避免语法错误，同时用USING传递参数，降低注入风险
• 临时表统一存储所有符合条件的表记录，不会像原代码那样每次执行EXECUTE IMMEDIATE返回单独的结果集
• 如果需要跳过无权限的项目/数据集，可以在遍历逻辑中增加BEGIN...EXCEPTION异常处理块

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