You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by KhajaAsmath Mohammed <md...@gmail.com> on 2021/03/11 20:18:18 UTC

Upsert logic with SQLServer - NIFI Putdatabase

Hi,

I am looking for some help on how to deal with upserts/updates on SQL
Server with NIFI.

I get a flow file where the records are updated. Primary key are already
defined on the table.

I don't want to try the logic of delete and insert, is there a way to
handle upsert automatically with this approach? This can be present for any
table and should be dynamic.

Thanks,
Asmath

Re: Upsert logic with SQLServer - NIFI Putdatabase

Posted by KhajaAsmath Mohammed <md...@gmail.com>.
Hi Matt,

I tried your suggestion on updating the records using putddatabase. It
works for small dataset but not for huge dataset. Any suggestions?

[image: image.png]

[image: image.png]

[image: image.png]

Thanks,
Asmath

On Thu, Mar 11, 2021 at 4:10 PM Matt Burgess <ma...@apache.org> wrote:

> Asmath,
>
> Upsert in SQL Server (without NiFi) can be difficult, and even
> error-prone if concurrency is needed [1]. I suspect that's why it
> hasn't been attempted in PutDatabaseRecord (well, via the MSSQL
> adapter(s)) as of yet. I haven't tried it without creating a procedure
> so I'm not sure if the MSSQL database adapter can create a standalone
> statement for MERGE or "try INSERT catch(error) then UPDATE".
>
> Other DBs such as MySQL and PostgreSQL have easier ways to achieve
> this so NiFi has been able to support Upsert for those DBs.
>
> Rather than deleting the row, maybe you could catch any INSERT errors
> and route them back to the processor using UPDATE instead?
>
> Regards,
> Matt
>
> [1]
> https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/
>
> On Thu, Mar 11, 2021 at 3:18 PM KhajaAsmath Mohammed
> <md...@gmail.com> wrote:
> >
> > Hi,
> >
> > I am looking for some help on how to deal with upserts/updates on SQL
> Server with NIFI.
> >
> > I get a flow file where the records are updated. Primary key are already
> defined on the table.
> >
> > I don't want to try the logic of delete and insert, is there a way to
> handle upsert automatically with this approach? This can be present for any
> table and should be dynamic.
> >
> > Thanks,
> > Asmath
>

Re: Upsert logic with SQLServer - NIFI Putdatabase

Posted by Matt Burgess <ma...@apache.org>.
Asmath,

Upsert in SQL Server (without NiFi) can be difficult, and even
error-prone if concurrency is needed [1]. I suspect that's why it
hasn't been attempted in PutDatabaseRecord (well, via the MSSQL
adapter(s)) as of yet. I haven't tried it without creating a procedure
so I'm not sure if the MSSQL database adapter can create a standalone
statement for MERGE or "try INSERT catch(error) then UPDATE".

Other DBs such as MySQL and PostgreSQL have easier ways to achieve
this so NiFi has been able to support Upsert for those DBs.

Rather than deleting the row, maybe you could catch any INSERT errors
and route them back to the processor using UPDATE instead?

Regards,
Matt

[1] https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

On Thu, Mar 11, 2021 at 3:18 PM KhajaAsmath Mohammed
<md...@gmail.com> wrote:
>
> Hi,
>
> I am looking for some help on how to deal with upserts/updates on SQL Server with NIFI.
>
> I get a flow file where the records are updated. Primary key are already defined on the table.
>
> I don't want to try the logic of delete and insert, is there a way to handle upsert automatically with this approach? This can be present for any table and should be dynamic.
>
> Thanks,
> Asmath