2

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?

HKR
  • 33
  • 1
  • 7

1 Answers1

2

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.

Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • Worked! I was expecting NULL_IF parameter in File Format to handle this for datetime column type as well as it did for other column types, but apparently it did not. Hence, had to use NULLIF function in the external table definition as per your suggestion. – HKR Apr 26 '20 at 04:01
  • Tried this but the error is still there: ```NULLIF(JSON_DATA:"Quantity"::double, NULL) as "´QUANTITY"``` – x89 Feb 16 '22 at 09:38
  • @x89 you may want to open a separate question on Stackoverflow, as your issue does not seem to be the same and there are not nearly enough details to assist you. – Mike Walton Feb 17 '22 at 02:40