Apache Spark & JDBC : Socket Exception : Connection Reset

user_3512678 0
Viewed 383

Our Spark Java application, task got an exception "com.microsoft.sqlserver.jdbc.SQLServerException: java.net.Socket Exception : Connection Reset", while it was running.

It makes a connection with database using following code, and table has millions of records:

session.read().format("jdbc")
                        .option("url", dbProperties.getProperty("URL"))
                        .option("driverClass", dbProperties.getProperty("DRIVERCLASS"))
                        .option("username", dbProperties.getProperty("USERNAME"))
                        .option("password", dbProperties.getProperty("PASSWORD"))
                        .option("dbtable", "(" + formattedSQL + ") as " + tablenameDS)
                        .load();

Is there a way in Apache Spark, that it performs some X connection retries to the database with a delay between retries?

Will increasing the following property "spark.task.maxFailures" default value from 4, fix this issue?

apache-spark sql-server java
1 Answers

Two solutions:

  1. Add JDBC-specific properties as options in option(), like connectRetryCount. You can check the list at: https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15.

  2. Add the JDBC properties in the URL itself.

Your code could look like:

session.read().format("jdbc")
    .option("connectRetryCount", 200)
    .option("url", dbProperties.getProperty("URL"))
    .option("driverClass", dbProperties.getProperty("DRIVERCLASS"))
    .option("username", dbProperties.getProperty("USERNAME"))
    .option("password", dbProperties.getProperty("PASSWORD"))
    .option("dbtable", "(" + formattedSQL + ") as " + tablenameDS)
    .load();

You can find detailed examples there: https://github.com/jgperrin/net.jgp.books.spark.ch08/tree/master/src/main/java/net/jgp/books/spark/ch08/lab100_mysql_ingestion.

user_31972440
Related