Share this article: | ![]() |
![]() |
![]() |
![]() |
Vertica Integration with Qlik Sense: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. This document provides guidance using the latest versions of Vertica and Qlik Sense as of May, 2018.
Vertica and Qlik Sense Versions Tested
Software | Version |
---|---|
Partner Client |
Qlik Sense (personal edition running locally) version 3.1.SR3 and Nov 2017 patch 2 11.24.4 |
Desktop Platform |
Windows 7 and 10 |
Vertica Client |
ODBC 9.00.01 and Windows 64 bit system DSN |
Vertica Server |
9.0.1 |
Server Platform | Linux CentOS 7.4 |
Qlik Sense Overview
Qlik Sense Desktop is a BI tool for data analysis. With Qlik Sense you can share knowledge and analyze data in groups and across organizations. You can run Qlik Sense locally or on the cloud. For more information on Qlik Sense go to the Qlik website.
Install Qlik Sense
Before downloading and installing Qlik Sense, go to the System Requirements for Qlik Sense page for hardware and software requirements.
Go to the Qlik Sense download page on the Qlik website to download and install the product. To download the distribution file (Qlik_Sense_Desktop_setup.exe), complete the registration form. Open Qlik Sense_Desktop_setup.exe and run the installation wizard. When running the wizard, you can change the installation path if you want.
Download and Install the Vertica Client Drivers
Qlik Sense doesn't come with a specific Vertica connector so you use its ODBC data source option to connect. To use the Qlik Sense data source, you need to download and install Vertica's ODBC driver, as follows:
- Go to the Vertica Client Drivers page.
- Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.
- Based on the client driver package you downloaded, follow the installation instructions in the Vertica documentation.
Configure an ODBC Data Source
For information on creating and configuring an ODBC data source, see Setting Up an ODBC DSN.
Connect Qlik Sense to Vertica
Connect Qlik Sense to the Vertica database as follows:
- From the Qlik Sense Desktop Hub, select Create New App:
- Enter the name of the new app and click Create:
- Click Open App:
- Click Add data from files or other sources:
- Select the ODBC data source:
- In the Create new connection (ODBC) dialog select the 64-bit DSN you created earlier. Then enter the following:
Username - enter the dbadmin user name
Password - enter the database password
Name - edit this field as you want. 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:
This screen contains the data you want to analyze. The Database field displays the name from your DSN. The Owner pulldown menu displays the schemas in your Vertica database.
When you select a schema, the tables found in that schema appear on this page. Further, 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 of them you are connecting to Vertica with Qlik Sense Desktop and you can start exploring Qlik's features.
If this is a test environment, we suggest reviewing the Vertica Quick Start for Qlik Sense guide before proceeding. The document guides you through some of the same steps found here. Once installed, the supplied Qlik Sense Desktop application has several dashboards pre-defined to display Vertica data and is a good way to explore how the two products can integrate.
Known Limitations
The following data type inconsistencies were seen during our level 1 data type testing:
- CHAR, VARCHAR, and LONGVARCHAR values longer than 512 chars are visually truncated with "...". For example:
4096 character string"START4Kaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa<repeating "a">aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4KEND"
shows as 512 character string"START4Kaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa<repeating "a">aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa..."
- Timetz and Timestamptz column values with milliseconds are truncated. For example:
14:30:45.999999+04
shows as14:30:45
2012-11-27 05:30:45.999999-05
shows as2012-11-27 05:30:45
- BINARY, VARBINARY, and LONG VARBINARY values show in vsql as octal, but in Qlik Sense as hex. For example:
\377\000\000\000
shows asFF000000
For More Information