|Share this article:|
Vertica Integration with CloverETL Designer: Connection Guide
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 version of Vertica and one version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested those other versions.
Vertica and CloverETL Designer: Versions Tested
CloverETL Designer 4.9
Vertica JDBC Driver 9.1.x
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.
Install CloverETL Designer
You can download a free trial version of CloverETL Designer by following these steps:
- On the CloverETL website, click TRIAL/DEMO CLOVERETL.
- Follow the on-screen instructions to install Clover ETL Designer.
Install the Vertica Client Driver
CloverETL ships the Vertica JDBC drivers for Vertica 7.0 and 7.1. Since Vertica drivers are forward compatible, you do not need to install a later version of the driver to use CloverETL with Vertica. However, if you are using a later version of Vertica and wish to enable more recent features, you must install the appropriate driver.
Note For details about client driver and server version compatibility, see the Vertica documentation.
To install the Vertica JDBC driver, follow these steps:
- Navigate to Vertica Client Drivers.
- Download the JDBC driver for your platform and Vertica server version.
Follow the installation instructions in the Vertica documentation:
Connect Vertica to CloverETL
- Launch the CloverETL Designer.
- To create a new project, click File > New > CloverETL project.
- Name your project and click Finish.
- To create a new graph, click File > New > ETL Graph.
- Navigate to the Outline panel to create a new connection to your Vertica database. Right click Connections > Create DB connection.
In the Database Connection dialog box, supply the values for the following required fields:
- 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.
In the JDBC specific field, select Vertica from the drop-down menu.
In the Available Drivers list, select the Vertica JDBC to use for the connection.
If you do not want to use the 7.0 or 7.1 driver that is bundled with CloverETL, click the plus sign to manually add your JDBC driver.
- Click Validate connection.
- If the connection is valid, a notification displays at the top of the dialog box. Click Finish.
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.
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.
The Outline is located in the bottom left panel and contains the metadata that describes fields and connections to data sources, including Vertica.
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 does not support IntervalSeconds, IntervalMonths, or UUID data types.
- CloverETL Designer does not display or load milliseconds with Vertica TIMETZ data types.