I'm using merge to upsert data into a table:
DeltaTable
.forName(DESTINATION_TABLE)
.as("target")
.merge(merge_df.as("source") ,"source.topic = target.topic and source.key = target.key")
.whenMatched()
.updateAll()
.whenNotMatched()
.insertAll()
.execute()
Id like to use one of the following generated columns to create surrogate keys on insert:
- surrogate_guid string generated always as (uuid())
- surrogate_id bigint generated always as identity
#1 doesn't work, I get the error "A generated column cannot use a non-deterministic expression".
#2 doesn't work, I get the error "cannot resolve surrogate_id in UPDATE clause"
What's best practice for merging and getting a unique identifier (preferably uuid) assigned?