Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Super Collaborator

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.

1 ACCEPTED SOLUTION

avatar

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). 


- Dennis Jaheruddin

If this answer helped, please mark it as 'solved' and/or if it is valuable for future readers please apply 'kudos'.

View solution in original post

3 REPLIES 3

avatar

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). 


- Dennis Jaheruddin

If this answer helped, please mark it as 'solved' and/or if it is valuable for future readers please apply 'kudos'.

avatar
Super Collaborator

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?

avatar
Super Collaborator

Hi ,

 

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