You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "Koji Kawamura (JIRA)" <ji...@apache.org> on 2019/03/12 07:46:00 UTC
[jira] [Updated] (NIFI-6115) Support custom lookup SQL at
DatabaseRecordLookupService
[ https://issues.apache.org/jira/browse/NIFI-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Koji Kawamura updated NIFI-6115:
--------------------------------
Description:
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.
was:
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.
> 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
(v7.6.3#76005)