如何批量删除BigQuery大量数据表 解决动态SQL删表报错问题

你写的动态SQL报错核心原因是：BigQuery的EXECUTE IMMEDIATE搭配USING传参时，仅支持替换字面量值（比如过滤条件里的字符串、数字参数），不能替换数据集名、表名、列名这类标识符。你写的?占位符放在反引号内作为标识符位置时，不会被替换成传入的record.dataset_id、record.table_id值，引擎会直接把?当成真实的数据集ID解析，自然触发ID格式不合法的错误。

另外你查询元数据用的__TABLES__是遗留的元数据系统表，更推荐用标准的INFORMATION_SCHEMA获取元数据，字段含义更清晰，还能直接区分普通表、视图、物化视图，避免误删。

用FORMAT函数拼接带标识符的动态SQL语句即可，执行前建议先打印待删除表列表做校验，避免误删：

1. 先校验待删除表范围：

-- 先执行这段，确认输出的表都是你要删除的
FOR record IN (
  SELECT 
    table_catalog AS project_id,
    table_schema AS dataset_id,
    table_name AS table_id
  FROM `你的项目名.mydataset.INFORMATION_SCHEMA.TABLES`
  WHERE 
    table_type = 'BASE TABLE' -- 只删普通表，排除视图
    -- 这里替换成你自己的筛选条件，比如按表名前缀、创建时间筛选
    AND table_name LIKE 'tmp_delete_%'
    AND creation_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
)
DO
  SELECT FORMAT("待删除表：`%s`.`%s`.`%s`", record.project_id, record.dataset_id, record.table_id););
END FOR;

2. 确认列表无误后，替换为删表语句执行：

FOR record IN (
  SELECT 
    table_catalog AS project_id,
    table_schema AS dataset_id,
    table_name AS table_id
  FROM `你的项目名.mydataset.INFORMATION_SCHEMA.TABLES`
  WHERE 
    table_type = 'BASE TABLE'
    -- 和校验阶段保持完全一致的筛选条件
    AND table_name LIKE 'tmp_delete_%'
    AND creation_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
)
DO
  EXECUTE IMMEDIATE FORMAT("DROP TABLE `%s`.`%s`.`%s`", record.project_id, record.dataset_id, record.table_id);
END FOR;

如果本地装了gCloud SDK（含bq命令行工具），可以直接用命令完成筛选+删表，不需要写SQL循环：

# 示例：删除mydataset下所有以tmp_开头的普通表
bq ls --max_results=10000 --format=prettyjson mydataset | \
  jq -r '.[] | select(.type == "TABLE" and (.tableId | startswith("tmp_"))) | .tableId' | \
  xargs -I {} bq rm -f -t mydataset.{}

参数说明：

• --max_results=10000：指定最多拉取10000张表的元数据，默认值只有100，表多的时候要调大
• jq：用来过滤JSON格式的元数据，筛选符合条件的表名
• bq rm -f -t：-f表示跳过二次确认提示，-t表示操作对象是表，避免误删数据集

如果待删表数量在几十张量级，也可以直接在BigQuery控制台的数据集资源列表里，按住Ctrl/Cmd多选目标表后点删除，操作更直观。

• 批量删表属于高危操作，必须先做范围校验，确认待删列表完全符合预期再执行删除
• 如果表内有重要数据，建议删前先做表快照备份，避免误删后无法恢复

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