Skip to content

Systems and Tech Thoughts

Snowflake CSV COPY Into Examples

January 22, 2021

This article lists a number snowflake configuration csv copy into parameters and the outputs they produce. I’m currently working on a snowflake migration which requires loading CSV files. The CSV options, as described in the snowflake documentation, were a bit confusing so I created a series of test cases that show the options, CSV input and the data as it was loaded into snowflake.

Defaults - Unquoted

CSV:

first,test_text
1,hi
2,bye

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

DATA: unquoted_default

Defaults - Quoted

CSV:

first,test_text
1,"hi"
2,bye

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

quoted_default

Defaults - Quoted Containing Quotes

CSV:

first,test_text
1,"h\"i"
2,"b\"ye"

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

DATA: default_quoted_with_quotes

Field Optionally Enclosed By ’”’ - Quoted

CSV:

first,test_text
1,"hi"
2,bye

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

DATA: optionally_quotes_with_quotes

Field Optionally Enclosed by - Quoted Containing Quotes

CSV:

first,test_text
1,"h\"i"
2,"b\"ye"

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOAD_FAILED" rowsParsed:2 rowsLoaded:0 errorsSeen:2

DATA: optionally_quotes_with_quotes

Field Optionally Enclosed By With ESCAPE - Quoted Containing Quotes

CSV:

first,test_text
1,"h\"i"
2,"b\"ye"

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
escape = '\\'
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

DATA: quotes_with_escape

Field Optionally Enclosed By With Escape - New Line Characters

CSV:

first,test_text
1,"h
i"
2,b
y
e

SQL:

COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
escape = '\\'
)
on_error = 'continue';

OUTPUT:

INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0

DATA: newlines

Conclusion

I found it easy to explore the COPY INTO csv API through a series of small test cases. If you’d like to see another test case, please let me know and I’d be happy to add it!

References:


Thoughts on Systems & Tech