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

The exact steps I did were:
Create a custom environment (preview) and upload a custom library to it:


After the upload, I published these changes.

Then I referenced this environment as the default environment for my workspace.
The Notebook that I am using is connected to this environment. 
Then I just launched the code I gave in my initial post. I did not call this library specifically in the notebook though. Is that necessary? If so, how?

Thanks.

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

Hi H26027 

 

Apologize for the delay in response from my end.

Following up to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others .

If the issue still persists, kindly provide the following additional information for further assistance.

To ensure compatibility and troubleshoot the Databricks JDBC driver error you're encountering, could you please confirm the version of your Databricks? Version compatibility might be a potential cause. We can troubleshoot by verifying the appropriate JDBC driver for your specific Databricks runtime version. This might help you resolve the issue.

For additional information please refer : Unity Catalog limitations

                                                               : Databricks JDBC Driver

 

I hope this information helps. Please do let us know if you have any further queries.

 

Thanks.

  • H26027's avatar
    H26027
    Regular Visitor
    2 years ago

    Hi v-cboorla-msft,

     

    I am using databricks cluster with runtime version 13.3 LTS in shared access mode that is unity catalog enabled.
    The JDBC Driver I downloaded is the latest (2.6.36) downloaded from here https://www.databricks.com/spark/jdbc-drivers-archive.


    The Databricks side is not the problem though, since the fabric notebook already says it cannot find the driver. I believe the driver is not loaded in the environment correctly, but I don't see anything I did wrong. 

    For testing purpose I added a public library from pip to the same environment. This library is available in the notebook without any problem.

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

      Hi H26027 

       

      Apologize for the inconvenience that you are facing here.

      Please reach out to our support team to gain deeper insights and explore potential solutions, it's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

      Please go ahead and raise a support ticket to reach our support team:

      https://support.fabric.microsoft.com/support

      After creating a Support ticket please provide the ticket number as it would help us to track for more information.

       

      Thank you.

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

        Hi H26027 

         

        I'm following up on my previous inquiry to see if you've had a chance to create a support ticket for this issue. If a ticket has been created, I would appreciate it if you could provide the ticket number for our reference. This will allow us to track the progress of the issue.

         

        Thanks.