Snowflake展开Variant列无结果，疑外层数组未正确剥离

• Python代码错误：你用r.text获取API返回的JSON字符串后，又调用json.dumps(data)，这会把原JSON字符串再次转义，导致上传到S3的文件内容是一个被引号包裹、内部带转义字符的字符串，而非原生JSON数组。比如原数据[{"id":1,...}]会变成"[{\"id\":1,...}]"。
• Snowflake加载异常：由于文件内容是字符串而非JSON数组，即使设置了STRIP_OUTER_ARRAY = TRUE也无法识别外层数组，最终加载到raw_source表的SRC列是一个字符串类型的Variant值，而非JSON数组，所以FLATTEN无法展开返回结果。

1. 修正Python代码，重新上传正确的JSON数据 #

把原代码中处理数据的部分修改为直接使用API返回的原生JSON内容，避免二次转义：

import requests
import boto3
import json

r = requests.get('https://fakestoreapi.com/products')
# 直接获取解析后的JSON对象，再转为正确的JSON字符串
data = json.dumps(r.json())

s3 = boto3.client('s3',
    aws_access_key_id='key_entered_here',
    aws_secret_access_key='secret_entered_here')

s3.put_object(
     Body=data,
     Bucket='s3_bucket_name_entered_here',
     Key='products.json'
)

或者更高效的方式，直接使用API返回的字节流：

import requests
import boto3

r = requests.get('https://fakestoreapi.com/products')
r.raise_for_status()  # 确保请求成功

s3 = boto3.client('s3',
    aws_access_key_id='key_entered_here',
    aws_secret_access_key='secret_entered_here')

s3.put_object(
     Body=r.content,
     Bucket='s3_bucket_name_entered_here',
     Key='products.json'
)

2. 清理Snowflake中错误加载的数据（可选） #

如果已经加载了错误的数据，先清空表：

TRUNCATE TABLE raw_source;

3. 重新加载数据到Snowflake #

原有的文件格式和外部阶段配置是正确的，直接重新执行COPY命令：

COPY INTO raw_source
  FROM @FakeStore.Public.FakeStore_Stage
  FILE_FORMAT = JSON_FORMAT;

此时查询SELECT src FROM raw_source;，每条记录应该是单个产品的JSON对象，而非外层带数组的字符串。

4. 错误数据应急处理（无需重新上传的情况） #

如果因为某些原因无法重新上传数据，也可以处理现有错误数据：先把字符串转为JSON数组，再执行FLATTEN，同时注意rating是嵌套对象，需正确提取内部字段：

SELECT
     final.value:id::number as id
    ,final.value:title::varchar as title
    ,final.value:price::number as price
    ,final.value:description::varchar as description
    ,final.value:category::varchar as category
    ,final.value:image::varchar as image
    ,final.value:rating:rate::number as rate
    ,final.value:rating:count::number as count
FROM raw_source AS rs,
LATERAL FLATTEN(input => PARSE_JSON(rs.src)) as final;

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