如何通过定时查询在BigQuery中批量备份数据集为JSON（替代UNION ALL）

可以通过INFORMATION_SCHEMA获取表名+动态SQL的方式批量处理，完全替代手动编写数百个UNION ALL语句，具体实现如下：

1. 核心思路 #

通过查询INFORMATION_SCHEMA.TABLES获取目标数据集的所有实体表名，然后动态拼接出包含所有表的UNION ALL查询语句，最后执行这个动态生成的SQL。

2. 完整实现代码 #

DECLARE sql_query STRING;

-- 生成包含所有表查询的动态SQL
SET sql_query = (
  SELECT STRING_AGG(
    FORMAT("""
      SELECT
        'mydataset' AS dataset,
        '%s' AS table,
        TO_JSON_STRING(ARRAY_AGG(t)) AS data,
        CURRENT_DATETIME() AS backup_date
      FROM `mydataset.%s` AS t
    """, table_name, table_name),
    ' UNION ALL '
  )
  FROM `你的项目ID.mydataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE' -- 仅处理实体表，排除视图
  -- 可选：添加表名过滤条件，比如只处理特定前缀的表
  -- AND table_name LIKE 'order_%'
);

-- 执行动态SQL
EXECUTE IMMEDIATE sql_query;

3. 关键说明 #

• STRING_AGG函数负责将每个表对应的查询片段用UNION ALL连接成完整SQL
• FORMAT函数用于替换表名占位符，确保每个表的查询语句正确生成
• 如果需要过滤特定表，直接在WHERE子句添加条件即可（比如注释中的表名前缀过滤）
• 注意：如果单表数据量极大，ARRAY_AGG可能触发内存限制，此时需要结合分批逻辑处理，但该方案适用于大多数常规数据量的表备份场景

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