Share this article:

Vertica Integration with KNIME: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that third-party vendors create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor's software. Other versions of the third-party product may work with Vertica, and may not have been tested.

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

This document describes how to:

  • Register your Vertica JDBC driver with KNIME.
  • Configure your connection from KNIME to Vertica.
  • Use the KNIME Database Reader node to read data from Vertica.
  • Use the KNIME Histogram node to create a chart of that data.

This document assumes that the reader is familiar with both KNIME and Vertica.

This document is based on the results of testing with Vertica 7.x and KNIME 2.10.

Before You Begin

Before you can connect KNIME to Vertica, you must download and install the KNIME software. Follow these instructions from the KNIME website:

Register the Vertica JDBC Driver with KNIME

To register the Vertica JDBC driver with KNIME, follow these steps:

  1. Start KNIME.
  2. Select File > Preferences > KNIME > Databases.
  3. Click New.
  4. Navigate to the location of the Vertica JDBC driver .jar file.
  5. Double-click the .jar The name appears in the Databases window.
  6. Click OK.

image002.png

For complete instructions on connecting KNIME to Vertica using JDBC or ODBC, see Installation and Setup on the KNIME website.

Configure the KNIME to Vertica Connection

Now that you've registered the Vertica JDBC driver, configure a connection to execute a query on the database and bring the data into KNIME:

  1. Drag the Database Reader icon onto the KNIME palette.

    image003.png

  2. Right-click the Database Reader icon and select Configure. The Settings tab appears. Enter information here for configuring the connection to Vertica.
  3. Under Database Driver, select the Vertica JDBC driver.
  4. Enter the URL for your Vertica database.
  5. Enter the username and password for your database.
  6. Enter the SQL statement that returns the data you want to load into KNIME. Do not terminate the SQL statement with a semi-colon.

This example returns the employee_region and annual_salary columns from the Vmart employee_dimension table.

image004.png

  1. Click Apply and then OK.

Import the Data from Vertica

Let's import some data from your Vertica database into KNIME. The Database Reader node connects to your Vertica database for the purpose of loading data into KNIME for analysis and visualization:

  1. To execute the SELECT query on the Vertica database, right-click the Database Reader node and select Execute. When the query completes, the circle at the bottom of the Database Reader icon turns green.
  2. Right-click the Database Reader node and select Data from Database.

KNIME displays the results of the SQL statement.

image005.png

Create a Chart in KNIME

You can use KNIME to make a chart from this data. This example uses the Histogram node:

  1. [Optional] Close the Data From Database window before creating the histogram.
  2. From the Data Views folder, drag the Histogram node to the palette.

    image006.png

  3. Click and drag a line from the output port of the Database Reader node to the input port of the Histogram node. This step indicates that the Histogram should use the data from the Database Reader to create the histogram.
  4. To configure which columns to use for the histogram, right-click the Histogram node and select Configure.

    image007.png

  5. Set the Binning column to employee_region.
  6. Under Aggregation column, select annual_salary and click add.
  7. Click Apply and OK to close this window.
  8. Right-click the Histogram node and select Execute and Open Views.

KNIME generates a chart like the following:

image008.png

For More Information

For general information about KNIME, see www.knime.org.

Share this article: