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
– rmesteves, Commented Oct 28, 2020 at 13:56
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
– Indrajeet Gour, Commented Oct 28, 2020 at 14:00
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
– rmesteves, Commented Oct 28, 2020 at 14:05
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
– Indrajeet Gour, Commented Oct 28, 2020 at 14:14
In this case, you can use ReplaceText with Regex Replace option. — rmesteves
– rmesteves, Commented Oct 28, 2020 at 14:35

Sdairs · Accepted Answer · 2020-10-29 12:30:15Z

1

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

answered Oct 29, 2020 at 12:30

Sdairs

2,0521 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

NiFi - Persist timestamp value used in ExecuteSQLRecord processor query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related