从用户阶段加载CSV数据到Snowflake表时遇错误，请求协助

错误信息 #

File '@~/vehicles.csv.gz', line 9, character 31
Row 9, column "VEHICLE"["CATEGORY":4]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE'
for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

当前配置 #

文件格式创建语句 #

CREATE OR REPLACE FILE FORMAT CSV_NO_HEADER_BLANK_LINES
    type = 'CSV'
    field_delimiter = ','
    field_optionally_enclosed_by = '"'
    skip_header = 0
    skip_blank_lines = true;

COPY INTO加载命令 #

COPY INTO VEHICLE
     FROM @~/vehicles.csv.gz
     file_format = CSV_NO_HEADER_BLANK_LINES;

问题数据行 #

报错的第9行数据：

"Dodge", "ATS", "2017", "Sedan,Wagon"

该行前后数据：

"GMC", "Titan Crew Cab", "2011", "Coupe"
"Dodge", "MX-5 Miata", "2014", "Hatchback"
"Dodge", "ATS", "2017", "Sedan,Wagon"
"Volvo", "Savana 2500 Cargo", "2019", "suv"
"Mercedes-Benz", "Armada", "2014", "Coupe Sedan"

问题原因与解决方案 #

问题根源 #

文件格式中field_optionally_enclosed_by参数使用了"（HTML转义的双引号），Snowflake无法识别该转义字符，导致实际未正确启用"字段可选包围"功能。第9行的"Sedan,Wagon"中包含逗号，被当成字段分隔符，造成列数不匹配，触发加载错误。

修正方案 #

将文件格式中的field_optionally_enclosed_by值改为直接使用双引号（用单引号包裹）：

CREATE OR REPLACE FILE FORMAT CSV_NO_HEADER_BLANK_LINES
    type = 'CSV'
    field_delimiter = ','
    field_optionally_enclosed_by = '"'
    skip_header = 0
    skip_blank_lines = true;

修正后重新执行COPY INTO命令，Snowflake会正确识别被双引号包围的字段内的逗号，不会将其作为分隔符处理，第9行数据可正常加载。

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