Share this article:

Vertica Integration with Tableau Desktop: Connection Guide

About Vertica Documentation

Vertica connection guides provide basic information about setting up connections to Vertica from software that third-party vendors create. This document provides guidance using the latest versions of Vertica and Tableau as of August, 2017.

For detailed information about configuration and optimization, see Vertica and Tableau: Tips and Techniques.

Introduction

Tableau Software provides visualization and business intelligence tools that work with your Vertica database.

This document is based on the results of testing Tableau with Vertica for several versions of both products. For a list of driver/server combinations that have been tested, see the Vertica Driver/Server Compatibility.

How Tableau Works with Vertica

The following sections briefly describe how the Tableau products work with Vertica:

Tableau Desktop

Tableau Desktop allows you to create visualizations and interactive dashboards with Tableau's easy-to-use, drag-and-drop interface.

  • Tableau Desktop can be installed on Windows and MacOS operating systems.
  • Tableau Desktop connects live to data on your Vertica database, both in the cloud or on-premises.
  • Tableau Desktop connects to Vertica via ODBC. Install the Vertica ODBC driver on your Tableau Desktop machine to visualize your data from Tableau Desktop.

For detailed information, see Vertica Integration with Tableau: Tips and Techniques on the Vertica Community.

Tableau Server

Tableau Server is a web-based application that allows you to visualize and edit the dashboards that you have created with Tableau Desktop.

There are three different deployment options of Tableau Server:

  • On-premises. This option can be installed and deployed on Windows operating systems.
  • In the cloud (AWS or Microsoft Azure).
  • As a Software as a Service offering, also known as Tableau Online.

tableau-vertica1.png

Tableau Online

Tableau Online is the cloud deployment of Tableau Server on Amazon Web Services (AWS). The Tableau team manages this deployment. Tableau Online verifies that your data is secure and private.

Using Tableau Online allows customers to get started with Tableau Server much faster than using an on-premises Tableau deployment. The cost of using Tableau drops because no IT, hardware, VPN, or firewalls are required for Tableau Online. Tableau Online supports SAML authentication.

Note Tableau Public also allows you to share your data and visualizations online, but does not have the same security and privacy that Tableau Online provides.

From Version 10.3 onward, Tableau Online connects live to data in Vertica on-premises via Tableau Bridge. For information about Tableau Bridge, see Allow Publishers to Maintain Live Connections to On-Premises Data.

For information about how to connect to data in Vertica on-premises, see Connecting to On-Premises Vertica Data from Tableau Online in this document.

Tableau Reader

Tableau Reader is a desktop application that reads packaged workbooks (*.twbx) using only data extracts. Tableau reader cannot read files with a *.twb extension.

You can download Tableau Reader for free. Tableau Reader provides offline snapshots of your data. Because of this feature, Tableau Reader does not allow you to

  • Connect to Vertica
  • Refresh the data extract
  • Edit the workbook

If you try to open a packaged workbook (*.twbx) that connects live to Vertica, you see an error that reads:

Unknown data source class 'vertica'.

Using Tableau Reader, you can open packaged workbooks (*.twbx) with extracted data only.

Tableau Mobile

Tableau Mobile is the Tableau mobile offering that connects to Tableau Server and reads *.twb and *.twbx files. Tableau Mobile connects live to Vertica through Tableau Server. To understand the connection behavior from the different types of deployments of Tableau server, see Tableau Server in this document.

For More Information

See www.tableau.com/products.

Installing Tableau

Prerequisites

Before you install Tableau 10.x:

  • Review the requirements for Tableau Desktop and Tableau Server. The following pages list the different requirements for each product:
  • For Tableau Server, Tableau recommends a deployment mode of at least a three-node cluster to ensure high availability. Tableau Server 9.x provides better replication and synchronization than in previous releases, and ensures that there is no single point of failure.

Note For more information about using Tableau Server, see the Tableau documentation.

