The last couple of days I was playing around with flowgraphs to get to know SDI and wanted to perform a delete using a map operation node in the data provisioning palette. Since I had some trouble creating a succesful flow, I thought I’d make a little post, it might save some people the trouble I had to go through 🙂
**note: for those that know data services, it is in fact almost the exact same flow.
1. Create Schema and table to test with
2. Add some data
3. Create a flowgraph
Right click on the package > choose new > search for flowgraph.
It is important to create it as a Task plan. If you do not, the data provisioning palette will not show up.
4. Create following flow
The first node is a simple data source. I used a table with some dummy data with an ID and a DESCRIPTION (ID: 1, DESC: “one” and so on). The last node is a data sink. It is important to use a table with the same columns as the data source, else it will not work. It is also important that the source and sink have a primary key or HANA will throw an error.
The second node is a filter node. In this node you filter the data source for the rows you want to delete in the data sink. Go to the general tab in the properties and fill in the filter expression as you see fit. In this example I wanted to delete the row with an ID of 5, so I put a filter on my data source in such a way that the filter returns exactly that line to push it to the data sink.
Now between the filter and the data sink, you have to place a map operation node. This node allows you to map certain database operations to another kind of database operation. The way a filter pushes the output to the data sink is through an opcode of “normal”. So in the map operations’ node properties, you have to map the “normal” operations to “delete” operations (Discard just ignores that specific operation, so we need delete here) like so:
There is no need for a SQL expression in the Mapping tab.
Before you save, be sure to check you filled in the Target Schema of the surrounding container (in my case the “DELETE_MAP_OPERATION”).
5. Save, activate and run.
If you check the target table, the corresponding rows you filtered on will have been deleted.