topic Re: Are MERGE INTO inserts supported when the delta table has an identity column ? in Data Engineering

Are MERGE INTO inserts supported when the delta table has an identity column ?

VaDim — Thu, 08 Sep 2022 09:33:05 GMT

I can't seem to make it work as I keep getting:

DeltaInvariantViolationException: NOT NULL constraint violated for column: dl_id.

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

-werners- — Thu, 08 Sep 2022 09:49:45 GMT

I would think it is supported as that is the whole purpose of the ID column, not providing the value yourself.

I haven't tested it though.

But what version of databricks do you use? You need a pretty recent version (10.4+).

Also: how is the identity column created?

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

VaDim — Thu, 08 Sep 2022 10:08:28 GMT

I use 11.2 runtime. identity column created as:

dl_id   BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY

and the pyspark code is an upsert:

t.alias("ec").merge(df.alias("uc"), "ec.dl_id = uc.dl_id") \
            .whenMatchedUpdateAll() \
            .whenNotMatchedInsertAll() \
            .execute()

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

-werners- — Thu, 08 Sep 2022 10:21:38 GMT

Hm, and both ec and uc have id's always filled in?

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

VaDim — Thu, 08 Sep 2022 10:25:25 GMT

`ec` - always. `uc` - not. because `uc` might contain new data that needs appended.

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

-werners- — Thu, 08 Sep 2022 10:29:44 GMT

I think that is the issue. uc contains a column which is supposed to be an id, but you cannot pass it to the merge as it is generated.

What happens if you merge on the natural key?

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

VaDim — Thu, 08 Sep 2022 10:41:22 GMT

I haven't tried but I suspect it will fail with the same message on INSERT because uc.dl_id is NULL for some rows and `whenNotMatchedInsertAll` will attempt to insert a value for dl_id field instead of generating one (as if it has been user provided).

In the meantime I found a workaround: explicitly set the column mapping and do not include one for `dl_id`.

cols2update is a dict with column mapping except dl_id, it works.

t.alias("ec").merge(df.alias("uc"), "ec.dl_id = uc.dl_id") \
            .whenMatchedUpdate(set=cols2update) \
            .whenNotMatchedInsert(values=cols2update) \
            .execute()

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

-werners- — Thu, 08 Sep 2022 10:43:58 GMT

I was just typing this workaround 🙂 Because the id col is defined as GENERATED ALWAYS AS IDENTITY it can never be updated. If you then use UpdateAll it will generate an error.

remove the Id col from the columns to be updated.

This is the way.

Re: Are MERGE INTO inserts supported when the delta table has an identity column ?

byrdman — Fri, 09 Sep 2022 01:35:39 GMT

if you are using 'delta.columnMapping.mode' = 'name' on your table i could not get it to work, without that line .. for the not matched .. WHEN NOT MATCHED

THEN INSERT (columnname,columnName2) values(columnname,columnName2)

WHEN MATCHED

Then UPDATE SET (target.columnname = source.columnname,target.columnname2 = source.columnname2) That worked for me.. Leave the id column out of any of these