How to use parameters in Nifi ExecuteSQL processor?

Question

Instead of doing a join, I want Nifi to first read all persons in a DB, and then for each person, I want it to bring the addresses. I know it is not the most efficient way, but we have some specific reasons for this.

The 'each person' part is resolved by using SplitAvro or SplitJson processor, but how can I get the ID for that current person and use it as parameter in next ExecuteSQL processor?

I have checked something similar to what I want, however the question is a little bit old (how to pass values dynamically in Apache NiFi from executeSQL to SelectHiveQL)

However the solution presented above seems more like a workaround for something that should be simple, I guess.

(In my case, I do not use Hive, both ExecuteSQL processor I use query on SQl Server DBs)

Answer 1

I acknowledge this is a convoluted scenario because rather than a JOIN, you're doing a read/iterate/query, but if that is what you truly want to do, I think ExecuteSQLRecord is what you are looking for. That processor will execute a SQL query and return the results as records using the configured RecordWriter controller service. You can then perform additional queries using LookupRecord, which can connect to arbitrary data sources (although RDBMS is not currently supported) to enrich data in record format, or use SplitRecord to create flowfiles from the individual records and route to another ExecuteSQLRecord processor, which can use the incoming flowfile attributes as query parameters for SQL.