Reading from databases with JDBC

Apache Spark · DataFrames

Theory

Spark reads and writes relational databases (PostgreSQL, MySQL, SQL Server, Oracle and others) through their JDBC drivers. You give a JDBC url, a table name (dbtable) or a SQL statement (query), credentials and the driver class, and the driver jar must be on the classpath (for example with --jars or --packages). A plain read opens one connection and returns one partition, so a big table is pulled through a single task.

To read in parallel, set partitionColumn (a numeric, date or timestamp column), lowerBound, upperBound and numPartitions. Spark splits that range into numPartitions strides and runs one query per stride, such as WHERE order_id >= 103 AND order_id < 105. The bounds only set the stride; rows outside them land in the first or last partition. Filters and selected columns are pushed down into the generated SQL, and the query option or a subquery in dbtable pushes a whole aggregation to the database. For example, reading ids 101 to 106 with numPartitions=3 creates three partitions of two rows each.

Why we use it

Operational data usually lives in databases, and a naive single-partition JDBC read is one of the most common reasons an ingestion job is slow or overloads the source. Knowing partitioned reads and pushdown is a frequent interview and on-the-job topic.

When to use it

Use JDBC for moderate tables, incremental loads (filter on an updated_at column) and lookups. Pick an evenly distributed, indexed partition column and keep numPartitions within what the database can handle. For very large or continuous extracts, prefer database exports or change data capture tools.

How it works

  1. spark.read.format('jdbc') with url, table and driver
  2. partitionColumn, bounds and numPartitions set? Yes: One query per stride. No: One query, one partition.
  3. Push filters and selected columns into each query's WHERE / SELECT
  4. Each task opens its own connection and runs its query
  5. Rows stream back in fetchsize batches
  6. DataFrame with one partition per stride

Syntax

spark.read.format("jdbc")
  .option("url", "jdbc:postgresql://host:5432/shop")
  .option("dbtable", "public.orders")      # or .option("query", "SELECT ...")
  .option("user", user).option("password", pwd)
  .option("partitionColumn", "order_id").option("lowerBound", 1)
  .option("upperBound", 1000000).option("numPartitions", 8)
  .option("fetchsize", 10000).load()
df.write.jdbc(url, "orders", mode="append", properties=props)

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[2]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

# Apache Derby ships inside PySpark's jars, so this JDBC demo runs without a server.
# For PostgreSQL / MySQL / SQL Server only url, driver and the driver jar change.
url = "jdbc:derby:memory:shop;create=true"
props = {"driver": "org.apache.derby.jdbc.EmbeddedDriver"}

orders = spark.createDataFrame([
    (101, "North", 55000.0), (102, "West", 30000.0), (103, "North", 1500.0),
    (104, "South", 52000.0), (105, "West", 25000.0), (106, "East", 15000.0),
], "order_id INT, region STRING, amount DOUBLE")
(orders.write.format("jdbc").option("url", url).option("dbtable", "ORDERS")
       .option("driver", props["driver"])
       .option("createTableColumnTypes", "region VARCHAR(20)")   # Derby maps STRING to CLOB
       .mode("overwrite").save())                                 # load the demo table

# 1) Plain read: ONE partition = one connection = one task
one = spark.read.jdbc(url, "ORDERS", properties=props)
print("plain read partitions:", one.rdd.getNumPartitions())

# 2) Parallel read: Spark splits order_id 101..106 into 3 ranges -> 3 queries
par = spark.read.jdbc(url, "ORDERS", column="order_id", lowerBound=101, upperBound=107,
                      numPartitions=3, properties=props)
print("parallel read partitions:", par.rdd.getNumPartitions())
(par.groupBy(F.spark_partition_id().alias("partition"))
    .agg(F.min("order_id").alias("min_id"), F.max("order_id").alias("max_id"))
    .orderBy("partition").show())

# 3) Filter pushdown: the WHERE clause is sent to the database
plan = par.filter("amount > 20000")._jdf.queryExecution().executedPlan().toString()
print("pushed filters:", plan[plan.find("PushedFilters"):].split("]")[0] + "]")

# 4) Spark created quoted lower-case columns, so the SQL below quotes them too.
#    Push a whole query with the 'query' option (or a subquery as dbtable)
(spark.read.format("jdbc").option("url", url).option("driver", props["driver"])
      .option("query", 'SELECT "region", SUM("amount") AS "sales" FROM ORDERS GROUP BY "region"')
      .load().orderBy("region").show())

# Output:
# plain read partitions: 1
# parallel read partitions: 3
# +---------+------+------+
# |partition|min_id|max_id|
# +---------+------+------+
# |        0|   101|   102|
# |        1|   103|   104|
# |        2|   105|   106|
# +---------+------+------+
#
# pushed filters: PushedFilters: [*IsNotNull(amount), *GreaterThan(amount,20000.0)]
# +------+-------+
# |region|  sales|
# +------+-------+
# |  East|15000.0|
# | North|56500.0|
# | South|52000.0|
# |  West|55000.0|
# +------+-------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic