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:

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.
  3. 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:

  1. From the Qlik Sense Desktop Hub, select Create New App:

  2. Enter the name of the new app and click Create:

  3. Click Open App:

  4. Click Add data from files or other sources:

  5. Select the ODBC data source:

  6. 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.


  7. Click Create.
  8. 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 as 14:30:45

    2012-11-27 05:30:45.999999-05 shows as 2012-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 as

    FF000000

For More Information

 

Share this article: