Vertica Integration with Oracle Data Integrator: Connection Guide

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 Oracle Data Integrator: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
Oracle Data Integrator 12.2.1
Desktop platform

Windows Server 2019

Vertica client

Vertica JDBC client driver 11.0.0

Vertica Server Vertica Analytical Database 23.4

Oracle Data Integrator Overview

Oracle Data Integrator (ODI) is a comprehensive data integration platform for building, deploying, and managing complex data warehouses. ODI performs data movement and synchronization, manages data quality, and ensures data accuracy and consistency across complex systems. ODI delivers data integration services within the Service Oriented Architecture (SOA) Suite of Oracle Fusion Middleware.

For an overview of ODI, see Getting Started in the Oracle Data Integrator documentation.

Installing Oracle Data Integrator

  1. Navigate to the Oracle Data Integrator Downloads page on the Oracle Technology Network.
  2. Accept the OTN license agreement.
  3. Download Oracle Data Integrator.
    Follow the installation instructions in the Oracle Data Integrator documentation.

Installing the Vertica Client Driver

Oracle Data Integrator uses JDBC to connect to Vertica. To install the Vertica JDBC driver, follow these steps:

  1. Go to the Vertica Client Drivers page.

  2. Download the JDBC driver package.

    3. Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Follow the instructions in the Vertica documentation to install the driver.
  4. Copy the driver to the following folder: 
    C:\Users\Administrator\AppData\Roaming\odi\oracledi\userlib

    If the AppData folder is in hidden mode, open Folder Options in Windows Control Panel to view it.

  5. Restart Oracle Data Integrator.

Connecting Oracle Data Integrator to Vertica

To connect Oracle Data Integrator to Vertica, you need to

  1. Create a master repository and a work repository.
    Follow the instructions in Creating the Master and Work Repository Schemas in the Oracle Data Integrator documentation.
  2. Create a Vertica topology that includes the connection information.

    Note This guide provides information to create the topology. We assume that you followed the instructions in the Oracle documentation to create the repositories.

Creating a Topology

  1. Open Oracle Data Integrator Studio.
  2. In the Topology tab, right-click Technology > New Technology:

  3. On the Definition tab for the new technology, type Vertica for Name and select Database Files (JDBC/ODBC) for Technology type. The Code field is populated automatically. Leave the Logical and Physical boxes checked.

    Scroll down to Data Handling and select the options shown in the following screen shot. For Support Set Operator, type the following in the Comma separated Operator List field:

    UNION,UNION ALL,UNION DISTINCT,EXCEPT,EXCEPT ALL,EXCEPT DISTINCT,INTERSECT,INTERSECT ALL,INTERSECT DISTINCT

    Scroll down to Naming Rules. Select the following options and type the values as shown:

  4. Open the Advanced tab, and set Default Table Prefixes as follows:

Creating a Connection to Vertica in the Topology

  1. In the Oracle Data Integrator Studio, expand the Topology tab and click Physical Architecture.

  2. Expand Technologies and select Vertica.

  3. Right-click Vertica > New Data Server.

  4. On the Definition tab, type a name for the new data server and the user ID and password for the Vertica database.

  5. Open the JDBC tab and type the following:

    • JDBC Driver: com.vertica.jdbc.Driver
    • JDBC URL: jdbc:vertica://Host_name:Port/Database_name

  6. Click Test Connection.

  7. When the Information dialog box appears, click OK.

  8. In the Test Connection dialog box, click Test.

  9. When the connection is successful, click OK.

Creating a Job in Oracle Data Integrator

Before you create a job in Oracle Data Integrator, you must

  • Create a logical schema
  • Create data types
  • Create a model
  • Create a project

Creating a Logical Schema

  1. In the Topology tab of Oracle Data Integrator Studio, expand Physical Architecture > Technologies > Vertica.
  2. Right-click the data server that you created in the previous section and select New Physical Schema.

  3. On the Physical Schema page for the data server, open the Context tab.

  4. Click the plus sign (+) to create a new logical schema.

  5. Type a name for the logical schema and press Ctrl+S to save it.

Creating Vertica Data Types

Before you create a model, you must define Vertica data types in the topology connection.

Note Oracle Data Integrator does not support all Vertica data types. See the Known Limitations section in this document.

  1. Open the Topology tab and expand Technologies.

  2. Expand Vertica, right-click Datatypes > New Data Type.

  3. Provide the information for a Vertica data type.

  4. Click Converted To and press Ctrl+S to save.

  5. Repeat these steps for each Vertica Data Type.

Creating a Model

  1. Open the Designer tab.
  2. Click the Models drop-down arrow and then click New Model.

  3. Provide a name for the model, and select the values for Technology and Logical Schema.

  4. Click Selective Reverse-Engineering.

  5. Check New Datastores and Objects to Reverse Engineer, then click Reverse Engineering.

  6. In the Confirmation window, click Yes.

    All the tables are loaded in the model.

Creating a Project and Running the Job

A project can include multiple models. Follow the steps for creating a model for each table that you want to include in the project.

  1. On the Designer tab, select Project.
  2. Click the drop-down arrow and select New Project.

  3. Provide a name for the project and press Ctrl+S to save it.

Before you can run the job,

  • You must import the Oracle Data Integrator Knowledge Modules that are related to the project.
  • You must create mappings for each of the models in the project.

Importing Oracle Data Integrator Knowledge Modules

  1. Return to the Designer tab, expand your project, right-click Knowledge Module. > Import Knowledge Modules.

  2. Select the modules and click OK.

  3. The Knowledge Modules which are imported and used for Vertica to Vertica transformations are as follows:

    LKM SQL Multi-Connect.GLOBAL

    IKM SQL TO SQL Control Append

    CKM SQL

Creating Mappings

  1. In the Designer tab, expand Projects.

  2. Right-click Mappings > New Mapping

  3. Drag the source and target tables from the models you created and map them.

  4. Click Run to run the job.

Enabling Bulk Load

  1. Go to Topology > Technologies > Vertica.

  2. Right-click the existing connection and click Open.

  3. You can adjust the following parameters based on your requirement.

    • Array Fetch Size : Enables the source connection to fetch multiple records at once.

    • Batch Update Size: Enables target connection for bulk copying.

    • Degree of Parallelism for Target: Adjust this based on the system hardware.



  4. To get the best performance, increase the Array Fetch Size Parameter at Source connection and increase the Batch Update Size at Target connection.

Known Limitations

  • INTERVALMONTH, INTERVALSECONDS, TIMESTAMPTZ, and UUID data types are not displayed.

For More Information