I'm writing a flow in Apache NiFi. In the problematic part, I have a Flow File containing several records and a table (table 1) in MySQL Server which need to be updated. Every record in Flow File can be mapped to a row in table 1 based on values in a key column. Now I want to achieve the following goals: 1. Update fields in the matched row using field values in records as long as they are different. 2. Document the changes in another MySQL table (table 2), which has columns: the mentioned key column, date when field changes, revision id (a column from the record in Flow File), field id (i.e. which field has been changed. I have a table which maps column names to field id, FYI), old value, new value) For example, if a record in the Flow File is: { 'key': 1234, 'field_1': 'new_1', 'field_2': 'new_2', field_3': 'no_change_3' } and the corresponding row in table 1 is: { 'key': 1234, 'field_1': 'old_1', 'field_2': 'old_2', field_3': 'no_change_3' } After processing, the row in table 1 should become: { 'key': 1234, 'field_1': 'new_1', 'field_2': 'new_2', field_3': 'no_change_3' } and table 2 should have 2 new rows: { 'key': 1234, 'date': 'some date', 'revision_id': 'some revision id', 'field_id': 1, 'old_value': 'old_1', 'new_value': 'new_1' }, { 'key': 1234, 'date': 'some date', 'revision_id': 'some revision id', 'field_id': 2, 'old_value': 'old_2', 'new_value': 'new_2' } I used PutDatabaseRecord before to do the update, but in this way, I could not achieve the second goal as the Processor seems not allow for customized queries. Is there a way to achieve my purpose here?
... View more
