JDBC

Overview

Learn how to read data from and write data to relational databases (like MySQL, PostgreSQL, or SQL Server) using Spark's JDBC driver.

What You Will Learn

Detailed Concept Explanation

Spark can connect to external databases using the standard Java Database Connectivity (JDBC) API.

The basic syntax is: spark.read.format("jdbc").option("url", "jdbc:db_type://host").option("dbtable", "table").load()

Parallel JDBC Reads

By default, Spark reads data from a JDBC database using a single thread, meaning only one worker node will fetch the data.

To load data in parallel, you must specify partitioning options:

• partitionColumn: The column to partition by (typically an auto-incrementing integer key).
• lowerBound and upperBound: The min/max values of the partition column.
• numPartitions: The number of parallel JDBC connection threads to launch.

Code Examples

Input Database Table Preview

Below is the relational database table users in MySQL:

| id | name | |----|------| | 1 | Alice| | 2 | Bob |

Python (PySpark) Implementation

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("JDBCTest").getOrCreate()

# Load table from PostgreSQL/MySQL via JDBC
db_df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://localhost:5432/mydb") \
    .option("dbtable", "users") \
    .option("user", "postgres") \
    .option("password", "secret") \
    .load()
db_df.show()

Expected Output

+---+-----+
| id| name|
+---+-----+
|  1|Alice|
|  2|  Bob|
+---+-----+

Execution Plan Diagram (Python & Scala)

Scala Implementation

import org.apache.spark.sql.SparkSession
import java.util.Properties

val spark = SparkSession.builder().appName("JDBCScala").getOrCreate()

val connectionProperties = new Properties()
connectionProperties.put("user", "postgres")
connectionProperties.put("password", "secret")

val df = spark.read.jdbc(
  "jdbc:postgresql://localhost:5432/mydb", 
  "users", 
  connectionProperties
)
df.show()

Expected Output

+---+-----+
| id| name|
+---+-----+
|  1|Alice|
|  2|  Bob|
+---+-----+

SQL Implementation

-- Registering JDBC table as an external SQL view
CREATE TEMPORARY VIEW db_users
USING org.apache.spark.sql.jdbc
OPTIONS (
  url 'jdbc:postgresql://localhost:5432/mydb',
  dbtable 'users',
  user 'postgres',
  password 'secret'
);

SELECT * FROM db_users;

Expected Output

Executing this SQL query returns:

Common Mistakes

• Overloading Databases: Loading a large database table using a high value for numPartitions (e.g. 500). This launches 500 parallel connection queries against the database, which can overload and crash the database server. Keep partition counts balanced.

Best Practices

• Configure Partition Bounds: When using parallel JDBC reads, choose an indexed, uniformly-distributed integer column (like an auto-incrementing ID) as the partition column to ensure even task distribution.

Interview Perspective

Interactive Challenges

Related Topics

• MySQL
• PostgreSQL
• Reading Data