Snowflake query history weird results

user_6664990 0
Viewed 241

I'm trying to look at the queries on certain tables and I'm getting weird results. I know for sure, there are atleast 5 queries on a certain table, but below queries are getting me different results. Anyone Snowflake users experienced similar issues.

SELECT QUERY_ID,QUERY_TEXT
from table(information_schema.query_history(       
end_time_range_start=>to_timestamp_ltz ('2022-03-29 11:00:00.000000'),     
end_time_range_end=>to_timestamp_ltz ('2022-03-29 14:00:00.000000')))    
where UPPER(query_text) like '%TABLE_NAME%'

-- This returns one record as result, Really weird behavior is when I increased the time range filter, I'm getting zero records (for below query). Does Snowflake stop execution if it finds data is so large

SELECT QUERY_ID,QUERY_TEXT
from table(information_schema.query_history(       
end_time_range_start=>to_timestamp_ltz ('2022-03-27 11:00:00.000000'),     
end_time_range_end=>to_timestamp_ltz ('2022-03-29 15:00:00.000000')))    
where UPPER(query_text) like '%TABLE_NAME%'

Currently I'm trying to run a script which calls above sql iteratively for 1 hour interval and loads to a different table which kind of gets me most of the data but feels like I still miss a lot of query history.

snowflake-cloud-data-platform
1 Answers

What is most likely happening is you are hitting the 10K rows limit earlier with the time range starting earlier, thus your WHERE clause is filter less results.

Which is to say the "tables" do not really behave as they "should" mostly because they are not tables.

Also without the RESULT_LIMIT =>10000 argument set, you will only be getting the default limit with is 100

try:

SELECT QUERY_ID, QUERY_TEXT
from table(information_schema.query_history(       
    end_time_range_start=>to_timestamp_ltz ('2022-03-27 11:00:00.000000'),     
    end_time_range_end=>to_timestamp_ltz ('2022-03-29 15:00:00.000000'),
    RESULT_LIMIT =>10000
))    
where query_text ilike '%TABLE_NAME%'
user_439920
Related