Hi I am new to nifi so below approach can be pretty noob but it worked for me.
My use case was as below:
1) Source of Data: CSV file
2) Destination: Oracle DB
3) Acceptance Criteria: If a record in CSV is matching with a record in a table then update the corresponding fields otherwise insert it as a new record on unmatched condition.
* Processors I used as follows:
1) GenerateFlowFile: In order to test this I have prepared subset of csv and added in Custom Text property.
2) LookupRecord: This Processor will use GenerateFlowFile contents as an input,Each record in csv will get looked up in a table (This is based on primery/unique at both data sets or we can use multiple columns to form a unique row to identify). We need to choose routing strategy as
"Route to 'matched' or 'unmatched'". if matched then its an update and if not matched then its an insert.
3)PutDatabaseRecord: this will be used to two times each for insert and update. Remember output of LookupRecord will be in CSV format (same as input to it), Suppose we have 100 records as flow file content input (CSV) to LookupRecord and 15 records got matched then matched (update) will have 15 records in csv and unmatched(insert) will have 85 records in csv, then we can use respective csv outputs as an input to PutDatabaseRecord processor to take care of further insert/update.
* Important Controller Service Used*
1) CSV Reader: This is used to read contents of CSV (Will be used by LookupRecord and PutDatabaseRecord processors)
2) CSVRecordSetWriter: This is used to write CSV. (Will be used by LookupRecord processor)
3) DatabaseRecordLookup: This is used for lookup on target table based on unique/primary Key (Will be used by LookupRecord processor)
4) DBCPConnectionPoolOracle: Oracle Connection service (Will be used by DatabaseRecordLookup service and PutDatabaseRecord processor)
