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.
Solved
How to load or unload empty field as non nulls into snowflake destination
+1- StreamSets Employee
- 48 replies
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.
+1- StreamSets Employee
- 48 replies
- Answer
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.