Forum Discussion

jonjoseph's avatar
jonjoseph
Regular Visitor
2 years ago

how to remove column from delta table?

I managed to add new columns to an existing table using 

 

    df.limit(0).write.format("delta").mode("append").option("mergeSchema", "true").save(tablename)

 

 

however when columns are removed , this method does not actually drop columns in the delta table. I think I read that it doesnt actually remove the column, but will set the value to null?

 

however when I then run the merge, it fails with 

AnalysisException: Cannot resolve <removed column> in UPDATE clause given columns .....

 

    delta_table.alias("target").merge(
        source=df.alias("source"),
        condition="target.accessnum = source.accessnum",  
    ).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

 

 

so this seems to me that it doesnt know that the source column has been removed.

I have the configuration set with 

 

spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.binSize", "1073741824")

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

#config to enable all new Delta tables with Change Data Feed
spark.conf.set("spark.microsoft.delta.properties.defaults.enableChangeDataFeed", "true")

 

 

so what do I have to do to handle columns being removed from the source when updating and inserting?

Thanks

 

data engineering

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jonjoseph ,

    Thanks for using Fabric Community.
    As I understand you are trying to insert a column into the target table that does not exist in the source table. 

    I haven't tried it, but you can refer this - MERGE on delta table with source structure change - Microsoft Community Hub

    Incase if my understanding is wrong, can you please share few more details so that I can guide you better.

    Hope this is helpful. Please let me know incase of further queries.

  • jonjoseph's avatar
    jonjoseph
    Regular Visitor
    2 years ago

    The source has removed the column, and I am doing a merge and update all the delta table. However updateall is trying to update the column that no longer exists in the source.

    It beginning to sound like I will have to specify the columns and can't just use updateall.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hello jonjoseph ,

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
        In case if you have any resolution please do share that same with the community as it can be helpful to others .
        Otherwise, will respond back with more details and we will try to help .