Nifi CSV rows processing to sql server - Check primary key

Question

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!

Do you need to convert to JSON then to SQL? If I had a CSV I'd be inclined to load to to SQL Server (as a staging table) as it is, then write a script to process it and INSERT from there. You can include a WHERE clause on the INSERT (eg using NOT EXISTS...). — Joe Shark

Frankenstine Joe Frankenstine Joe · Accepted Answer · 2020-12-21T19:24:35

GetFile -> SplitRecord -> EvaluareJSONPath (To get the unique key into attribute) -> GenerateTableFetch + ExecuteSQL ( with that Unique/Primary key from previous step to get the corresponding record from DB ) -> RouteOnAttribute (to check a record exists from previous - If there isnt any then proceed to insert other wise update/ignore depending on your requirement ) -> transform (with ConvertJSONtoSQL -> UpdateAttribute (date conversion) ) -> PutSQL.

Should be something like this

Nifi CSV rows processing to sql server - Check primary key

1 Answers