beginner
PostgreSQL
Overview
Connect Spark to PostgreSQL databases to query tables and write data outputs using the PostgreSQL JDBC driver.
What You Will Learn
In this lesson, you will learn:
- PostgreSQL Connection: Setting connection URLs and ports.
- Authentication: Passing secure credentials to the database.
- DataFrame Operations: Reading and writing PostgreSQL tables.
Detailed Concept Explanation
To connect Spark to PostgreSQL, you use the PostgreSQL JDBC driver (org.postgresql.Driver).
The standard URL structure is:
jdbc:postgresql://<host>:<port>/<database> (default port is 5432).
Code Examples
Python (PySpark) Implementation
python
from pyspark.sql import SparkSession
spark = SparkSession.builder \
.appName("PostgresTest") \
.config("spark.jars.packages", "org.postgresql:postgresql:42.3.3") \
.getOrCreate()
# Read from PostgreSQL table
pg_df = spark.read.format("jdbc") \
.option("url", "jdbc:postgresql://localhost:5432/mydb") \
.option("dbtable", "users") \
.option("user", "postgres") \
.option("password", "secret") \
.option("driver", "org.postgresql.Driver") \
.load()
pg_df.show()
Expected Output
text
+------+------+
|userId|status|
+------+------+
| 1|active|
+------+------+
Execution Plan Diagram (Python & Scala)
Execution Plan Diagram
SparkSession.builder
read.format(jdbc)
option(url
jdbc:postgresql)
load()
show()
Scala Implementation
scala
import org.apache.spark.sql.SparkSession
val spark = SparkSession.builder().appName("PostgresScala").getOrCreate()
val pgDF = spark.read.format("jdbc")
.option("url", "jdbc:postgresql://localhost:5432/mydb")
.option("dbtable", "users")
.option("user", "postgres")
.option("password", "secret")
.option("driver", "org.postgresql.Driver")
.load()
pgDF.show()
Expected Output
text
+------+------+
|userId|status|
+------+------+
| 1|active|
+------+------+
Common Mistakes
- Incorrect Driver Class: Setting the driver to
com.mysql.jdbc.Driveror omitting it when connecting to PostgreSQL. Always useorg.postgresql.Driver.
Related Topics
DPA
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.