BigQuery动态对列数不确定的多列执行字符串替换方法

核心思路：通过BigQuery内置的INFORMATION_SCHEMA元数据表自动识别所有符合命名规则的目标列，动态生成SELECT * REPLACE()语法的执行语句，无需硬编码列名，100%保留原表列名、列顺序和非目标列内容，自动适配任意增减的statement_nn列。

直接使用以下可执行SQL，仅需要修改开头3个参数为你的实际业务值即可：

-- 替换为你的实际业务参数
DECLARE target_table STRING DEFAULT 'project.dataset.your_table_name';
DECLARE match_segment STRING DEFAULT '需要被替换的指定字符串片段';
DECLARE replace_segment STRING DEFAULT '替换后的目标内容';

-- 自动拉取所有符合规则的列，拼接替换逻辑
DECLARE replace_clause STRING;
SET replace_clause = (
  SELECT STRING_AGG(
    FORMAT("REPLACE(%s, '%s', '%s') AS %s", column_name, match_segment, replace_segment, column_name),
    ','
  )
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = EXTRACT(TABLE FROM target_table)
    AND table_schema = EXTRACT(SCHEMA FROM target_table)
    AND STARTS_WITH(column_name, 'statement_')
    AND data_type = 'STRING' -- 仅处理字符串类型的目标列，避免类型报错
);

-- 拼接最终查询语句
DECLARE final_query STRING;
SET final_query = FORMAT("""
  SELECT * REPLACE(%s)
  FROM %s
""", replace_clause, target_table);

-- 执行语句返回结果
EXECUTE IMMEDIATE final_query;

使用注意事项 #

• 如果替换的字符串片段包含单引号，只需要将单引号转义为两个连续单引号即可，例如待替换内容为user's，参数值写为'user''s'
• 逻辑会自动过滤所有非statement_前缀的列，主键、时间戳、维度字段等其他内容会原样保留，不会修改原有表结构
• 后续表新增statement_前缀的列时不需要修改代码，脚本会自动识别新列执行替换
• 如果需要将处理后的结果写入新表，只需要修改final_query的拼接规则，在SELECT前增加CREATE OR REPLACE TABLE project.dataset.new_table_name AS即可
• 该方案没有行列转换的额外开销，性能远高于数组聚合后再处理的方案，也不会出现数组方案丢失原列名的问题——数组方案本质是将多列打平为行结构处理，若后续没有做行转列映射原列名，必然会出现结构和原表不一致的问题。

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