CODEWITHSUNDEEP
pythondatabricksazure-databrickspyspark
Sree Nair
Sree Nair

Reputation: 101

Drop Table in SQL Database from Azure Databricks

I need to drop a table in SQL Database using Azure Databricks (PySpark).

I am required to not use anything outside PySpark to achieve this. Can this be done to using DataFrameWriter. Below code (internally I believe) first deletes the table and then creates a new table with the same name and then writes the dataframe data into the table. 

MyDataFrame.write.format("jdbc").option("url", "jdbc:sqlserver://<SQLServerHost>.windows.net:1433").option("database", 'MyDBName').option("dbtable", 'MyTableName').option("user", "MyUsername").option("password", "MyPassword").mode('overwrite').save()

I want to simply delete the table completely. Is there a code to do the same?

regards, Sree

Upvotes: 2

Views: 5279

Answers (3)

Dhruv
Dhruv

Reputation: 443

You can use prepareQuery. From the documentation

A prefix that will form the final query together with query.
...
<prepareQuery> SELECT <columns> FROM (<user_specified_query>) spark_gen_alias

Putting your code into it

sparkspark.read.format('jdbc')\
.option("url", "jdbc:sqlserver://MyServerName:MyPort")\
.option("database", 'MyDBName')\
.option("user", "MyUser")\
.option("password", "MyPassword")\
.option("prepareQuery","DROP TABLE tablename;")\
.option("query", "SELECT 1")\
.load()

You will need the query or table parameter along with it since it is just a prefix to the main query.

Upvotes: 1

Vikas Jain
Vikas Jain

Reputation: 84

you can try the following.

#Setup the JDBC connection. 
jdbcUrl = "jdbc:mysql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
      "user" : jdbcUsername,
      "password" : jdbcPassword,
      "driver" : "com.mysql.jdbc.Driver"
    }

#Create a query to drop the required table
pushdown_query = "DROP table tablename"

#run the query
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)

I hope this helps.

Upvotes: 1

Sree Nair
Sree Nair

Reputation: 101

df = spark.read.format('jdbc')\
.option("url", "jdbc:sqlserver://MyServerName:MyPort")\
.option("database", 'MyDBName')\
.option("user", "MyUser")\
.option("password", "MyPassword")\
.option('query', 'drop table tablename')

this did the trick.

Upvotes: 2

Related Questions