Share this article:

Vertica Integration with CloverETL Designer: Connection Guide

Click here to view a PDF version of this document.

Applies to Vertica 7.1.x and earlier 

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners 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. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and CloverETL Designer as of November, 2015.

CloverETL Designer Overview

CloverETL Designer is a visual tool that lets you extract data from a source, transform it, and store it in a target. CloverETL Designer is an Eclipse-based Java application that runs on any platform where Java 1.6 or later is supported, including Windows, Macintosh OS X, and Linux. CloverETL uses the JDBC driver to connect to Vertica.

This document is based on the results of testing Vertica 7.1.x with CloverETL Designer 4.1 on a Windows 64-bit platform.

Download and Install CloverETL Designer

  1. Download the free trial for CloverETL Designer:

    http://www.cloveretl.com/download
  2. Pick an empty folder where you want CloverETL Designer to store your projects and data. This is known as the Workspace.
  3. The first time you start CloverETL Designer, you are prompted for a license key. Copy and paste the key from the License key as text area into the one-line text area. You must include the Begin License and End License

    clover1.png

  4. Click Next.
  5. Agree to the terms and conditions.
  6. Click Finish.

Download and Install the Vertica Client Driver

Before you can connect to Vertica using CloverETL, you must download and install the Vertica client package. This package includes the Vertica JDBC driver that CloverETL Designer uses to connect to Vertica.

Note If you are using Vertica 7.2.x, update the client package for complete functionality with version 7.2.x.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Connect Vertica to CloverETL

  1. Launch the CloverETL Designer.
  2. To create a new project, click File > New > CloverETL project.
  3. Name your project and click Finish.
  4. To create a new graph, click File > New > ETL Graph.
  5. Navigate to the Outline panel to create a new connection to your Vertica database. Right click Connections > Create DB connection.
  6. In the Database Connection dialog box the following fields are required:

    • Connection: Automatically fills after you are connected to Vertica.
    • Name: Your connection name.
    • User: Your Vertica database user.
    • Password: Your database password.
    • URL: The JDBC connection string.

    Example format:

    jdbc:vertica://<ip_address>:<port_number>/<database_name>
  7. The Vertica 7.0 and 7.1 JDBC drivers appear in the Available Drivers Select the driver that matches your version of Vertica.

    clover2.png

  8. If you are using an earlier version of Vertica, you can manually add the JDBC driver. Click the green plus sign on the right hand side to add the Vertica drivers.
  9. Click Local Files and search for the location where your Vertica driver exists in your system.
  10. Click OK. You should see the Vertica driver was added to the list of available drivers.
  11. In the JDBC specific field, select Vertica from the drop-down menu.
  12. Select the Vertica driver from the list of Available Drivers and click Validate connection.
  13. If the connection is valid, a notification displays at the top of the dialog box. Click Finish.
  14. You are now connected to Vertica through CloverETL Designer. Drag and drop elements, such as the DBInputTable from the Palette panel to read data out of a Vertica object or the DBOutputTable to write data into your Vertica database.

For more information about advanced connection settings, see the CloverETL Documentation.

CloverETL Designer Interface

The CloverETL Designer Interface contains a few important components:

  • The Navigator is located in the top left panel and contains all CloverETL Designer projects. Some projects contain folders and subfolders.

    clover3.png

  • The Palette is located in the right panel and contains all the components and elements that you can drag and drop onto the canvas. Components are sorted by type.

    clover4.png

  • The Outline is located in the bottom left panel and contains the metadata that describes fields and connections to data sources, including Vertica.

    clover5.png

Known Limitations

Known limitations for data types using the JDBC driver are:

  • CloverETL Designer displays Vertica NaN values as NULL.
  • For versions earlier than CloverETL Designer 4.1, the designer maps INTERVAL values to bytes. If you manually change the value from byte to string, the INTERVAL value displays.
  • CloverETL Designer does not display or load milliseconds with Vertica TIMETZ data types.

For More Information

For More Information About… …See

CloverETL Designer

http://www.cloveretl.com/

CloverETL Advanced Settings

CloverETL Documentation

Vertica Community

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: