Spark JDBC Connections

JDBC lets Spark read tables from relational databases such as MySQL, PostgreSQL, SQL Server, and Oracle. It also writes results back. Companies keep much of their business data in these databases, so a JDBC connection often forms the first step of a data pipeline.

The Layman View

A database holds records in a locked cabinet. JDBC is the key and the corridor between the cabinet and Spark. A driver file acts as the translator. Each database vendor supplies its own driver, and Spark loads it at startup.

Connection Pieces

Spark executors  -->  JDBC driver  -->  Network  -->  Database server
   (workers)         (translator)                      (tables)

• url names the server, port, and database.
• dbtable names the table or a query wrapped as a table.
• user and password prove who you are.
• driver names the Java class of the driver.

Loading the Driver

Start Spark with the driver jar or a package name.

pyspark --packages org.postgresql:postgresql:42.7.3
# or
spark-submit --jars /opt/drivers/postgresql.jar my_job.py

Reading a Table

Keep passwords out of source code. Read them from environment variables or a secrets manager.

import os

df = (spark.read.format("jdbc")
        .option("url", "jdbc:postgresql://db.example.com:5432/shop")
        .option("dbtable", "public.orders")
        .option("user", os.environ["DB_USER"])
        .option("password", os.environ["DB_PASS"])
        .option("driver", "org.postgresql.Driver")
        .load())

df.printSchema()

Why a Plain Read Runs Slowly

A plain read uses one connection and one task. A table with 100 million rows flows through a single pipe while the other executors sit idle.

Plain read:
Database ===== one connection =====> Executor 1     (Executors 2, 3, 4 idle)

Parallel Reads

Tell Spark how to cut the table into ranges. Each range becomes one task with its own connection.

df = (spark.read.format("jdbc")
        .option("url", url)
        .option("dbtable", "public.orders")
        .option("user", user).option("password", pwd)
        .option("partitionColumn", "order_id")
        .option("lowerBound", 1)
        .option("upperBound", 1000000)
        .option("numPartitions", 8)
        .load())

order_id 1 ---------------------------------- 1,000,000

Task 1: 1 - 125,000          Task 5: 500,001 - 625,000
Task 2: 125,001 - 250,000    Task 6: 625,001 - 750,000
Task 3: 250,001 - 375,000    Task 7: 750,001 - 875,000
Task 4: 375,001 - 500,000    Task 8: 875,001 - 1,000,000

Choose a numeric or date column with evenly spread values. The bounds only decide the slice size. Spark still reads rows outside the bounds.

Pushing Work to the Database

Filters on a JDBC DataFrame often move into the database query. The database returns fewer rows. A custom query gives full control.

query = "(SELECT order_id, total FROM orders WHERE order_date >= '2026-01-01') AS recent"
df = spark.read.format("jdbc").option("url", url).option("dbtable", query) \
          .option("user", user).option("password", pwd).load()

Writing to a Database

(result.write.format("jdbc")
       .option("url", url)
       .option("dbtable", "public.daily_summary")
       .option("user", user).option("password", pwd)
       .option("batchsize", 5000)
       .mode("append")
       .save())

• append adds rows to the existing table.
• overwrite drops and recreates the table unless you set truncate to true.
• errorifexists stops the job when the table already exists.

Safe Practices

• Limit the number of partitions. Every partition opens a connection, and a busy database can refuse too many at once.
• Read from a replica server for heavy analysis to protect the main application.
• Create a database account with read-only rights for read jobs.
• Copy large tables to Parquet once and run repeated analysis on the copy.

Testing the Connection

Run a tiny query before a large job. A one-row read proves that the driver, network path, and credentials all work.

test = (spark.read.format("jdbc")
          .option("url", url)
          .option("dbtable", "(SELECT 1 AS ok) AS t")
          .option("user", user).option("password", pwd).load())
test.show()

Typical failures include a missing driver class, a blocked firewall port, and a wrong password. The error message names the cause in most cases, so read the first lines of the stack trace.