|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.1.1.017 on Windows and Linux platforms
Vertica JDBC 9.2.0-0
Vertica Database 9.2.0-0
CloverDX provides tools for large scale data transformation and integration, and includes the following components:
- CloverDX Designer: A visual tool that lets you extract data from a source, transform it, and store it in a target.
- CloverDX Server: A platform to monitor and automate the execution of data jobs that you create in CloverDX Designer. CloverDX Server connects to Vertica using the connection information specified in CloverDX Designer when building your data jobs.
CloverDX Designer and Server are Java applications that run on any platform where Java 1.6 or later is supported, including Windows, Macintosh OS X, and Linux.
This connection guide is based on testing Vertica on-premises with CloverDX Designer only.
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.
CloverDX Designer bundles Vertica’s JDBC driver version 9.1.1, so you do not need to download and install Vertica’s JDBC driver to use CloverDX Designer with Vertica. Vertica JDBC driver version 9.1.1 is both backward and forward compatible.
Note For details about client driver and server version compatibility, see the Vertica documentation.
If you are using a more recent version of Vertica Server (later than 9.1.1) and you wish to enable specific features introduced with that version, then you must install the appropriate driver as follows:
- 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 Vertica from the list of available drivers.
Vertica’s JDBC driver version 9.1.1 is bundled automatically with CloverDX. If you require features that are only available in a later version of Vertica, you can click the plus sign to manually add the 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.
Note In this example image, we have manually added Vertica’s JDBC jar file version 9.2.0, which is displayed in the list of available drivers as:
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 for values of Char, VarChar, and LongVarChar data types are displayed in the CloverDX Designer Data Inspector.
- NaN values are loaded as Null.
IntervalSeconds, IntervalMonths, Binary, VarBinary, and LongVarBinary are displayed as blank in the CloverDX Designer Data Inspector.
- In Time, TimeStamp, TimeTz, and TimeStampTz data types, milliseconds are truncated and rounded off.