Recently when I tried to connect Azure Databricks to Power BI Desktop using the preview Spark (Beta) connector and I experienced some problems where I did not have a Premium Sku Cluster.
In this blog I will give a brief description of how to connect Azure Databricks using Power BI Desktop without having the a Premium Sku Cluster.
How to Connect
Connecting Azure Databricks to Power BI Desktop using the Spark (Beta) connector is quite simple and can be done in a few steps.
- Start an Azure Databricks Cluster that has tables.
- Create non-expiring Access Token in Azure Databricks, under User Settings.
- Open Power BI Desktop, select Get Data and choose Spark (Beta)
- Enter Server URL, select Protocol and Data Connectivity
- Select the tables you need for report or analysis.
**Where’s the problem?**
Locating the Server URL details can be a bit complicated than you would expect. The Server URL is constructed using the JDBC URL information, which can normally be found under the JDBC/ODBC tab in the Clusters settings.
However, if you do not have a Premium SKU cluster it will be greyed out and not available, as the JDBC/ODBC connectivity option is only available if you have a Premium SKU cluster.
How to resolve this?
If you don’t want to change to Premium SKU but still want to connect to Power BI, you can do this by obtaining the JDBC URL information from the URL bar of the Cluster configuration page.
It does mean you will need to do the following additional steps to get the values in the right places to construct the final Server URL.
- Take the first part of the URL (Region URL) up to the forward slash. i.e. https://westeurope.azuredatabricks.net
- Add a colon and port number (443) with a forward slash at the end. i.e. https://westeurope.azuredatabricks.net:443/
- Add the SQL Protocol (sql/protocolv1) with a forward slash at the end. i.e. https://westeurope.azuredatabricks.net:443/sql/protocolv1/
- Add o/ and then copy the O value from the URL including a forward slash at the end. i.e. https://westeurope.azuredatabricks.net:443/sql/protocolv1/o/1659108793111072/
- Lastly, take the Cluster Id value from the URL and add to the end. i.e. https://westeurope.azuredatabricks.net:443/sql/protocolv1/o/1659108793111072/0517-135903-outdo75
Now you have constructed the final Server URL, you can enter this into the Power BI Spark (Beta) dialog box. Example of the final Server URL address:
https://westeurope.azuredatabricks.net:443/sql/protocolv1/o/1659108793111072/0517-135903-outdo75
You must select the HTTP under Protocol and either Data Connectivity mode. Once the Access Token credentials have been entered in the next dialog box, you will be able to select the required tables for report or analysis.
Wrap up
I hope you found this workaround useful, hopefully soon Azure Databricks will provide the Server details without having to constructed it yourself.
