Forum Discussion

H26027's avatar
H26027
Regular Visitor
2 years ago
Solved

Extract Data from Databricks Unity Catalog

Hi, I want to connect to Databricks UnityCatalog tables from Fabric Notebooks. Therefore I tried to utilize the databricks jdbc driver to read data with pyspark in this way:   personal_access_toke...
data engineering
  • H26027's avatar
    H26027
    2 years ago

    I found a solution to my previous mentioned problem. The connection from Fabric Notebook to Databricks Unity Catalog is now working as expected - but not as described initially by Microsoft Documentation.

    What I did now - in case anybody meets the same issue:

    1. Download the Databricks JDBC Driver and store the .jar to Fabric Lakehouse.

    2. Create a custom environment in Fabric 

    3. Add a spark property for spark.jars that is pointing to the ABFSS location of the uploaded .jar file:


    4. In the Notebook itself, connect to this custom environment and use this call for reading the data:

    personal_access_token = "XXX"
jdbc_url = "jdbc:databricks://adb-XXX.azuredatabricks.net:443/default"
http_path = "sql/protocolv1/o/XXX/XXX" 
query = "[catalog].[schema].[table]" # or "(SELECT [selection] FROM [table]) as alias"

df = (spark.read.format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", query)
    .option("user", "token")
    .option("password", personal_access_token)
    .option("driver", "com.databricks.client.jdbc.Driver")
    .option("ssl", "1")
    .option("ThriftTransport", "2")
    .option("AuthMech", "3")
    .option("httpPath", http_path)
    .option("UseNativeQuery", "0")
    .load())

    The option "UseNativeQuery" is necessary too. Otherwise the jdbc dialect seems to be wrong, causing the before mentioned issue with data types (+ issues with quotes).

     

    Thanks v-cboorla-msft for your help.

H26027's avatar
H26027
Regular Visitor
2 years ago

Hi v-cboorla-msft ,

thanks again for the feedback. The ticket is opened under #2403140050003202.

The link you just provided actually really did the trick, but that is definetely not how it should work according to the official documentation.

 

Anyways, I could establish the connection now. However, I am getting errors when reading data that is not of type string.
Reading data to the dataframe is fine. Printing the schema works fine too and shows the correct datatypes. When I want to display the data, I get errors for everything else than string e.g. like: "Error converting value to Timestamp" (or for LONG values too).
If I already cast the value as string in the select sql for the jdbc connection, then it works fine.

The data itself is not corrupted, since the same operations work fine in databricks directly.

 

Any ideas?
Thanks

H26027's avatar
H26027
Regular Visitor
2 years ago

I found a solution to my previous mentioned problem. The connection from Fabric Notebook to Databricks Unity Catalog is now working as expected - but not as described initially by Microsoft Documentation.

What I did now - in case anybody meets the same issue:

1. Download the Databricks JDBC Driver and store the .jar to Fabric Lakehouse.

2. Create a custom environment in Fabric 

3. Add a spark property for spark.jars that is pointing to the ABFSS location of the uploaded .jar file:


4. In the Notebook itself, connect to this custom environment and use this call for reading the data:

personal_access_token = "XXX"
jdbc_url = "jdbc:databricks://adb-XXX.azuredatabricks.net:443/default"
http_path = "sql/protocolv1/o/XXX/XXX" 
query = "[catalog].[schema].[table]" # or "(SELECT [selection] FROM [table]) as alias"

df = (spark.read.format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", query)
    .option("user", "token")
    .option("password", personal_access_token)
    .option("driver", "com.databricks.client.jdbc.Driver")
    .option("ssl", "1")
    .option("ThriftTransport", "2")
    .option("AuthMech", "3")
    .option("httpPath", http_path)
    .option("UseNativeQuery", "0")
    .load())

The option "UseNativeQuery" is necessary too. Otherwise the jdbc dialect seems to be wrong, causing the before mentioned issue with data types (+ issues with quotes).

 

Thanks v-cboorla-msft for your help.

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    Hi H26027 

     

    Glad that your query got resolved and thank you for sharing the details in the community as it can be helpful to others, much appreciated.

    Please continue using Fabric Community for any help regarding your queries.

     

    Thanks.