6.06.2014

Dealing with Nested Documents in MongoDB and Talend (aka Baking a Cake)

Abstract:

MongoDB is a very popular document database that gives you a huge amount of flexibility when it comes to storing data. On the other hand, the traditional relational model is far less flexible in how data is stored - you're limited to columns and rows. Sometimes you want to go from a flexible model like MongoDB to a relational one, that's what this post attempts to explain using Talend/JaspersoftETL (ETL tools).

I do not want to get into the relational vs non-relational model argument in this post - it's only an example if you need to do this for some reason...I'm pro choice :)

Scenario:

We have a JSON document in MongoDB that looks like this:
 {  
      "id": "0001",  
      "type": "donut",  
      "name": "Cake",  
      "ppu": 0.55,  
      "batters":  
           {  
                "batter":  
                     [  
                          { "id": "1001", "type": "Regular" },  
                          { "id": "1002", "type": "Chocolate" },  
                          { "id": "1003", "type": "Blueberry" },  
                          { "id": "1004", "type": "Devil's Food" }  
                     ]  
           },  
      "topping":  
           [  
                { "id": "5001", "type": "None" },  
                { "id": "5002", "type": "Glazed" },  
                { "id": "5005", "type": "Sugar" },  
                { "id": "5007", "type": "Powdered Sugar" },  
                { "id": "5006", "type": "Chocolate with Sprinkles" },  
                { "id": "5003", "type": "Chocolate" },  
                { "id": "5004", "type": "Maple" }  
           ]  
 }  
We want to put the document into three different tables,  for use in a relational database:

Main Table:

ID TYPE NAME PPU
0001 donut cake 0.55

Batter Table:

BATTERID BATTERTYPE CAKEID
1001 regular 0001
1002 chocolate 0001
1003 blueberry 0001
1004 devil's food 0001

Topping Table:

TOPPINGID TOPPINGTYPE CAKEID
5001 None 0001
5002 Glazed 0001
5005 Sugar 0001
5007 Powdered Sugar 0001
5006 Chocolate with Sprinkles 0001
5003 Chocolate 0001
5004 Maple 0001

Talend tMongoDBInput Component

The tMongoDBInput component is what we'll use to extract the data from MongoDB and input into Talend. This is fairly straightforward, you can configure the connection and then you will have to map some fields. We map id, type, name, ppu as regular fields. The batter and topping fields are somewhat special. 

Toppings:
The toppings field will be mapped a string and will contain an object in each row that we'll need to deal with later

Batters:
Batters is a nested document within another nested document and we'll need to define a parent node path. tMongoDBInput does not allow you to use node notation to select fields (like batters.batter) so you'll define a parent node like this:
In the end the schema for tMongoDBInput will look like this:

tMap Component - Splitting

Now we'll use a tMap component to split the outputs into three (main, topping, batter)



tExtractJSONFields - Expanding Documents
So now we have to deal with the special fields, batter and topping - each one of them contains a list that we want to denormalize into it's own rows. We can use the tExtractJSONFields component for this. The component has a couple of key things: schema, Loop XPath Query and XPath query. This determines what fields to map.

Schema:

Then set the Xpath queries:

And finally glue it all together:
Note that we have 3 input rows, then for each batter and topping we are extracting.

If you are interested in this example, you can download it here:

  • Data: This is a JSON object you can import with mongoimport (like  mongoimport --jsonArray --collection nestedSample < doc.json)
  • JaspersoftETL Job: This job works with JaspersoftETL 5.4.1 Big Data Edition (or presumably the Open Source Big Data edition of Talend)

4.04.2014

Deleting Old Scheduled Report Ouptuts Using the Jaspersoft REST API

I get this request quite a bit - how do I clean up old artifacts from the Jaspersoft repository? Old artifacts are typically reports that are scheduled and then saved into the repository. I explain this in a short video:



Here are some specifics about the Talend/Jaspersoft ETL job used:
  • tHttpRequest - Does a GET to the URI "http://localhost:8080/jasperserver-pro/rest_v2/resources?recursive=true&type=file&folderUri=/public/ScheduledReports" and writes it to an XML file. API reference- see "Searching the Repository"
  • tFileInputXML - Reads the XML response from file (written by tHttpRequest)
  • tFilterRow - Filters the XML file on the field "creationDate" with the value: TalendDate.parseDate("yyyy-MM-dd", "2014-03-04")
  • tFlowToIterate - takes each request and iterates through them
  • tREST - For each file (URI) we run the DELETE command: "http://localhost:8080/jasperserver-pro/rest_v2/resources" + ((String)globalMap.get("row2.uri")) API reference- see "Deleting Resources"
You can find my example job here - it is for Talend 5.4.1