在Google BigQuery加载数据时重命名含特殊字符的列

可以在加载过程中直接处理特殊字段名，无需预处理源数据，具体方案如下：

• 创建能识别特殊字段的外部表
  用反引号（`）将JSON中带空格、连字符的字段名括起来，BigQuery会正确匹配对应字段：

CREATE OR REPLACE EXTERNAL TABLE `<proj>.<dataset>.<raw_external_table>` (
    teams_remove STRUCT<
        team_a STRING,
        team_b STRING,
        `winning team` STRING,
        `losing-manager` STRING
    >
)
OPTIONS (
    format = 'JSON',
    uris = ['gs://your-bucket/path/to/json/*.json'] -- 替换为你的JSON文件存储路径
);

• 创建视图重命名为合法列名
  通过视图将特殊字段名转换为符合BigQuery规范的名称（无空格、连字符），后续业务查询直接使用该视图即可：

CREATE OR REPLACE VIEW `<proj>.<dataset>.<clean_view>` AS
SELECT
    STRUCT(
        teams_remove.team_a,
        teams_remove.team_b,
        teams_remove.`winning team` AS winning_team,
        teams_remove.`losing-manager` AS losing_manager
    ) AS teams_remove
FROM `<proj>.<dataset>.<raw_external_table>`;

• 直接提取并重命名的简化方式
  如果不想额外创建原始外部表，也可以直接通过JSON_EXTRACT_SCALAR函数在创建外部表时完成字段提取与重命名：

CREATE OR REPLACE EXTERNAL TABLE `<proj>.<dataset>.<final_table>`
OPTIONS (
    format = 'JSON',
    uris = ['gs://your-bucket/path/to/json/*.json']
) AS
SELECT
    STRUCT(
        JSON_EXTRACT_SCALAR(raw_json, '$.teams_remove.team_a') AS team_a,
        JSON_EXTRACT_SCALAR(raw_json, '$.teams_remove.team_b') AS team_b,
        JSON_EXTRACT_SCALAR(raw_json, '$.teams_remove."winning team"') AS winning_team,
        JSON_EXTRACT_SCALAR(raw_json, '$.teams_remove."losing-manager"') AS losing_manager
    ) AS teams_remove
FROM (SELECT * FROM `<proj>.<dataset>.<raw_json_table>`);

注：<raw_json_table>是一个未指定Schema的外部表，BigQuery会自动将整个JSON内容作为raw_json字符串字段读取。

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