How to write into Microsoft SQL Server table even if table exist using PySpark

Question

I have a PySpark Code which writes into SQL Server database like this

 df.write.jdbc(url=url, table="AdventureWorks2012.dbo.people", properties=properties)

However problem is that I want to keep writing in the table people even if the table exist and I see in the Spark Document that there are possible options error, append, overwrite and ignore for mode and all of them throws error, the object already exist if the table already exist in the database.

Spark throw following error py4j.protocol.Py4JJavaError: An error occurred while calling o43.jdbc. com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'people' in the database

Is there way to write data into the table even if the table already exits ? Please let me know you need more explanation

Answer 1

For me the issue was with Spark 1.5.2. The way it checks if the table exists (here) is by running SELECT 1 FROM $table LIMIT 1. If the query fails, the tables doesn't exist. That query failed even when the table was there.

This was changed to SELECT * FROM $table WHERE 1=0 in 1.6.0 (here).