Query MySQL with Databricks
This example queries MySQL using its JDBC driver. For more details on reading, writing, configuring parallelism, and query pushdown, see Query databases using JDBC.
Note
You may prefer Lakehouse Federation for managing queries to MySQL. See Run queries using Lakehouse Federation.
Using JDBC
driver = "org.mariadb.jdbc.Driver"
database_host = "<database-host-url>"
database_port = "3306" # update if you use a non-default port
database_name = "<database-name>"
table = "<table-name>"
user = "<username>"
password = "<password>"
url = f"jdbc:mysql://{database_host}:{database_port}/{database_name}"
remote_table = (spark.read
.format("jdbc")
.option("driver", driver)
.option("url", url)
.option("dbtable", table)
.option("user", user)
.option("password", password)
.load()
)
val driver = "org.mariadb.jdbc.Driver"
val database_host = "<database-host-url>"
val database_port = "3306" # update if you use a non-default port
val database_name = "<database-name>"
val table = "<table-name>"
val user = "<username>"
val password = "<password>"
val url = s"jdbc:mysql://${database_host}:${database_port}/${database_name}"
val remote_table = spark.read
.format("jdbc")
.option("driver", driver)
.option("url", url)
.option("dbtable", table)
.option("user", user)
.option("password", password)
.load()
Using the MySQL connector in Databricks Runtime
In Databricks Runtime 11.2 and above, you can use the named connector to query MySQL. See the following examples:
remote_table = (spark.read
.format("mysql")
.option("dbtable", table_name)
.option("host", database_host_url)
.option("port", 3306)
.option("database", database_name)
.option("user", username)
.option("password", password)
.load()
)
DROP TABLE IF EXISTS mysql_table;
CREATE TABLE mysql_table
USING mysql
OPTIONS (
dbtable '<table-name>',
host '<database-host-url>',
port '3306',
database '<database-name>',
user '<username>',
password '<password>'
);
SELECT * from mysql_table;
val remote_table = spark.read
.format("mysql")
.option("dbtable", table_name)
.option("host", database_host_url)
.option("port", 3306)
.option("database", database_name)
.option("user", username)
.option("password", password)
.load()