Skip to main content
Solved

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

  • July 25, 2022
  • 1 reply
  • 3565 views
Pradeep
StreamSets Employee
Forum|alt.badge.img+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.

Best answer by Pradeep

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.

View original
Did this topic help you find an answer to your question?

Pradeep
StreamSets Employee
Forum|alt.badge.img+1
  • PradeepStreamSets Employee
  • StreamSets Employee
  • July 25, 2022

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


Terms & Conditions