Solved

How to load or unload empty field as non nulls into snowflake destination

  • 25 July 2022
  • 1 reply
  • 16 views

Userlevel 2
Badge +1

Empty values are loaded as NULLs when loading the data into a snowflake table. For example, I have an input field of varchar type and sometimes the data in it is empty(‘’). I would want the input data ‘’ to be loaded into snowflake same as ‘’ and not NULL. This will be helpful in cases where I have defined the destination table field as NON NULL.

icon

Best answer by Pradeep 25 July 2022, 14:04

View original

1 reply

Userlevel 2
Badge +1

By default, a temporary file format is created to load any staged file. This file format has EMPTY_FIELD_AS_NULL set to true by default.

Solution: Create a file format by setting EMPTY_FIELD_AS_NULL to false and use the file format in Snowflake File Format option for Snowflake stage. 

create file format "STREAMSETS_DB"."PRADEEP"."MY_FILE_FORMAT" type = CSV empty_field_as_null=false FIELD_DELIMITER=',' TRIM_SPACE = true FIELD_OPTIONALLY_ENCLOSED_BY = '\'' ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE';

If pre-created file format is passed, snowflake destination would use the same when loading data.

Reply