beginner
MySQL
8 min readLast updated: 2026-07-08
Overview
Connect Spark to MySQL databases to read tables and write query outputs using the MySQL JDBC driver.
What You Will Learn
In this lesson, you will learn:
- Driver Coordinate: Identifying the MySQL driver coordinates class.
- MySQL Connection: Setting MySQL connection URLs and ports.
- Read/Write Operations: Running queries on MySQL tables.
Detailed Concept Explanation
To connect Spark to MySQL, you must use the MySQL JDBC driver (com.mysql.cj.jdbc.Driver).
The standard URL structure is:
jdbc:mysql://<host>:<port>/<database> (default port is 3306).
Code Examples
Python (PySpark) Implementation
python
from pyspark.sql import SparkSession
spark = SparkSession.builder \
.appName("MySQLTest") \
.config("spark.jars.packages", "mysql:mysql-connector-java:8.0.28") \
.getOrCreate()
# Read from MySQL table
mysql_df = spark.read.format("jdbc") \
.option("url", "jdbc:mysql://localhost:3306/sales_db") \
.option("dbtable", "customers") \
.option("user", "admin") \
.option("password", "admin123") \
.option("driver", "com.mysql.cj.jdbc.Driver") \
.load()
mysql_df.show()
Expected Output
text
+---+-----+
| id| name|
+---+-----+
| 1| Alice|
| 2| Bob|
+---+-----+
Execution Plan Diagram (Python & Scala)
Execution Plan Diagram
SparkSession.builder
read.format(jdbc)
option(url
jdbc:mysql)
load()
show()
Scala Implementation
scala
import org.apache.spark.sql.SparkSession
val spark = SparkSession.builder().appName("MySQLScala").getOrCreate()
val mysqlDF = spark.read.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/sales_db")
.option("dbtable", "customers")
.option("user", "admin")
.option("password", "admin123")
.option("driver", "com.mysql.cj.jdbc.Driver")
.load()
mysqlDF.show()
Expected Output
text
+---+-----+
| id| name|
+---+-----+
| 1| Alice|
| 2| Bob|
+---+-----+
SQL Implementation
sql
-- Creating local table maps to MySQL tables
CREATE TABLE mysql_customers
USING org.apache.spark.sql.jdbc
OPTIONS (
url 'jdbc:mysql://localhost:3306/sales_db',
dbtable 'customers',
user 'admin',
password 'admin123',
driver 'com.mysql.cj.jdbc.Driver'
);
Common Mistakes
- Omitting MySQL Driver Jar: Forgetting to load the MySQL connector JAR at session startup. This will cause Spark to fail with a
java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Drivererror.
Best Practices
- Use Driver class explicitly: Always set the
driveroption tocom.mysql.cj.jdbc.Driverto ensure the correct driver class is loaded.