Query MySQL with Azure 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 What is Lakehouse Federation.
Using JDBC
Python
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()
)
Scala
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
Using Databricks Runtime 11.3 LTS and above, you can use the named connector to query MySQL. See the following examples:
Python
remote_table = (spark.read
.format("mysql")
.option("dbtable", "table_name")
.option("host", "database_hostname")
.option("port", "3306") # Optional - will use default port 3306 if not specified.
.option("database", "database_name")
.option("user", "username")
.option("password", "password")
.load()
)
SQL
DROP TABLE IF EXISTS mysql_table;
CREATE TABLE mysql_table
USING mysql
OPTIONS (
dbtable '<table-name>',
host '<database-host-url>',
port '3306', /* Optional - will use default port 3306 if not specified. */
database '<database-name>',
user '<username>',
password '<password>'
);
SELECT * from mysql_table;
Scala
val remote_table = spark.read
.format("mysql")
.option("dbtable", "table_name")
.option("host", "database_hostname")
.option("port", "3306") # Optional - will use default port 3306 if not specified.
.option("database", "database_name")
.option("user", "username")
.option("password", "password")
.load()
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om