Share this article:

Vertica Integration with KNIME: 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.

KNIME Overview

KNIME is an open source data analytics platform. You can analyze, transform, and visualize data in your Vertica database using tools that KNIME provides. You can also use KNIME to create reports about your data. The KNIME Analytics Platform is available under GPL license, as described in the KNIME License Terms and Conditions

Vertica and KNIME Software Versions

This document provides guidance using the software listed as follows:

Software Version
Partner Product

KNIME Linux 4.1.0

KNIME Windows 4.1.0

Partner Product Platform

CentOS Linux 7 (Core)

Windows Server 2016 R2

Vertica Client

Vertica JDBC 9.3.0-0

Vertica Server

Vertica Analytic Database 9.3.0-0

Installing KNIME

To install KNIME

  1. Go to the KNIME website.
  2. Click Download.
  3. Follow the instructions here https://www.knime.com/installation to install KNIME in a Linux or Windows environment.

Installing the Vertica Client Driver

To install the client driver

  1. Open KNIME Analytics Platform.
  2. Select File > Install Knime Extensions.
  3. In the search bar, type Vertica and select KNIME Vertica Driver.
  4. Click Next twice, accept the license agreement, and then click Finish.

Connecting KNIME to Vertica

To connect KNIME to Vertica

  1. Create a workflow in KNIME Explorer.

    In this example, we created the workflow Vertica.

  2. In Node Repository, expand DB.

  3. For a source connection, drag Vertica Connector to the workflow.
  4. Right-click the connector and click Configure.
  5. In the Connection Settings tab, enter the host name, database name, username, and password of the source database, and then click OK.

  6. Right-click Vertica Connector and click Execute to connect to the Vertica database.

  7. Drag DB Table Selector to the workflow.

  8. Connect Vertica Connector and DB Table Selector.

  9. Right-click DB Table Selector, select the table from the database, and then click Apply.

  10. Drag DB Reader and connect DB Table Selector to DB Reader.

  11. For a target connection, drag Vertica Connector to the workflow.
  12. Right-click Vertica Connector and click Configure.
  13. In the Connection Settings tab, enter the host name, database name, username, and password of the target database, and then click OK.

  14. Drag DB Insert to the workflow and connect it to target Vertica Connector.
  15. Connect DB Reader and DB Insert nodes as in the following image.

  16. Right-click Vertica Connector and click Execute.
  17. Right-click DB Insert and click Select table.

    Select the table from the listed schemas.

  18. Click the Execute icon in the KNIME quick access bar to execute the workflow.

Known Limitations

  • Decimal data type supports precision till 30 digits.
  • Time data type does not support milliseconds.
  • TimestampTz data type is not supported.

For More Information

Share this article: