Share this article:

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
Desktop platform

Windows Server 2019

Vertica client

Vertica JDBC client driver 11.0.0

Vertica Server Vertica Analytical Database 11.0.0

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.

ODI and ELT

ODI uses an Extract, Load and Transform (ELT) paradigm instead of the more traditional Extract, Transform and Load (ETL) method. ETL systems extract data from multiple sources, then perform the transformations before loading it into the target database. ELT systems extract and load the data, then push the transformation processing down to the target database, where the processing is typically more performant.

Reverse Engineering

ODI supports multiple heterogeneous data sources and targets, including relational databases, ERPs, LDAP, XML, and flat files. Regardless of the underlying technology, all data appears in ODI in the form of datastores that can be manipulated and integrated in the same way. Datastores describe data in tabular format. A Model is the description of a set of datastores. A topology describes a set of models as a logical schema that maps to a physical data source.

When a new model is created, it does not contain any datastores. Reverse engineering is the process that populates the model by retrieving metadata from the data source. There are two different types of reverse engineering:

• Standard reverse engineering, which uses standard JDBC driver features to retrieve the metadata.

• Customized reverse engineering, which uses a Reverse Knowledge Module (RKM) specific to the data source to retrieve the metadata.

Note The Vertica topology for ODI uses customized reverse engineering.

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.

  4. Follow the instructions in the Vertica documentation to install the driver.
  5. 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.

  6. 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 Mater 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 SQL tab for the new technology and provide the values as follows:

    • In the WHERE section, select Complex Expression for each option.
    • In the Ordered Joins (ISO) section, type FROM for Clause Location, click each of the check boxes, and type the values as in the image.
    • In the Specific Queries section, select Current Date, and type SELECT NOW().

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

  6. Open the Language tab, and type the following for Word Separator:

    Word Separator:  ,()+*-/|<>;!=&{}`\^%.#:$

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.

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.

Known Limitations

  • INTERVALMONTH and INTERVALSECONDS data types are not supported
  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported.

For More Information

Share this article: