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.
1. https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-scripting-nar/1.4.0/org.apache.nifi.lookup.script.ScriptedLookupService/index.html 2. https://gist.github.com/jfrazee/ff9bcd859227d6939aca7faa9d54f2e2 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
