Delta lake merge doesn't update schema (automatic schema evolution enabled)

user_12290469 0
Viewed 2308

I get an error when I execute the following line of code:

deltaTarget.alias('target').merge(df.alias('source'), mergeStatement).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

The error is the following:

AnalysisException: cannot resolve new_column in UPDATE clause given columns {List of target columns}. The 'new_column' is indeed not in the schema of the target delta table, but according to the documentation, this should just update the existing schema of the delta table and add the column.

I also enable the autoMerge with this command:

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled ","true")

I am not sure what exactly causes this error because in the past I was able to evolve the schema of delta tables automatically with these exact pieces of code.

Is there something that I am overlooking?

delta-lake pyspark databricks
4 Answers

you have to remove the space after ..autoMerge.enabled in the spark.conf.set

--> it's

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled","true")

, not

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled ","true")`
user_136875220

I have the same problem with you, but i find that in delta lake docs, it may not likely support the part columns with upsertAll() and insertAll(); So i choose the upsertExpr() and insertExpr() with a big map contains all the columns.

delta lake merge : Schema validation

user_164058120

If I'm not mistaken you need to use the insertAll or updateAll options on the MERGE operation

user_147308400

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled","true")

Ensure there is no space after "enabled" in above line.

then you can use pass a spark sql:

spark.sql(f"""
MERGE INTO {data_path} delta USING global_temp.src source
       ON   delta.col1   =    source.key1
       AND  delta.col2   =    source.key2
      
       WHEN MATCHED THEN
         UPDATE SET *
         
       WHEN NOT MATCHED THEN
         INSERT *
""")
user_103109390
Related