postgresqlvariablesapache-nififlowfile

Add two columns together using apache-nifi

I have following scenario. I one database table I have :

| id | name | basic_salary | allowance |
_______________________________________|
| 1  | sach | 2000         | 1000      |
| 2  | nala | 5000         | 2500      |
|______________________________________|

add basic_salary and allowance togther and making it as net_salary and insert to a new table called net_salary

For first step, I used ExecuteSQLRecord processor and can get all the records. But problem is : How to add those two columns coming in flowfile.

So final result should be :

| id | name | net_salary |
|________________________|
| 1  | sach | 3000       |
| 2  | nala | 7500       |

This is directly related to my previous variable question. How to handle flowfile variables to perform operations in apache-nifi?

I used How to compute the sum of multiple columns in PostgreSQL inside ExecuteSQL processor, but it can not understand flowfile variables.

Solution

  • There is a tricky one. Use UpdateRecord twice.

    The first one is

    Record Reader               CSVReader
Record Writer               CSVRecordSetWriter
Replacement Value Strategy  Record Path Value
/net_salary                 concat(/basic_salary, ',', /allowance)

    and the second one is

    Record Reader               CSVReader
Record Writer               CSVRecordSetWriter
Replacement Value Strategy  Literal Value
/net_salary                 ${field.value:substringBefore(','):toNumber():plus(${field.value:substringAfter(','):toNumber()})}

    where it gives the result as follows.

    id,name,basic_salary,allowance,net_salary
1,sach,2000,1000,3000
2,nala,5000,2500,7500