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

3 replies
5390 views
Translate All

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.

Apache NiFi

DennisJaheruddiAnswer

Platinum ⭐️

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

DennisJaheruddiAnswer

Platinum ⭐️

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

Translate

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

ManusAuthor

Gold ⭐️

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?

Translate

ManusAuthor

Gold ⭐️

Hi ,

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

Translate

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.