[jira] [Created] (NIFI-6115) Support custom lookup SQL at DatabaseRecordLookupService

["Koji Kawamura (JIRA)" <ji...@apache.org>]

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)