Copy into CSV from Snowflake to S3 encloses all fields in quotes, not just the ones with commas

user17449991 0
Viewed 39

I'm attempting to copy out from a Snowflake table to a CSV on S3 using the "copy into" command.

I noticed that using the following parameters in my copy into statement leads to ALL fields in the entire CSV file being enclosed in double quotes.

file_format=(type=csv compression=none field_optionally_enclosed_by='"' empty_field_as_null=false null_if='' trim_space=true field_delimiter=',') header=true overwrite=true single=true;

Is this expected behavior for the field_optionally_enclosed_by option? I thought that just the fields containing commas in the value would be enclosed by "", but all fields in the full file are enclosed.

Is there another option to see the output that I want? I'd like just the fields containing a comma in the value to be enclosed, but the rest not enclosed.

snowflake-cloud-data-platform
1 Answers

That's expected behaviour as it's explained here:

https://docs.snowflake.com/en/user-guide/data-unload-considerations.html#empty-strings-and-null-values

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

Use this option to enclose strings in the specified character: single quote ('), double quote ("), or NONE.

The same parameter is also used when ingesting data, this is why its name is "FIELD_OPTIONALLY_ENCLOSED_BY". When you use this parameter on loading, it doesn't expect all values are enclosed by this character, but they may be enclosed optionally.

Of course, there could be a smarter way to apply this parameter. For example, when the data contains the field_delimiter character, it could be enclosed. This could be a good feature request.

Gokhan Atil0
Related