Hi Valentina,
Not sure if this will work for you, but I have achieved 2 key lookups using
a LookupService by creating a composite field in the csv. Admittedly I was
creating the csv in nifi from another data source so I had control of the
CSV's shape.
You may be able to form a composite key in your streamed data without
needing a script but I wrote a custom groovy script to combine the two
fields in my dataset on the fly to use in the look up.
// Near the start of the `onTrigger`
def lookupService =
context.getProperty(RECORD_LOOKUP).asControllerService(LookupService)
// Located in a function called from both locations of `// TODO process
first record`
final Optional<?> lookupValueOption
def key =
"${record.getValue('ReferenceTypeId')}-${record.getValue('Id')}".toString()
def lookupCoordinates = ['key':key]
try {
lookupValueOption = lookupService.lookup(lookupCoordinates)
} catch (final Exception e) {
throw new ProcessException('Failed to lookup coordinates '
+ lookupCoordinates + ' in Lookup Service', e)
}
if (!lookupValueOption.isPresent()) {
getLogger().debug('Reference ' + lookupCoordinates + ' not
found in Lookup Service. Not adding to reference map')
continue
}
def Record lookupRecord = (Record) lookupValueOption.get()
def referenceTypeCode =
lookupRecord.getValue('ReferenceTypeCode')
Matthew's blog post(s) helped me immensely with this adventure:
http://funnifi.blogspot.com/2019/04/record-processing-with.html
See how you go.
Cheers,
Dirk.
On Fri, 20 Nov 2020 at 02:29, Valentina Ivanova <[email protected]>
wrote:
> Hello everyone!
>
> I am working on the following data enrichment scenario. I am receiving a
> streaming data from which I am extracting several attributes. Now I want to
> use three of these attributes as keys to query a csv file and retrieve two
> values from it. (Something equivalent to this SQL statement)
> SELECT value1, value2 FROM csv_file WHERE key1=att_key_1 and
> key2=att_key_2 and key3=att_key_3
> I made this scenario work using QueryRecord, however, I need to read the
> csv file every time I receive the streaming data - which does not seem
> really scalable solution.
>
> I have looked for other options and read about LookupRecord[1] and
> LookupAttribute[2] which are build for such scenarios but unfortunately the
> respective LookupServices does not seem to allow specifying more than one
> key. I always get an ERROR saying that the expected key should be one of
> [my csv file attributes]. (Using one key at a time also results in an error
> saying that the values for it repeat which is the case as only the
> combination of the three is unique)
>
> So I am wondering if someone has encountered a similar problem and can
> provide any advice. (For now I would prefer not to implement a custom
> processor but if nothing works any directions and templates that would help
> will be also very appreciated.)
>
> Many thanks in advance,
>
> Valentina
>
>
> [1]
> https://community.cloudera.com/t5/Community-Articles/Data-flow-enrichment-with-NiFi-part-1-LookupRecord-processor/ta-p/246940
> [2]
> https://community.cloudera.com/t5/Community-Articles/Data-flow-enrichment-with-NiFi-part-2-LookupAttribute/ta-p/247072
>
>
--
Regards,
--
Dirk Arends
