nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Uwe Geercken" <uwe.geerc...@web.de>
Subject Aw: Re: Nifi and Kafka Inserts and Updates
Date Sun, 15 Oct 2017 19:02:49 GMT
<html><head></head><body><div style="font-family: Verdana;font-size:
12.0px;"><div>
<div>Thank you Joey - I will have a look.</div>

<div>&nbsp;</div>

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

<div>&nbsp;</div>

<div>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&#39;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.</div>

<div>&nbsp;</div>

<div>Greetings,</div>

<div>&nbsp;</div>

<div>Uwe</div>

<div>&nbsp;
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px
solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b>&nbsp;Sonntag, 15. Oktober
2017 um 19:50 Uhr<br/>
<b>Von:</b>&nbsp;&quot;Joey Frazee&quot; &lt;joey.frazee@icloud.com&gt;<br/>
<b>An:</b>&nbsp;nifi &lt;users@nifi.apache.org&gt;, users@nifi.apache.org<br/>
<b>Betreff:</b>&nbsp;Re: Nifi and Kafka Inserts and Updates</div>

<div name="quoted-content">
<div style="font-size: 14.0px;">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&rsquo;t do multi-column
where&rsquo;s, but it would probably be sufficient to identify an existing row if there&rsquo;s
a natural key in the data somewhere.
<div>
<div>
<div>&nbsp;</div>

<div>1.&nbsp;<a href="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"
target="_blank">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</a></div>

<div>2.&nbsp;<a href="https://gist.github.com/jfrazee/ff9bcd859227d6939aca7faa9d54f2e2"
target="_blank">https://gist.github.com/jfrazee/ff9bcd859227d6939aca7faa9d54f2e2</a></div>
</div>
</div>
</div>

<div style="font-size: 14.0px;"><br/>
On Oct 13, 2017, 2:20 PM -0500, Uwe Geercken &lt;uwe.geercken@web.de&gt;, wrote:
<blockquote style="margin: 5.0px 5.0px;padding-left: 10.0px;border-left: thin solid rgb(26,188,156);">
<div style="font-family: Verdana;font-size: 12.0px;">
<div>Hello,</div>

<div>&nbsp;</div>

<div>I am looking for some advice: I have Nifi sending flowfiles to Kafka. As we know
for Kafka everything is an &quot;insert&quot;. Messages are inserted into the Kafka
log.</div>

<div>&nbsp;</div>

<div>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 &quot;upsert&quot; mode, but I don&#39;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?</div>

<div>&nbsp;</div>

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

<div>&nbsp;</div>

<div>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&#39;t think it is an unusual use
case, so I hope that somebody can help or share thought with me.</div>

<div>&nbsp;</div>

<div>Thanks,</div>

<div>&nbsp;</div>

<div>Uwe</div>
</div>
</blockquote>

<div>&nbsp;</div>
</div>
</div>
</div>
</div>
</div></div></body></html>

Mime
View raw message