snowflake session_id on query_history, can it be reinitialized to have new id per application execution

user_12223871 0
Viewed 46

I want to use snowflake query_history's sessionId to find all the queries executed in one session. It works fine on the snowflake end when I have different worksheets which create different sessions. But from other tools (it looks to be using the same connection pool until it recreates the connection), it creates the same session id for multiple jobs on the snowflake side query_history. Is there a way to have sessionID created on every execution? I am using the control-m scheduling/job automation tool to execute multiple jobs which execute different snowflake stored procs. I want to see if I can get different sessionID for each execution of the procedure on the snowflake query_history table.

Thanks Djay

snowflake-cloud-data-platform
1 Answers

You can change the "idle session timeout". See documentation here

You can set it to as low as 5, which means any queries that are at least 5 minutes apart will need to reauthenticate and will have a new session.

CREATE [OR REPLACE] SESSION POLICY DO_NOT_IDLE SESSION_IDLE_TIMEOUT_MINS = 0

Though I believe this will affect any applications that use your account, and will make your applications need to reauthenticate every time the session expires.

Another option, if you need a window smaller than 5 minutes, is to get the sessionid and explicitely run an ABORT_SESSION after your query has finished.

which would look something like this

SEELCT SYSTEM$ABORT_SESSION(CURRENT_SESSION())
user_21178720
Related