[NIFI-1575] Add ability to query DB table using "last value" from column

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.6.0
• Component/s: Extensions
• Assignee: Matt Burgess
• Reporter: Matt Burgess
• Created: 29/Feb/16 15:56
• Updated: 18/Mar/16 02:28
• Resolved: 16/Mar/16 19:56

Description

It would be a useful feature for a processor to be able to query from a database table for only those records that have are added/available since the last time the query was executed. For example, if the processor could keep the max value of a timestamp column for the last result set returned, the next time the query was issued, it could use that timestamp value and column to only return records whose timestamps were greater than "the last time".

This shouldn't be limited to timestamps of course; it would be useful for any strictly-increasing value (like primary key ID) but would be up to the user to select the table and column. The processor would simply keep the state for the specified column's max value.

Proposed is a "QueryDBTable" processor which would have a properties to specify the table name and the column in the table for which to keep state information about the last maximum value retrieved. Subsequent queries of the table use this value to filter for rows whose value for the specified column are greater than the "last maximum value". Upon each successful query, the "last maximum value" is updated.