Most aggregation operations in MongoDB are done by the Aggregation Framework, which provides better performance, but in some cases, it is necessary that it possesses flexibility that isn't present in it and is just possible with Map/Reduce commands.
Ivy Information Systems has contributed a plugin with two MongoDB steps—MongoDB Map/Reduce and MongoDB Lookup—under the AGPL license. These are available on GitHub at https://github.com/ivylabs/ivy-pdi-mongodb-steps.
To get ready for this recipe, you will need to start your ETL development environment Spoon, and make sure that you have the MongoDB server running with the data from the previous chapters.
Perform the following steps to create a quick sample for users with MongoDB Map/Reduce in PDI:
- Let's install the Ivy PDI MongoDB by performing the following steps:
- On the menu bar of Spoon, select Help and then Marketplace.
- A PDI Marketplace popup will show you the list of plugins available for installation. Search for
MongoDBin the Detected Plugins field. - Expand the Ivy PDI MongoDB Steps Plugin item. As you can see in the following screenshot:
- Click on the Install this plugin button.
- Next, click on the OK button in the alert for restarting Spoon.
- Restart Spoon.
- Let's make the same Map/Reduce transformation that was made in the first chapter with User Defined Java Class to prove how much easier it is:
- In Spoon, create a new transformation with the name
mongodb-map-reduce.ktr. - Under the Transformation properties and Parameters tab, create a new parameter with the CUSTOMER_NAME name.
- Select the Design tab in the left-hand-side view.
- From the Big Data category folder, find the MongoDB Map/Reduce Input step, and drag and drop it into the working area in the right-hand-side view.
- Double-click on the step to open the MongoDB Map/Reduce Input configuration dialog.
- Set Step Name to Get data.
- In the Configure connection tab, click on the Get DBs button and select the SteelWheels option for the Database field. Then, click on the Get collections button and select the Orders option for the Collection field.
- In the Map function tab, set this JavaScript map function:
function() { var category; if ( this.customer.name == '${CUSTOMER_NAME}' ) category = '${CUSTOMER_NAME}'; else category = 'Others'; emit(category, {totalPrice: this.totalPrice, count: 1}); } - In the Reduce function tab, set the following JavaScript reduce function:
function(key, values) { var n = { count: 0, totalPrice: 0}; for ( var i = 0; i < values.length; i++ ) { n.count += values[i].count; n.totalPrice += values[i].totalPrice; } return n; } - Then, in the Fields tab, click on the Get fields button, and you'll be able to get new fields there: _id, count, and totalPrice. Remove the _id field. The final configuration should look like this:
- Click on the OK button.
- From the Flow category folder, find the Dummy (do nothing) step, and drag and drop it into the working area in the right-hand-side view.
- Connect the Get data step to the Dummy (do nothing) step.
- Double-click on the step to open the Dummy (do nothing) configuration dialog.
- Set Step Name to OUT.
- Click on the OK button. The transformation should be similar to what is shown in the following screenshot, and you may be able to preview the execution transformation:
- In Spoon, create a new transformation with the name
Using this step for Map and Reduce is much easier than using the UJDC step, but the latter is much flexible in the way for processing data; however, users are prone to making mistakes.
The Map and Reduce functions in MongoDB are in JavaScript, and you can get more flexibility because the map function can create more than one key and value mapping or no mapping at all.
This recipe was a simple example based on the last recipe of the first chapter, but using this popular data processing paradigm, you can perform many complex queries as you like.