X

Best Practices from Oracle Development's A‑Team

A Universal Cloud Applications Adapter for ODI

Christophe Dupupet
Sr Director, A-Team - Cloud Solutions Architects

Cloud computing is dramatically accelerating the number of systems and technologies with which integration solutions have to interact. As new Cloud applications seem to appear every day, defining the specifics for each one of these applications one at a time quickly becomes a daunting task. To address this new challenge, we have created a universal approach that allows us to better leverage ODI with all of these emerging technologies, from the latest fad to the most established ones.

Update 1: for a demonstration of the technologies described in this blog, you can view this recording: Oracle Data Integrator Special Topic:  Salesforce.com & Universal Cloud.

Update 2: ODI now supports Salesforce connectivity out-of-the-box. The example below remains relevant for other technologies, but if you need to connect to Salesforce specifically, please use the components delivered with ODI.

1. Background: Generating the best possible code

To be able to generate the most efficient SQL code for each and every database, ODI embeds a complete definition of their characteristics. This includes the definition of the native SQL supported by the databases, the definition of their native data types and how they convert to other technologies, and all the details that allow ODI to generate the best possible code for each technology. This includes DDL and DML generation, truly setting ODI in a category of its own.

In a Cloud environment and with Cloud applications in particular, the requirements are somewhat different. Similarly to databases, JDBC drivers can be used to access Cloud applications (see the DataDirect Cloud JDBC drivers for instance). But Cloud applications often impose limitations in what code can be generated: there is usually no support for DDL execution (objects are typically created from the UI, not with a SQL statement); DML is typically limited to the most basic statements (no analytic functions for instance).

2. Defining a Universal Technology

Creating a generic technology that supports all Cloud applications makes a lot of sense from an ODI perspective. Since applications impose a lot of limitations on what is possible with the generated code, the definition of all these technologies in ODI ends up being very similar - with the exception of data types that remain application specific. But as long as we are using JDBC, we can rely on standard JDBC data types and thus use the exact same data types for all Cloud applications. The number one limitation with this approach is that DDL generation is not be possible anymore: this is not an issue here as Cloud applications rarely allow this.

3. Making a Universal Cloud Applications Adaptor for ODI

From an ODI perspective, we need two elements to properly implement a Universal Cloud Applications technology:

  • We need to define a new Technology in ODI Topology. This defines what SQL code can be generated, how to generate it and what data types are supported.
  • By default, when ODI reverse-engineers objects from a system, it uses native datatypes for the definition of the columns. Because we want to use generic data types instead of the native ones, we need to have a Reverse-engineering Knowledge Module (RKM) that returns the generic data types.

Once these elements are in place, generic Knowledge Modules can be used with this technology. The ODI A-Team is working with customers on alterations to some of these KMs to further improve performance related to Cloud Connectivity, but the existing KMs definitely works. The team is also working on some Integration KMs that are more application specific, but these KMs still take advantage of the Cloud Applications technology described here.

Let’s now look into the specifics of the technology and the RKM.

3.1 The technology

We have created the necessary technology, called Cloud Applications. It can be downloaded from OTN here for ODI 12c. This technology can be used create data servers that contain the necessary parameters to connect to Cloud applications used as source or target in ETL mappings.

The Cloud Applications technology is used the same way as any other ODI technology: define your data server and enter the appropriate parameters for your JDBC driver, then create the necessary physical and logical schemas.

If you are using JDBC drivers that are not provided out of the box with ODI, you have to copy them into the following locations:

  • For the ODI Studio on a windows system: \Users\<yourUserName>\AppData\Roaming\odi\oracledi\userlib
  • For the ODI Studio on a unix system: $ODI_HOME/.odi/oracledi/userlib
  • For a standalone agent; <YourODIAgentHome>\odi\agent\lib
  • For a JEE agent, you can create an agent template to deploy the driver. This is described here in the Oracle documentation: Creating a Server Template for the Java EE Agent

Remember to restart your Studio and Agents after installing the drivers to make sure that these new drivers are properly loaded.

One great benefit is that under this Cloud Applications technology, each data server defines how to connect to one Cloud application. Different applications can be listed under this technology as Data Servers, each using its own JDBC driver and connection parameters. Below is an example of what the technology and associated datatypes look like in ODI Topology, with sample data servers defined to connect to Eloqua, Oracle Service Cloud and SalesForce:

Techno

