Share this article:

Vertica Integration with Oracle Data Integrator: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested these other versions.

About this Document

This document explains how to connect Vertica as both a source and a target within Oracle Data Integrator.

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 E-LT

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.

Vertica and ODI Product Versions

The information in this document is based on the testing we have done with the following software versions:

Software Version

Oracle Data Integrator

12.2

Client Platform

Windows Server 2012

Vertica Client

Vertica JDBC 8.0.x

Vertica Server

Vertica Analytic Database 8.0.x

Server platform Linux

Install ODI

  1. Navigate to the Oracle Data Integrator Downloads page on the Oracle Technology Network:
    http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html
  2. Accept the OTN license agreement.
  3. Download ODI.
  4. Follow the installation instructions in the Oracle Data Integrator documentation.

Install the Vertica Client Driver

ODI uses JDBC to connect to Vertica. To install the Vertica JDBC driver, follow these steps:

  1. Go to the Vertica Client Drivers page on http://my.vertica.com.
  2. Under All Operating Systems, select the JDBC driver for Vertica 8.0.x.
  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 Unhide it.

  5. Restart ODI.

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

Connect Vertica to ODI

Creating a new connection between Vertica and ODI consists of two tasks:

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

Note In this document, we provide instructions for creating the topology. We assume that you have followed the instructions in the Oracle documentation to create the repositories.

Create a Topology

  1. Start Oracle Data Integrator Studio.
  2. On the Topology tab, right-click Technology and select 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. Under Support Set Operator, type the following for Comma separated Operator List:

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

    Scroll down to Naming Rules. Select the options and type the values shown in the following screen shot:

  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 shown in the screen shot.
    • In the Specific Queries section, select Current Date, and type SELECT NOW().

    In the Specific Queries section of the SQL tab, you will also specify the reverse engineering for this technology.

  5. In the Reverse Engineer PK's tab, copy and paste the following SQL:

    KC.column_name	as COLUMN_NAME,
    KC.ordinal_position as POS
    From 	v_catalog.table_constraints KE,
    v_catalog.key_column_usage KC
    where
    KE.constraint_type = 'PRIMARY KEY'
    And	KE.constraint_schema = KC.constraint_schema
    And	KE.constraint_name = KC.constraint_name
    And	KE.table_schema =  :SCHEMA
    and	KE.table_name	= :TABLE
  6. In the Reverse Engineer AK's tab, copy and paste the following SQL:

    Select 	KE.constraint_name as AK_NAME,
    KC.column_name	as COLUMN_NAME,
    KC.ordinal_position as POS
    From 	v_catalog.table_constraints KE,
    v_catalog.key_column_usage KC
    where
    KE.constraint_type =  'UNIQUE'
    and	KE.constraint_schema = KC.constraint_schema
    and	KE.constraint_name = KC.constraint_name
    and	KE.table_schema =  :SCHEMA
    and	KE.table_name	= :TABLE
  7. In the Reverse Engineer FK's tab, copy paste the following SQL:

    select
    TP.table_name as PK_TABLE_NAME,
    FK.constraint_name as FK_NAME,
    CF.column_name as FK_COLUMN_NAME,
    CP.column_name as PK_COLUMN_NAME
    from 	v_catalog.referential_constraints FK,
    v_catalog.table_constraints TF,
    v_catalog.key_column_usage  CF,
    v_catalog.table_constraints TP,
    v_catalog.key_column_usage  CP
    where	FK.constraint_name = TF.constraint_name
    And	FK.constraint_schema = TF.constraint_schema
    And	TF.constraint_type='FOREIGN KEY'
    And	TF.constraint_name = CF.constraint_name
    And	TF.table_schema = CF.table_schema
    And	TF.table_name = CF.table_name
    And	FK.unique_constraint_name = TP.constraint_name
    And	FK.unique_constraint_schema = TP.constraint_schema
    And	TP.constraint_type='PRIMARY KEY'
    And	TP.constraint_name = CP.constraint_name
    And	TP.table_schema = CP.table_schema
    And	TP.table_name = CP.table_name
    And 	CF.ordinal_position = CP.ordinal_position
    And 	TF.table_name = :TABLE
    And 	FK.constraint_schema = :SCHEMA
    And 	TF.constraint_schema = :SCHEMA
  8. In the Reverse Engineer Indexes tab, copy and paste the following SQL:•

    SELECT MyindexClass.relname  as INDEX_NAME ,
    Mycolumn.attname as COLUMN_NAME,
    1 as POSITION
    FROM pg_index Myindex,
    pg_class Mytable,
    pg_class MyindexClass,
    pg_attribute Mycolumn
    WHERE Mytable.relname='client'
    AND Mytable.oid=Myindex.indrelid
    AND Myindex.indisunique != 't'
    AND Myindex.indisprimary != 't'
    AND MyindexClass.relkind = 'i'
    AND MyindexClass.oid = Myindex.indexrelid
    AND Mytable.oid = Mycolumn.attrelid
    AND Mycolumn.attnum>0
    AND Mycolumn.attnum = Myindex.indKey[0]
    union
    SELECT MyindexClass.relname   ,
    Mycolumn.attname ,
    2
    FROM pg_index Myindex,
    pg_class Mytable,
    pg_class MyindexClass,
    pg_attribute Mycolumn
    WHERE Mytable.relname='client'
    AND Mytable.oid=Myindex.indrelid
    AND Myindex.indisunique != 't'
    AND Myindex.indisprimary != 't'
    AND MyindexClass.relkind = 'i'
    AND MyindexClass.oid = Myindex.indexrelid
    AND Mytable.oid = Mycolumn.attrelid
    AND Mycolumn.attnum>0
    AND Mycolumn.attnum = Myindex.indKey[1]
    union
    SELECT MyindexClass.relname   ,
    Mycolumn.attname ,
    3
    FROM pg_index Myindex,
    pg_class Mytable,
    pg_class MyindexClass,
    pg_attribute Mycolumn
    WHERE Mytable.relname='client'
    AND Mytable.oid=Myindex.indrelid
    AND Myindex.indisunique != 't'
    AND Myindex.indisprimary != 't'
    AND MyindexClass.relkind = 'i'
    AND MyindexClass.oid = Myindex.indexrelid
    AND Mytable.oid = Mycolumn.attrelid
    AND Mycolumn.attnum>0
    AND Mycolumn.attnum = Myindex.indKey[2]
    union
    SELECT MyindexClass.relname   ,
    Mycolumn.attname ,
    4
    FROM pg_index Myindex,
    pg_class Mytable,
    pg_class MyindexClass,
    pg_attribute Mycolumn
    WHERE Mytable.relname='client'
    AND Mytable.oid=Myindex.indrelid
    AND Myindex.indisunique != 't'
    AND Myindex.indisprimary != 't'
    AND MyindexClass.relkind = 'i'
    AND MyindexClass.oid = Myindex.indexrelid
    AND Mytable.oid = Mycolumn.attrelid
    AND Mycolumn.attnum>0
    AND Mycolumn.attnum = Myindex.indKey[3]
  9. In the Reverse Check Constraints tab, copy and paste the following SQL:

    SELECT	c.conname  AS CHECK_NAME,
    c.consrc  as CHECK_TXT,
    '1' as CHECK_STATUS,
    c.conname ||  ' Non Respectée'  as CHECK_DESC
    FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r
    WHERE 	c.contype ='c'
    and 	nc.oid = c.connamespace
    and 	nr.oid = r.relnamespace
    and 	c.conrelid = r.oid AND r.relkind = 'r'
    and 	pg_has_role(r.relowner, 'MEMBER')
    and	nr.nspname	=  :SCHEMA
    and	r.relname	= :TABLE
  10. In the Reverse Engineer Native Sequences tab, copy and paste the following SQL:

    select c.relname as SEQUENCE_NAME
    from pg_class c, pg_namespace ns
    where upper(c.relkind) = 'S'
    and c.relnamespace = ns.oid
    and ns.nspname = :SCHEMA
    order by c.relname
  11. In the Sequences Next Value In Non-binded Mode tab, copy and paste the following SQL:

    select nextval('%SCHEMA.%OBJECT')
  12. Open the Advanced tab, and set Parameters and Default Table Prefixes as follows:

  13. Open the Language tab, and type the following under Word Separator:

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

