I have a data flow that I'd like to add some data to using a MS SQL datasource for the lookup. I've attempted to follow @Matt Burgess' post, SQL in NiFi with ExecuteScript, but unfortunately I can seem to get my head wrapped around how to accomplish this. I've also read through the forum topic How to get DBCP service inside ScriptedLookupService and tried to accomplish the same thing through the LookupRecord processor but can't seem to get anything to work. I know it's because I don't have the correct objects or methods defined. Unfortunately, I'm not a programmer by trade so some concepts do allude me. Usually I'm pretty good at figuring out things by example but this one has me stumped so I'm reaching out for some help.
I think using the LookupRecord method would be best as it would seem to be easier to inject it into the current flow that's already setup. But I can't get the ScriptedLookupService in the controller services to work following the code examples from the above linked posts. I'm good with not doing the name lookup for the DBConntionPool service, I'll use the Id if it makes the code simpler. And I think I get the concept that the ScriptedLookupService doesn't include objects that say the ExecuteScript processor does.
If someone has done something similar to what I'm trying to accomplish, and would not mind providing some more detailed examples or pushing me in a more correct direction (even if its off a cliff), I would be very grateful!
I just finished up a lookup service and lookup record service that does just this for any jdbc data source.
I will open source it today. Putting this message here to remind me.
@Chris Parker, thanks a lot. Is there any way in which I can use the SQLLookupService to check if a given attribute value is present in a specific column of a table? BTW. I had to add "implements StringLookupService" to the SQLLookupService Class for it to be visible in the "LookupAttribute" processors choices of LookupServices.