Vertica Integration with Qlik Sense: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.
Vertica and Qlik Sense Versions Tested
Software | Version |
---|---|
Partner Client |
Qlik Sense Hub (August 2022) |
Desktop Platform |
Windows Server 2016 |
Vertica Client |
Vertica ODBC Driver 12.0.1 |
Vertica Server |
Vertica Analytic Database 12.0.1 |
Server Platform | Linux RHEL 8.7 |
Qlik Sense Overview
Qlik Sense is a Business Intelligence tool for data analysis. You can share knowledge and analyze data in groups and across organizations. You can run Qlik Sense locally or on the cloud.
Installing Qlik Sense
Before you install Qlik Sense, review the hardware and software requirements.
Open Qlik Sense_setup.exe and run the installation wizard. When running the wizard, you can change the installation path if you want.
Installing the Vertica Client Driver
Qlik Sense uses the Vertica ODBC driver to connect to your Vertica database.
- Go to the Vertica Client Drivers page.
- Download the version of the Vertica client package that is compatible with your Vertica server version.
Note For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
-
Follow the installation instructions in the Vertica documentation.
-
Create and configure a DSN as described in Setting Up an ODBC DSN.
Connecting and Enabling Pushdown from Qlik Sense to Vertica
The following sections explain the steps for
Connecting Qlik Sense to Vertica
To connect Qlik Sense to Vertica,
- From the Qlik Sense Hub, select Create new app:
- Enter the name of the new app and click Create:
- Click Open App:
- Click Add data from files and other sources:
- Select the ODBC data source.
- In the Create new connection (ODBC) dialog, select the 64-bit DSN you created earlier. Provide the following connection information:
- Username: Enter the user name.
- Password: Enter the database password.
- Name: You can leave the DSN name or modify it to a name of your choice.
- Click Create.
- Once you have a successful connection, the following appears:
The Database field displays the name from your DSN. The Owner drop-down list displays the schemas in your Vertica database.
When you select a schema, the tables found in that schema appear on this page. When you select a table, a preview of the table's columns and data appear.If you are able to successfully display tables and data previews, then you are connected to Vertica with Qlik Sense Hub and you can now start exploring its features.
Enabling Pushdown to Vertica
You can pushdown to the Vertica database from Qlik Sense using DIRECT QUERY syntax. This loads the metadata of the table that enables Qlik Sense to generate the appropriate SQL queries and push queries directly down to the Vertica database.
For more information about Qlik Direct Discovery, refer to the video Qlik Direct Discovery.
-
After you connect to Vertica, select the tables you want to load and click Next.
-
In the Prepare tab, select Data load editor.
-
In the Data load editor script, add your script using the syntax based on DIRECT QUERY as in the following example and image. We used a table with sales data to view the average sales amount for a product.
LIB Connect To 'Vertica_vmart (intw2k19-73-33_administrator)'; DIRECT QUERY dimension sale_date_key, ship_date_key, product_key, customer_key, customer_key, call_center_key measure sales_dollar_amount, ship_dollar_amount FROM online_sales.online_sales_fact;
You can add the required fields of your table under dimension and measure, and provide the name of the table that you want to pushdown to Vertica. LIB Connect To is used to connect to the required database.
Click Load data.
.
-
After data is loaded successfully, open Data manager in the Prepare tab.
-
You can see a new table is created with the name DirectTable with the fields that were provided in the script.
-
Click Synchronize scripted tables to view the fields that are loaded.
-
You can now view the fields. It only loads the metadata of the table.
-
In the Analyse tab, create any sheet or dashboard of your choice using the fields of DirectTable.
-
We created a dashboard to get the average sales amount for a product. You can select a product key based on your requirement.
After you select the product key, Qlik Sense generates the appropriate query to pushdown to the Vertica database. -
You can verify in the Vertica log that it was pushed down to the Vertica database.
You can verify from the system table
query_requests
to check if the query was pushed down to Vertica.select * from query_requests
Known Limitations
The following is a list of known limitations with data types when connecting Qlik Sense to Vertica:
- For CHAR, VARCHAR, and LONGVARCHAR data types, values beyond 2100 chars are truncated and replaced with "...".
- For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, milliseconds are truncated.
- For TIMETZ and TIMESTAMPTZ data types, time zone is not displayed.
- For BINARY, VARBINARY, and LONG VARBINARY data types, values are displayed as hexadecimal. For example,
\377\000\000\000
is displayed asFF000000
- For NUMERIC data types, values are rounded off after 15 digits.
For More Information
- Qlik Sense website
- Qlik Sense documentation
- Vertica Community Edition
- Vertica User Community
- Vertica Documentation