|Share this article:|
Vertica Integration with CloverDX Designer: Connection Guide
To read this document in PDF format, click here.
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.
Vertica and CloverDX Designer: Latest Versions Tested
CloverDX Designer 5.0.0.020
Vertica JDBC 9.1.1-0
Vertica Database 9.1.1-0
Windows Server 2012 R2
CloverDX Designer Overview
CloverDX Designer is a visual tool that lets you extract data from a source, transform it, and store it in a target. CloverDX 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 CloverDX Designer
You can download a free trial version of CloverDX Designer by following these steps:
- On the CloverDX website, click Get a Trial.
- Follow the on-screen instructions to install CloverDX Designer.
Install the Vertica Client Driver
CloverDX 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 CloverDX 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 package.
Follow the installation instructions in the Vertica documentation:
Connect CloverDX Designer to Vertica
- Launch CloverDX Designer.
Select the Workspace directory and click Launch:
- Click File > New > CloverDX project to create a new project.
- Name your project and click Finish.
- Click File > New > Graph to create a new graph.
Select the parent project, then type the name of the graph and click Finish.
- In the Outline panel, right-click Connections > Create DB connection to create a connection to your Vertica database.
In the Create DB Connection dialog box, supply the values for the following required fields:
- Connection name: Enter your connection name.
Available Drivers: Select the Vertica JDBC driver to use for the connection.
You can select Vertica 7.0 or Vertica 7.1 which are bundled with CloverDX. Or you can supply your own driver. Click the plus sign to manually add your own JDBC driver.
- JDBC specific: Select Vertica from the drop-down list
URL: Enter the JDBC connection string.
- User: Enter your Vertica database user.
- Pasword: Enter your database password.
Click Validate connection.
If the connection is valid, a notification displays at the top of the dialog box
You are now connected to Vertica through CloverDX Designer. Drag and drop elements from the Palette panel to read and write Vertica data.
For more information about advanced connection settings, see the CloverDX Documentation.
Using the CloverDX Designer User Interface
Configure a Vertica Source
Use the DBInputTable component from the Readers section of the Palette to read data from Vertica:
To configure the source:
- Double-click DBInputTable.
- Select the source connection name from the drop-down list.
In SQL query, click the 3 dots button.
The SQL query editor window appears.
In the SQL query editor window, select the schema and table, then click Generate Query.
Validate the query, then click OK.
Configure a Vertica Target
Use the DBOutputTable component from the Writers section of the Palette to write data to Vertica:
To configure the target:
- Double-click DBOutputTable.
Select the target connection name from the drop-down list.
In DB table, click the 3 dots button.
The Select database table window appears.
In the Select database table window, select the target schema and table and click OK.
Connect the Source and the Target
Use an Edge to connect the source and target:
Note The dotted line connecting the source and target indicates that the source metadata has not been passed to the target.
To connect the source and target:
- Right-click the DBInputTable component.
Select Extract metadata.
Check the incoming data type of the columns, then click Finish.
View the newly-extracted metadata in the Metadata component of the Outline panel.
Right-click the Edge and select the newly-extracted metadata:
The dotted Edge changes to a solid line after you provide the metadata.
- Save the graph.
- From the toolbar menu, select Run to execute the graph.
- Up to 247 characters of Char, VarChar, and LongVarChar data types are displayed during data read operations.
- NaN values are loaded as Null.
- IntervalSeconds, IntervalMonths, Binary, VarBinary, LongVarBinary are displayed as blank during data read operations.
- In Time, TimeStamp, TimeTz, TimeStampTz data types, milliseconds are truncated and rounded off.