topic Re: how to remove column from delta table? in Data Engineering

how to remove column from delta table?

jonjoseph — Tue, 13 Feb 2024 16:33:06 GMT

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

Re: how to remove column from delta table?

Anonymous — Wed, 14 Feb 2024 12:32:51 GMT

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.

Re: how to remove column from delta table?

jonjoseph — Wed, 14 Feb 2024 13:21:54 GMT

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.

Re: how to remove column from delta table?

Anonymous — Thu, 15 Feb 2024 11:45:12 GMT

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 -

Re: how to remove column from delta table?

Anonymous — Fri, 16 Feb 2024 14:51:37 GMT

Re: how to remove column from delta table?

Anonymous — Mon, 19 Feb 2024 09:02:34 GMT

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 .