Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to perform UPSERT in Oracle DB using Apache NiFi?

avatar

Folks,

Trying to insert/update csv data in oracle db table.

consider csv file having ID,NAME,AGE,CITY and ID as Primary Key in a table

Tried below approach

1. GetFile

2.PutDatabaseRecord :- To first insert data (thinking that since unique key constraint violation will cause failure for few records)

3.PutDatabaseRecord :- Another one to update the failed ones from above.

But, its seen that the data with new record is not inserted.

Basically, I want to perform UPSERT.

Thanks in Advance 🙂

10 REPLIES 10

avatar
New Contributor

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)