JDBC databricks to databricks connection

user_13849040 0
Viewed 763

I am looking to connect to a delta lake in one databricks instance from a different databricks instance. I have downloaded the sparksimba jar from the downloads page. When I use the following code:

result = spark.read.format("jdbc").option('user', 'token').option('password', <password>).option('query', query).option("url", <url>).option('driver','com.simba.spark.jdbc42.Driver').load()

I get the following error:

Py4JJavaError: An error occurred while calling o287.load.: java.lang.ClassNotFoundException: com.simba.spark.jdbc42.Driver

From reading around it seems I need to register driver-class-path, but I can't find a way where this works.

I have tried the following code, but the bin/pyspark dir does not exist in my databricks env:

%sh bin/pyspark --driver-class-path $/dbfs/driver/simbaspark/simbaspark.jar --jars /dbfs/driver/simbaspark/simbaspark.jar

I have also tried:

java -jar /dbfs/driver/simbaspark/simbaspark.jar

but I get this error back: no main manifest attribute, in dbfs/driver/simbaspark/simbaspark

pyspark azure-databricks databricks jdbc python
2 Answers

If you want to do that (it's really not recommended), then you just need to upload this library to DBFS, and attach it to the cluster via UI or the init script. After that it will be available for both driver & executors.

But really, as I understand, your data is stored on the DBFS in the default location (so-called DBFS Root). But storing data in the DBFS Root isn't recommended, and this is pointed in the documentation:

Data written to mount point paths (/mnt) is stored outside of the DBFS root. Even though the DBFS root is writeable, Databricks recommends that you store data in mounted object storage rather than in the DBFS root. The DBFS root is not intended for production customer data.

So you need to create a separate storage account or container in existing storage account, and mount it to the Databricks workspace - this could be done to the multiple workspaces, so you'll solve the problem of data sharing between multiple workspaces. It's a standard recommendation for Databricks deployments in any cloud.

Alex_Ott_186270

Here's an example code block that I use (hope it helps)

hostURL = "jdbc:mysql://xxxx.mysql.database.azure.com:3306/acme_dbuseSSL=true&requireSL=false"
databaseName = "acme_db"
tableName = "01_dim_customers"
userName = "xxxadmin@xxxmysql"
password = "xxxxxx"


df = (
   spark.read
        .format("jdbc") 
        .option("url", f"{hostURL}") 
        .option("databaseName", f"{databaseName}")
        .option("dbTable", f"{tableName}") 
        .option("user", f"{userName}") 
        .option("password", f"{password}") 
        .option("ssl", True) 
        .load() 
)

display(df)
user_172100550
Related