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,

Did you find a solution for this issue?

avatar

This is not a straightforward implementation. Though there are workarounds available. Have a look at this article on the community which talks about using a Stored Procedure to do the stuff. This may cost you some performance but will do the needful.

avatar
New Contributor

Hi,

How about below workaround ?

For each record do a SQL select query using ExecuteSQL.

Use flowfile attribute executesql.row.count which gives the number of records found.

If there are no records means we need to insert.

And if count is greater than 0, its an update.

Have to play with RouteOnAttributes.

Have not tried but seems a good use case.

Will post an update once I try the same.

@RANKESH NATH. @Rahul Soni. @Caio Boratto :- Do comment if anyone have tried or succeeded with this use case.

avatar
New Contributor

Folks,

Considering I understood the use case well 🙂

Tried at my end.Attaching screenshot

1. GetFile gets CSV file

2. SplitText splits lines with Header

3. RouteOnAttribute skips Header and just passes data to be inserted and not header

4. ExtractText extracts columns from each line considering it is comma separated

5. ExecuteSQL checks if record already present

6. RouteOnAttribute differentiates between INSERT and UPDATE records

7. 2 ReplaceText either creates INSERT statement or UPDATE statement

8. PutSQL to execute statement received from ReplaceText

upsert-csv-to-sql.png

avatar
New Contributor

Hi @Miten Asar could you please share the .xml template that you created to solve this problem?
Thanks in advance.

avatar
Expert Contributor

yes , I am late to join the party.. but can someone share the .xml please ??

@miten4u @garygavigan  for the upsert in NIFI

avatar
New Contributor

Hi @Miten Asar could you please share the .xml template that you created to solve this problem?
Thanks in advance.

avatar
Master Guru

In Oracle an UPSERT is done by a MERGE, so alternatively you could store your data in a new temporary table and then run ExecuteSQL/PutSQL with a MERGE command to merge from the temp table into the target table.

avatar
New Contributor

Yes this is best approach, Inserting all in staging table and then using merge to insert/update on target table, Just need to truncate staging table for next round of inserts.