Brett Ryan has 2 different processors on GitHub that can do something similar.
https://github.com/brettryan/nifi-drunken-bundle In the master branch, he has an UpdateAttributesFromSQL. When he discussed this on the list in 2017, I think, it was suggested to turn into a lookup. If you look at the dev branch, though, he started LookupAttributeFromRecord and LookupAttributeFromSQL. I'm using the both the UpdateAttributesFromSQL and LookupAttributeFromSQL in different areas right now. I modified his UpdateAttributesFromSQL locally to allow for a not-found relationship since I needed to do some work if the record wasn't found, where I wouldn't want that to be a 'failure'. It does not allow parameterized SQL, though, which I have thought about adding, but hadn't gotten around to it yet. John -------------------------------------------- On Wed, 11/21/18, Koji Kawamura <[email protected]> wrote: Subject: Re: SQL Result to Attributes To: [email protected] Date: Wednesday, November 21, 2018, 8:53 PM Hi Nick, I thought there was a discussion about adding a general database lookup service, but I can't find LookupService impl available that can fetch value from external databases. If there's such controller service, you can use LookupAttribute processor. (wondering if you're interested in contributing that..) As a workaround, you may be able to construct a flow like this: - Pass the input FlowFile into two branches, 'main' and 'lookup' - At 'lookup' branch, fetch data with ExecuteSQL, then extract the required data from the result and put it to a cache using PutDistributedMapCache - At 'main' branch, use LookupAttribute with DistributedMapCacheLookupService to lookup the value stored by 'lookup' branch - 'main' branch may need some retry & timeout mechanism Or if the dataset is relatively small, you can download and save it as a CSV file periodically, then lookup using LookupAttribute with SimpleCsvFileLookupService. Thanks and Happy Thanksgiving! Koji On Thu, Nov 22, 2018 at 5:58 AM Nick Carenza <[email protected]> wrote: > > I would like to add a sql query result to attributes but I don't see a way to do this currently. Anyone know of a processor I am missing or have a workaround? > > Preferably the result columns would become attributes. > > I think I coooouuld use exctract test to move the current flowfile contents into an attribute, runthe query with ExecuteSQL, convert to json from avro, ExtractJsonPath the columns I want then use ReplaceText to bring the original flowfile back... but that seems excessive and it requires putting the larger of the 2 contents in an attribute. > > Thanks and Happy Thanksgiving Nifi-Users, > Nick
