如何在Snowflake外部S3 Stage的JSON文件上创建视图

嘿，这事儿其实不难，咱们直接调整下FLATTEN的用法就行！之前你处理带Data节点的JSON时，是把FLATTEN的输入指向$1:Data，但现在你的JSON顶层就是数组，直接用$1作为FLATTEN的输入就搞定了。

我给你一步步拆解：

1. 先理清楚数据结构：你的JSON是[{ 'key1':val1, 'key2':val2 }, { 'key1':val1, 'key2':val2 }]这种顶层数组，Snowflake读取文件后，$1就代表整个数组内容。

2. 用FLATTEN展开数组：通过LATERAL FLATTEN(input => $1)把数组里的每个JSON对象拆成单独的行，每行对应一个value（也就是数组里的单个对象）。

3. 提取字段并转换类型：从value里提取key1、key2这些字段，记得根据实际数据类型加转换，比如字符串用::STRING，数字用::INT，日期用::DATE之类的。

完整的创建视图示例 #

假设你的S3外部stage是@my_s3_stage，JSON文件是data.json，那创建视图的SQL可以这么写：

CREATE OR REPLACE VIEW my_json_view AS
SELECT
    f.value:key1::STRING AS key1,  -- 按需调整类型，比如val1是数字就改成::INT
    f.value:key2::INT AS key2
FROM
    @my_s3_stage/data.json,  -- 如果要读取stage下所有JSON文件，去掉文件名即可
    LATERAL FLATTEN(input => $1) f;

先验证数据再创建视图（可选） #

你可以先跑下面的查询确认结果没问题，再创建视图：

SELECT
    f.value:key1::STRING,
    f.value:key2::INT
FROM
    @my_s3_stage/data.json,
    LATERAL FLATTEN(input => $1) f;

要是你的stage里有多个同结构的JSON文件，直接写@my_s3_stage就行，Snowflake会自动遍历所有文件里的顶层数组并展开。

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