I've dug through as many similar posts I can find on this issue, and the closest is this question:Apache Spark java.sql.SQLException: No suitable driver for my Azure SQL Server however I tried that user's solution in the comments and I'm still getting the same error. This code works perfectly well in my dev environment, but is failing in production. I'm using the same cluster type/parameters in each environment (9.1 LTS, but I also tried 7.3LTS which apparently worked for the other poster).
Here is the code I'm using to connect to my azure sql server database (database name/user/pw swapped out, in the actual code I'm pulling this connection string from keyvault):
jdbcConnectionString = "jdbc:sqlserver://databasename.database.windows.net:1433;database=databasename;user=username@databasename;password=password;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
data = spark.createDataFrame([(userEmail, emailSubject ,emailBody)], ["address_to", "email_subject","body"])
data.write.jdbc(jdbcConnectionString,"dbo.email_queue", mode="append")
As I understand it from the comments to the linked question, this driver should be part of DBR. To verify, I ran this command and got the following output:
res0: Class[_] = class com.microsoft.sqlserver.jdbc.SQLServerDriver
As far as I can tell, this should be working just as it is in dev, but I'm still getting the error:
java.sql.SQLException: No suitable driver
I resolved this by using the dev JDBC connection string and simply inserting into the dev database, and it worked fine. So it must not have been an issue with the driver, but something between databricks and the prod db. Since all I'm doing is inserting rows for a logic app to grab and email out, it didn't necessarily need to work in prod.