Oracle

Including JSON in ODI 12c

by Yuli Vasiliev

Including JSON in ODI 12c


By Yuli Vasiliev

ODI 12c comes with JSON support, meaning you can now use JSON as a data source. Unfortunately, the documentation does not offer a comprehensive example on how to do it. This article explains in detail how you can take advantage of this functionality, using as an example the PurchaseOrders.dmp coming with Oracle Database 12c as a sample. This file contains 10000 sample purchase order documents in JSON format. You’ll see how to make those documents available in ODI 12c, with the ability to convert them into any other supported format.

Let’s outline what you’ll need to do to get a JSON data file into ODI 12c. Below are the general guideline steps:

  1. Define a source topology physical architecture using the Complex File technology. In this step, you define a Native Schema (nXSD) file, among other things.
  2. Define a source topology logical architecture, binding it with the physical schema defined in the previous step.
  3. Define a source designer model, binding it with the logical schema. Within this model, you create a data store associated with the JSON data source.

Looks simple, doesn’t it? However, you’ll need to do some tricks to get this job done. As you’ll learn through the rest of the article, creating the physical architecture is the trickiest part here.

So, you start with creating the physical architecture. The steps below provide the details on how to do it:

  1. Launch ODI Studio, and connect to the repository.
  2. On the Topology tab, expand the Physical Architecture node, then expand Technologies.
  3. Right-click Complex File, and select New Data Server.
  4. On the Definition tab, enter the name for the data server being created; for example: comp_file1.
  5. On the JDBC tab, click the magnifying glass button on the right of the JDBC driver field and select oracle.odi.jdbc.driver.file.complex.ComplexFileDriver.
  6. Next, click the Edit nXSD… button to launch the Native Format Builder.
  7. Leave for a while ODI Studio, and find the PurchaseOrders.dmp file on your computer. If you are an Oracle Database 12c user, this file can be found in the following directory: $ORACLE_HOME/demo/schema/order_entry. Open this file in a text editor and copy a single PO document, say, PONumber 1, to the clipboard. Then, create a new blank file, say, OnePurchaseOrder.dmp, and insert the copied PO there, so that you have a file with a single PO document from the PurchaseOrders.dmp JSON dump file. Edit the document, removing the gap in the Special Instructions attribute name to avoid problems when generating a nXSD file. Make the same replacement in the PurchaseOrders.dmp original file (Don’t worry about the number of documents in the file, your editor can do this job for you automatically).
  8. Turn back to the Native Format Builder launched in ODI Studio, on the File Name and Directory builder screen, enter a file name for the nXSD being created, say, nxsd_json.xsd, and select a directory to save it in.
  9. On the Choose Type screen of the builder, choose JSON Interchange Format.
  10. On the JSON File Description screen of the builder, select OnePurchaseOrder.dmp file (created in step 7 earlier) for the File name field. On this same builder screen, edit the Root element field , changing it to POs.
  11. On the Generated Native Format Schema File screen, you should see the native format file generated by the builder and available for making manual changes. Look through this xsd document and find the following line: xsd:element name="UPCCode" type="xsd:integer, in which change the type to long as follows: xsd:element name="UPCCode" type="xsd:long. This is illustrated in the screenshot below:

  1. After completing the builder, you should be on the JDBC tab of the Data Server panel again. Here, enter the following line in the JDBC URL line: jdbc:snps:complexfile?f=/home/oracle/PurchaseOrders.dmp&d=/home/oracle/nxsd_json.xsd&re=POs. Of course, the actual paths may be different. Note, however, that the f parameter refers to the PurchaseOrders.dmp file (the original one) rather than OnePurchaseOrder.dmp with a single document, which you created to build a nXSD file with the builder.
  1. 13.On the JDBC tab of the Data Server panel, go down to the Properties box, and find dp_numeric_lenght. Turn off Use Default for this property, and manually set it to 14, as illustrated in the screenshot below:

  1. In ODI Studio, select File->Save to save the newly created data server.

Now that you have the data server created, what is left is to create: a physical schema, a logical schema and a model. This is a fairly trivial process, so I won’t cover it here.

Finally, to make sure that everything works as expected, expand Designer ->Models -> YourModelName, and right-click the data store. You should see the PurchaseOrders.dmp JSON data in a table format, as shown in the screenshot below:

You can scroll down till to the last, 10000’s JSON document in the file.

Conclusion

The article walked you through the steps needed to get a JSON data file into ODI 12c, so that you could then convert the data into any other format supported by ODI 12c.

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.