How to delete a table in Azure Synapse Dedicated Pool from a Synapse Spark notebook

@Jimmy Dobbins Welcome to Microsoft Q&A platform and thanks for posting your question

It seems you’re encountering a common issue where the overwrite mode in Apache Spark does not drop the target table as expected. Here’s a concise summary of what you can do:

• Use truncate Option: Include .option("truncate", "true") in your write command to truncate the table instead of dropping it
• Managed vs External Tables: Ensure you’re not dealing with a managed table, which may not be dropped or overwritten as expected. If necessary, convert it to an external table.
• Append Mode: If you need to update existing records, consider using append mode after performing deletions outside of Spark
• JDBC Write Modes: Refer to the Spark JDBC documentation for detailed behavior of different write modes

Hope this helps. Do let us know if you any further queries.

Thanks so much for the quick response. I am aware of the options you presented, but the issue is that the usual Spark option of mergeSchema (which gracefully adds new columns not in the target table) is not available using the Apache Spark connector for Synapse. Also, my table is not in Spark SQL, it is in Synapse dedicated SQL pool. This is why I need to drop the target table before loading it. I had hoped the overwrite save mode would accomplish this, but if a new column is in the source table I get an error stating that source and target table schemas are different. I have created a stored procedure to drop the table first, but calling it has not been possible.

@Jimmy Dobbins Since the mergeSchema option is not available with the Apache Spark connector for Synapse and you’re dealing with a table in Synapse dedicated SQL pool, the usual methods might not work.

One possible workaround could be to execute a SQL command to drop the table before loading the data. Here’s an example of how you can do this using PySpark:

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
# Execute a SQL command directly on the SQL pool
spark._jsparkSession.sql("DROP TABLE IF EXISTS stg.customer")
# Then you can load your dataframe
df.write.mode("overwrite").saveAsTable("stg.customer")

This code first drops the table stg.customer if it exists, and then loads the dataframe df into the table. Please replace stg.customer and df with your actual table name and dataframe.

please make sure that your Spark session has the necessary privileges to execute the DROP TABLE command.