This chapter describes how to work with Autonomous Data Warehouse Cloud (ADWC) in Oracle Data Integrator.
This chapter includes the following sections:
Autonomous Data Warehouse Cloud (ADWC) is a fully-managed, high-performance elastic cloud service providing analytical capability over data stored in the database and Oracle Object Store.
Oracle Data Integrator (ODI) seamlessly integrates with ADWC. By integrating ODI with ADWC, you can get the full performance of Oracle Database, in a fully-managed environment that is tuned and optimized for data warehouse workloads.
The Oracle ADWC concepts map the Oracle Data Integrator concepts as follows: An Oracle ADWC Instance corresponds to a data server in Oracle Data Integrator. Within this instance, a schema maps to an Oracle Data Integrator physical schema. A set of related objects within one schema corresponds to a data model, and each table, view or synonym will appear as an ODI datastore, with its attributes, columns and constraints.
Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to Oracle ADWC instance. All connections to the ADWC require the use of an Oracle Wallet to manage public key security credentials.
Oracle Data Integrator provides the following Knowledge Modules (KM) for loading data into ADWC. The KMs use Oracle specific features. It is also possible to use the generic SQL KMs with ADWC.
Table 3-1 ADWC Knowledge Modules
| Knowledge Module | Description |
|---|---|
|
LKM SQL to Oracle (Built-In) |
Loads data from any ANSI SQL-92 source database to an Oracle staging area. |
|
LKM SQL Multi-Connect |
Enables the use of multi-connect IKM for target table. Built-in IKM. |
|
LKM File to Oracle (SQLLDR) |
Loads data from a file to an Oracle staging area using the SQL*Loader command line utility. |
|
IKM SQL to File Append |
Integrates data in a target file from any ANSI SQL-92 compliant staging area in replace mode. |
|
IKM Oracle Insert |
Integrates data into an Oracle target table in append mode. The data is loaded directly in the target table with a single INSERT SQL statement. Built-in KM. |
|
IKM Oracle Update |
Integrates data into an Oracle target table in incremental update mode. The data is loaded directly into the target table with a single UPDATE SQL statement. Built-in KM. |
|
IKM Oracle Merge |
Integrates data into an Oracle target table in incremental update mode. The data is loaded directly into the target table with a single MERGE SQL statement. Built-in KM. |
|
IKM Oracle Multi-Insert |
Integrates data from one source into one or many Oracle target tables in append mode, using a multi-table insert statement (MTI). This IKM can be utilized in a single mapping to load multiple targets. Built-in KM. |
|
RKM Oracle |
Reverse-engineers tables, views, columns and creates data models to use as targets or sources in Oracle Data Integrator mappings. |
The following prerequisites are essential for connecting to ADWC environment. Make sure you go through the following prerequisites, before connecting to ADWC environment.
Note:
The following prerequisites are common for both ODI Studio and ODI Agent.Wallet Configuration
All connections to ADWC require the use of an Oracle Wallet to manage public key security credentials. A wallet is a password-protected container used to store authentication and signing credential, including private key, certificates, and trusted certificates needed by SSL. Oracle Wallet provides a simple and easy method to manage database credentials across multiple domains. It allows you to update database credentials by updating the Wallet instead of having to change individual data source definitions. To connect to the ADWC, applications need access to the Oracle wallet.
For more details on wallet, refer to Securing Passwords in Application Design section of Managing Security for Application Developers in Database Security Guide.
The JDBC properties require a Wallet file location.
Get the wallet zip file from ADWC wallet location and place it in a local directory accessible to both ODI Studio and ODI Agent and unzip it.
Java Cryptography
Download and install JCE Unlimited Strength under the JAVA_HOME used by ODI studio and ODI Agent. JCE Unlimited Strength provides a higher level of security as compared to the standard Java installation.
Download and install Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files from the location — Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files.
Java Security configuration
Note:
Steps listed below are not required for JDK1.8.0_u161.Update the file java.security, from the location JDK_HOME\jre\lib\security and add the line "security.provider.11=oracle.security.pki.OraclePKIProvider" as shown below:
security.provider.1=sun.security.provider.Sun
security.provider.2=sun.security.rsa.SunRsaSign
security.provider.3=sun.security.ec.SunEC
security.provider.4=com.sun.net.ssl.internal.ssl.Provider
security.provider.5=com.sun.crypto.provider.SunJCE
security.provider.6=sun.security.jgss.SunProvider
security.provider.7=com.sun.security.sasl.Provider
security.provider.8=org.jcp.xml.dsig.internal.dom.XMLDSigRI
security.provider.9=sun.security.smartcardio.SunPCSC
security.provider.10=sun.security.mscapi.SunMSCAPI
security.provider.11=oracle.security.pki.OraclePKIProvider
Note:
Please note the ADWC data server is created under Oracle Technology.Setting up the Topology consists of:
This section contains the following topics:
In most of the cases, consider using the standard JDBC reverse engineering for starting. Standard reverse-engineering with Oracle retrieves tables, views, columns and references.
Consider switching to customized reverse-engineering for retrieving more metadata. Oracle customized reverse-engineering retrieves the table and view structures, including columns, indexes, check constraints, synonyms, and references.
To perform a Standard Reverse-Engineering on an Oracle model for ADWC, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.
To perform a Customized Reverse-Engineering on Oracle model for ADWC with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.
This section details only the fields specific to the Oracle technology. In the Reverse Engineer tab of the Oracle Model, select the KM — RKM Oracle.<project name>.
You can use Oracle ADWC as a source or a target of a mapping. It is also possible to create ETL-style mappings based on the Oracle technology for ADWC.
The KM choice for a mapping or a check determines the abilities and performance of this mapping or check. The recommendations in this section help in the selection of the KM for different situations concerning an Oracle data server.
ADWC can be used as a source or target of a mapping. The LKM choice in the Mapping's Loading Knowledge Module tab to load data between Oracle and another type of data server is essential for the performance of a mapping.
The following KMs implement optimized methods for loading data from an Oracle database to a target database. In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.
1. Loading Data using Oracle KMs
You can load data into Oracle tables using the following Oracle KMs by designing a mapping where ADWC Oracle datastores can be the target. KMs that can be used for mapping are:
LKM SQL to Oracle (Built-In)
IKM Oracle Insert
IKM Oracle Update
IKM Oracle Merge
IKM Oracle Multi-Insert
2. Loading Data using SQL* Loader KMs
You can also load data into Oracle tables for ADWC using the SQL* Loader KM. LKM File to Oracle (SQLLDR) loads data into Oracle tables from files. You can design a mapping that uses the data stores for an Oracle Schema for ADWC as the target of the mapping, where the data is loaded using the SQL*Loader KM.
Make sure your tnsnames.ora and sqlnet.ora properties are configured to use a Wallet file.
For Example:
sqlnet.ora : WALLET_LOCATION=(SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="<wallet_directory>"))) SSL_SERVER_DN_MATCH=yes
tnsnames.ora :
<db_name>_DB_high=(description=(address=(protocol=tcps)(port=<port>)(host=<hostname or ipaddress>))(connect_data=(service_name=<db_service>))(security=(ssl_server_cert_dn="<certificate_info>")) )
<db_name>_DB_medium=(description=(address=(protocol=tcps)(port=<port>)(host=<hostname or ipaddress>))(connect_data=(service_name=<db_service>))(security=(ssl_server_cert_dn="<certificate_info>")) )
<db_name>_DB_low=(description=(address=(protocol=tcps)(port=<port>)(host=<hostname or ipaddress>))(connect_data=(service_name=<db_service>))(security=(ssl_server_cert_dn="<certificate_info>")) )
For more details, refer to Use of an External Password Store to Secure Passwords section of Database Security Guide.
In ODI, you can extract data from ADWC via JDBC using Oracle KMs.
You can design a mapping that uses the Data Stores for an ADWC Schema as the source of the mapping, where you can extract data via JDBC using the following Oracle KMs:
Extract data from ADWC and load to on premise Oracle Table (Component KMs) through
LKM SQL to Oracle (Built-In)
IKM Oracle Insert
Extract data from ADWC and load to on premise File through
LKM SQL Multi-Connect
IKM SQL to File Append