Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

NiFi data enrichment using MS SQL Datasource

Solved Go to solution

NiFi data enrichment using MS SQL Datasource

New Contributor

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!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: NiFi data enrichment using MS SQL Datasource

New Contributor
5 REPLIES 5

Re: NiFi data enrichment using MS SQL Datasource

New Contributor

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.

Re: NiFi data enrichment using MS SQL Datasource

New Contributor

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

Re: NiFi data enrichment using MS SQL Datasource

New Contributor

Re: NiFi data enrichment using MS SQL Datasource

New Contributor

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

Highlighted

Re: NiFi data enrichment using MS SQL Datasource

New Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here