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.

Vertica and KNIME: Latest Versions Tested

Software Version
Partner Product

KNIME 5.1.0
Partner Product Platform

Windows Server 2019
Vertica Client

Vertica JDBC 9.3.0-0 (Bundled with KNIME. 9.3.0 is the latest version supplied with KNIME)
Vertica Server

Vertica Analytic Database 23.3.0
Vertica Server Platform Linux RHEL 8.7

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. 

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

Preview Limitations

  • For DECIMAL data type, values are rounded off and infinity is displayed if the precision is beyond 310 digits.

  • For BINARY, VARBINARY, and LONGVARBINARY data types, values are displayed as Hexadecimal.

  • For TIME and TIMETZ data types, milliseconds are not displayed.

  • For TIMETZ data type, timezone is not displayed.

  • TIMESTAMPTZ data type is not displayed.

Write Data Limitations

  • TIMESTAMPTZ data type is not loaded.

  • For TIME and TIMETZ data types, milliseconds are not displayed.

  • For DECIMAL data type, values are rounded off and precision is displayed up to 310 digits.

For More Information