Support Questions
Find answers, ask questions, and share your expertise

Best approach for comparing flowfile attribute value to all column values in a postgres db column?

New Contributor

Ok, so I am pretty new to nifi but certainly not inept at it. I just find myself going a bit in circles thinking about the design of this flow: I ingest data from various sources all in JSON format and enrich and route those files to various outputs (kafka, elasticsearch, postgres, etc). In the middle of the flow, an attribute requires validation against a known list of acceptable values - let's just say a uuid for simplicity. These ids vary depending on the data sources and desired destination(s) and currently, I hard-code the known ids using unique




expressions in a RouteOnAttribute processor with a property routing strategy. While everything works great right now, I expect the list of valid ids to eventually become very, very large. We use postgres in other parts of the flow with one of the db tables containing the known ids as its own column. So instead of manually updating the processor every time an id change is required, what I would like to do is query that  column in postgres from nifi and use the result set for comparison against the id in each flowfile as it passes through, routing as I do now based on validity and so on.

I am not looking for fully implemented solutions, just guidance on where to start or what I can use to achieve this in a performant way. I have looked at QueryDatabaseTable, GenerateTableFetch, ExecuteSQL(Record), etc but querying one cell at a time isn't efficient, and I don't want to re-query every time a new flowfile passes through. What should I be looking at in terms of storing/caching a list of values so I can pass an attribute's value for comparison (is it there? yes, good. no, bad.)?

Thank you for any tips! I am enjoying learning more and more about the power of nifi!