Nifi: MERGE (or UPSERT) for MS SQL Server

[#1]

I am using MS SQL Server as my data store.

The PutSQL processor is great if we are clear about the kind of DB operation we want to perform ie. INSERT or UPDATE.

But what if we want to do something like UPSERT or MERGE operation?

eg. https://myadventuresincoding.wordpress.com/2016/01/05/sql-server-how-to-write-an-upsert-using-merge/

I can't find any suitable set of processors that can achieve this.

Anyone done this before?

[#2 · Accepted solution]

Hi Mud,

well this is not to simple. The reason for this is that MS SQL does not support UPSERT ootb. If it would, then you could simple create an INSERT Statement in NIFI and replace the INSERT with UPSERT using the ReplaceText processor. (This approach is used when interacting with Apache Phoenix that supports the UPSERT SQL verb).

However there are two options you have.

First you could work with Triggers and simply catch your insert to check if it should be changed into an update. This will cost performance on DB side and it really depends on a) number of inserts and b) who else is interacting with your table.

The second option would be to use the ExecuteProcess / Execute Script processor to invoke a shell or groovy script that will do the UPSERT for you. This approach will also cost some performance / additional I/O, but will do the job.

This also allows you to either do the magic on DB layer (e.g. with an stored procedure) or in the script itself.

Here is an example of a stored procedure approach (http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx).

There is also a entry here in the community where calling an stored procedure is explained: https://community.hortonworks.com/questions/26170/does-executesql-processor-allow-to-execute-stored....

HTH.

PS: Same approach for using MERGE, even much simpler since MERGE is completely MS SQL Server based - you will first have to populate a source table in MS SQL Server and then invoke the MERGE script ....

[#3]

Thanks Peter 🙂

I will probably go with the ExecuteProcess processor to invoke a script to invoke the SQL Server MERGE command

[#4]

In my case to prevent appending duplicate data I firstly send flowfile to DELETE in SQL and then to INSERT

So firstly Nifi deletes all rows in SQL table which are matched with rows in my flowfile and then inserts all rows from flowfile 

[#5]

This is what actually did the job for us. Thanks!