如何将含JSON的Avro文件导入Snowflake并转换为表结构？

我明白你现在的困惑——作为Snowflake新手，处理嵌套的Avro+JSON数据确实容易卡壳，而且你之前的中间表确实是冗余的，完全可以用一个COPY INTO语句搞定所有转换逻辑。

先说说你之前报错的原因 #

你最后一步用COPY INTO data_table FROM (SELECT ... FROM avro_as_json_table)触发错误，是因为COPY INTO本质是直接从外部存储/Stage读取数据的导入语句，它不支持从已存在的Snowflake表做数据源（这时候应该用INSERT INTO ... SELECT ...）。但咱们根本不需要这步，把解码和解析逻辑合并到初始的COPY INTO里就行。

完整解决方案：单语句完成Avro解码+JSON解析+导入 #

下面是修改后的完整代码，我会一步步解释关键部分：

-- 1. 初始化数据库和目标表（和你之前的逻辑一致）
CREATE DATABASE IF NOT EXISTS MY_DB; 
USE DATABASE MY_DB; 
CREATE OR REPLACE TABLE data_table( 
    "column1" STRING, 
    "column2" INTEGER, 
    "column3" STRING 
); 

-- 2. 创建Avro文件格式（保持不变）
CREATE OR REPLACE FILE FORMAT av_avro_format 
    TYPE = 'AVRO' 
    COMPRESSION = 'NONE'; 

-- 3. 创建指向Azure Blob的Stage（保持不变，注意替换你的SAS令牌和路径）
CREATE OR REPLACE STAGE st_capture_avros 
    URL='azure://xxxxxxx.blob.core.windows.net/xxxxxxxx/xxxxxxxxx/xxxxxxx/1/' 
    CREDENTIALS=(AZURE_SAS_TOKEN='?xxxxxxxxxxxxx') 
    FILE_FORMAT = av_avro_format; 

-- 4. 核心：单COPY INTO完成所有转换
COPY INTO data_table("column1", "column2", "column3")
FROM (
    SELECT 
        -- 把Avro里的Body字段（十六进制编码）解码成JSON字符串，再转成JSON对象
        PARSE_JSON(HEX_DECODE_STRING($1:Body)):"jsonKeyValue1" AS column1,
        PARSE_JSON(HEX_DECODE_STRING($1:Body)):"jsonKeyValue2" AS column2,
        PARSE_JSON(HEX_DECODE_STRING($1:Body)):"jsonKeyValue3" AS column3
    FROM @st_capture_avros
);

关键逻辑拆解 #

• $1：代表从Stage读取的每一条Avro记录
• $1:Body：提取Avro记录里名为Body的字段（这个字段是十六进制格式的JSON字符串）
• HEX_DECODE_STRING()：把十六进制的Body转换成可读的JSON字符串
• PARSE_JSON()：把JSON字符串转换成Snowflake可操作的JSON对象，这样就能用:"键名"提取对应的值
• 最后直接把提取的字段映射到data_table的列，一步完成导入

额外优化（可选） #

如果担心重复解析JSON影响性能，可以用LET子句把解析后的JSON对象存为变量，避免重复计算：

COPY INTO data_table("column1", "column2", "column3")
FROM (
    SELECT 
        parsed_body:"jsonKeyValue1" AS column1,
        parsed_body:"jsonKeyValue2" AS column2,
        parsed_body:"jsonKeyValue3" AS column3
    FROM @st_capture_avros
    LET parsed_body = PARSE_JSON(HEX_DECODE_STRING($1:Body))
);

这样写更简洁，性能也更好~

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