Downloading and Installing Tableau Desktop

  1. From the Tableau website, download the Tableau installation file that matches your operating system.
  2. Launch the executable.
  3. Follow the prompts for installation.

Downloading and Installing the Vertica Client

Before you connect to Vertica using Tableau, you must download and install the Vertica client package. This package contains the Vertica ODBC driver that Tableau uses to connect to Vertica.

  1. Go to https://my.vertica.com/download/vertica/client-drivers/.
  2. Download the Vertica client package that matches your operating system.
  3. Follow the installation steps, depending on which operating system you use:
    • Installing the Vertica ODBC Client for Windows
    • Installing the Vertica ODBC Client for Mac OS

Installing the Vertica ODBC Client for Windows

You can connect from Tableau 10.x to Vertica 8.x on your Windows computer using ODBC.

Windows Prerequisites

Make sure to download the Vertica client package that matches the architecture (32-bit or 64-bit) for your Tableau and Windows applications.

Installing the Windows ODBC Client

Double-click the installer you just downloaded and follow the prompts to install the ODBC component. The client package includes other drivers such as ADO.NET. You only need to install the ODBC driver. You do not need to create a DSN to connect to Vertica from Tableau.

Installing the Vertica ODBC Client for Mac OS

Double-click the installer to start the installation. Follow the prompts as the wizard guides you through each step of the process.

Connecting Tableau Desktop to Vertica

For optimal performance and functionality, connect to Vertica from Tableau using Tableau's Vertica native connector.

To connect to Vertica using the native connector, follow these steps:

  1. Launch Tableau Desktop.

    The Connect page opens.

  2. On the Connect page, under To a server, select More Servers > HP Vertica.

    Tableau connection_plain.png

  3. On the Server Connection page, enter the server name, database name, username, and password.

    Connection window.PNG

  4. Click OK to make the connection.

    The following window displays.

    tableau9_datasource_screen.png

  5. From the Schema drop-down list, select or search for the schema you want to work with.

    After you select a schema, a list of tables in that schema appears.

  6. To define a data source for your visualizations, do one of the following:
    • Drag and drop tables to the join area. Tableau tries to guess the joins between the tables.

      Or

    • Click New Custom SQL to enter a query that returns data that you use to design your dashboards.
  7. After you define your data source, click Sheet1 to start designing your visualizations.

Connecting to On-Premises Vertica Data from Tableau Online

Before connecting live to Vertica on-premises from Tableau Online, you have to install Tableau Bridge on your Tableau Desktop machine. Then, make sure that Tableau Bridge is active and one of the following is true:

  • You have published a workbook from Tableau Desktop to Tableau Online

    or

  • You have published a data source from Tableau Desktop to Tableau Online.

To set up Tableau Bridge follow the instructions at Allow Publishers to Maintain Live Connections to On-Premises Data.

After you have installed the Tableau Bridge and it is active, there are two ways you can connect to on-premises data in Vertica from Tableau Online, as described in the next two sections.

Publish Your Workbook to Tableau Online

To connect to Vertica data from Tableau Online, one option is to publish your Tableau workbook to Tableau Online. Before you start Tableau Desktop, set up Tableau Bridge and make it active.

Take these steps:

  1. Open Tableau.
  2. Create a workbook.
  3. Select Server > Publish Workbook.
  4. When prompted, connect to Tableau Online.
  5. In the Publish Workbook to Tableau Online dialog box, in the Data Sources section, click Edit.
  6. In the pop-up screen, in the Publish Type drop-down menu, select Publish Separately.
  7. Cick Maintain connection to a live data source.

    Note The first time you publish a Tableau workbook from Tableau Desktop to Tableau Online, the Publish Type Embedded in workbook is selected by default, however this option do not allow live connection to Vertica and instead publishes the workbook with an extract of the data. You need to change the selection to “Published separately” in the “Publish Type” drop down to connect live to Vertica.

    Note from Tableau: Live connection support is available only for data sources that are published separately. Live connection support is not availabe for data sources that are embedded in published workbooks.

  8. Check Show sheets as tabs.
  9. Click Publish Workbook & 1 Data Source.
  10. When you see the following confirmation that publishing is complete, click Done.

  11. In Tableau Online, in your workbook, select the Data Sources tab.
  12. View the Data Source you published with your workbook. The connection should show LIVE.

  13. Now you can visualize the worksheets, or edit the workbook and see the queries hitting Vertica LIVE.
  14. You can also create a new workbook from that data source in Tableau Online and the queries will be pushed to Vertica.

