Snowflake table is not accepting null values in date field-CodePudding

I have one table in snowflake, I am performing bulk load using. one of the columns in table is date, but in the source table which is on sql server is having null values in date column.

The flow of data is as :

sql_server-->S3 buckets -->snowflake_table

I am able to perform the sqoop job in EMR , but not able to load the data into snowflake table, as it is not accepting null values in the date column.

The error is :

Date '' is not recognized File 'schema_name/table_name/file1', line 2, character 18 Row 2, 
column "table_name"["column_name":5] If you would like to continue loading when an error is 
encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option.

can anyone help, where I am missing

CodePudding user response：

The error shows that the dates are not arriving as nulls. Rather, they're arriving as blank strings. You can address this a few different ways.

The cleanest way is to use the TRY_TO_DATE function on your COPY INTO statement for that column. This function will return database null when trying to convert a blank string into a date:

https://docs.snowflake.com/en/sql-reference/functions/try_to_date.html#try-to-date

CodePudding user response：

Using below command you can able to see the values from stage file:

select t.$1, t.$2 from @mystage1 (file_format => myformat) t;

Based on the data you can change your copy command as below:

COPY INTO my_table(col1, col2, col3) from (select $1, $2, try_to_date($3) from @mystage1) 
file_format=(type = csv FIELD_DELIMITER = '\u00EA' SKIP_HEADER = 1 NULL_IF = ('') ERROR_ON_COLUMN_COUNT_MISMATCH = false EMPTY_FIELD_AS_NULL = TRUE)  
    on_error='continue'