Thank you Joey - I will have a look.
I was also thinking of a different solution. The database server is probably the best place to detect updates, inserts and deletes. In MySql for example it is possible to add a trigger that is fired when an delete happens. This could be captured into a different table with the unique key and a timestamp. This way deletes could be propagated to other systems.
I will futher work on this and see if I find a reliable and easy way to do this. Actually many tools or systems have nice concepts for handling updates or deletes but most of them don't have one for deletes. I find that rather strange. This could be solved by marking records as deleted instead of physically deleting them. But then, this is not always possible in source systems.
Greetings,
Uwe
Gesendet: Sonntag, 15. Oktober 2017 um 19:50 Uhr
Von: "Joey Frazee" <[email protected]>
An: nifi <[email protected]>, [email protected]
Betreff: Re: Nifi and Kafka Inserts and Updates
Von: "Joey Frazee" <[email protected]>
An: nifi <[email protected]>, [email protected]
Betreff: Re: Nifi and Kafka Inserts and Updates
Uwe, on the issue on a LookupService for a database, you could use the ScriptedLookupService [1] and the usual DBCP to do this or maybe check out the following, somewhat limited, DatabaseLookupService [2]. It builds on Apache Commons Config so it forces you to treat the table a bit like a key-value store and can’t do multi-column where’s, but it would probably be sufficient to identify an existing row if there’s a natural key in the data somewhere.
On Oct 13, 2017, 2:20 PM -0500, Uwe Geercken <[email protected]>, wrote:
Hello,I am looking for some advice: I have Nifi sending flowfiles to Kafka. As we know for Kafka everything is an "insert". Messages are inserted into the Kafka log.Now I wonder what is the best way to insert OR update a relational database table from Kafka messages using Nifi. What is the best way to determine if the record (data) I get from Kafka needs to be updated or inserted in the relational db table? Ok. MySQL e.g. has an "upsert" mode, but I don't see we have a processor for that. So I would need to generate the SQL statement and execute that. But is there a more elegant (straight ahead) way?I could use MongoDB as well where we have a processor that allows for an upsert, but I wanted to know how to solve this with a relational db.My idea was to use a db lookup service controller - to lookup if a certain key exists - but there is none from what I have seen. I don't think it is an unusual use case, so I hope that somebody can help or share thought with me.Thanks,Uwe
