Why putsql processor do not execute update query for each flowfiles?

[#1]

Hi All, 

I am tyring to execute update query through putSQL processor. I want to update number of processed file count column of one of the transactional table in mysql. 

The issue is that whenever  multiple flowfiles coming as input to putSQL processor at a time,the processed file count is getting incremented and updated only once. Ideally,the behaviour should be like increment the counter one by one for each flowfile and then update the processed file count column in MySQL table.

NiFi flow:

Fetch processed file count using select query->updateattribute : processed file count+1->putSQL:update query.

Thanks in advance

Please share you response on it.

[#2 · Accepted solution]

The update Attribute processor is for updating an individual flow file. It seems like you want to have a more global count. 

This kind of counting over time is usually called a window operation, and is not something Nifi is really designed for. In general you would use something like Spark for complex operations.

In your specific case you could try something 'ugly' like letting Nifi execute an SQL statement that updates a field with +1 each time. However this obviously will not scale, and I am not sure if a correct outcome is guaranteed if two updates are initiated in parallel (that may be more of a DB question). 

[#3]

Hi @DennisJaheruddi 

Thank you for writing to me.
There are some situation or places in NiFi flow ,where i require to execute SQL INSERT for each flowfilea and at some places need to execute SQL INSERT only once irrespective on number of flowfiles.

Could you please point me in right direction for the same?

[#4]

Hi ,

My assumptions was wrong , putsql processor does execute update query per flowfile