Can Import from Azure DataBricks, but cannot Direct Query?

[zerosugar]

Has anyone had difficulty with getting data from Azure DataBricks when they want to Direct Query the data? 

As a background, I have report in which I was able to import a small dataset from the particular Azure DataBricks workspace. However, when I user the same Server Hostname and HTTP Path to connect but select Direct Query as the default storage method, I get this error: 
DataSource.Error: ODBC: ERROR [08S01] [Microsoft][Hardy] (115) Connection failed with error: SSL_connect: wrong version number Details: DataSourceKind=Databricks DataSourcePath={"host":"adb-...","httpPath":"sql\..."} OdbcErrors=[Table]

I have removed the text of the "host" and "httpPath" values from those key-value pairs. What does this error mean and how can I resolve it? In the JDBC URL text box in my Cluster Advanced Settings, I can see a a field where ssl is equal to a number. Am I supposed to pass that along to the Azure Databricks connection tool in PBI Desktop? 

I should also note that I can't resolve this issue by downloading and using ODBC driver, because I am on a secure workspace and not able to download anything from the web. 

I'd apprecaite any insight on this issue. Thank you!

[v-henryk-mstf]

Hi @zerosugar ,

According to your description, for Azure DataBricks data source, it supports both import and direct query modes.

If you want to switch to import mode, you can refer to the following steps：

• Follow the steps described in Connect with Power BI Desktop to create a connection, using Import as the data connectivity mode.
• In the Navigator, right click the top-most item containing the selected host name and HTTP path and click Transform Data to open the Power Query Editor.
• Click Transform Data in the NavigatorIn the function bar, replace the function name Databricks.Catalogs with Databricks.Query and apply the change. This creates a Power Query function that takes a SQL query as parameter.
• Enter the desired SQL query in the parameter field and click Invoke. This executes the query and a new table is created with the query results as its contents.

More details, you can read below document link:

Connect to Power BI - Azure Databricks | Microsoft Learn

Connect Power BI to Azure Databricks (bizone.se)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zerosugar]

Hi @v-henryk-mstf, 

Thank you for the quick reply! I tried what you have suggested, but I'm still facing issues. 

Here are the steps I took:

1. Before doing anything, I went to Options -> Security -> Authentication Browser -> Check On Use my default web browser. I have to do this because I am unable to connect to my Azure Databricks Workspace without using my default web browser.

2. Get Data- > Azure Databricks. Sign in using Azure Active Directory. This took me to my web browser where I clicked on my user profile to sign-in. Back in Power BI, I entered the Server Hostname and HTTP Path. I got both of these from Compute -> All-Purpose Clusters -> Advanced Options -> JDBC/ODBC. There is only one cluster and it is running.

3. I tried with specifying the Catalog and Database and also without specifying Catalog and Database. We have one catalog but multiple databases. Each database has multiple tables. I'm trying to get Table01 from Database01. I was already able to get the data from that Table01 by using Import. 

5. I right-clicked on the top-most item containing the Server Hostname : HTTP Path and selected Transform Data. I then changed Databricks.Catalogs to Databricks.Query and entered a SQL Query.

4. I tried these SQL queries: 

SELECT * FROM Table01. SELECT * FROM Database01.Table01. Select * FROM Catalog01.Database01.Table01. None of the queries successfully returned a table.

I received this error message:

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][ThriftExtension] (14) Unexpected response from server during a HTTP connection: connect() failed: errno = 10060.
ERROR [HY000] [Microsoft][ThriftExtension] (14) Unexpected response from server during a HTTP connection: connect() failed: errno = 10060.
Details:
DataSourceKind=Databricks
DataSourcePath={"host":"adb-...","httpPath":"sql\..."}
OdbcErrors=[Table]

Do you know what could be causing this?

[amitchandak]

@zerosugar , Make sure, you have taken the correct details available under ODBC/JDBC

refer: https://youtu.be/AWTDEQCoOVw?t=686

https://social.msdn.microsoft.com/Forums/azure/en-US/a9ad2ab0-78e5-4022-b9e9-580586078803/databricks...

[zerosugar]

Hi @amitchandak,

Thank you for your insight! I watched the video and can confirm that I am taking the correct details available under ODBC/JDBC. I tried @v-henryk-mstf's solution but ran into some issues as I had mentioned above. 

Additionally, I've also looked at the link you sent and tried connecting through Spark, but am getting this error message: "Realm is a required parameter". I'm getting this message but am not being given the opportunitiy to even look at the 'Realm' parameter field. If I click edit, it just takes me back to the first Spark parameter entry page (for Server, Protocol, Advanced Options, and Data Connectivity Mode). 

Any thoughts on this situation? Not sure where to go from here.