Connect to databricks sql using spark and databricks jdbc

Question

I try to read a table from databricks using the databricks jdbc driver and spark

df = spark.read().format("jdbc")
        .option("url", databricks_url)
        .option("driver", "com.databricks.client.spark.jdbc.SparkJDBC42Driver")
        .option("dbtable", "default.age")
        .load() ;
        df.show();

The full stacktrace :

Exception in thread "main" java.lang.ClassCastException: com.databricks.client.spark.jdbc.SparkJDBC42Driver cannot be cast to java.sql.Driver
    at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:54)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1(JDBCOptions.scala:101)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1$adapted(JDBCOptions.scala:101)
    at scala.Option.foreach(Option.scala:437)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:101)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:39)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:33)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350)
    at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:274)
    at org.apache.spark.sql.DataFrameReader.$anonfun$load$3(DataFrameReader.scala:245)
    at scala.Option.getOrElse(Option.scala:201)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:245)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:174)
    at SparkTest.main(SparkTest.java:31)

ZAK · Accepted Answer · 2022-08-12 14:37:16Z

2

I have solved this problème like that : By installing the ODBC https://www.databricks.com/fr/spark/odbc-drivers-download while keeping the jdbc jar in the libs, and by removing the driver option from spark read; and by applying some changes on the url :

replacing spark by databricks

adding these options :

Driver=Simba; UseNativeQuery=0;

String databricks_url = "jdbc:databricks://adb-xxxxx.azuredatabricks.net:443/default;
transportMode=http;
ssl=1;
httpPath=sql/protocolv1/o/....;
AuthMech=3;
UID=token;
PWD=<my databricks_token>;
Driver=Simba;
UseNativeQuery=0;
";

In pyspark the query myst be between simple quote : '

df = spark.read.format("jdbc").option("url", databricks_url) \
.option("query", 'SELECT * FROM table') \
.load()
df.show(vertical=True)

edited Aug 12, 2022 at 14:37

answered Aug 12, 2022 at 14:28

ZAK

311 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

la_femme_it Over a year ago

Interesting, I used double quotes " for query option...

alpinedelight Over a year ago

UseNativeQuery=0; did the trick thanks - was getting "Error converting value to Timestamp"

Collectives™ on Stack Overflow

Connect to databricks sql using spark and databricks jdbc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related