Publish Your Data Source to Tableau Online

To connect live to Vertica on-premises from Tableau Online, the second option is to publish your data source from Tableau Desktop to Tableau Online. Befoore you start Tableau Desktop, set up Tableau Bridge and make it active.

Take these steps:

  1. Open Tableau Desktop.
  2. Create a workbook.
  3. Select Server > Publish Data Source. From the pop-up list, select the data source you want to publish.
  4. If you haven't already done so, sign in to Tableau Online.
  5. In the Publish Data Source to Tableau Online window, in the Tableau Bridge required for on-premises data section, select Maintain connection to a live data source.
  6. Click Publish. On Tableau Online, you see this window:

  7. Look at the Datasource that got published with your workbook and that the connection shows LIVE:

Now you can create a new workbook using this Datasource and see the queries hitting Vertica LIVE.

Data Type Support

The following is a list of known data type limitations using Tableau Desktop with the ODBC driver:

  • Tableau does not support LONG VARBINARY data types. Tableau displays #Error when Tableau tries to display LONG VARBINARY values:

  • Tableau displays up to 65,000 characters for LONG VARCHAR values. When Tableau tries to display a very large text value, it displays only the first 65000 characters of that string, truncating the remainder of the string.
  • For TIMETZ and TIMESTAMPTZ data types, Tableau 10.2 does not support milliseconds and time zone offsets.
  • Tableau supports the TIME data type but requires formatting to custom format: HH:mm:SS. Right-click the field, click Default properties > Date format.
  • The maximum number of digits for numeric values is 15. Tableau truncates very large numeric values to 15 digits as shown in these examples:

    79228162514264337593543950335 displays as

    79228162514264300000000000000.

    7922816251426433759354395.0335 displays as

    7922816251426430000000000.00.

  • Digits to the right of the decimal point are truncated, for example:

    7.9228162514264337593543950335 displays as 7.9228162514264300

    and

    79228162514264.337593543950335 displays as

    79228162514264.3

  • Digits to the right of the decimal point are truncated, for example:

    7.9228162514264337593543950335 displays as

    7.9228162514264300

    and

    79228162514264.337593543950335 displays as

    79228162514264.3

  • Tableau rounds some decimal values:

    n –99999999999999.9999 rounds to –100000000000000.

  • Tableau supports the FLOAT data type except for the following values:
    • n NaN
    • n +Infinity
    • n –Infinity

Example Tableau Dashboards

The following figures show a variety of Tableau dashboards that use data from an HP Vertica database.

These dashboards are examples of visualizing and analyzing data that a Telecom application might collect. Note that the blue data indicates improvement in dropped calls, where there are fewer dropped calls. The orange data indicates more dropped calls.

The first dashboard shows information about dropped mobile calls by city, manufacturer, and call duration:

  • Five cities (shown in orange) have not improved their dropped call rate. Those cities are all located in the south.
  • Manufacturers 3 and 4 have improved their dropped call rates, but still have the highest number of dropped calls.
  • The number of dropped calls is lower for longer call durations.

tableau_dashboard_1.png

The second dashboard shows dropped call data by manufacturer and individual phone models over time. A business can use this data to see if certain phone models are more likely to drop calls than other models. Model 6 has the most number of dropped calls.

tableau_dashboard_2.png

The third dashboard shows which age groups and which mobile plans are experiencing fewer dropped calls.

tableau_dashboard_3.png

For More Information

For more information about using Tableau to connect to Vertica, visit their Training and Tutorials site.

Share this article: