CODEWITHSUNDEEP
snowflake-cloud-data-platform
danD
danD

Reputation: 716

snowflake handle null while reading csv file

I am trying to load a CSV file from S3. which has a null value in the integer type data field in the snowflake table. So I try to use IFFNULL function but gets the error.

 Numeric value 'null' is not recognized.

For example when I try

select IFNULL(null,0)

I get the answer as 0.

but the same thing when I try while reading the CSV file won't work

select $1,$2,ifnull($2,0)
from 
@stage/path
(file_format => csv)

I get the null not recognized Error. and it fails when $2 is null.

My csv format is as below.

create FILE FORMAT CSV 
COMPRESSION = 'AUTO' FIELD_DELIMITER = ',' 
RECORD_DELIMITER = '\n' SKIP_HEADER = 0 
FIELD_OPTIONALLY_ENCLOSED_BY = '\042' 
TRIM_SPACE = FALSE 
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = '\134' 
ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO' 
TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('\\N');

Basically, I am just trying to convert null to 0, when reading from the stage.

Upvotes: 1

Views: 4611

Answers (2)

Aleksandra Angelova
Aleksandra Angelova

Reputation: 81

I used the second option listed in the Snowflake documentation specifying FIELD_OPTIONALLY_ENCLOSED_BY=NONE and EMPTY_FIELD_AS_NULL = FALSE in which case I'd need to provide a value to be used for NULLs (NULL_IF=('NULL')

https://docs.snowflake.com/en/user-guide/data-unload-considerations.html

"Leave string fields unenclosed by setting the FIELD_OPTIONALLY_ENCLOSED_BY option to NONE (default), and set the EMPTY_FIELD_AS_NULL value to FALSE to unload empty strings as empty fields.

If you choose this option, make sure to specify a replacement string for NULL data using the NULL_IF option, to distinguish NULL values from empty strings in the output file. If you later choose to load data from the output files, you will specify the same NULL_IF value to identify the NULL values in the data files."

So my query looked something like the following:

COPY INTO @~/unload/table FROM (
SELECT * FROM table
)
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'GZIP' 
FIELD_DELIMITER = '\u0001' 
EMPTY_FIELD_AS_NULL = FALSE 
FIELD_OPTIONALLY_ENCLOSED_BY = NONE    
NULL_IF=('NULL')) 
OVERWRITE = TRUE;

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

The null string literal could be handled by setting NULL_IF:

CREATE FILE FORMAT CSV 
...
NULL_IF = ('null', '\\N');

Upvotes: 2

Related Questions