CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Jedi_mc
Jedi_mc

Reputation: 61

Copy into stage with pipe delimited and null values

I have some tables that I would like to export the results of into a internal (or external) stage of snowflake. The problem is Snowflake throws an error.

COPY INTO @test_stage/test_file.csv.gz FROM (
             SELECT *
               FROM database.shema.table)
file_format = (type = 'CSV' 
               field_delimiter = '|' 
               compression = 'gzip' 
               null_if = () 
               field_optionally_enclosed_by = None)
 HEADER = True
 OVERWRITE = True
 MAX_FILE_SIZE = 4900000000
 SINGLE = True;

Cannot unload empty string without file format option field_optionally_enclosed_by being specified.

If I do not specify the field_optionally_enclosed_by I will get that error.

If I set it field_optionall_enclosed_by to None I get that error (none is default).

If I set it to a quote or double quote it’s fine but I do not want strings quoted. I want empty values like this:

2020-05-10|||

Not this:

2020-05-10|""|""|

Upvotes: 2

Views: 2219

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11046

This should do it:

COPY INTO @test_stage/test_file.csv.gz FROM (
             SELECT *
               FROM db.schema.table)
file_format = (type = 'CSV' 
               field_delimiter = '|' 
               compression = 'gzip' 
               field_optionally_enclosed_by = None
               EMPTY_FIELD_AS_NULL = false
               null_if = ''
              )
 HEADER = True
 OVERWRITE = True
 MAX_FILE_SIZE = 4900000000
 SINGLE = True;

Upvotes: 1

Jedi_mc
Jedi_mc

Reputation: 61

I believe I have found the answer.

Instead of this:

file_format = (type = 'CSV' 
               field_delimiter = '|' 
               compression = 'gzip' 
               null_if = () 
               field_optionally_enclosed_by = None)

I used this:

file_format = (type = 'CSV' 
               field_delimiter = '|' 
               compression='gzip' 
               null_if = ('') 
               empty_field_as_null = false)

So we are skipping the optionally enclosed by and setting null_if to a blank string. That second part is the key.

Upvotes: 4

Related Questions