Last active
September 2, 2021 15:46
-
-
Save mikeharding/e8dfbb6bd40ccfcd6d275274e2e07538 to your computer and use it in GitHub Desktop.
Instrument Snowflake Queries using a Query Tracker
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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