Snowflake - 解析JSON错误：字符串外无效字符：'/

Question

Snowflake让我在把JSON数据加载到表格时头疼不已。

通过外部阶段用airflow dag任务加载JSON文件时，出现了这个错误：

错误 - 执行任务copy_raw_data的作业856失败 (100069 (22P02): 01b2ef42-3201-1fd5-0000-00052d827631: 解析JSON时出错：字符串外的无效字符：'/' 文件 'transactions/transactions.json'，第1行，第1个字符 第0行，第$1列

这是返回错误的示例JSON代码：

[
    {
        "id": "3f8e52c7f8d84f63a28c7d3174a825e7",
        "date": "01102023",
        "items": [
            {
                "product_id": "10052645",
                "price": 995.0
            },
            {
                "product_id": "10206747",
                "price": 3599.0
            }
        ],
        "customer": {
            "id": 1,
            "name": "Virginie Paradis",
            "email": "VirginieParadis@email.com",
            "address": "1095 allé Lemire, L'-L'-Nadeau, QC Y1Y 9A3",
            "phone": "945-434-8939x061",
            "card_number": "********5089"
        }
    }
]

这是airflow任务：

    copy_raw_data = SnowflakeOperator(
    task_id='copy_raw_data',
    snowflake_conn_id='snowflake_staging_conn',
    sql="""
        COPY INTO "STORE"."STAGING"."RAW_DATA"
        FROM @s3_data_pipeline_repo
        FILES=('transactions.json')
        FILE_FORMAT=(
            TYPE=JSON,
            STRIP_OUTER_ARRAY=TRUE,
            REPLACE_INVALID_CHARACTERS=TRUE,
            DATE_FORMAT=AUTO,
            TIME_FORMAT=AUTO,
            TIMESTAMP_FORMAT=AUTO
        )
        MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE
        ON_ERROR=ABORT_STATEMENT
        """,
        dag=dag,
    )

这是表格命令：

CREATE OR REPLACE TABLE staging.raw_data (
  id STRING,
  date STRING,
  customer OBJECT,
  items VARIANT
);

我检查了文件开头是否有BOM，因为错误提示了“第1行，第1个字符”，但用hexdump命令或任何JSON验证工具都没有发现。

当我在Snowflake界面手动加载数据，并在系统文件目录中手动选择文件时，数据加载没有问题！?!?

类似的问题提到将列的数据类型改为VARCHAR可以解决，但我试了并没有效果，很多其他的dag运行使用这种格式也都正常。

任何建议都非常感谢！！！

Answer 1

我来详细解释一下我对你问题的评论。你提供的JSON结构和COPY INTO命令看起来都没问题。如果你要加载的数据就是这些，那我建议你可以这样做：

CREATE OR REPLACE TABLE staging.raw_data (
  payload VARIANT
);

然后在这个表上创建一个视图：

CREATE OR REPLACE VIEW staging.vw_raw_data_flat as
SELECT
  payload:id::string as id,
  payload:date::string as date, ## not a good column name
  payload:customer::variant as customer
  payload:items::variant
FROM staging.raw_data;

理论上，如果需要的话，你也可以很简单地为客户的属性创建单独的列。