NiFi - Persist timestamp value used in ExecuteSQLRecord processor query

Question

My use-case is simple but I did not find the right solution so-far.

I write the query which tag the data with the current timestamp in of the column at the time ExecuteSQLRecord processor hit and get the data from database now want I wanted is that created flowfile has to have the same timestamp in his name as well but i did not know how to capture the attribute which is ${now():format("yyyyMMddHHmmss")} so I can use alter for renaming the flowfile

Basically, I wanted to store the timestamp "at the time I hit the database", I can not use the update processor just before the executeSQL processor to get the timestamp needed (why => because if prior execution is still in process with executeSQL and all the flow files will pass updateattribute processor with the timestamp value and will sit in the queue until executeSQL processor process current thread).

Note - I am running NiFi in standalone mode so I can not run executeSQL in multiple threads.

Any help is highly appreciated. thanks in advance

You want to put your attribute's value inside your flowfile? — rmesteves
yes, update the flowfile with the timestamp which I have done in a query but the second part is tricky when I wanted to use the same timestamp value to update the same flowfile name — Indrajeet Gour
If you want to just put the attribute inside the content, try using the processor ReplaceText and put your attribute in the replacement field. You can select the mode Append if you just want to gather this information in the end of your content. — rmesteves
that timestamp has to be added into each row of my flowfile content, I am having csv format inside the flowfile and that timestamp column shows the processing time which the same I wanted to use later in filename for that flowfile — Indrajeet Gour
In this case, you can use ReplaceText with Regex Replace option. — rmesteves

Sdairs Sdairs · Accepted Answer · 2020-10-29T12:30:15

ExecuteSQLRecord writes an attribute called executesql.query.duration which contains the duration of the query + fetch in milliseconds.

So, we can put an UpdateAttribute processor AFTER the ExecuteSQLRecord that uses ${now():toNumber():minus(${executesql.query.duration})} to get the current time as Epoch Millis, then minus the total query duration, to get the time at which the Query started.

You can then use :format('yyyyMMddHHmmss') to bring it back to the timestamp format you want.

It might be a few milliseconds off of the exact time (time taken to get to the UpdateAttribute processor).

See docs for ExecuteSQLRecord

NiFi - Persist timestamp value used in ExecuteSQLRecord processor query

1 Answers