Introduction
Connecting Qlik Sense to Databricks can be a game-changer in data analytics, as it allows users to work with big data and machine learning at scale. This integration offers a plethora of possibilities for exploring, visualizing, and analyzing data. In a previous insight, Jana Van Haver explained how to connect Power BI to Databricks. In this insight, we will take a closer look at how to connect Qlik Sense to Databricks and compare the two available options: manual and Partner Connect. Whether you are a data analyst, data scientist, or business user, this article will provide you with the knowledge you need to make informed decisions when connecting these two powerful tools.
Guide to setting up the Databricks - Qlik connection manually
The first method involves setting up the connection manually in Qlik. This requires the user to have a good understanding of both Qlik and Databricks and can be time-consuming. The steps involved in setting up the connection manually are:
- Go to the Data Load editor (or data manager) of an app and click “Create new connection”
Image
- Under Data sources select Databricks
Image
- In the Create new connection (Databricks) dialog, enter the following information:
- Host name, Port and HTTP Path can be found in the connection details of a SQL warehouse or a cluster. The images below show how to get the connection details for a SQL warehouse. It’s also possible to establish a connection to a cluster using the same process.
Image
-
Image
- For Database name, enter the name of the database that you want to use.
- There’s multiple authentication mechanisms. Azure Oauth is the recommended approach, as a client secret is linked to a machine and not to a person. However, for this demo a personal access token is used.
- In Credentials, for User name, enter the word “token”
- The Password is a generated token.
Image
-
Image
- For SSL Options, select the boxes Enable SSL, Allow Self-signed Server Certificate, Allow Common Name Host Name Mismatch, and Use System Trust Store.
- For Name, enter a name for the connection, or leave the default name.
- Host name, Port and HTTP Path can be found in the connection details of a SQL warehouse or a cluster. The images below show how to get the connection details for a SQL warehouse. It’s also possible to establish a connection to a cluster using the same process.
- Test and create the connection. Follow the on-screen directions to add tables to your connection and to filter the tables’ data. If the cluster or SQL warehouse is not yet running, it will be triggered to start up which can take some minutes.
Advantages
The main advantage of manually setting up the connection is that it gives the user more control over the connection and allows them to fine-tune it to meet their specific needs.
Disadvantages
The main disadvantage of this method is that it is more time consuming and requires the user to have a good understanding of both Qlik and Databricks. Furthermore, it can be difficult to troubleshoot when encountering problems.
Guide to setting up the Databricks – Qlik connection using Partner Connect
The second method involves using Partner Connect. Qlik Cloud was recently integrated with Databricks Partner Connect. The steps involved in setting up the automatic connection are:
- Click on in the sidebar on Databricks and select Qlik Sense to connect.
- Follow the instructions. You will need the following information:
- Qlik Sense Tenant URL: A tenant is a unit in the software subscription service from Qlik, a container for items such as users, apps, and spaces.
Image
- Qlik Sense Tenant API Key: An API key is a unique identifier used for authentication of a user, developer, or calling program to an API.
-
Image
-
Image
- Qlik Sense Tenant URL: A tenant is a unit in the software subscription service from Qlik, a container for items such as users, apps, and spaces.
- Test and create the connection. Follow the on-screen directions to add tables to your connection.
Advantages
Setting up the Qlik-Databricks connection with Partner Connect is very easy and straightforward.
Disadvantages
The user has less control over the connection as manually setting it up in Qlik would. Additionally, this method only works on Qlik Cloud and you can only connect to SQL warehouses for Qlik Sense. If you want to connect to a Cluster, you will have to create the connection manually.
Direct Query with Databricks
Since the December 2022 release, it’s also possible to set up a direct query connection between Qlik Sense SaaS and Databricks. With a direct query connection Qlik will generate a SQL query directly at the cloud database while the user is interacting with the data in the app. The direct query connection is useful when working with massive amounts of data or for use cases where near real-time data is necessary. Keep in mind that a direct query connection is only possible in Qlik Sense SaaS and only with a SQL warehouse.
To Create a Direct Query connection with Databricks:
- Create a new app.
- Click files and other sources.
Image
- Select an existing connection or create a new one by applying the above steps.
- In the connection menu, click the three dots at the top right and select ‘Go to Direct Query’.
Image
- You can now select the data which you want to direct query.
Conclusion
In conclusion, both methods of connecting Qlik Sense to Databricks have their advantages and disadvantages. While manually setting up the connection provides greater control, it can be time-consuming and requires technical expertise. On the other hand, using Databricks Partner Connect is quick and easy to set up, but comes at the cost of sacrificing some control over the connection. Additionally, it should be noted that Partner Connect is only available in the cloud and only supports SQL warehouses.
Overall, the choice between the two methods will depend on the specific needs and preferences of the user. If they prioritize ease of use and quick setup, Partner Connect may be the better option. However, if they require more control and customization over the connection, setting it up manually in Qlik would be the way to go. Ultimately, it is recommended that users evaluate their own requirements and use cases to determine the best approach for their needs.
For more insights & research visit the element61 Knowledge base at www.element61.be.