CODEWITHSUNDEEP
snowflake-cloud-data-platform
RMC_DEV
RMC_DEV

Reputation: 119

How to get the record of the last query executed from snowflake query history table

In snowflake I am trying to get the last called stored procedure with the latest timestamp My code selects the latest stored procedure but it gets the latest timestamp of each stored procedure called What am I doing wrong? Below is my code

SELECT * FROM TABLE(INFORMATION_SCHEMA.query_history_by_session())
WHERE (end_time) IN 
    (SELECT MAX(end_time) 
    FROM TABLE(INFORMATION_SCHEMA.query_history_by_session()) 
    WHERE UPPER(QUERY_TEXT) LIKE 'CALL%' 
    AND UPPER(QUERY_TEXT) <> 'CALL "DB"."TK".GET_EXECUTION_ATTRIBUTES();')
ORDER BY END_TIME DESC;

Upvotes: 0

Views: 1481

Answers (2)

Michael Golos
Michael Golos

Reputation: 2049

Note that the query_history% function returns 100 queries by default, you can extend it using the attribute RESULT_LIMIT => num and narrow down the time interval END_TIME_RANGE_START => constant_expr END_TIME_RANGE_END => constant_expr

More about it: https://docs.snowflake.com/en/sql-reference/functions/query_history.html

If that's not enough, maybe it's worth asking account_usage.query_history view:

USE ROLE ACCOUNTADMIN;
SELECT *
FROM snowflake.account_usage.query_history
WHERE query_text ilike 'call%'
ORDER BY END_TIME DESC
LIMIT 100;

More about it: https://docs.snowflake.com/en/sql-reference/account-usage/query_history.html

But be careful, latency for the view may be up to 45 minutes.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

To get latest record ORDER BY ... DESC LIMIT 1 could be used:

SELECT *
FROM TABLE(INFORMATION_SCHEMA.query_history_by_session()) 
WHERE QUERY_TEXT ILIKE 'CALL%' 
  AND QUERY_TEXT NOT ILIKE 'CALL "DB"."TK".GET_EXECUTION_ATTRIBUTES();'
ORDER BY END_TIME DESC
LIMIT 1;

For case insensitive comparison ILIKE is recommended.

but it gets the latest timestamp of each stored procedure called

Hypothesis: Incorrect scope was chosen(i.e., end_time IN ( ... MAX(end_time)...).

To be sure, it is advisable to use explicit aliasing for all columns:

SELECT o.*
FROM TABLE(INFORMATION_SCHEMA.query_history_by_session()) AS o
WHERE (o.end_time) IN 
    (SELECT MAX(i.end_time) 
    FROM TABLE(INFORMATION_SCHEMA.query_history_by_session()) AS i
    WHERE UPPER(i.QUERY_TEXT) LIKE 'CALL%' 
    AND UPPER(i.QUERY_TEXT) <> 'CALL "DB"."TK".GET_EXECUTION_ATTRIBUTES();')
ORDER BY o.END_TIME DESC;

Upvotes: 2

Related Questions