Optimizing Snowflake (4): monitoring and debugging stored procedures using query tagging

Johan

— 2 min read
Optimizing Snowflake (4): monitoring and debugging stored procedures using query tagging

When you run Snowflake stored procedures, debugging is not the easiest thing. This is my example stored procedure, dynamically dropping tables:

create or replace procedure groupby.public.droptables(message VARCHAR) 
RETURNS VARCHAR NOT NULL 
LANGUAGE SQL 
AS 
$$ 
DECLARE   
v_sql_query VARCHAR; 
c_table CURSOR FOR SELECT TABLE_NAME FROM groupby.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'; 
 
BEGIN 
v_sql_query := ''; 
     
FOR record IN c_table DO 
    v_sql_query := 'DROP TABLE GROUPBY.PUBLIC.' || record.TABLE_NAME; 
    EXECUTE IMMEDIATE v_sql_query; 
END FOR;     
 
RETURN 'completed'; 
END; 
$$; 
 
call groupby.public.droptables('');

Looking at the query history, the call to the stored procedure is only the call itself:

The statements that are executed from the stored procedure are also visible in the query history. In my example — an isolated trial account with only one user — it is pretty easy to find:

But what happens when you run inside a production environment, with thousands of queries executed, how can we then trace & debug this stored procedure from the query history. One approach is to use query tagging.

We are going to add a line to the stored procedure:

ALTER SESSION SET QUERY_TAG = 'droptables';

When we now run the create procedure statement, we will get an error:

“Uncaught exception of type ‘STATEMENT_ERROR’ on line 7 at position 0 : Stored procedure execution error: Unsupported statement type ‘ALTER_SESSION’.”

The reason for this: the default “EXECUTE AS” setting for a stored procedure is EXECUTE AS OWNER. This setting will cause the above error, when we change the procedure to EXECUTE AS CALLER this will be fixed.

For a good explanation of the difference between EXECUTE AS CALLER/OWNER, I recommend this article: link.

The code for the stored procedure will now become:

create or replace procedure groupby.public.droptables(message VARCHAR) 
RETURNS VARCHAR NOT NULL 
LANGUAGE SQL 
EXECUTE AS CALLER 
AS 
$$ 
DECLARE   
v_sql_query VARCHAR; 
c_table CURSOR FOR SELECT TABLE_NAME FROM groupby.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'; 
 
BEGIN 
ALTER SESSION SET QUERY_TAG = 'droptables'; 
v_sql_query := ''; 
     
FOR record IN c_table DO 
    v_sql_query := 'DROP TABLE GROUPBY.PUBLIC.' || record.TABLE_NAME; 
    EXECUTE IMMEDIATE v_sql_query; 
END FOR;     
 
RETURN 'completed'; 
END; 
$$;

When executing the stored procedure, this will be the result in the query history:

The SET QUERY_TAG itself will not have a tag, but all commands executed with the call, which resembles a Snowflake session, have the tag. We can then use the filter option in the query history screen to see all statements:

P.S. do you see the dark screen mode in Snowflake. Just stumbled upon it today:

Read more