[
https://issues.apache.org/jira/browse/NIFI-1706?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16336813#comment-16336813
]
ASF GitHub Bot commented on NIFI-1706:
--------------------------------------
Github user ijokarumawak commented on a diff in the pull request:
https://github.com/apache/nifi/pull/2162#discussion_r163442381
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java
---
@@ -156,10 +157,22 @@
.addValidator(StandardValidators.NON_EMPTY_VALIDATOR)
.build();
+ public static final PropertyDescriptor SQL_QUERY = new
PropertyDescriptor.Builder()
+ .name("db-fetch-sql-query")
+ .displayName("Custom Query")
+ .description("A custom SQL query used to retrieve data.
Instead of building a SQL query from "
+ + "other properties, this query will be used. Query
must have no WHERE or ORDER BY statements. "
+ + "If a WHERE clause is needed use a sub-query or the
'Additional WHERE clause' property.")
--- End diff --
Can we generate a sub-query from this property by simply wrapping it with
braces and add the specified `Table name` as the alias name of it? If we do so,
we can use Expression Language and incoming FlowFiles more creatively with the
single QueryDatabaseTable processor instance. Also it will allow user to define
WHERE and ORDER BY statement here.
With that approach, we can keep using the `Table Name` property as it is,
and the custom validation is not required. This `Custom Query` property can be
truly an optional property.
Current proposed approach use a constant "NiFi_QDBT" table name for state
keys, and it can limit the use-case. How do you think?
> Extend QueryDatabaseTable to support arbitrary queries
> ------------------------------------------------------
>
> Key: NIFI-1706
> URL: https://issues.apache.org/jira/browse/NIFI-1706
> Project: Apache NiFi
> Issue Type: Improvement
> Components: Core Framework
> Affects Versions: 1.4.0
> Reporter: Paul Bormans
> Assignee: Peter Wicks
> Priority: Major
> Labels: features
>
> The QueryDatabaseTable is able to observe a configured database table for new
> rows and yield these into the flowfile. The model of an rdbms however is
> often (if not always) normalized so you would need to join various tables in
> order to "flatten" the data into useful events for a processing pipeline as
> can be build with nifi or various tools within the hadoop ecosystem.
> The request is to extend the processor to specify an arbitrary sql query
> instead of specifying the table name + columns.
> In addition (this may be another issue?) it is desired to limit the number of
> rows returned per run. Not just because of bandwidth issue's from the nifi
> pipeline onwards but mainly because huge databases may not be able to return
> so many records within a reasonable time.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
