如何解决Snowflake中JSON列名与表列名不匹配的COPY INTO加载问题

问题根源 #

你遇到的报错是因为Snowflake默认处理JSON文件时，会将整个JSON对象解析为单个Variant类型列，而你直接指定目标表的多个字符串列，导致格式冲突。同时JSON中的键名带空格（my name、my city、my team），与表的下划线命名列（my_name、my_city、my_team）不匹配，需要针对性处理。

解决方案 #

以下两种方法均可解决问题，根据你的需求选择：

方法1：直接通过子查询映射字段（推荐）

在COPY语句的FROM子句中，提取JSON中的带空格键值，转换类型后映射到目标表的列：

COPY INTO dummy_data (my_name, my_city, my_team, id)
FROM (
    SELECT 
        $1:"my name"::VARCHAR,  -- 提取JSON中带空格的键并转VARCHAR
        $1:"my city"::VARCHAR,
        $1:"my team"::VARCHAR,
        NULL AS id  -- 若id列允许NULL，否则替换为有效值（如UUID_STRING()、SEQ4()）
    FROM @mystage/mydetails.json
)
FILE_FORMAT = MY_JSON_FORMAT;

方法2：使用MATCH_BY_COLUMN_NAME匹配列名

先在子查询中将JSON字段重命名为与表列一致的名称，再启用列名匹配选项：

COPY INTO dummy_data
FROM (
    SELECT 
        $1:"my name"::VARCHAR AS my_name,
        $1:"my city"::VARCHAR AS my_city,
        $1:"my team"::VARCHAR AS my_team,
        UUID_STRING() AS id  -- 示例：生成UUID作为id值
    FROM @mystage/mydetails.json
)
FILE_FORMAT = MY_JSON_FORMAT
MATCH_BY_COLUMN_NAME = EXACT;  -- 精确匹配列名，也可使用CASE_INSENSITIVE忽略大小写

关键注意事项 #

• 如果你的JSON文件是数组格式（如[{"my name":...}, {...}]），需要在文件格式中设置STRIP_OUTER_ARRAY = TRUE，否则需要用FLATTEN($1)解析数组元素。
• 执行COPY前，可单独运行子查询部分（SELECT ... FROM @mystage/mydetails.json）验证提取的数据是否正确。
• 若id列不允许NULL，必须为其提供有效值，比如序列、时间戳或业务逻辑生成的ID。

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