One element to keep in mind is that, when using this technology, you have to use the RKM described below to reverse engineer cloud objects.

The mappings from these data types to most supported technologies are already part of the Cloud Applications technology definition. At this point, only the Oracle technology has the necessary mappings TO this technology. If you are using the Cloud Applications technology with other technologies than Oracle, you have to define the data type mappings from these technologies into the appropriate Cloud application data types. This must be done whether the Cloud application is used as a source or a target. To do this, edit each data type in the other technology, and select the matching type in the Cloud application. The example below shows how to do this matching in ODI Topology for the VARCHAR data type of MySQL.

DataTypeMapping

Note that not all data types have to be mapped, only the ones that are used in the mappings.

3.2 The Reverse-Engineering Knowledge Module

The RKM is used like any other RKM: import the RKM as a Global KM or as a local KM into the project of your choice (please note that a Global RKM would be a better choice as an RKM should really not be project specific).

Once the KM has been imported, create a model that points to the logical schema created for the Cloud application that you want to use. In the Reverse Engineer tab of the model, select the Customized option, and then select the RKM Cloud Application in the Knowledge Module drop down, as shown below:

CloudApplicationRKM

Keep in mind that with an RKM you do not have access to the Selective Reverse Engineering tab: if you want to filter or restrict the list of objects returned by the KM, use the Mask option. For instance, set the mask to ORDERS% to retrieve only table names that start with ORDERS.

Retrieving primary keys and foreign keys is disabled by default in the RKM options because not all technologies support referential integrity. As long as the Cloud application supports them, and as long as the associated JDBC driver supports them too, you can enable the options to import these as well.

The RKM can be downloaded from OTN here for ODI 12c. Obviously you can easily extend its use and capabilities if need be by modifying the content and behavior.

4. Using the Cloud Applications technology and the associated RKM

If you reverse engineer the Cloud objects for this technology without the RKM Cloud Application, you have to be aware that the behavior can be misleading. First it looks like the reverse engineering process worked: tables and columns are be properly listed. But if you look in the details of the columns, you can see that very few (if any) of these columns have any data types. As you build your mappings, these columns without data types are ignored by ODI when it generates the code (there is no way to convert data to and from unknown data types) and the resulting SQL code is missing a large number of mapped columns.

So remember, when using this technology, you do have to use the RKM Cloud Application (same RKM for all Cloud applications). Once this is done, you can use these tables either as source or targets for your mappings as with any other tables.

5. Beyond basics

As always in ODI, it is possible to expand the use of the Cloud Applications technology with additional Knowledge Modules that can be application or vendor specific. As Cloud applications mature over time, it will always be possible to derive a new, more advanced technology on a case by case basis, and to support more advanced features for the more advanced of these applications.

Conclusion

By combining the Cloud Applications technology, the associated RKM and a JDBC driver that gives access to the applications, ODI can integrate Cloud data the same way it integrates all other data. And the Cloud Applications technology is ready to supports the JDBC drivers of tomorrow’s Cloud applications.

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for ODI.

Acknowledgements

Special thanks to Sumit Sarkar from DataDirect and Shankar Malayya from Forrester Research for their help and support in putting this solution together, as well as my friend and colleague Tim Garrod for reviewing the KMs.

Join the discussion

Comments ( 2 )
  • Ankush Wednesday, May 1, 2019
    Thanks for this very informative article.

    We are trying to integrate ODI with AWS Athena for one of our requirements.
    We have downloaded the Athena JDBC driver from https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html#jdbc-url-format

    Copied the same into the userlib directory of ODI
    Imported the Cloud Applications technology that you mentioned above.
    Now trying to add new Data server but not able to form JDBC url.
    JDBC string Sample format (which isn't working):
    jdbc:awsathena://AwsRegion=[Region];User=[AccessKey];Password=[SecretKey];S3OutputLocation=[Output];

    Can you please help with the correct JDBC url format to be used in ODI for AWS Athena.

    Many Thanks.
  • Christophe Friday, May 17, 2019
    What error message do you get when you try the connection with the URL defined above? The trick is to look into the error stack: if there is a mention of the driver you are using in that stack(jdbc:awsathena), then ODI is probably only returning the error message that it gets from the driver. If not, it could be that somehow ODI either did not find the driver (check the owner of the file, privileges on that file, Java version compatibility for that driver, etc.) or could not load it for some reason.
    I hope this helps!
    -Christophe
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha