topic Re: Managed table overwrites existing location for delta but not for oth in Data Engineering

Managed table overwrites existing location for delta but not for oth

Dhruv-22 — Thu, 08 Feb 2024 09:35:38 GMT

I am working on Azure Databricks, with Databricks Runtime version being - 14.3 LTS (includes Apache Spark 3.5.0, Scala 2.12). I am facing the following issue.

Suppose I have a view named v1 and a database f1_processed created from the following command.

CREATE DATABASE IF NOT EXISTS f1_processed
LOCATION "abfss://processed@formula1dl679student.dfs.core.windows.net/"

This is creating a database in the container named processed. Suppose I already have some folder named circuits in that container.

If I run the following command to create a managed table in parquet format from a dataframe in that location using the command below.

circuits_final_df.write.mode("overwrite").format("parquet").saveAsTable("f1_processed.circuits")

It gives an error as follows

SparkRuntimeException: [LOCATION_ALREADY_EXISTS] Cannot name the managed table as 
`spark_catalog`.`f1_processed`.`circuits`, as its associated location 
'abfss://processed@formula1dl679student.dfs.core.windows.net/circuits' already exists. 
Please pick a different table name, or remove the existing location first. SQLSTATE: 42710

However, if I try the same thing in delta format, it runs fine. So the following code runs fine.

circuits_final_df.write.mode("overwrite").format("delta").saveAsTable("f1_processed.circuits")

Also, while creating this delta table, it doesn't remove any files from the folder. It just adds the new files.

Since the result mixes the existing data and new data, it seems it is a bug and it should not happen. Any help is appreciated.

Re: Managed table overwrites existing location for delta but not for oth

Red_blue_green — Thu, 08 Feb 2024 10:26:50 GMT

Hi,

this is how the delta format work. With overwrite you are not deleting the files in the folder or replacing them. Delta is creating a new file with the overwritten schema and data. This way you are also able to return to former versions of the delta table.

I believe you are having a table in delta formath in the path, its not possible to overwrite it as parquet format. You need to delete the folder in the abfss location. Then you can create a parquet file there again.

Re: Managed table overwrites existing location for delta but not for oth

Dhruv-22 — Thu, 08 Feb 2024 11:33:05 GMT

Hey, my main doubt was that a managed table cannot be created at a location that already exists. So the error should come for all formats (delta, parquet ...). The code is giving an error for parquet but not for delta. Why is this behaviour? Why is delta able to create a managed table at a non-empty location?

Also, I just kept a random csv file in a folder named circuits to check for the error.

Re: Managed table overwrites existing location for delta but not for oth

Dhruv-22 — Thu, 08 Feb 2024 18:47:32 GMT

Hey @Red_blue_green , I think I get it. The 'delta' mode does not need to delete files to overwrite data. It can keep all files by maintaining history, even the existing ones. Am I right?

Re: Managed table overwrites existing location for delta but not for oth

Red_blue_green — Mon, 12 Feb 2024 09:15:51 GMT

Hey @Dhruv-22 , yes, this is how I understand your problem. You have a folder perhaps with delta files and try to overwrite it with parquet files? I'm not familiar with parquet but I believe you need to delete parquet files each time before you create the table in the same location. With delta like you said, its not neccessary anymore. Delta keeps each file and when you do a action like overwrite, merge or just add data to the table. Delta creates a new file in these cases in order to maintain history. Then you can use the time travel feature to move back to a prior version of the table.