Nifi CSV rows processing to sql server - Check primary key

[#1]

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!

[#2 · Accepted solution]

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).

[#3 · Accepted solution]

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).

[#4 · Accepted solution]

executesqlrecord or putdatabaserecord may be easier.

[#5]

Hello Timothy!

Could you elaborate, please?

[#6]

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