topic Re: How do I specify column types when writing to a MSSQL server using the JDBC driver ( in Data Engineering

How do I specify column types when writing to a MSSQL server using the JDBC driver (

jonathan-dufaul — Wed, 14 Dec 2022 17:50:16 GMT

I have a pyspark dataframe that I'm writing to an on-prem MSSQL server--it's a stopgap while we convert data warehousing jobs over to databricks.

The processes that use those tables in the on-prem server rely on the tables maintaining the identical structure. I have a structure, for instance, like below.

SOURCE	VARCHAR(3)
LOCATION_NO	VARCHAR(3)
SKU_NO	LONG
CASE_SKU_NO	LONG
ITEM_TYPE	VARCHAR(3)
SYSTEM	VARCHAR(MAX)
PRICE	DOUBLE

when I've just done vanilla

  (my_dataframe.write.format("jdbc") 
    .option("url",sqlsUrl) 
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") 
    .option("dbtable", table_name )
    .option("user", username) 
    .option("password", password) 
    .save(mode=mode)
  )

I get a different structure, biggest difference being varchar becomes nvarchar(max). Since the jobs on the server join to other tables that have varchar type, it means that jobs take hours that used to take minutes.

Ideally I'd like to specify the schema type and then save the table.

Follow up question. Am I using the most recent/correct JDBC driver?

I hope that makes sense.

Re: How do I specify column types when writing to a MSSQL server using the JDBC driver (

dasanro — Wed, 08 Nov 2023 15:50:13 GMT

It's happenging to me too!

Did you find any solution @jonathan-dufaul ?

Thanks!!

Re: How do I specify column types when writing to a MSSQL server using the JDBC driver (

jonathan-dufaul — Thu, 05 Dec 2024 16:58:20 GMT

I think I ended up doing a truncate and mode = "append" so the structure stayed the same.

For truncate I had to get some direct access from the sparkcontext/sc variable

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url, mssql_user, mssql_pass)
connection.prepareCall("TRUNCATE TABLE my_table").execute()
connection.close()