Logging Executed SQL Commands

[#1]

Hello! 

Attached below is a flow I have created to perform a simple sorting task with a given CSV file 

1. Getfile processor brings the target CSV file into the flow 

2. QueryRecord processor executes SQL commands and send the flowfile two ways

3. Each flow file is saved into Postgresql database with PutDatabaseRecord processors. 

What I am trying to do is. 

1. Keep a log of all SQL commands that are executed on Queryrecord processor and keep timestamp (When they were executed) of them 

2. Save the log into a Postgresql table named 'validation_log" that has 3 columns. (CSV file name, SQL command, Timestamp)

How can I make this work? 

[#2]

Hi,

If I understood you correctly you want to log some information when the QueryRecord is executed, in this case you can use the Original relationship coming out of the QueryRecord where you can log the information like filename,timestamp...etc.

[#3]

Thank you for the reply !. Can you suggest a processor that will perform the task you mentioned? Would LogAttribute do ?

[#4]

@Lewis_King ,

> Keep a log of all SQL commands...

What do you mean by the above? The QueryRecord processor always executes the same SQL query, which you configure in the processor as a relationship. The SQL query doesn't change; what changes are the flowfiles that go through the processor.

Cheers,

André

[#5]

Oh I guess I didnt make my question clear here.

for example , when I have 2 SQL queries (a, b) set on my Queryrecord Processor,

I want to find out if I can keep a log like 

SQL query     |      Timestamp (Time of the query execution)

a                    |        20220714, 12:00

b                    |        20220714 , 12:00

[#6]

@Lewis_King ,

Do you want to append one row for each flowfile or one row for each processed record to this log?

André

[#7]

One row for each flowfile would do ! 

[#8]

Actually can you show me both methods ? Thank you so much for help !

[#9 · Accepted solution]

@Lewis_King ,

Here's an idea. You can fork the "a" output of the QueryRecord processor and send it to a sequence of processors as shown below:

The ReplaceText processor will simply replace the entire contents of the flowfile with the information you want to register in the log. For example:

This will produce one row per flow file with the source type ("a") and the timestamp.

You can them send these to a MergeRecord to avoid saving to many small log files and them to a PutFile to persist the log.

You can process the "b" output in a similar way.

Cheers,

André