Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Nifi: MERGE (or UPSERT) for MS SQL Server

Solved Go to solution

Nifi: MERGE (or UPSERT) for MS SQL Server

New Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Nifi: MERGE (or UPSERT) for MS SQL Server

Contributor

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 ....

2 REPLIES 2
Highlighted

Re: Nifi: MERGE (or UPSERT) for MS SQL Server

Contributor

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 ....

Re: Nifi: MERGE (or UPSERT) for MS SQL Server

New Contributor

Thanks Peter :)

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

Don't have an account?
Coming from Hortonworks? Activate your account here