如何在BigQuery脚本中为表名添加参数以批量创建副本表？

要批量处理符合project.dataset.ffm_{lang}_{event}命名规则的表，创建带自定义聚类和分区的ffm_demo_{lang}_{event}副本，你可以用BigQuery的SQL脚本结合动态SQL来实现，替代参数化查询或通配符的局限，具体步骤如下：

1. 筛选目标原表并提取参数 #

先通过INFORMATION_SCHEMA.TABLES获取所有符合命名模式的表，同时提取lang和event参数：

SELECT 
  table_name,
  SPLIT(REGEXP_EXTRACT(table_name, r'ffm_(\w+)_(\w+)'), '_')[OFFSET(0)] AS lang,
  SPLIT(REGEXP_EXTRACT(table_name, r'ffm_(\w+)_(\w+)'), '_')[OFFSET(1)] AS event
FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'ffm_%_%'
  AND table_type = 'BASE TABLE';

执行这个查询可以确认要处理的表是否正确，避免后续批量操作出错。

2. 动态批量创建副本表 #

用BigQuery的脚本功能循环生成并执行CREATE TABLE语句，同时指定聚类和分区配置：

DECLARE tables_to_copy ARRAY<STRUCT<table_name STRING, lang STRING, event STRING>>;

-- 加载需要复制的表列表
SET tables_to_copy = (
  SELECT ARRAY_AGG(STRUCT(
    table_name,
    SPLIT(REGEXP_EXTRACT(table_name, r'ffm_(\w+)_(\w+)'), '_')[OFFSET(0)] AS lang,
    SPLIT(REGEXP_EXTRACT(table_name, r'ffm_(\w+)_(\w+)'), '_')[OFFSET(1)] AS event
  ))
  FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE 'ffm_%_%'
    AND table_type = 'BASE TABLE'
);

-- 循环创建副本表
FOR table_item IN UNNEST(tables_to_copy) DO
  EXECUTE IMMEDIATE FORMAT("""
    CREATE TABLE `project.dataset.ffm_demo_%s_%s`
    LIKE `project.dataset.%s`
    OPTIONS(
      -- 替换为你的实际分区和聚类配置
      partition_by = DATE(created_at), -- 示例：按created_at字段的日期分区
      clustering_fields = ['user_id', 'category'] -- 示例：按user_id和category字段聚类
    )
  """, table_item.lang, table_item.event, table_item.table_name);
END FOR;

关键说明 #

• EXECUTE IMMEDIATE用于动态执行拼接好的SQL语句，FORMAT函数负责替换表名中的lang和event参数，完美解决参数化查询不能传表名的问题。
• OPTIONS中的配置可以根据实际需求调整：如果原表已有分区/聚类，LIKE会继承这些设置，你可以在OPTIONS中覆盖或新增规则。
• 替换脚本中的project.dataset为你的实际项目和数据集名称，同时调整分区、聚类字段为表中存在的字段。

注意事项 #

• 确保你拥有创建表的权限，以及读取INFORMATION_SCHEMA的权限。
• 建议先单独执行第一步的查询，确认筛选出的表列表无误后，再运行完整的脚本。
• 如果需要复制表数据，在CREATE TABLE后添加AS SELECT * FROM原表即可，比如：

  CREATE TABLE `project.dataset.ffm_demo_%s_%s`
  LIKE `project.dataset.%s`
  OPTIONS(...)
  AS SELECT * FROM `project.dataset.%s`
  

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