How can I get the Nifi ExecuteSQL processor COUNT(*) Result and use it in PutSQL processor?

Question

I have a table (T1) that is inserted frequently. I want to check whether the insertions successful. I have another table (T2) to store the row counts of this table.

I used ExecuteSQL processor to send SELECT COUNT(*) query against the T1. I want to put the result into T2 with PutSQL processor.

Can I get the result of the query using flowfile attributes?

Answer 1

If you are allowed to use custom processor then use below which does the same, creating FlowFile attributes from SQL query resultset instead of replacing FlowFile content, have a look at source code also. There is an alternative approach using DistributedMapCache implementation.

1. UpdateAttributesFromSQL

• https://github.com/brettryan/nifi-drunken-bundle
• https://mvnrepository.com/artifact/com.drunkendev/nifi-drunken-nar
• https://lists.apache.org/thread/dlcfmvs3djpm5hn4rsj11vht0fo4pp11

2. Alternate approach mentioned here

• https://community.cloudera.com/t5/Support-Questions/Apache-Nifi-Add-attribute-to-Flow-File-based-on-Sql-query/m-p/349885