Querying History By Sessions in Snowflake

Last time, we talked about a query using the Query_History_By_User table function in Snowflake. This time, I want to discuss a similar table function named Query_History_By_Session. Thankfully, it works in a very similar fashion to Query_History_By_User.

SELECT *
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER())
ORDER BY START_TIME DESC; 

Yes, it shows the same information as Query_History_By_User, so I am not displaying it here.

By the table function name, you’re probably wondering “Sherpa, why on Earth do I really need to look at query history based on the session? I have Query_History_By_User which gives me a broader look at my data. Who really cares?”

Great question, my friend! You’re right. This new Query_History_By_Session table function isn’t going to give you a lot of data that you can’t get easier – and more helpfully – with Query_History_By_User. Why did Snowflake provide me this “useless” function?

Well, it’s not especially helpful when used like the above. When it is helpful, though, is when we’re working with tasks in Snowflake that call stored procedures.

SELECT th.NAME, qh2.* 
FROM
   TABLE(information_schema.task_history(TASK_NAME=>'NIGHTLY_TASK' )) th,
   TABLE(information_schema.query_history()) qh,
   TABLE(information_schema.query_history()) qh2
WHERE qh.QUERY_ID = th.QUERY_ID
AND qh.start_time between th.query_start_time and th.completed_time
AND qh2.start_time between th.query_start_time and th.completed_time
AND qh.SESSION_ID = qh2.SESSION_ID
ORDER BY qh2.start_time;

The query above shows each query of the task NIGHTLY_TASK in the order that it was run. If you’ve ever had the pleasure of working with SQL Server Profiler – he says snarkily – then you’ll enjoy the data that is produced by this query. Each procedure is broken down into its individual queries and you can see exactly how long each part of the task takes.

Thank you for hanging out with the Sherpa today as we delve more into Snowflake Query data. Finding data about an individual session doesn’t always sound cool, but I hopefully have provided a query that can you help you get the data you’ll need for dealing with tasks. So until next time, my friends!