Like other articles in this blog on the connection and configuration of relational databases with the IRI Voracity data management platform — and its ecosystem products: CoSort, NextForm, FieldShield, DarkShield and RowGen — this article details how to reach Vertica sources. It describes the ODBC and JDBC connections and configurations necessary to register Vertica with the SortCL engine and IRI Workbench job design client common to most IRI software processing and protecting data in high-performance SQL databases and data analytics platforms.

Interoperability Prerequisites

To support Vertica database integration using the IRI back-end SortCL data manipulation engine written in C, an ODBC connection is required. To support visual table browsing and metadata interchange in the IRI Workbench GUI, a JDBC connection is required. Those two connection types will need to be registered in IRI Workbench to map between them. This article explains these steps.

Note that if IRI DarkShield with Vertica is used for finding and masking PII or other sensitive data in structured, semi- and/or unstructured columns per this article, or through the DarkShield RDB API, only JDBC or another connection protocol would be needed.

JDBC

Micro Focus offers a JDBC driver for Vertica. Download, extract and make a note of the installation location.

In Workbench, in the Data Source Explorer, create a new connection profile. Select Vertica. Change the name and click Next.

On the next page, click New Driver Definition next to the drop down. On the Name/Type tab, click the database version and enter a custom driver name.

On the JAR List tab, click vertica-jdbc-11.0.0-0.jar and click Edit JAR/Zip. Navigate to the location of the JDBC driver. Select vertica-jdbc-11.0.0-0.jar.

On the Properties tab, in the URL, replace host:port/database with the proper information. Enter the database name. Optionally, enter the user name and click OK.

Back on the New Connection Profile page, enter a password if applicable. The driver does not save the password but it is required to test the connection if authentication is set up on the database. Test the connection to make sure the settings are correct. Then, click Finish.

The tables are now visible under the database and schema in the Data Source Explorer.

The system tables may be visible here. However, following the instructions from this article can filter out those tables from the connection.

ODBC

These instructions are based on a Windows OS. If Workbench is installed on another operating system, please research the applicable instructions for that system.

Micro Focus offers an ODBC driver for Vertica. Download, extract and make a note of the installation location.

Open ODBC Data Source Administrator and select Add. Using Vertica, enter the DSN name, database, server, port, user, and password. Check the other tabs and add applicable options. Click Test Connect and then OK.

Data Connection Registry

In Workbench, open Preferences > IRI > Data Connection Registry. If the new Vertica DSN is not visible, click Refresh. Then, double-click the entry to edit it. Enter a schema filter if desired. If authentication is set up on the database, enter the user and password here to avoid prompts for the password when running the scripts in Workbench. In Mapping, select the JDBC connection set up previously. Click OK.

The Vertica database can now be used in wizards throughout Workbench. Note that some functionality is unavailable (i.e. automatic creation of create table statements) within some wizards. There will be a pop-up displayed if this is the case.

If you need help establishing or verifying the Vertica connection with IRI software, contact support@iri.com.