Not able to connect to Azure database for MySQL server from databricks cluster using jdbc and spark connector

1
votes

I am trying to connect to Azure database for MySQL server using databricks clusters. I tried using 2 ways described below-

  1. using jdbc

    val jdbcHostname = "<serverName>.mysql.database.azure.com"
val jdbcPort = 3306
val jdbcDatabase = "<db>"
val jdbcUrl = s"jdbc:mysql://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}?useSSL=true&requireSSL=false"

import java.sql.DriverManager
val connection = DriverManager.getConnection(jdbcUrl, "<user>@<serverName>", "<password>")

but I got the error saying "Client with IP address 'SOME_IP_ADDRESS' is not allowed to connect to this MySQL server"

I added this ip to firewall rules of Azure database for MySQL server and was able to access then. But everytime cluster restarts, ip address changes and it throws error. I don't want to "Allow access to Azure services" in mySQL server as it will allow users from another subscription as well.

  1. using Spark connector- downlaoded "com.microsoft.azure:azure-sqldb-spark:1.0.2" jar

    val config = Config(Map(
"driver"         -> "org.mariadb.jdbc.Driver",
"url"            -> "<serverName>.mysql.database.azure.com:3306",
"databaseName"   -> "<db>",
"dbTable"        -> "<dbtable>",
"user"           -> "<user>@<serverName>",
"password"       -> "<password>"
))
val data = spark.read.sqlDB(config)

But it throws error saying "java.lang.IllegalArgumentException: requirement failed: The driver could not open a JDBC connection. Check the URL: jdbc:sqlserver://<serverName>.mysql.database.azure.com:3306"

I tried in this way also-

    val df = spark.read.format("jdbc").option("driver", "org.mariadb.jdbc.Driver")
   .option("url", "jdbc:mysql://<serverName>.mysql.database.azure.com:3306/<db>?useSSL=true&requireSSL=false")
   .option("databaseName", "<db>")
   .option("dbTable", "<dbtable>")
   .option("user", "<user>@<serverName>")
   .option("password", "<password>")
   .load()

but it also throws error "Client with IP address 'SOME_IP_ADDRESS' is not allowed to connect to this MySQL server"

1
mysqlazureapache-sparkazure-databricksazure-sql-server
As far as I knew, the spark connector just supports azure SQL database and sql server. It does not support mysql. For more details, please refer to docs.microsoft.com/en-us/azure/databricks/data/data-sources/… - Jim Xu
Hi Sonali Sharma, If the answer is helpful for you, please accept(mark) it as answer. This can be beneficial to other community members. Thank you. - Leon Yue

1 Answers

1
votes

Azure SQL database for MySQL is not supported as data source in Azure Databricks. It's not on the list.

enter image description here

Please reference: Data sources for Azure Databricks and the document @Jim Xu provided for you.

Hope this helps.