topic Speed Up JDBC Write from Databricks Notebook to MS SQL Server in Data Engineering

Speed Up JDBC Write from Databricks Notebook to MS SQL Server

berserkersap — Sat, 16 Sep 2023 15:02:48 GMT

Hello Everyone,

I have a use case where I need to write a delta table from DataBricks to a SQL Server Table using Pyspark/ python/ spark SQL .

The delta table I am writing contains around 3 million records and the SQL Server Table is neither partitioned nor indexed. It is taking around 8-10 min to write into the SQL Server Table no matter what compute I use (using default JDBC driver).

The code being used:

df.write.mode("overwrite").option("truncate", True ).jdbc(Url, "dbtable" , properties = properties )

I have executed this code on DataBricks runtime 11.3LTS using the compute E4ds v4 and E16ds v4 but in both the cases it took 8-10 min.

Can anyone please suggest a way to reduce this time ?

P.S. I have tried to increase the batch size but even that was not helping. If it was more than 20000 the process became even slower

Also, I cannot install anything on the cluster according to the client's requirement. But if this is absolutely necessary please let me know what to install and how to install.

I have tried using the driver "com.microsoft.sqlserver.jdbc.spark", however, it gave me an error

java.lang.ClassNotFoundException: Failed to find data source: com.microsoft.sqlserver.jdbc.spark.

Thank You

Re: Speed Up JDBC Write from Databricks Notebook to MS SQL Server

NandiniN — Wed, 01 Jan 2025 19:30:20 GMT

You can partition the DataFrame in PySpark before writing it to SQL Server. For example:

df.repartition(10).write.mode("overwrite").option("truncate", True).jdbc(Url, "dbtable", properties=properties)

Re: Speed Up JDBC Write from Databricks Notebook to MS SQL Server

NandiniN — Wed, 01 Jan 2025 19:32:14 GMT

https://docs.databricks.com/en/connect/external-systems/jdbc.html#control-parallelism-for-jdbc-queries

When writing to databases using JDBC, Apache Spark uses the number of partitions in memory to control parallelism. You can repartition data before writing to control parallelism. Avoid high number of partitions on large clusters to avoid overwhelming your remote database. The following example demonstrates repartitioning to eight partitions before writing:

(employees_table.repartition(8)
  .write
  .format("jdbc")
  .option("url", "<jdbc-url>")
  .option("dbtable", "<new-table-name>")
  .option("user", "<username>")
  .option("password", "<password>")
  .save()
)

Re: Speed Up JDBC Write from Databricks Notebook to MS SQL Server

NandiniN — Wed, 01 Jan 2025 19:33:55 GMT

https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html

numPartitions

(none)

The maximum number of partitions that can be used for parallelism in table reading and writing. This also determines the maximum number of concurrent JDBC connections. If the number of partitions to write exceeds this limit, we decrease it to this limit by calling coalesce(numPartitions) before writing.

read/write

Re: Speed Up JDBC Write from Databricks Notebook to MS SQL Server

VZLA — Fri, 03 Jan 2025 10:51:28 GMT

@berserkersap have you had time to identify where's the bottleneck? e.g.: sequential writes, network latency/throughput, or maybe you have a connection pool in the target much lower than the number of connection threads in the source?