Create a Connection to Vertica in the Topology

  1. Expand the Topology tab of Oracle Data Integrator Studio and click Physical Architecture.

  2. Expand Technologies and select Vertica.

  3. Right-click Vertica and select 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.

Create a Job in ODI

Before you can create a job in ODI, you must complete the following:

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

Create a Logical Schema

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

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

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

  6. Type a name for the logical schema and press Ctrl-S to save it.

Create the Vertica Data Types

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

Note ODI 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, then right-click Datatypes and select New Data Type.

  3. Provide the information for a Vertica data type

  4. Open the Converted To tab and press Ctrl-S to save.

  5. Repeat for each Vertica Data Type.

Create a Model

  1. Open the Designer tab.
  2. Select Model and click the down arrow.
  3. Click New Model.

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

  5. Open the Select Reverse Engineering tab

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

  7. In the Confirmation dialog box, click Yes.

    All the tables are loaded in the model.

Create a Project and Run the Job

A project can include multiple models. Follow all 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 down arrow and select New Project.

  3. Provide a name for the project and press Ctrl-Save to save it.

Before you can run the job, you must import the ODI Knowledge Modules that are related to the project.

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

  2. Select the modules and click OK.

Before you can run the job, you must create mappings for each of the models in the project. To create the mappings:

  1. In the Designer tab, expand Projects.

  2. Expand your project.

  3. Right-click Mappings, and select New Mapping

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

  5. Click the Run button to run the job.

Known Limitations

ODI does not support the following Vertica data types:

  • IntervalSeconds
  • IntervalMonth
  • Binary, VarBinary
  • Long VarBinary

Find More Information

To learn more about Vertic, see the following:

Share this article: