如何在BigQuery中对所有分区表执行列类型转换并写入？

Great question! Handling batch schema updates across multiple partitioned tables in BigQuery doesn’t have to be a tedious task—there are a few streamlined approaches to get this done without writing repetitive SQL for each table. Let’s walk through the most practical options:

方法1：BigQuery 脚本（推荐，无需额外工具） #

This approach lets you automate the entire process directly in the BigQuery console using scripting. It uses INFORMATION_SCHEMA to fetch all your partitioned tables, then loops through each to update the target column.

完整脚本示例 #

DECLARE table_list ARRAY<STRING>;
DECLARE current_table STRING;
DECLARE i INT64 DEFAULT 1;

-- Step 1: 获取数据集内所有分区表
SET table_list = ARRAY(
  SELECT CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`')
  FROM `your_project_id.your_target_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
    AND is_partitioned = 'YES'
  -- 可选：添加表名过滤条件，比如 AND table_name LIKE 'sales_%'
);

-- Step 2: 循环处理每个表，更新目标列
WHILE i <= ARRAY_LENGTH(table_list) DO
  SET current_table = table_list[OFFSET(i-1)];
  
  -- 用CREATE OR REPLACE原子性更新表（保留分区配置）
  EXECUTE IMMEDIATE FORMAT("""
    CREATE OR REPLACE TABLE %s
    PARTITION BY DATE(_PARTITIONTIME) -- 调整为你的表实际分区字段，比如 DATE(order_date)
    AS
    SELECT * EXCEPT(nums), CAST(nums AS STRING) AS nums
    FROM %s
  """, current_table, current_table);
  
  SET i = i + 1;
END WHILE;

替代方案：原地修改列（适合不想重建表的场景） #

如果你更倾向于原地修改而非重建表（注意：这需要多步ALTER/UPDATE操作）：

EXECUTE IMMEDIATE FORMAT("""
  -- 1. 添加临时列存储转换后的数据
  ALTER TABLE %s ADD COLUMN nums_temp STRING;
  
  -- 2. 向临时列写入转换后的值（更新所有分区）
  UPDATE %s
  SET nums_temp = CAST(nums AS STRING)
  WHERE TRUE;
  
  -- 3. 删除原列
  ALTER TABLE %s DROP COLUMN nums;
  
  -- 4. 将临时列重命名为原列名
  ALTER TABLE %s RENAME COLUMN nums_temp TO nums;
""", current_table, current_table, current_table, current_table);

方法2：命令行批量处理（适合熟悉shell的用户） #

如果你习惯使用bq命令行工具，可以用bash脚本完成批量处理：

1. 先将分区表列表导出到文本文件：

bq query --format=csv "SELECT CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') FROM `your_project_id.your_target_dataset.INFORMATION_SCHEMA.TABLES` WHERE is_partitioned='YES'" | tail -n +2 > partitioned_tables.txt

2. 循环读取文件处理每个表：

while read table; do
  bq query --use_legacy_sql=false "
    CREATE OR REPLACE TABLE $table
    PARTITION BY DATE(_PARTITIONTIME)
    AS
    SELECT * EXCEPT(nums), CAST(nums AS STRING) AS nums
    FROM $table
  "
done < partitioned_tables.txt

关键注意事项 #

• 先测试再落地: 一定要先在测试表或数据副本上验证脚本，再应用到生产表。
• 成本考量: 全表扫描和写入会产生BigQuery费用，提前确认表大小和预算。
• 分区配置匹配: 确保脚本中的PARTITION BY子句和你的表实际分区策略一致（比如有些表用自定义日期列而非_PARTITIONTIME）。
• 权限要求: 确保你拥有足够的权限（比如bigquery.dataEditor、bigquery.jobUser）来修改表和执行查询。

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