Writing a dataframe to a SQL database without replacing table format

Question

I'm using Azure Databricks and pyspark to process data using dataframes and I use Azure SQL Database to store the data after it's been processed. I have created the output tables using ordinary CREATE TABLE scripts in SQL, but I realized that the dataframe write method overwrites the table format. E.g. all the string columns become nvarchar(max). Is there any way to keep the table format as specified in the CREATE TABLE script?

Example of my write statement in pyspark:

df.write
  .mode("overwrite")
  .format("jdbc")
  .option("url", f"jdbc:sqlserver://myserver.database.windows.net;databaseName=mydatabase;")
  .option("dbtable", "mytable")
  .option("user", jdbcUsername)
  .option("password", jdbcPassword)
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
  .save()

Change the mode to append instead of overwrite. See here: Save modes — Nikhil
– Nikhil, Commented May 24, 2020 at 8:22
Thanks, but I want to replace the data already in the table. If I'm using append, is there some way to truncate the table first? — Magnus Johannesson
– Magnus Johannesson, Commented May 24, 2020 at 10:18

moon · Accepted Answer · 2020-05-25 04:54:23Z

0

You can specify the schema using customSchema

Below is an example:

df.write
  .mode("overwrite")
  .format("jdbc")
  .option("url", f"jdbc:sqlserver://myserver.database.windows.net;databaseName=mydatabase;")
  .option("dbtable", "mytable")
  .option("user", jdbcUsername)
  .option("password", jdbcPassword)
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
  .option("customSchema", "your_str_col_here STRING")
  .save()

answered May 25, 2020 at 4:54

moon

1,8523 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing a dataframe to a SQL database without replacing table format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related