In this recipe, we will cover the functionality of the MongoDB Delete step. This step was developed by Maas Dianto and is open source under the Apache License version 2.0. It is available on GitHub at https://github.com/maasdi/pentaho-mongodb-delete-plugin.
As the name suggests, this step deletes documents from a collection based on conditions defined by the user.
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.
Let's install and use the MongoDB Delete step in a small example by following the next steps:
- Now let's install the MongoDB Delete step:
- 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 MongoDB in the Detected Plugins field.
- Expand the MongoDB Delete 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 delete the order of the Baane Mini Imports customer with priceEach more than or equal to 100:
- Using the MongoDB shell, check how many documents exist. Upon running the following query, you should get 20 as the result:
db.Orders.find({"priceEach":{$gte:100},"customer.name":"Baane Mini Imports"}).count() - In Spoon, create a new transformation with the name
delete-mongodb-documents.ktr. - Select the Design tab in the left-hand-side view.
- From the Input category folder, find the Generate Rows step and drag and drop it into the working area in the right-hand side view.
- Double-click on the step to open the Generate Rows configuration dialog.
- Set Step Name to Get Values.
- Set the Limit field to 1.
- In the Fields table, add the
customerNamefield as aStringtype with the valueBaane Mini Imports. In a new row, add thepriceEachfield as a Number type with the value 100. - In the Big Data category folder, find the MongoDB Delete step and drag and drop it into the working area in the right-hand-side view.
- Connect the Get Values step to the MongoDB Delete step.
- Double-click on the step to open the MongoDB Delete configuration dialog.
- Select the Delete options tab, click on the Get Dbs button, and select SteelWheels from the Database field. Then, click on the Get collections button and select Orders from the Collection field.
- Select the Delete Query tab. In the Mongo document path field, add the priceEach and customer.name fields. The Comparator field for priceEach is >=, and for customer.name, it is =. In incoming field 1, set priceEach and customerName, as you can see in this screenshot:
- Finally, run the transformation with a structure like what is shown in the following screenshot:
- If you run the same query that was executed before, you should get 0 as the result.
- Using the MongoDB shell, check how many documents exist. Upon running the following query, you should get 20 as the result:
In this recipe, using the MongoDB Delete step, we delete from the SteelWheels database all the documents in the Orders collection that have the Baane Mini Imports customer name and whose priceEach value is more than or equal to 100. We use the Generate Rows step just to create one row, for testing purposes.
However, PDI gives you the flexibility to read data from different data sources and then apply the rules that you need. For example, you can read customer names from a hypersonic database and then delete them from a MongoDB database. This is a good exercise for you to try.