4.1 JDBC Reads, Writes, and Parallelism

JDBC as a Spark SQL data source

Java Database Connectivity (JDBC) lets Spark read a relational database into a DataFrame and write tabular results back. It differs from a Spark SQL JDBC server: the data-source connector makes Spark the client of another database, while a SQL server endpoint lets an external client query Spark. Certification scenarios often test the correct direction and where parallelism is configured.

The source-specific JDBC driver must be available on Spark's classpath, and the driver/executor environment needs appropriate network access and authorization. Supplying a PostgreSQL URL does not install its driver. In the examples below, jdbc_url, jdbc_user and jdbc_password are assumed to be supplied securely by the environment. They are placeholders for already authorized connection settings, not values to paste into shared source or logs.

orders = (spark.read.format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", "sales.orders")
    .option("user", jdbc_user)
    .option("password", jdbc_password)
    .option("driver", "org.postgresql.Driver")
    .load())
orders.createOrReplaceTempView("jdbc_orders")
report = spark.sql("""
    SELECT region, SUM(amount) AS revenue
    FROM jdbc_orders
    WHERE status = 'COMPLETE'
    GROUP BY region
""")

The relational operations use Spark's SQL engine, with eligible filters pushed to the source according to connector support. Inspect the plan and database workload rather than assuming the whole query runs exclusively on either system. Schema discovery itself can contact the database before a later row action.

Reading a query or a table

The dbtable option identifies a table or a parenthesized, aliased query usable in a SQL FROM clause. The query option supplies a SELECT which Spark wraps as a subquery. Do not specify both. A database can impose restrictions on the SQL accepted inside that wrapper. The standard reader also forbids combining query with partitionColumn; put an aliased subquery into dbtable when parallel partition reads are required.

active = (spark.read.format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", "(SELECT id, region, amount FROM sales.orders WHERE status = 'COMPLETE') AS src")
    .option("user", jdbc_user)
    .option("password", jdbc_password)
    .option("partitionColumn", "id")
    .option("lowerBound", 0)
    .option("upperBound", 1000000)
    .option("numPartitions", 4)
    .load())

The explicit WHERE inside dbtable filters the population. By contrast, lowerBound and upperBound only calculate partition strides. Rows outside those values are not thereby excluded. If a question requires only IDs from 0 through 999999, encode that predicate in the source query or an appropriate filter; do not answer that the partition bounds enforce it. The partitioning column must be numeric, date or timestamp, and all required partition options must be supplied together.

Balance parallelism against source pressure

numPartitions limits read/write parallelism and concurrent JDBC connections for the connector. More connections can improve throughput until the database, network or connection pool saturates. They can also hurt a transactional source. A small table may not benefit from dozens of readers; a highly skewed ID range can leave one read partition much larger than the rest.

For a worked capacity decision, suppose a source owner permits at most four concurrent extraction connections. A requested numPartitions of 32 violates that budget even if the Spark cluster has 32 cores. Start with four and measure. Increasing Spark executors does not override database limits. If one partition dominates, inspect value distribution or use deliberately chosen predicate partitions rather than increasing load blindly.

Writing results and save modes

(report.write.format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", "analytics.region_revenue")
    .option("user", jdbc_user)
    .option("password", jdbc_password)
    .option("numPartitions", 4)
    .option("batchsize", 1000)
    .mode("append")
    .save())

Append inserts rows; it does not deduplicate a previous load or update an existing primary key automatically. Re-running this code can insert duplicate reports or fail a database constraint. Overwrite can drop/recreate a table depending on dialect and options, potentially affecting indexes and grants. A supported truncate option has different behavior, but is not portable across all databases or schema changes. Verify source-specific behavior before using overwrite for a production table.

A write is distributed across partitions/connections. A transaction isolation setting applies to a connection, not an automatic global transaction over every partition and external system. Retries and partial success need a deliberate ingestion design, such as staging data under a load identifier and applying a database-side transactional merge. The connector alone does not supply business-key upsert semantics.

Choosing the answer in a scenario

Separate three questions: which rows should be read, how many connections may read them, and where the output should go. Use a WHERE predicate for selection, partition options for read strides, and a deliberate writer mode for destination behavior. Keep credentials outside code, confirm the driver/classpath, and inspect pushed filters and per-partition workload. A SQL temporary view registered from the JDBC DataFrame remains session-scoped and does not create a new persistent database table.

See the official JDBC source documentation for supported options and dialect restrictions.