CODEWITHSUNDEEP
snowflake-cloud-data-platform
expert
expert

Reputation: 30135

NULL value in Avro column is loaded as VARIANT NULL

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 ?

Upvotes: 1

Views: 278

Answers (2)

expert
expert

Reputation: 30135

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.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

"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.

Upvotes: 0

Related Questions