I work a lot with Azure Databricks and a topic that always comes up is reporting on top of the data that is processed with Databricks. Even though notebooks offer some great ways to visualize data for analysts and power users, it is usually not the kind of report the top-management would expect. For those scenarios, you still need to use a proper reporting tool, which usually is Power BI when you are already using Azure and other Microsoft tools.
So, I am very happy that there is finally an official connector in PowerBI to access data from Azure Databricks! Previously you had to use the generic Spark connector (docs) which was rather difficult to configure and did only support authentication using a Databricks Personal Access Token.
With the new connector you can simply click on “Get Data” and then either search for “Azure Databricks” or go the “Azure” and scroll down until you see the new connector:
The next dialog that pops up will ask you for the hostname and HTTP path – this is very similar to the Spark connector. You find all the necessary information via the Databricks Web UI. As this connection is always bound to an existing cluster you need to go the clusters details page and check the Advanced Tab “JDBC/ODBC” as described here:
(NOTE: you can simply copy the Server Hostname and the HTTP Path from the cluster page)
The last part is then the authentication. As mentioned earlier the new connector now also supports Azure Active Directory authentication which allows you to use the same user that you use to connect to the Databricks Web UI!
Personal Access Tokens are also still supported and there is also Basic authentication using username/password.
Once you are connected, you can choose the tables that you want to import/connect and start building your report!
Here is also a quick overview which features are supported by the Spark and the Azure Databricks connector as there are some minor but important differences:
| Feature Comparison | Spark Connector | Databricks Connector |
|---|---|---|
| Power BI Desktop | YES | YES |
| Power BI Service | YES | YES * |
| Direct Query (Desktop) | YES | YES |
| Direct Query (Service) | YES | YES * |
| Import Mode | YES | YES |
| Manual Refresh (Service) | YES | YES * |
| Scheduled Refresh (Service) | YES | YES * |
| Azure Active Directory (AAD) Authentication | NO | YES |
| Personal Access Token Authentication | YES | YES |
| Username/Password Authentication | YES | YES |
| General Available | YES | YES |
| Performacne Improvements with Spark 3.x | NO * | YES * |
| Supports On-Premises data gateway | YES | NO |
*) Updated 2020-10-06: the new Databricks Connector for PowerBI now supports all features also in the PowerBI service!
Update 2020-10-06: So from the current point of view the new Databricks Connector is a superset of old Spark Connector with additional options for authentication and better performance with the latest Spark versions. So it is highly recommended to use the new Databricks Connector unless you have very specific reasons to use the Spark connector! Actually the only reason why I would still use the Spark connector is the support for the On-Premises data gateway in case your Spark or Databricks cluster is hosted in a private VNet.
So currently the generic Spark connector still looks superior simply for the support in the Power BI Service. However, I am quite sure that it will be fully supported also by the Power BI Service in the near future. I will update this post accordingly!
On the other hand, Azure Active Directory authentication is a huge plus for the native Azure Databricks connector as you do not have to mess around with Databricks Personal Access Tokens (PAT) anymore!
Another thing that I have not yet tested but would be very interesting is whether Pass-Through security works with this new connector. So you log in with your AAD credentials in Power BI, they get passed on to Databricks and from there to the Data Lake Store. For Databricks Table Access Control I assume this will just work as it does for PAT as it is not related to AAD authentication.