Snowflake加载含ARRAY字段的本地文件时解析报错求助

报错里的Error parsing JSON: nm0005690已经点明核心问题：Snowflake 默认将 ARRAY 类型字段按 JSON 格式解析，但你的 TSV 数据中，DIRECTORS 和 WRITERS 字段是普通字符串（比如nm0005690）或逗号分隔的字符串，并非合法的 JSON 数组（JSON 数组需要用方括号包裹元素，例如["nm0005690"]），因此解析失败。

提供三种可行方案，按需选择：

方案1：在 COPY INTO 语句中用函数转换字符串为数组 #

直接在 COPY 逻辑中对目标字段做转换，用STRTOK_TO_ARRAY将逗号分隔的字符串拆分为数组，同时处理\N空值：

COPY INTO "TESTDB"."LAYER2"."CREW" 
FROM (
    SELECT 
        $1 AS TCONST,
        -- 空值处理：如果是\N则返回NULL，否则转成数组
        CASE WHEN $2 = '\\N' THEN NULL ELSE STRTOK_TO_ARRAY($2, ',') END AS DIRECTORS,
        CASE WHEN $3 = '\\N' THEN NULL ELSE STRTOK_TO_ARRAY($3, ',') END AS WRITERS
    FROM @TEST_2/ui1770650179898
)
FILE_FORMAT = '"TESTDB"."LAYER1"."TSV"' 
ON_ERROR = 'ABORT_STATEMENT' 
PURGE = TRUE;

注意：需确保你的 TSV 文件格式已配置FIELD_DELIMITER = '\t'和NULL_IF = ('\\N')，未配置的话先调整文件格式。

方案2：修改文件格式，适配数组解析规则 #

如果不想写转换逻辑，可以给 TSV 文件格式添加数组元素分隔符配置，让 Snowflake 自动将逗号分隔的字符串转为数组：

-- 创建或替换适配数组的文件格式
CREATE OR REPLACE FILE FORMAT "TESTDB"."LAYER1"."TSV_ARRAY"
TYPE = CSV
FIELD_DELIMITER = '\t'
NULL_IF = ('\\N')
PARSE_HEADER = TRUE
ARRAY_ELEMENT_DELIMITER = ','; -- 指定用逗号拆分数组元素

之后用这个新格式执行 COPY 操作：

COPY INTO "TESTDB"."LAYER2"."CREW" 
FROM @TEST_2/ui1770650179898
FILE_FORMAT = '"TESTDB"."LAYER1"."TSV_ARRAY"' 
ON_ERROR = 'ABORT_STATEMENT' 
PURGE = TRUE;

方案3：预处理源数据为标准 JSON 数组格式 #

如果能修改源 TSV 文件，将数组字段改成标准 JSON 格式即可：

• 把nm0005690改为["nm0005690"]
• 把nm0374658,nm0005690改为["nm0374658","nm0005690"]
  修改完成后，用原来的 COPY 语句就能正常加载数据。

1. 单独测试转换函数是否正常工作：

SELECT STRTOK_TO_ARRAY('nm0374658,nm0005690', ',');

预期返回结果为["nm0374658", "nm0005690"]。
2. 用 VALIDATE 命令排查所有错误详情：

COPY INTO "TESTDB"."LAYER2"."CREW" 
FROM @TEST_2/ui1770650179898
FILE_FORMAT = '"TESTDB"."LAYER1"."TSV"' 
ON_ERROR = 'SKIP_FILE' 
PURGE = FALSE
VALIDATE_MODE = RETURN_ALL_ERRORS;

该命令会返回所有错误的具体信息，方便确认问题是否解决。

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