Skip to content

Instantly share code, notes, and snippets.

@mikeharding
Last active September 2, 2021 15:46
Show Gist options
  • Save mikeharding/e8dfbb6bd40ccfcd6d275274e2e07538 to your computer and use it in GitHub Desktop.
Save mikeharding/e8dfbb6bd40ccfcd6d275274e2e07538 to your computer and use it in GitHub Desktop.
Instrument Snowflake Queries using a Query Tracker
alter session set QUERY_TAG = 'MY_TAG';
select
//QTR:Query_123//
start_station_id, end_station_id,
count(1) num_trips,
avg(datediff("minute", starttime, stoptime))::integer avg_duration_mins
from trips
group by 1, 2
order by 3 desc;
alter session set QUERY_TAG = '';
with filtered_query_history as (
select *
from snowflake.account_usage.query_history
where 1=1
and query_type in ('SELECT') -- Add types if needed
and execution_status = 'SUCCESS'
-- and cluster_number > 0 -- filter out results from cache
-- and warehouse_size IS NOT NULL -- filter out results from cache
and start_time > '2021-08-31'::timestamp
and query_tag = 'MY_TAG'
)
select
query_id
, query_tag AS query_tag
, DATE_TRUNC('minute', start_time) AS interval_time
, extract(minute from start_time) AS minute
, SUBSTR(query_text, REGEXP_INSTR(query_text, 'QTR:', 1, 1) + 4, REGEXP_INSTR(query_text,'//', 1, 2) - (REGEXP_INSTR(query_text, 'QTR:', 1, 1) + 4) ) AS query_tracker
, warehouse_name AS warehouse_name
, warehouse_size AS warehouse_size
, cluster_number AS cluster_number
, ROUND(execution_time / 1000, 2) AS execution_time
, ROUND(queued_overload_time / 1000, 2) AS queued_time
from filtered_query_history
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment