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

["Ajeesh B (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17249518#comment-17249518 ] 

Ajeesh B commented on NIFI-6115:
--------------------------------

This will be a cool feature.

> 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
>            Priority: Major
>
> NIFI-6082 added DatabaseRecordLookupService. Further, we could add CUSTOM_LOOKUP_SQL to 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
(v8.3.4#803005)