|Share this article:|
|✉ Send Feedback for 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 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:
|Partner Product Platform||
CentOS Linux 7.4 (Core)
Vertica JDBC 9.3.0-0 (Bundled with KNIME. 9.3.0 is the latest version supplied with KNIME)
Vertica Analytic Database 11.1.0-0
To install KNIME
- Go to the KNIME website.
- Click Download.
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
- Open KNIME Analytics Platform.
- Select File > Install Knime Extensions.
In the search bar, type Vertica and select KNIME Vertica Driver.
Click Next twice, accept the license agreement, and then click Finish.
Connecting KNIME to Vertica
To connect KNIME to Vertica
Create a workflow in KNIME Explorer.
In this example, we created the workflow Vertica.
In Node Repository, expand DB.
- For a source connection, drag Vertica Connector to the workflow.
- Right-click the connector and click Configure.
In the Connection Settings tab, enter the host name, database name, username, and password of the source database, and then click OK.
Right-click Vertica Connector and click Execute to connect to the Vertica database.
Drag DB Table Selector to the workflow.
Connect Vertica Connector and DB Table Selector.
Right-click DB Table Selector, select the table from the database, and then click Apply.
Drag DB Reader and connect DB Table Selector to DB Reader.
- For a target connection, drag Vertica Connector to the workflow.
- Right-click Vertica Connector and click Configure.
In the Connection Settings tab, enter the host name, database name, username, and password of the target database, and then click OK.
- Drag DB Insert to the workflow and connect it to target Vertica Connector.
Connect DB Reader and DB Insert nodes as in the following image.
- Right-click Vertica Connector and click Execute.
Right-click DB Insert and click Select table.
Select the table from the listed schemas.
- Click the Execute icon in the KNIME quick access bar to execute the workflow.
- Decimal data type supports precision till 30 digits.
- Time data type does not support milliseconds.
- TimestampTz data type is not supported.