question Re: Nifi CSV rows processing to sql server - Check primary key in Support Questions

Nifi CSV rows processing to sql server - Check primary key

joaoluistr — Thu, 17 Dec 2020 19:31:13 GMT

Hello Community!

Hope you're all doing fine.

I'm beggining my Nifi studying and i'de like to replicate a process that i have running in other plataforms.

My goal is to read a CSV and process it row by row to insert into a sql server table.

So far so good, i was able to do this with the following:

GetFile -> SplitRecord -> ConvertRecord (to json) -> ConvertJSONtoSQL -> UpdateAttribute (date conversion) -> PutSQL.

But i need to validade if the record already exists in the destiny table. Can anyone help me with that? Already looked for but i wasn't able to find somethig like it.

If you guys have a solution that is better and completely different, no problem, i'm open to any change.

Thanks in advance!

Re: Nifi CSV rows processing to sql server - Check primary key

joaoluistr — Tue, 29 Dec 2020 15:53:38 GMT

Hello everyone!

I'm here to post the solution i was able to develop to solve my problem.

I managed to do the following:

GetFile -> SplitRecord (converting to JSON) -> EvaluateJsonPath (to get key columns) -> ExecuteSQL (select with keys to check if exists) -> RouteOnAttribute (to execute update or insert depending on select result) -> ExecuteSQL (It can be update or insert).

Re: Nifi CSV rows processing to sql server - Check primary key

TimothySpann — Tue, 05 Jan 2021 21:28:13 GMT

executesqlrecord or putdatabaserecord may be easier.

Re: Nifi CSV rows processing to sql server - Check primary key

joaoluistr — Mon, 11 Jan 2021 14:06:52 GMT

Hello Timothy!

Could you elaborate, please?

Re: Nifi CSV rows processing to sql server - Check primary key

TimothySpann — Mon, 11 Jan 2021 15:11:15 GMT

https://www.datainmotion.dev/2020/12/simple-change-data-capture-cdc-with-sql.html

https://www.datainmotion.dev/2020/07/ingesting-all-weather-data-with-apache.html

https://www.datainmotion.dev/2019/10/migrating-apache-flume-flows-to-apache_15.html