snowflake handle null while reading csv file

Question

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.

Lukasz Szozda · Accepted Answer · 2021-06-05 11:02:23Z

2

The null string literal could be handled by setting NULL_IF:

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

answered Jun 5, 2021 at 11:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleksandra Angelova · Accepted Answer · 2021-09-16 18:15:06Z

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;

Collectives™ on Stack Overflow

snowflake handle null while reading csv file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related