Koji Kawamura created NIFI-6115:
-----------------------------------
Summary: Support custom lookup SQL at DatabaseRecordLookupService
Key: NIFI-6115
URL: https://issues.apache.org/jira/browse/NIFI-6115
Project: Apache NiFi
Issue Type: Improvement
Reporter: Koji Kawamura
NIFI-6082 added DatabaseRecordLookupService. Further, we could add
{{CUSTOM_LOOKUP_SQL t}}o support custom where conditions and order by ... etc.
Design:
* If CUSTOM_LOOKUP_SQL is specified, TABLE_NAME and LOOKUP_KEY_COLUMN are not
required
* User would specify a custom query like {{select firstName, lastName from
users where companyName = ${companyName} and department = ${department} order
by job_responsibility}}
* Then DatabaseRecordLookupService can understand what coordinates are
required by using VariableImpact.isAffected. ProcessGroup can be a reference
for how to do this.
[https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-framework-bundle/nifi-framework/nifi-framework-core/src/main/java/org/apache/nifi/groups/StandardProcessGroup.java#L2975]
* Then, the ControllerService replaces EL part into NamedParameter. Should
note on the doc that EL function can not be used here. If needed, it should be
done at Processor's dynamic properties.
* The generated SQL would look like {{select firstName, lastName from users
where companyName = :companyName and department = :department order by
job_responsibility}}
* Then user would configure LookupRecord with two dynamic properties:
** {{companyName}}: {{/companyName}}
** {{departmentName}}: {{/departmentName}}
This way, user can lookup record very flexibly.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
