Snowflake External Table failed to cast variant value NULL to DATETIME/TIMESTAMP_NTZ type

Question

Created an external table with a column of type datetime (TIMESTAMP_NTZ type), the external stage has a csv file with null value in the column. Selecting from the external table is giving "Failed to cast variant value "null" to TIMESTAMP_NTZ"

CREATE OR REPLACE EXTERNAL TABLE ext_table_datetime (
   col1 datetime as (value:c1::datetime)
   )
    with location = 's3://bucket_name'
    file_format = file_format_1
    auto_refresh = true;

Also I have the file format defined as follows, which works for other column types (varchar etc) having null values for external table and also for datetime type in regular table. So, basically just doesn't work for external table with datetime type.

CREATE OR REPLACE FILE FORMAT file_format_1 type = 'CSV'
                              field_delimiter = ','
                              ESCAPE_UNENCLOSED_FIELD = NONE
                              SKIP_HEADER=1
                              NULL_IF = 'null';

Any ideas on how to load/sync null value into external table datetime type?

Mike Walton Mike Walton · Accepted Answer · 2020-04-26T03:12:37

Have you tried using the NULLIF function in your EXTERNAL TABLE definition:

CREATE OR REPLACE EXTERNAL TABLE ext_table_datetime (
   col1 datetime as (NULLIF(value:c1,'null')::datetime)
   )
    with location = 's3://bucket_name'
    file_format = file_format_1
    auto_refresh = true;

Also, since this is a Preview feature for Snowflake, I would suggest opening a support ticket. The NULL_IF parameter of your format file should probably be handling this for you as you expected.

Snowflake External Table failed to cast variant value NULL to DATETIME/TIMESTAMP_NTZ type

1 Answers