[
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)
