ODI - MongoDB and Java Table Functions Anyone?

Let's see how we could integrate MongoDB using ODI, first take a step back. Derby/JavaDB introduced table functions a few years ago. Table functions are really useful, they are in the Oracle database and as much fun in JavaDB! ODI is a great platform for integration and JavaDB and table functions provide a really nice way to integrate arbitrary Java APIs into your designs. What I have done here is;

  • built a very simple java table function to project a table to represent the documents in a MongoDB collection. The collection is passed as a parameter to the KM and the column names are the keys for the MongoDB document. The data comes from the values.
  • built a very simple LKM from a Java table function to SQL

All of this will use the JavaDB in-memory, so no admin, simple to use. Rick Hillegas wrote a nice article with some handy classes that I have used. The mongo_table class I have written uses the EnumeratorTableFunction class included in Rick's examples. The MongoDB DBCursor class is a Java Iterator, which makes it really nice to pass to the  EnumeratorTableFunction class, and let it do all of the work.

The LKM I constructed declares the table function to JavaDB/Derby, for example below, the function is declared based on the source datastore name (MONGO_EMPS) and columns (my source datastore has EMPNO,ENAME,SAL, note the table function will actually project types defined in ODI's datastore), the function has the MongoDB database name and collection name as parameters.

  1. create function MONGO_EMPS( dbName varchar( 330), collectionName varchar( 30))
  2. returns table
  3. (
  4.   EMPNO VARCHAR(20),
  5.   ENAME VARCHAR(30),
  6.   SAL NUMERIC(10),
  7. )
  8. language java
  9. parameter style DERBY_JDBC_RESULT_SET
  10. no sql
  11. external name 'mongo_table.readCollection'

Then the actual code to use the function as a source is executed from a source task (the target is SQL as I mentioned earlier for the LKM). Below you can see my execution using the test MongoDB and the myStuff collection;

  1. select
  4. MON.SAL   C6_SAL
  5. from table(MONGO_EMPS('test', 'myStuff' )) MON
  6. where (1=1)
  7. And (MON.SAL > 4000)

Note I can also perform some filtering as an example, here it is being done in JavaDB and in my case its in-memory. No setup, no persistence just on the fly Java. Ideally I would push the filter down to MongoDB rather than reading and filtering in the driver - more on that later.

I had defined my documents in MongoDB using the following basic commands in the mongo shell;

  1. use test
  2. a1 = { EMPNO: "1", ENAME : "Fred", SAL : 10000 }
  3. a2 = { EMPNO: "2", ENAME : "John", SAL : 2000 }
  4. db.myStuff.insert( a1 )
  5. db.myStuff.insert( a2 )

In ODI, I can simply then use the datastore representing the MongoDB collection of documents in an interface and map it to my target;

The physical design uses the LKM JavaTableFunction to SQL KM and sets the MongoDB databases, collection and the Java table function name.

That's it. Pretty straightforward and we are reading and consuming MongoDB documents. So what about complex document types like this? These are transported, more to come. The other point here is that this is a generic LKM that you can plug in other arbitrary table functions - so you can consume from any API, its very simple. For the LKM I created, I just defined 2 parameters (because my table function only had two), but really we need a better way to handle this and ensure they are ignored if not defined etc. That's all polishing tho, anyway fun stuff you can see all with a small piece of code leveraging JavaDB!


hi David,
i try to read from mongo by ODI in a linux system, the first steps in LKM( JavaTableFunction to SQL)are ok : Drop work table,Create work table,Drop Table Function,Define Table Function, but the step(Load data)
return this message:

oracle.odi.runtime.agent.exception.ExecutionEngineException: java.sql.SQLException: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.call(FastJDBCRecordSet.java:276)
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.call(FastJDBCRecordSet.java:37)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)

can you help me?


Posted by guest on January 28, 2015 at 02:53 AM PST #

Hi Davide

Can you provide the full stack trace?


Posted by David on January 28, 2015 at 08:17 AM PST #

Hi David,

this is the full stack trace:

ODI-1217: Errore della sessione Copia di Lettura_ugtest_mongo_Fisico_SESS (27) con codice restituito ODI-1298.
ODI-1226: Errore del passo Fisico_STEP dopo 1 tentativo/i.
ODI-1227: Errore del task SERIAL-MAP_MAIN- sulla connessione di origine <Valore vuoto>.
ODI-1298: Il task seriale "SERIAL-MAP_MAIN- (10)" non è riuscito a causa di un errore del task figlio "SERIAL-EU-MongoDb_UNIT (20)".
ODI-1298: Il task seriale "SERIAL-EU-MongoDb_UNIT (20)" non è riuscito a causa di un errore del task figlio "Load data-LKM JavaTableFunction to SQL- (70)".
ODI-1227: Errore del task Load data-LKM JavaTableFunction to SQL- sulla connessione di origine Derby-embedded.
Caused By: java.sql.SQLException: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:196)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:204)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:100)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:38)
at oracle.odi.runtime.agent.execution.Executor.handleDataMovementTask(Executor.java:309)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:51)
at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:189)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:111)
at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:864)
at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2024)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:562)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 30 more
Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
... 27 more
Caused by: java.lang.NullPointerException
at mongo_table.readCollection(mongo_table.java:36)
at org.apache.derby.exe.ac7bc640efx014bx30eex4e77x00000761a3000.g0(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
at org.apache.derby.impl.sql.execute.VTIResultSet.openCore(Unknown Source)
at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.

Posted by guest on January 30, 2015 at 01:58 AM PST #

You should check your mongo_table class at line 36 (the stack trace you provided it has the null pointer), see what code is executed on line 36, then figure out why it has a null pointer exception. You must have altered the code I provided slightly as line 36 does not have code. Once you have identified that, then you have to figure out why the call from ODI with the text generated in the task ends up produced the error. If you struggle you can send me line 36 +/- a few lines plus the task in ODI operator that invokes the function.

Caused by: java.lang.NullPointerException
at mongo_table.readCollection(mongo_table.java:36)


Posted by David on January 30, 2015 at 09:13 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed

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


« July 2016