Optimize Spark dataframe write performance for JDBC

Apache Spark is a popular big data processing engine that is designed to handle large-scale data processing tasks. When it comes to writing data to JDBC, Spark provides a built-in JDBC connector that allows users to write data to various relational databases easily. We can write Spark dataframe to Azure SQL Server, Azure Synapse DWH,Postgres, etc. However, the factors such as database schema, network bandwidth, data size, etc significantly impact the performance of Spark JDBC writes. In this blog, we will discuss some best practices that can be used to optimize spark dataframe write performance for JDBC to improve performance and reduce latency. Below are some important points that we need to consider while loading data from Spark Dataframe to an RDBMS table.

1. Use batch mode

Spark JDBC provides an option to write data in batch mode which significantly improves performance as compared to writing data one row at a time. Batch mode writes multiple rows in a single transaction which reduces the overhead of establishing a connection and committing for every row.

2. Use partitioning:

Spark allows users to partition data while writing to JDBC, which allows parallelism and improves write performance. We should consider the size of the data and available cluster resources to decide the number of partitions.

3. Optimize connection parameters:

Spark JDBC provides various connection parameters such as batch size, fetch size, isolation level, and so on. We need to optimize these connection parameters to improve write performance. For example, increasing batch size can help reduce the number of network round trips and improve performance.

4. Use appropriate data types:

While writing data to JDBC, it is important to use appropriate data types that are compatible with the target database. Using incompatible data types can lead to data conversion and affect write performance.

5. Optimize database schema:

We need to optimize the database schema so that the database schema matches the structure of the data we are writing. For example, using appropriate indexes and partitions can help reduce write time.

6. Use compression:

We can use data compression techniques to help reduce the size of the data that we are writing. This can help us to improve our writing performance. Spark provides various compression options such as Snappy, gzip, and so on.

7. Monitor network bandwidth:

Network bandwidth can significantly impact write performance, especially in a distributed environment. Monitoring network bandwidth can help identify bottlenecks and improve write performance.

// write data to Azure SQL using Spark JDBC with rewriteBatchedStatements enabled

df.write.mode(SaveMode.Append)

  .option("batchsize", "10000")

  .option("isolationLevel", "NONE")

  .option("numPartitions", "8")

  .option("truncate", "true")

  .option("compression", "snappy")

  .option("rewriteBatchedStatements", "true")

  .jdbc(url, "mytable", new java.util.Properties() {{

    setProperty("user", username)

    setProperty("password", password)

  }})

In this example, we use the rewriteBatchedStatements option to enable batched statement rewriting, which can improve write performance. We set the option to “true” and include it in the .option() statement along with other optimization options such as batch size, number of partitions, and compression. When Spark JDBC writes the data to the SQL database, it will automatically rewrite individual INSERT statements into batched INSERT statements, reducing the number of roundtrips and improving performance.