how to remove column from delta table?

[jonjoseph]

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

[jonjoseph]

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]

Hi @jonjoseph ,

Is this helpful - MERGE on delta table with source structure change - Microsoft Community Hub
It is also having a discussion on this point too -

[Anonymous]

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 .

[Anonymous]

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 .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .

[Anonymous]

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.