NULL value in Avro column is loaded as VARIANT NULL

user_226895 0
Viewed 142

I'm bulk loading data to Snowflake using stage with avro files. Column in snowflake table is of type VARIANT is defined as union of null&string in avro file. Sadly null value in avro for such column is loaded into Snowflake as VARIANT null instead of regular SQL NULL. It complicates my subsequent MERGE query. Here is my COPY query:

copy into PUBLIC."_bp_staging_1621364335535_xxx"
    from @~/batches
    file_format = (type = avro compression = auto)
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    pattern = '.*parallel_batch_18441164121617616245\.avro$';

I can of course use NULL_IF parameter but then I'd have to emit that value for every empty VARIANT field which seems pretty wasteful to me.

Is there better way ?

snowflake-cloud-data-platform
2 Answers

"Sadly null value in avro for such column is loaded into Snowflake as VARIANT null instead of regular SQL NULL. It complicates my subsequent MERGE query"

NULL Values

To convert a VARIANT “null” value to SQL NULL, cast it as a string.

user_50708790

Looks like there is no way to change this behavior but engineer from Snowflake suggested altering my MERGE query instead.

In MERGE query I use column is not null when condition in one of the cases of update section. Instead, he suggested to use IS_NULL_VALUE function which I do only for VARIANT destination columns.

user_2268950
Related