|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.
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:
Windows Server 2012 R2
Vertica JDBC 9.2.1-0
Vertica Analytic Database 9.2.1-0
Before You Begin
Note This document assumes that the reader is familiar with both KNIME and Vertica.
Before you connect KNIME to Vertica, you must download and install the KNIME software. Follow these instructions from the KNIME website:
Installing Vertica JDBC Client Driver
Before connecting KNIME to Vertica, you must download and install the Vertica JDBC client driver by following these steps:
1. Navigate to the Vertica Client Drivers page on the Vertica website.
2. Download the JDBC driver package.
Note For more details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
3. Follow the instructions in Installing the Client Drivers and Tools on Windows in the Vertica documentation.
4. Copy the Vertica JDBC .jar file to the client directory for external libraries.
Registering Vertica JDBC Driver with KNIME
To register the Vertica JDBC driver with KNIME, follow these steps:
- Start KNIME.
- Select File > Preferences > KNIME > Databases.
- Click New.
- Navigate to the location of the Vertica JDBC driver .jar file.
- Double-click the .jar file. The name appears in the Databases window.
- Click OK.
Connecting KNIME to Vertica
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:
Drag the Database Reader icon onto the KNIME palette.
- Right-click Database Reader and select Configure. The Settings tab appears. Enter information here for configuring the connection to Vertica.
- Under Database Driver, select the Vertica JDBC driver.
- Enter the URL for your Vertica database.
- Enter the username and password for your database.
- 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.
- Click Apply and then OK.
Reading 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:
- 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.
- Right-click the Database Reader node and select Data from Database.
KNIME displays the results of the SQL statement.
Creating a Chart in KNIME
You can use KNIME to create a chart from this data. This example uses the Histogram node:
- [Optional] Close the Data From Database window before creating the histogram.
- From the Data Views folder, drag the Histogram node to the palette.
- 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.
- To configure which columns to use for the histogram, right-click the Histogram node and select Configure.
- Set the Binning column to employee_region.
- Under Aggregation column, select annual_salary and click add.
- Click Apply and OK to close this window.
- Right-click the Histogram node and select Execute and Open Views.
KNIME generates the follwing chart:
For More Information
- KNIME Website
- KNIME Documentation
- Vertica Community Edition
- Vertica User Community
- Vertica Documentation