如何轻松将后缀含日期的旧分区表转为BigQuery新分区表

我刚好处理过类似的需求，其实核心就是把旧表名里的日期后缀提取出来，转换成BigQuery的DATE类型后填充到_partitiondate字段里，下面给你详细的实现步骤和两种常用方法：

首先要基于旧表的Schema创建带日期分区的新表，你可以直接拿任意一张旧表作为Schema模板，只建表不导入数据：

CREATE OR REPLACE TABLE `your-project.your-dataset.new_partitioned_table`
PARTITION BY _partitiondate
OPTIONS(
  partition_type = 'DAY',
  require_partition_filter = TRUE -- 可选，强制查询时指定分区过滤，优化性能和成本
)
AS SELECT * EXCEPT (
  -- 这里可以列出旧表中不需要保留的字段，没有的话就删掉这行
)
FROM `your-project.your-dataset.mytable_20240101` -- 随便选一张旧表当模板
WHERE FALSE; -- 关键：只复制表结构，不导入数据

这里提供两种实用方法，你可以根据自己的场景选择：

方法一：用BigQuery SQL脚本批量处理 #

适合手动执行或者在BigQuery控制台里运行，通过游标遍历所有旧表，动态提取日期并插入：

DECLARE table_cursor CURSOR FOR
SELECT table_name
FROM `your-project.your-dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'mytable_%';

DECLARE current_table STRING;
OPEN table_cursor;

LOOP
  FETCH table_cursor INTO current_table;
  IF NOT FOUND THEN LEAVE; END IF;
  
  -- 提取表名里的日期后缀，比如mytable_20240101 → 20240101
  DECLARE partition_date DATE PARSE_DATE('%Y%m%d', SUBSTR(current_table, LENGTH('mytable_') + 1));
  
  -- 动态执行插入，把日期填充到_partitiondate
  EXECUTE IMMEDIATE FORMAT("""
    INSERT INTO `your-project.your-dataset.new_partitioned_table`
    SELECT *, %L AS _partitiondate
    FROM `your-project.your-dataset.%s`
  """, partition_date, current_table);
END LOOP;

CLOSE table_cursor;

⚠️ 注意：如果你的日期后缀格式不是YYYYMMDD（比如是YYYY-MM-DD），要把PARSE_DATE里的格式符改成%Y-%m-%d。

方法二：用bq命令行工具自动化 #

适合写脚本定时执行，或者处理超大量表的场景：

PROJECT="your-project"
DATASET="your-dataset"
NEW_TABLE="new_partitioned_table"

# 遍历所有符合命名规则的旧表
for TABLE in $(bq ls --project_id=$PROJECT $DATASET | grep 'mytable_' | awk '{print $1}'); do
  # 提取日期后缀（去掉前缀mytable_）
  DATE_SUFFIX=${TABLE#mytable_}
  # 转换为BigQuery支持的DATE格式（如果后缀是YYYYMMDD，转成YYYY-MM-DD）
  PARTITION_DATE=$(date -d $DATE_SUFFIX +%Y-%m-%d)
  
  # 执行插入命令
  bq query --project_id=$PROJECT --use_legacy_sql=false """
    INSERT INTO \`$PROJECT.$DATASET.$NEW_TABLE\`
    SELECT *, DATE('$PARTITION_DATE') AS _partitiondate
    FROM \`$PROJECT.$DATASET.$TABLE\`
  """
done

插入完成后，你可以用下面的SQL检查分区日期和数据量是否匹配：

SELECT _partitiondate, COUNT(*) AS row_count
FROM `your-project.your-dataset.new_partitioned_table`
GROUP BY _partitiondate
ORDER BY _partitiondate;

额外提醒 #

• 如果旧表数据量极大，建议分批次插入，或者用BigQuery的批量加载工具，避免一次性插入导致性能瓶颈。
• 如果旧表有重复数据，可以在SELECT语句里加上DISTINCT，或者用MERGE语句代替INSERT来做去重同步。
• 确保你的账号拥有旧表的读取权限、新表的创建和写入权限。

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