Snowflake: Long Running Queries Seek & Destroy

So, you might have some long running queries in your Snowflake account? Let’s say someone is running a recursive cartesian join because they used this WHERE clause “and (created_on >= ‘2020–08–03 00:00:00’ and created_on < ‘2020–08–31 00:00:00’) or (created_on >= ‘2020–02–03 00:00:00’ and created_on < ‘2020–03–02 00:00:00’)” instead of this one “and ((created_on >= ‘2020–08–03 00:00:00’ and created_on < ‘2020–08–31 00:00:00’) or (created_on >= ‘2020–02–03 00:00:00’ and created_on < ‘2020–03–02 00:00:00’))” …maybe that was me on SnowHouse yesterday…order of operations matter people. Here I never thought 8th grade Algebra II would come in handy in life. Now let’s go back to 8th grade and harness our lessons from Metallica, time to get searching, seek and destroy!

Searching Long Running Queries
Thankfully someone at Snowflake decided to add a function in the INFORMATION SCHEMA where we can find these types of metrics on long running queries. We will go ahead and turn this function into a table function in Snowflake so we can call it with less verbose syntax for reuse with X number of seconds as a threshold. INFORMATION_SCHEMA.QUERY_HISTORY https://docs.snowflake.com/en/sql-reference/functions/query_history.html

Because the INFORMATION_SCHEMA also has functions for QUERY_HISTORY_BY_USER and QUERY_HISTORY_BY_WAREHOUSE you could easily scope this code down from the Account level to specific Users or Warehouses. That might be more useful in Production.

User Name Version

Warehouse Name Version

Seek and Destroy Long Running Queries
Now we can use the function “get_long_running_queries() “ as a baseline for our seek and destroy function. Be careful, this can terminate ALL queries in a Snowflake account if you are playing around.

Conclusion
Now you have some tools to identify and terminate…er…Seek and Destroy long running queries in your Snowflake accounts. This can all be automated by using a task with this code, but it would be easier to set up Account global timeouts with STATEMENT_TIMEOUT_IN_SECONDS ( link). Happy Seek and Destroy Querying!

Originally published at http://bigdatadave.com on September 5, 2020.