topic Re: MetadataChangedException in Data Engineering

MetadataChangedException

Anonymous — Thu, 23 Jun 2022 17:38:14 GMT

A delta lake table is created with identity column and I'm not able to load the data parallelly from four process. i'm getting the metadata exception error.

I don't want to load the data in temp table . Need to load directly and parallelly in to delta table.

Re: MetadataChangedException

Anonymous — Thu, 23 Jun 2022 17:55:04 GMT

MetadataChangedException: The metadata of the Delta table has been changed by a concurrent update. Please try the operation again

Re: MetadataChangedException

Anonymous — Thu, 23 Jun 2022 18:21:29 GMT

No alter table operations are carried out. Just loading data from four parallelly running notebooks in to same delta lake table which is having ID as identity column is making the issue.

when loading the data in to temp table and putting in to target table having identity column doesn't make any issues.

But for some reason i need to load the data parallelly in to the table which is having identity column.

Re: MetadataChangedException

Hubert-Dudek — Thu, 23 Jun 2022 18:43:57 GMT

@Gokul K , Identity is stored in table schema (which is the awful solution). That's why concurrent inserts are not supported.

I even record a video about that problem: Delta Identity Column with Databricks 10.4 - crash test - YouTube

Re: MetadataChangedException

Anonymous — Thu, 23 Jun 2022 19:01:22 GMT

Thanks @Hubert Dudek

Re: MetadataChangedException

Databricks3 — Tue, 13 Jun 2023 07:45:54 GMT

@Hubert Dudek @Kaniz Fatma

I am experiencing the same issue. Now that I understand the reason behind it, I would appreciate your assistance in finding a solution for generating a sequence for the table. Multiple concurrent jobs will be performing insertions and updates on the same table. To address the concurrent update issue, I have partitioned the table. However, I am struggling to determine the best approach for generating the Id values. I would greatly appreciate any suggestions you can provide.

Re: MetadataChangedException

Anonymous47 — Wed, 08 Nov 2023 17:03:59 GMT

Even in retry method or in try & exception method, there is no guarantee that the load of another parallel process is complete especially for large volume tables. So in such cases even if you try to repeat the write in exception, it would fail. What is best possible solution for this? Is there any other way to generate id column with auto increment method without using GENERATE clause in DDL?

Re: MetadataChangedException

seans — Wed, 07 Aug 2024 04:09:21 GMT

I recently ran into this MetadataChangedException. Watching the video @Hubert Dudek posted it's pretty clear what is going on: object storage folks not thinking like someone who builds relational database engines built it. That's to be expected. Databricks is wonderful in many ways, but in SQL and relational database engine features like sequences, they're evolving slowly.

I switched to a serial write to get around the problem because of a deadline but we really should open a ticket on this with Databricks to get some clarity on an issue (parallel sequence updates) relational databases solved 50+ years ago. Like the video says, it's a bad idea to store the identity information in schema. Needs to be a something like a separate file with a thread safe approach to updates.

Re: MetadataChangedException

cpc0707 — Thu, 05 Dec 2024 22:43:30 GMT

I'm having the same issue, need to load a large amount of data from separate files into a delta table and I want to do it with a for each loop so I don't have to run it sequentially which will take days. There should be a way to handle this 🙃

Re: MetadataChangedException

lprevost — Sat, 03 Jan 2026 12:34:23 GMT

I'm also having the same problem. I'm using autoloader to load many files into a delta table with an identity column. What used to work now dies with this problem -- after running for a long time!!