|Share this article:|
|✉ Send Feedback for 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:
|Oracle Data Integrator||12.2|
Windows Server 2019
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.
Installing Oracle Data Integrator
- Navigate to the Oracle Data Integrator Downloads page on the Oracle Technology Network.
- Accept the OTN license agreement.
- 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:
Go to the Vertica Client Drivers page.
- Download the JDBC driver package.
- Follow the instructions in the Vertica documentation to install the driver.
- Copy the driver to the following folder:
If the AppData folder is in hidden mode, open Folder Options in Windows Control Panel to view it.
- Restart Oracle Data Integrator.
Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Connecting Oracle Data Integrator to Vertica
To connect Oracle Data Integrator to Vertica, you need to
- 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.
- 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
- Open Oracle Data Integrator Studio.
- In the Topology tab, right-click Technology > New Technology:
On the Definition tab for the new technology, type
Verticafor 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:
Open the Advanced tab, and set Default Table Prefixes as follows:
Creating a Connection to Vertica in the Topology
- In the Oracle Data Integrator Studio, expand the Topology tab and click Physical Architecture.
Expand Technologies and select Vertica.
Right-click Vertica > New Data Server.
On the Definition tab, type a name for the new data server and the user ID and password for the Vertica database.
Open the JDBC tab and type the following:
- JDBC Driver:
- JDBC URL:
- JDBC Driver:
Click Test Connection.
When the Information dialog box appears, click OK.
In the Test Connection dialog box, click Test.
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
- In the Topology tab of Oracle Data Integrator Studio, expand Physical Architecture > Technologies > Vertica.
- Right-click the data server that you created in the previous section and select New Physical Schema.
On the Physical Schema page for the data server, open the Context tab.
Click the plus sign (+) to create a new logical schema.
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.
- Open the Topology tab and expand Technologies.
Expand Vertica, right-click Datatypes > New Data Type.
Provide the information for a Vertica data type.
Click Converted To and press Ctrl+S to save.
Repeat these steps for each Vertica Data Type.
Creating a Model
- Open the Designer tab.
- Click the Models drop-down arrow and then click New Model.
Provide a name for the model, and select the values for Technology and Logical Schema.
Click Selective Reverse-Engineering.
Check New Datastores and Objects to Reverse Engineer, then click Reverse Engineering
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.
- On the Designer tab, select Project.
- Click the drop-down arrow and select New Project.
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
- Return to the Designer tab, expand your project, right-click Knowledge Module. > Import Knowledge Modules.
Select the modules and click OK.
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
In the Designer tab, expand Projects.
Right-click Mappings > New Mapping
Drag the source and target tables from the models you created and map them.
Click Run to run the job.
- INTERVALMONTH, INTERVALSECONDS, TIMESTAMPTZ, and UUID data types are not supported