如何在Google BigQuery中批量更新150+表及缺失描述的列？

针对你遇到的150+表、每张表百余个列导致ALTER语句过多的问题，可以通过批量生成并执行语句的方式大幅减少执行次数，同时精准只更新缺失描述的对象，具体实现思路如下：

核心思路 #

1. 利用BigQuery的INFORMATION_SCHEMA元数据视图，筛选出当前没有描述的表和列
2. 通过预定义的描述配置表（提前维护好所有表/列的目标描述），匹配需要更新的对象
3. 对同一个表的所有待更新列，将ALTER COLUMN语句合并成一个批量操作，每张表仅执行一次EXECUTE IMMEDIATE

具体实现步骤 #

1. 创建描述配置表 #

先建一张表存储所有需要更新的表和列的描述信息，结构如下：

CREATE OR REPLACE TABLE `your-project.your-dataset.schema_descriptions` (
  dataset_id STRING NOT NULL,
  table_id STRING NOT NULL,
  column_name STRING, -- 为NULL时代表是表描述
  table_description STRING,
  column_description STRING
);

把150多张表的表描述、缺失描述的列的目标描述导入这张表。

2. 编写批量更新存储过程 #

这个存储过程会自动遍历配置表，只处理当前无描述的表/列，且批量执行列更新语句：

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.update_missing_descriptions`()
BEGIN
  DECLARE done BOOL DEFAULT FALSE;
  DECLARE dataset_id, table_id, target_table_desc STRING;
  
  -- 第一步：更新缺失描述的表
  DECLARE table_cursor CURSOR FOR
    SELECT DISTINCT s.dataset_id, s.table_id, s.table_description
    FROM `your-project.your-dataset.schema_descriptions` s
    LEFT JOIN `your-region`.INFORMATION_SCHEMA.TABLES t
    ON s.dataset_id = t.table_schema AND s.table_id = t.table_name
    WHERE COALESCE(t.table_description, '') = ''; -- 匹配无描述的表

  OPEN table_cursor;
  FETCH table_cursor INTO dataset_id, table_id, target_table_desc;
  WHILE NOT done DO
    -- 转义描述中的单引号避免语法错误
    SET target_table_desc = REPLACE(target_table_desc, "'", "\\'");
    EXECUTE IMMEDIATE FORMAT("""
      ALTER TABLE `%s.%s` SET DESCRIPTION '%s'
    """, dataset_id, table_id, target_table_desc);
    
    FETCH table_cursor INTO dataset_id, table_id, target_table_desc;
    IF NOT FOUND THEN SET done = TRUE; END IF;
  END WHILE;
  CLOSE table_cursor;

  -- 第二步：批量更新同一张表中缺失描述的列
  SET done = FALSE;
  DECLARE dataset_id_col, table_id_col, batch_alter_stmt STRING;
  
  DECLARE column_cursor CURSOR FOR
    SELECT 
      s.dataset_id,
      s.table_id,
      -- 将同一张表的所有列更新语句合并为一个字符串
      STRING_AGG(
        FORMAT(
          "ALTER COLUMN `%s` SET DESCRIPTION '%s'",
          REPLACE(s.column_name, "`", "\\`"), -- 转义列名中的反引号
          REPLACE(s.column_description, "'", "\\'")
        ),
        ";\n"
      ) AS batch_statements
    FROM `your-project.your-dataset.schema_descriptions` s
    LEFT JOIN `your-region`.INFORMATION_SCHEMA.COLUMNS c
    ON s.dataset_id = c.table_schema 
    AND s.table_id = c.table_name
    AND s.column_name = c.column_name
    WHERE COALESCE(c.column_description, '') = '' -- 匹配无描述的列
      AND s.column_name IS NOT NULL
    GROUP BY s.dataset_id, s.table_id;

  OPEN column_cursor;
  FETCH column_cursor INTO dataset_id_col, table_id_col, batch_alter_stmt;
  WHILE NOT done DO
    IF batch_alter_stmt IS NOT NULL THEN
      EXECUTE IMMEDIATE FORMAT("""
        ALTER TABLE `%s.%s`
        %s
      """, dataset_id_col, table_id_col, batch_alter_stmt);
    END IF;
    
    FETCH column_cursor INTO dataset_id_col, table_id_col, batch_alter_stmt;
    IF NOT FOUND THEN SET done = TRUE; END IF;
  END WHILE;
  CLOSE column_cursor;
END;

3. 执行存储过程 #

直接调用存储过程即可完成批量更新：

CALL `your-project.your-dataset.update_missing_descriptions`();

关键优化点 #

• 减少执行次数：原本每张表100个列需要100次ALTER操作，现在每张表仅需1次EXECUTE IMMEDIATE执行所有列的更新语句
• 精准筛选：通过INFORMATION_SCHEMA仅处理当前无描述的表/列，避免重复操作
• 特殊字符处理：自动转义列名中的反引号、描述中的单引号，避免SQL语法错误
• 可扩展性：后续新增表/列只需更新配置表，无需修改存储过程

注意：替换代码中的your-project、your-dataset、your-region为实际的项目ID、数据集ID和BigQuery区域（如region-us、region-eu）。

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