如何通过Snowpipe将含root键的JSON加载至Snowflake表？附示例

现有存储在S3桶中的带root键的JSON数据，需要将其中的行数据加载至Snowflake表：

{   
   "root":[  
      {
         "id":1,  
         "kind":"person",
         "fullName":"John Doe",
         "age":22,
         "gender":"Male"
      },
      {
         "id":2,  
         "kind":"person",
         "fullName":"Mike Jones",
         "age":35,
         "gender":"Male"
      },
      {
         "id":3,  
         "kind":"person",
         "fullName":"Jane Doe",
         "age":22,
         "gender":"Female"
      }
   ]
}

请问：

• 是否可通过Snowpipe直接处理该带root键的格式？
• 对应的查询语句示例是什么？
• 是否必须将格式修改为移除root键的纯数组形式（如下格式已验证可正常加载，但希望避免预处理）：

[  
    {  
       "id":1,
       "kind":"person",
       "fullName":"John Doe",
       "age":22,
       "gender":"Male"
    },
    {
       "id":2,  
       "kind":"person",
       "fullName":"Mike Jones",
       "age":35,
       "gender":"Male"
    },
    {
       "id":3,  
       "kind":"person",
       "fullName":"Jane Doe",
       "age":22,
       "gender":"Female"
    }
]

已知通过Snowflake查询可实现处理，但暂未找到Snowpipe的实现方式，求具体的Ingestion示例。

1. 无需修改JSON格式，Snowpipe可直接处理带root键的结构 #

不需要预处理移除root键，Snowpipe配合Snowflake的半结构化数据解析能力，就能直接提取数组内的行数据。

2. 具体Ingestion实现示例 #

步骤1：创建目标表

先创建匹配数据结构的Snowflake表：

CREATE OR REPLACE TABLE PERSONS (
    ID INT,
    KIND STRING,
    FULL_NAME STRING,
    AGE INT,
    GENDER STRING
);

步骤2：创建指向S3桶的外部阶段

假设已完成S3存储集成的权限配置，创建外部阶段：

CREATE OR REPLACE STAGE S3_PERSONS_STAGE
URL = 's3://your-bucket-path/'
STORAGE_INTEGRATION = your_s3_integration
FILE_FORMAT = (TYPE = JSON);

步骤3：创建Snowpipe管道

在COPY INTO语句中指定解析逻辑，直接展开root数组并提取字段：

CREATE OR REPLACE PIPE PERSONS_PIPE
AUTO_INGEST = TRUE
AS
COPY INTO PERSONS (ID, KIND, FULL_NAME, AGE, GENDER)
SELECT
    value:id::INT,
    value:kind::STRING,
    value:"fullName"::STRING,
    value:age::INT,
    value:gender::STRING
FROM @S3_PERSONS_STAGE,
LATERAL FLATTEN(input => $1:root); -- 核心：用FLATTEN展开root数组为多行

步骤4：触发数据加载（可选）

若开启了AUTO_INGEST，S3有新文件时会自动触发加载；如需手动测试：

ALTER PIPE PERSONS_PIPE REFRESH;

3. 核心逻辑说明 #

• LATERAL FLATTEN(input => $1:root)会将JSON中的root数组展开为独立的行记录，每行对应一个person对象。
• 通过value:字段名提取对象属性，并通过::数据类型完成类型转换，直接映射到目标表的对应列。

参考说明 #

基于Snowflake官方社区的JSON解析方案，核心是利用Snowflake的半结构化数据处理能力，在数据加载阶段直接完成数组展开和字段提取，无需提前修改源JSON文件。

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