NIFI - How to insert distinct data from the flow and refer to that data ID in other places

Question

I'm trying to learn NIFI so this is all new to me, I used to work with Talend and I have hard time translating to NIFI. So the main idea: For example is I have two tables in Postgresql

Table CITY :

ID (auto generated), city_name

Table PERSON :

ID (auto generated), first_name, last_name, city_id

and I have a CSV file :

first_name, last_name, city_name

Can you please explain how I can insert in tow tables from one flowfile and refer in the table PERSON to the ID of the city not the name from the table CITY.

Thank you

Answer 1

you could use LookupRecord to enrich each record with city id and split input in two files: matched/unmatched.

for matched you have to execute simple insert into PERSON table - because city id was found.

for unmatched you have to generate insert/upsert into CITY table and then route all those records to lookup record again.

---

or you could insert everything as is into temp table with structure that matches your CSV.

and then execute 2 simple sql statements:

1. populate missing cities from temp table
2. insert into person from temp table with lookup city id on the level of SQL server