question How to run certain MySQL queries based on records in a FlowFile? in Support Questions

question How to run certain MySQL queries based on records in a FlowFile? in Support Questions https://community.cloudera.com/t5/Support-Questions/How-to-run-certain-MySQL-queries-based-on-records-in-a/m-p/384080#M245259 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? Wed, 28 Feb 2024 07:17:50 GMT iriszhuhao 2024-02-28T07:17:50Z