Snowflake - Capture execution time of a query and insert it into a separate table

roblen 0
Viewed 37

We have a tracking table used to monitor the runtime of processes. It captures data from other sources beyond Snowflake so we can't just use the history data that is automatically captured when queries run. Our idea is to have a pre statement that captures the start time as a variable, a snowflake query would then run (could be anything), and lastly a post statement which outputs the required tracking data into a different table. However, when we try the below statement or variations of it we get an error.

Error: SQL compilation error: syntax error line 3 at position 0 unexpected 'SELECT'. syntax error line 8 at position 0 unexpected 'INSERT'.

Statement Example:

-- Pre Statement: setting the start time of the query
SET(starttime)=current_timestamp()

-- Intra Statement: query to run in Snowflake
SELECT * 
FROM "Customer"

-- Post Statement: inserting output into tracking table
INSERT INTO "Tracking"
    SELECT 'Example', current_timestamp(), current_warehouse(), DATEDIFF(hour,$starttime,current_timestamp()), current_user();

Tracking Table Fields:

PROCESS_TAG, RUN_DATE, WAREHOUSE, RUN_TIME, ACCOUNT

snowflake-cloud-data-platform
1 Answers

Thank you @Sergiu and @Greg Pavlik for finding my mistake of not including ";" after each statement. The query runs as expected now.

-- Pre Statement: setting the start time of the query
SET(starttime)=current_timestamp();

-- Intra Statement: query to run in Snowflake
SELECT * 
FROM "Customer";

-- Post Statement: inserting output into tracking table
INSERT INTO "Tracking"
    SELECT 'Example', current_timestamp(), current_warehouse(), DATEDIFF(hour,$starttime,current_timestamp()), current_user();
roblen0
Related