Creating a JSON file

Oracle Data Integrator and JSON

    Oracle Data Integrator 12.1.3 adds the capability to read and write JSON files. Both of these work through the Complex File dataserver definition of ODI. There does exist one limitation to JSON capability in that the JSON data must have a single root. This is analogous to XML dataserver in that a recurring element cannot be used as the root element.
    In order to specify that the Complex File dataserver is meant to read and write JSON file, a new property has been added to Complex File technology viz. "translator_type" or "tt". For JSON reading and writing the value of this property must be set to "json". For other types of Complex Files do not specify this property at all.
    If the Complex File dataserver has associated with it the XSD generated by Native File Format wizard then there is no need to explicitly provide this property specifying the translator type. If you look into a generated XSD file, it will have an annotation nxsd:version=JSON. For other source formats, the wizard will generate XSD file with annotation nxsd:version=NXSD.
   One more item of interest is that the Complex File dataserver accpets all the commands and properties supported by XML driver. So you can - for intents and purposes - treat Complex File technology as XML technology.

Getting our feet wet

    Let us jump right in and see an example about reading (and writing) a JSON file. Shown below is the JSON that we are going to use as source.

JSON

{ "completed_in": 0.012, "max_id": 130, "max_id_str": "136536013832069120", "next_page": "?page=2&max_id=136536013832069120&q=twitterapi&rpp=1", "page": 1, "query": "twitterapi", "refresh_url": "?since_id=136536013832069120&q=twitterapi", "results": [ { "created_at": "Tue, 15 Nov 2011 20:08:17 +0000", "from_user": "fakekurrik", "from_user_id": 370, "from_user_id_str": "370773112", "from_user_name": "fakekurrik", "geo": null, "id": 136, "id_str": "136536013832069120", "iso_language_code": "en", "metadata": { "result_type": "recent" }, "profile_image_url": "http://a1.twimg.com/profile_images/1540298033/phatkicks_normal.jpg", "source": "<a href="http://twitter.com/">web</a>", "text": "@twitterapi, keep on keeping it real", "to_user": "twitterapi", "to_user_id": 62, "to_user_id_str": "6253282", "to_user_name": "Twitter API" } ], "results_per_page": 1, "since_id": 0, "since_id_str": "0" } The corresponding XSD file shall be As you can see it has the attribute nxsd:version="JSON". Also you can see that the XSD has a root element called "tweet". Do not be concerned. This is just an empty holder.     We shall be using the same XSD for defining source and target Complex File dataservers, but with different value for the "s=" parameter of the dataserver.     Let us define the source Complex File dataserver. See below the definition. You can see that the "tt" property is set. This is to clarify the nature of the dataserver. Now create Physical Schema, Logical Schema and a Model for this dataserver. See below the Model and datastores that will get created. Now create a target Complex File dataserver. As can be seen the "f=" parameter is not specified. So this dataserver will not have any content. It will only have a shape. Also note the different value given to "s=" parameter. Again create Physical Schema, Logical Schema and Model for this dataserver. Now that we have the Models created, let us create a Mapping. Create a Mapping with empty dataset. Then multi-select (Ctrl-click) all datastores from "twitter_simple" model. Darg-drop them into the default dataset. Re-arrange them to see all the datastores. Now multi-select all datastores from "twitter_simple_targ". Darg-drop them outside of the default dataset. Re-arrange them. Create mapping from the dataset to each of the three target datastores. Choose to do auto-mapping in each case. You will end with the following. Execute this mapping once to verify that there are no errors. No file will be created, so do not look for it. We are just populating data into the target via this Mapping. Next step is to actually write out the data into a JSON file. For this we will use a Procedure and the 'CREATE FILE' XML driver command. The JSON file is being created from the schema of the target Complex File dataserver. Since we want this Procedure to be executed *after* the Mapping, but in the same Session so that we will get the data, we shall put the Mapping and Procedure into a Package. Execute this Package. The contents of our initial JSON file will get written out to the new JSON file. You will notice that the psuedo-root node "tweet" is absent in the result. This is a basic example of reading (and writing) JSON file using Oracle Data Integrator. More complex flows can be created and the source/target of data can be changed. The main take-aways should be about the pseudo-root and the fact that Complex File technology works the same way as XML technology and accepts the same properties and commands. Note: In the mapping shown  above, all the target tables are in the same mapping. This depends on the Parallel Target Table load feature of ODI and has all its limitations. Please verify that your target tables are indeed capable of being loaded in parallel. Otherwise you will need to create one mapping per XML hierarchy level.

Using ODI to create a Complex File

Reading, writing and .............

It is trivial to configure a Complex File dataserver, reverse engineer it and then read data from the datastores. It is a little cumbersome, but no black magic to do the reverse of it. But there seems to be an impression that there is some arcane process involved in writing out a new Complex File. In this post we shall examine the steps needed to write out some data to a Complex File.

First of all let us configure a Complex File dataserver.

As you may see there is nothing fancy. Just to make things a little more interesting I have added the XML driver property 'ldoc=false' (load on connect = false). This gives me control over when data is actually loaded. Now I test the connection and reverse engineer. This leaves me with the model seen below.

Since I want to explicitly load data into this model I need to create an ODI Procedure. Create a new Procedure and add a Task. All that the Task does is load data from the file that is specified in the JDBC URL into the datastores.

In the above screenshot you can see the configuration of the Task. The Technology is set to 'Complex File', the Logical Schema is set to that of the source dataserver that we have set up. The command to execute is the XML driver's SYNCHRONIZE command.

Only Target Command has content. Source Command section is empty for this Task.

Now we are ready for a target. Use the same XSD as the one for the source dataserver and create a new Complex File dataserver. Be sure to choose a different value for the 's=' property in the JDBC URL of this dataserver. Also see that there is no 'f=' property. We only want the bare datastores for the target. Data in it is going to come from the source.

As before, reverse engineer to create a model and datastores. This will result in a structure equivalent to the one for the first dataserver. Note that we are doing it this way just for our convenience. Nothing stops you from using totally different XSDs for the source and target.

Now comes the part of actually populating the target with data. For this create 4 mappings. Each mapping will take care of moving data from one of the source datastores to the corresponding target datastore. Let us look at the first mapping, an in-between mapping and the last mapping.

First mapping:

As you may see, it is very simple. Just the root element from source is mapped to root element of target. I have chosen to use an Oracle dataserver as my staging area, but this is irrelevant.

The LKM and IKM are the default ones - LKM SQL to SQL (Built-in).GLOBAL and IKM SQL Incremental update - with FLOW_CONTROL and TRUNCATE turned off.

In-between mapping:

This is the mapping between CUSTOMERCHOICE datastores of source and target. Again staging area is the Oracle dataserver. KMs are as for the ones in the first mapping.

Last mapping:

The last mapping is the one with ITEMCHOICE.

Now we have moved data from source datastores into target datastores. What we need to do next is to push this data out into a complex file. In order to do this we need another ODI Procedure. This time the Procedure Task will use XML driver's CREATE FILE command.

Here is the Procedure Task. As before the Technology is set to 'Complex File' and Logical Schema is set to the target dataserver's logical schema. The command is 'CREATE FILE FROM SCHEMA "COM02" where 'COM02' is the value of the 's=' property set on the JDBC URL of the target dataserver.

Next step is to assemble all these Mappings and Procedures into a control flow. For this we use an ODI Package.

Execute this Package and you will see the data that you loaded into the target datastores written out into a complex file that follows the structure that is defined by the XSD associated with the target dataserver. That is it.

What if .....

One error you might encounter when trying to write out the data is 'Start of root element expected'. This error means that the XML data being written out does not conform to the XSD file that you have used for configuring the Complex File data server. There is no easy way to debug this. But here is something you can try.

Use the XSD file that has been used in the Complex File dataserver to set up an XML dataserver. Create exactly same Interfaces/Mappings and Procedure as you have used for the Complex File creation, but this time using the XML datastores as the targets. Now write out the data and examine it. If the problem is in the root element you can spot it immediately. Otherwise you can use the Native File Format command line to test the generated XML against the XSD. Follow the instructions in this blog post.

What is new for ODI XML JDBC driver in Oracle Fusion Middleware 11g R1 (11.1.1.7.0)

ODI XML driver issues

ODI XML driver had several issues such as

1. would not support elements with same name but different types. For example a local element with same namespace URI and local name as a global variable would not be handled.
2. does not support list/union simpleTypes, any, anyType, mixed type and so on.

In ODI 11.1.1.7.0 a new compatibility mode parameter has been added. This is an XML driver property called 'compat_mode'. Its default value is 'v2'. But if you specify the value 'v3' for this parameter, you can avoid issue (1). As you might imagine, if you use 'v3' for the value of this parameter, you will run across some changes in the behaviour of the driver.

Differences between 'v2' and 'v3' modes

1. Handling of elements with same local name and resolved type. In current XML driver, data from such elements end up stored in the same table, but the rows have different FK references. With default mode, we will use different tables to store the elements. This has implication for elements that are 'ref's of a global element. Each of the reference (and descendants) gets its own table.
2. Determining the root element. The XML JDBC driver allows users to specify the local name of the root element, and not a qualified name. One plain assumption here is that the root element has a unique local name. In 'v2' mode, if a root element name is not specified then the first element encountered by the parser is set as root element. This is the first element from the XSD hierarchy where the XSD does not have an 'include' or 'import'. In 'v3' mode this is indeterminate. The XSD should either have only one global element or user must specify the name of the element to be used as root. Failing this, one of the global elements from one of the target namespaces is used as root.
3. As a corollary to (2), only a global element is allowed to be used as root element. Old parser would allow use of even local elements. This is carry-over from DTD times where all elements are global elements. If user specifies a local element name (which also is not the same a global element name) then an exception is raised. Note that for the old parser, if the user had provided an XSD, then the restriction applies that root element must be a global element.
4. When using a DTD/specifying XML driver URL parameter to generate DTD, user must provide root element name. The new XML driver converts the DTD to XSD in-memory and uses this XSD to construct the data model. This conversion requires that a root element be specified. However, DTDs that are for XML structures that need to use namespaces must continue to use 'v2' mode.

Note that since JMS XML Queue/Topic drivers and Complex File driver uses XML driver internally, these drivers will also accept 'compat_mode' parameter. 11g JMS XML driver though, will not accept this parameter. It only supports a subset of XML driver parameters. Starting from 12c this is not the case

Complex File technology and pre Java 7 bug

Problem statement

How many times have you tried to use a user-defined file structure using Native Format and registered it with ODI Topology as a Complex File Data server - only to have it fail when testing the connection with an error saying :

ORABPEL-11100

Translation Failure.

[Offset=7] Translation from native failed. For input string: "...............".

The incoming data does not conform to the NXSD schema. Please correct the problem.

Well, it could be problem with the input data, but if you are absolutely sure that your data is conformant with the XSD you have created (btw you can use commandline to check this : see here) then there is one thing you can look for : in the error message does the reported string start with "+" and is the datatype for this element as defined in the XSD xsd:long?

If you can say yes to all of this then congratulations! You have been bitten by a Java bug tht has been fixed in Java 7 onwards. The bug is in Java's Long.valueOf where the code only checks for "-" and not "+". So when it finds "+" in there it throws a NumberFormatException. This exception however gets swallowed up and the impression that is created is that it is the fault of the data in the file.

Solution

If you are using ODI 11.1.1.5.0, tough luck! With ODI 11.1.1.6.x, you will need to set up a standalone or JEE agent. These agents must run using Java 7. For standalone agent edit /oracledi/agent/bin/odiparams.sh/bat where ODI_JAVA_HOME will point to your Java location. For JEE agent when you are configuring the domain choose a Java 7 installation. Then for 'test connection' use this agent. For reverse engineering choose 'Customized' rather than 'Standard' on the reverse engineering finger tab and also choose this agent. All ODI Sessions that use this data server must execute on this agent.