Tuesday Dec 03, 2013

ODI 12c - Mapping SDK the ins and outs

The ODI 12c SDK provides a mechanism to accelerate data integration development using patterns and the APIs in the SDK. With OWB many customers automated mundane tasks - tasks that were repeated and consistent. I've uploaded a bunch of examples covering many characteristics of the 12c mapping SDK, the examples are primarily slanted towards the logical side right now. You can find examples of the components and images for each on the java.net site (download ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy). This is an open community area where you too can contribute.

The mappings I have covered include (click on hyperlink for an image of each);

  1. basic datastore to datastore mapping with configuration of the LKM and IKM on the deployment spec which options set (datastore component)
  2. simple filter example writing to a target (filter component).
  3. datastores being joined, with a left outer join property set then written to a target (join component).
  4. data being looked up from a reference table using the (lookup component).
  5. data being aggregated, SUM aggregation used and data automatically grouped (aggregate component).
  6. defining expressions which may be reused across many map parts, also useful for explicitly seeing expressions in canvas (expression component).
  7. unifying data using the relational set component to UNION/MINUS data (set component).
  8. if then/else capabilities using the split component to define branches (split component).
  9. defining a dataset to source data from, defines joins and filters (dataset component)
  10. deduplicating data with distinct (distinct component).

These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. I plan on more examples, plenty more to show including some of the nitty gritty details on the physical deployment specification side. I'd also like to show the mapping accelerators, I posted some while back this kind of capability for 11g (see here).

If you are brand new to the ODI SDK, I tend to break down the labyrinth of classes into the following sections (I blogged about this for 11g here);

Entry to the Platform

Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Mapping IMappingFinder Mapping
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios

Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects

Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule OdiSchedule

How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")
odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");

odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession) sessItr.next()
  odiInstance.getTransactionalEntityManager().remove(sess)
}
tm.commit(txnStatus)

Hopefully these examples will get you on your way. This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. The mappings examples are a good start, more to come on those, remember and check out the ODI area in java.net for the examples (here). Have fun, happy coding

Friday Dec 28, 2012

ODI - Basic Hive Queries

Here we will see a basic example joining the movie lens data and then loading a Hive table based on the tables from the Reverse Engineering Hive Tables post. The Hive table was defined and created via ODI, I duplicated the movies table and added a column for the rating, just for demo purposes...

When I build my interface, I add movies for my source and movies_info as my target, the auto mapping completes much of the mapping, the rating (which is not mapped below) comes from another table - this is where ODI's incremental design is nice, I can add in a new datastore as a source and map columns from it, then describe the join. 

 After I have added the movie ratings table, I will define the join just by dragging movie_id from movies to the ratings table movie_id column. That's the join...mostly defined.

The other thing you need to check is that the ordered join property is set. This will generate the ordered join (ANSI style, but using the Hive technology's template) syntax.

 We can also perform transformations using built in or user defined functions, below I am performing the Hive built-in UPPER function on the movie name for example.

In the physical, or flow view I am using the Hive Control Append IKM, I am using ODI to create the target table in Hive and also performing a truncate if it exists. Also have the control flow switched off. 

Executing this is just like any other interface apart from we leverage Hive to perform the heavy lifting. The resultant execution can be inspected in the ODI operator or console and the resultant table inspected when complete. 

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today