Share this article:

Vertica Integration with Tableau: Connection Guide

To read this document in PDF format, click here.

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one version of Vertica and one version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested those other versions.

Vertica and Tableau Versions

This document is based on the results of testing the following versions of Tableau and Vertica:

Software Version
Tableau
  • Tableau Desktop 10.5.2 on Windows and MaacOS
  • Tableau Server 10.5.2 on Linux and Windows
  • Tableau Online 10.5.2 (SaaS)
Vertica Client

Vertica ODBC Driver 9.0.1-0

Vertica Server

Vertica Analytic Database 9.0.1-0

Tableau Overview

Tableau Software provides visualization and business intelligence products, including:

  • Tableau Desktop: Graphical interface for creating visualizations and interactive dashboards in project files called workbooks.
  • Tableau Server: Multi-user environment that enables collaborative access to Tableau workbooks and data.
  • Tableau Online: Software as a Service (SaaS) deployment of Tableau Server on Amazon Web Services (AWS).
  • Tableau Public: Like Tableau Online, but without the security and privacy protections.
  • Tableau Mobile: Tableau Desktop for mobile devices.
  • Tableau Reader: Free desktop application that provides offline access to packaged Tableau workbooks.

For details about Tableau, visit the Tableau website.

How Tableau Works with Vertica

This topic summarizes how Vertica works with different Tableau products. Details are provided later in this document and in Vertica Integration with Tableau: Tips and Techniques in the Vertica User Community.

Vertica and Tableau Desktop

Tableau Desktop runs on Windows and MacOS and connects live to Vertica on-premises or in the cloud. The connection uses ODBC.

Vertica and Tableau Server

Tableau Server runs on Windows and Linux and connects live to Vertica on-premises. Tableau Server may be deployed on-premises, hosted on the cloud, or offered as a managed service (Tableau Online).

tableau-vertica1.png

Vertica and Tableau Online

From Version 10.3 onward, Tableau Online connects live to Vertica on-premises using Tableau Bridge.

Tableau Bridge

Tableau Bridge enables a Tableau Online site to connect to underlying data that it cannot access directly. When Tableau Online is connected to Vertica through Tableau Bridge, it can access Tableau workbooks and data in Vertica and publish them to Tableau Online. See the following topics in this document for details:

For details about Tableau Bridge, see the Tableau documentation.

Vertica and Tableau Mobile

Tableau Mobile connects live to Vertica through Tableau Server using any of the deployment scenarios described in Vertica and Tableau Online.

About Tableau Reader

Because Tableau Reader provides offline snapshots of Tableau workbooks, it does not support connections to Vertica. You cannot refresh or edit Vertica data that you may view with Tableau Reader.

Tableau Reader opens workbook files that have been packaged with the data. Packaged workbook files have a TWBX extension. Tableau Desktop and Tableau Server can open workbook files that are connected to a live data source. These workbook files have a TWB extension. If you attempt to open a TWB workbook that uses Vertica data with Tableau Reader, the following error is returned:

Unknown data source class 'vertica'.

Install Tableau Desktop

  1. Ensure that your system meets the requirements specified in the Tableau documentation.
  2. From the Tableau website, download the Tableau installation file that matches your operating system.
  3. Launch the executable.
  4. Follow the prompts for installation.

Note To install Tableau Server and other Tableau products, follow the instructions in the Tableauu documentation.

Install the ODBC Driver

Windows or MacOS

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

  1. Navigate 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.

    Note The Vertica ODBC driver is now both backward and forward compatible. The ODBC driver was made backwards compatible from version 8.1.1 to enable connection to most Vertica server versions.

    For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Follow the installation instructions for your platform:
    • Windows: Double-click the installer 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. For details, see The Vertica Client Drivers and Tools for Windows.
    • MacOS: Double-click the installer to start the installation. Follow the prompts as the wizard guides you through each step of the process. For details, see Installing the ODBC Driver on MacOS.

Note You do not need to create a DSN to connect to Vertica from Tableau.

Linux

You can run Tableau Server on Windows or Linux. If Tableau Server is running on Windows, follow the instructions in the previous section to install the ODBC driver. If Tableau Server is running on Linux, follow these steps:

  1. Navigate to the Vertica Client Drivers page.
  2. Download the ODBC driver:
    • CentOS and Redhat Enterprise Linux: Download the RPM.
    • Ubuntu: Download the TAR file
  3. Run the following command:
    • CentOS and Redhat Enterprise Linux:
      sudo rpm --nodeps -ivh vertica-client-9.0.1-0.x86_64.rpm
    • Ubuntu: Download the TAR file
      sudo tar -C / -xzvf vertica-client-9.0.1-0.x86_64.tar.gz
  4. Append the following section to the /etc/odbcinst.ini file:
    [Vertica]
    Driver=/opt/vertica/lib64/libverticaodbc.so
  5. Restart Tableau Server.

Note You do not need to create a DSN to connect to Vertica from Tableaux.

For more information, see Installing ODBC Drivers on Linux, Solaris, AIX, and UX in the Vertica documentation.

Connect Tableau Desktop to Vertica

  1. Launch Tableau Desktop.

    The Connect page opens.

  2. Under To a Server, select More Servers > Vertica.

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

  4. Click OK to make the connection.

    The following page 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. (Recommended)

      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.

Connect Tableau Online to Vertica

You can connect Tableau Online to Vertica on-premises as described in Vertica and Tableau Online.

Connection Requirements

Before you can connect, you must:

  1. Install and activate Tableau Bridge, as described in the Tableau documentation.
  2. Publish a workbook or a data source from Tableau Desktop to Tableau Online.

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

Publish a Workbook to Tableau Online

  1. Ensure that Tableau Bridge is active.
  1. Open Tableau Desktop.
  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. Click 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.

  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 a 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. Follow these steps:.

  1. Ensure that Tableau Bridge is active.
  2. Open Tableau Desktop.
  3. Create a workbook.
  4. Select Server > Publish Data Source. From the pop-up list, select the data source you want to publish.
  5. If you haven't already done so, sign in to Tableau Online.
  6. 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.
  7. Click Publish. In Tableau Online, you see this window:

  8. Ensure that the data source was published and that the connection shows LIVE:

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

Known Limitations

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

  • Tableau rounds some decimal values:

    –99999999999999.9999 rounds to –100000000000000.

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

For More Information

See the following resources::

Share this article: