NiFi data enrichment using MS SQL Datasource

[#1]

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!

[#2]

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.

[#3]

@Chris Parker, You have me waiting in anticipation, sir...

[#4 · Accepted solution]

@Jason Zondor

Here you go: https://github.com/mrcsparker/nifi-sqllookup-services-bundle

[#5]

I can't compile it for version 1.9.1!
please recompile it.

[#6]

@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.

[#7]

To perform a data lookup using MS SQL as the datasource in NiFi, you can use the LookupRecord processor along with the DBCPConnectionPool and ScriptedLookupService. Here is an outline of the steps you can follow:

1. Create a DBCPConnectionPool service:

• Go to the NiFi UI and click on the gear icon to access the Controller Services.
• Click on the "+" button and select "DBCPConnection Pool"
• Configure the connection pool with the details of your MS SQL database, such as the driver class name, URL, username, password, etc.

1. Create a ScriptedLookupService:

• In the same Controller Services screen, click on the "+" button and select "ScriptedLookupService".
• In the ScriptedLookupService, you need to write a Javascript code that will be used to perform the lookup. You can refer to the example code provided in the forum topic How to get DBCP service inside ScriptedLookupService.

1. Configure the LookupRecord processor:

• Add a LookupRecord processor to your data flow.
• In the Properties tab, set the "Lookup Service" to the ScriptedLookupService you created in step 2.
• In the same Properties tab, set the "Connection Pool" to the DBCPConnectionPool you created in step 1.
• Connect the LookupRecord processor to your data flow, with the input flow file containing the data that you want to enrich.