Databricks Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table

user_15520615 0
Viewed 2053

I am attempting to do a merge with Databricks but I'm getting the error:

UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same
target row in the Delta table in possibly conflicting ways.

I have seen this issue many times on SO, and I understand that a merge operation can fail if multiple rows of the source dataset match and the merge attempts to update the same rows of the target Delta table.

In plain English this happens when the source table has multiple rows trying to update the same target row. This could happen if the updates table has records with the same ID. In my case I don't think that applies.

Can someone take a look at my code and let me know where if they can spot anything obvious

(deltadf.alias("t")
     .merge(
      df.alias("s"),
      "s.primary_key_hash = t.primary_key_hash")
    .whenMatchedUpdateAll("s.change_key_hash <> t.change_key_hash")
    .whenNotMatchedInsertAll()
   .execute()
  )

sample deltadf

enter image description here

Sample df

enter image description here

Please forgive the images.. I'm struggling to with adding data with Markup language

pyspark azure-databricks apache-spark
2 Answers

I tried to reproduce this issue and the following are the delta table data and input dataframe data.

  • Delta table:

enter image description here

  • Input dataframe:

enter image description here

There are duplicates for hash_key in the input dataframe. When I try to use merge on these delta table and the source dataframe, it throws the error.

deltadf.alias("t").merge(df.alias("s"),"s.hash_key = t.hash_key")\ 
    .whenMatchedUpdateAll("s.change_key <> t.change_key")\ 
    .whenNotMatchedInsertAll()\ 
   .execute()

Error:

 java.lang.UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and 
attempted to modify the same target row in the Delta table in possibly conflicting ways. By SQL 
semantics of Merge, when multiple source rows match on the same target row, the result may be 
ambiguous as it is unclear which source row should be used to update or delete the matching target 
row. You can preprocess the source table to eliminate the possibility of multiple matches.

We can see in the error that we need to preprocess the source to eliminate the possibility of multiple matches which is the case here. So, please try to check for duplicate values in your dataframe df and drop them using df.dropDuplicates('primary_key_hash'). Since you are matching using primary_key_hash, make sure there are no duplicate values in this column of the dataframe.

when I execute the merge the first time its without any issues. However, when I execute the merge the second time without updating the input data I get the error. Do you think that is why I'm getting the problem.

No, if the merge operation is executed successfully for the first time and you use the same input for the 2nd execution as well, it would run without any error because primary_key_hash would be matched and the change_key_hash will be updated without any inserts this time (because new primary_key_hash are inserted in previous run when not matched).

user_188445850

I had the same error and figured out the problem was the first time it worked. My data source indeed had duplicates on my primary_key so the second time I ran the code I got the error of multiple rows. Once I cleared the destiny table and the source df, it worked every time.

user_200530890
Related