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

Question

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

Jimmy Dobbins 41 Microsoft Employee

I have a notebook that loads several tables in Azure Synapse Dedicated pool. I am using the Apache Spark connector for Synapse dedicated pools. I am using mode("overwrite"), but this does not drop the target table. I need to do something similar to "drop table if exists stg.customer" before loading from a dataframe in the notebook. I have tried everything I can think of, including a stored procedure, but nothing works. Any advice is greatly appreciated! A screenshot of my cell and what I have tried follows (note that the procedure works if I run it from a SQL script, but the table remains even after this cell runs successfully...nothing really happened):

User's image

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-11T06:23:28.4366667+00:00
@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.
Jimmy Dobbins 41 Reputation points Microsoft Employee

2024-04-11T19:02:59+00:00

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.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-12T09:00:11.9633333+00:00
@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.
Jimmy Dobbins 41 Reputation points Microsoft Employee

2024-04-12T20:42:18.4066667+00:00

@phemanth, thanks for the suggestion. I was unable to find a _jsparkSession in my Spark session. I ended up going with PyODBC and it worked.

Accepted answer

1 additional answer

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-11T06:23:28.4366667+00:00

@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.
Jimmy Dobbins 41 Reputation points Microsoft Employee

2024-04-11T19:02:59+00:00

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.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-12T09:00:11.9633333+00:00

@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.
Jimmy Dobbins 41 Reputation points Microsoft Employee

2024-04-12T20:42:18.4066667+00:00

@phemanth, thanks for the suggestion. I was unable to find a _jsparkSession in my Spark session. I ended up going with PyODBC and it worked.

Answer 1

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

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

**Ask:**I have a notebook that loads several tables in Azure Synapse Dedicated pool. I am using the Apache Spark connector for Synapse dedicated pools. I am using mode("overwrite"), but this does not drop the target table. I need to do something similar to "drop table if exists stg.customer" before loading from a dataframe in the notebook. I have tried everything I can think of, including a stored procedure, but nothing works. Any advice is greatly appreciated! A screenshot of my cell and what I have tried follows (note that the procedure works if I run it from a SQL script, but the table remains even after this cell runs successfully...nothing really happened):

User's image

Solution: I got this to work by using PyODBC. Relevant snippets of my code is below, I hope this helps!

import pyodbc
# define constants
SERVER = synapse_server
DATABASE = synapse_ded_pool
USERNAME = username
PASSWORD = mypassword
connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
# establish connection
conn = pyodbc.connect(connectionString)
target_table = "stg.customer"
drop_query = f"""
        IF OBJECT_ID('{target_table}') is not null
            BEGIN
                drop table {target_table}
            END;
        """
# drop table in Synapse dedicated pool before loading
conn.execute(drop_query)
df.write \
	.option(Constants.SERVER, synapse_server) \
    .option(Constants.TEMP_FOLDER, synapse_temp_folder) \
    .options(mergeSchema=True) \
    .mode("overwrite") \
    .synapsesql(synapse_ded_pool + '.' + target_table) # 3-part notation required
conn.close()

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Abhinesh Kumar Lal Karn 0 Reputation points

2024-07-18T08:31:48.3733333+00:00

Want to delete a row from a table using jbc url which I am getting via linked service, how to do that in synapse notebook

Answer 2

I got this to work by using PyODBC. Relevant snippets of my code is below, I hope this helps!

import pyodbc

# define constants
SERVER = synapse_server
DATABASE = synapse_ded_pool
USERNAME = username
PASSWORD = mypassword

connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'

# establish connection
conn = pyodbc.connect(connectionString)

target_table = "stg.customer"

drop_query = f"""
        IF OBJECT_ID('{target_table}') is not null
            BEGIN
                drop table {target_table}
            END;
        """

# drop table in Synapse dedicated pool before loading
conn.execute(drop_query)

df.write \
	.option(Constants.SERVER, synapse_server) \
    .option(Constants.TEMP_FOLDER, synapse_temp_folder) \
    .options(mergeSchema=True) \
    .mode("overwrite") \
    .synapsesql(synapse_ded_pool + '.' + target_table) # 3-part notation required

conn.close()

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-13T00:04:54.4333333+00:00

@Jimmy Dobbins Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Share via

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

1 additional answer

Your answer