question Re: Logging Executed SQL Commands in Support Questions

Logging Executed SQL Commands

Lewis_King — Wed, 13 Jul 2022 08:06:44 GMT

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?

Re: Logging Executed SQL Commands

SAMSAL — Wed, 13 Jul 2022 16:18:06 GMT

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.

Re: Logging Executed SQL Commands

Lewis_King — Wed, 13 Jul 2022 23:44:05 GMT

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

Re: Logging Executed SQL Commands

araujo — Thu, 14 Jul 2022 01:44:23 GMT

@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é

Re: Logging Executed SQL Commands

Lewis_King — Thu, 14 Jul 2022 03:00:38 GMT

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

Re: Logging Executed SQL Commands

araujo — Thu, 14 Jul 2022 05:48:52 GMT

@Lewis_King ,

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

André

Re: Logging Executed SQL Commands

Lewis_King — Thu, 14 Jul 2022 06:03:13 GMT

One row for each flowfile would do !

Re: Logging Executed SQL Commands

Lewis_King — Thu, 14 Jul 2022 06:06:21 GMT

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

Re: Logging Executed SQL Commands

araujo — Thu, 14 Jul 2022 06:20:17 GMT

@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é