Query Tag is not setting through task in snowflake

user_13584820 0
Viewed 388

We have a stored procedure which alter session and set query_tag parameter to some value. When this procedure is called directly through call statement the query tag is setting, whereas when this procedure is called through task, the query tag is not setting. Please suggest.

CREATE OR REPLACE TASK mytask
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '1 minute'
AS CALL dummy();

create or replace procedure dummy()
    RETURNS VARCHAR(50)
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
as $$    

snowflake.execute( {sqlText: "ALTER SESSION SET QUERY_TAG = 'execute_dummy_proc'" } );
var sql_command = "INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP())"; 
snowflake.execute ({sqlText: sql_command});    

$$;
snowflake-cloud-data-platform session parameters tags stored-procedures
2 Answers

I reused the provided code. Just changed the second query in your task for a basic select to make it more simple.

CREATE OR REPLACE TASK mytask
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 minute'
AS CALL dummy();

create or replace procedure dummy()
    RETURNS VARCHAR(50)
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
as $$    

snowflake.execute( {sqlText: "ALTER SESSION SET QUERY_TAG = 'execute_dummy_proc'" } );
var sql_command = "SELECT 1"; 
snowflake.execute ({sqlText: sql_command});     
$$;

As you can see in screenshot below, there is two consecutive runs of the task and every time the secund query (SELECT 1) is effectively tagged.

enter image description here

In Snowflake each task executes in a new session so query tag only appear after the alter session. I see nothing wrong here. Just in case you have the possibility to set your variable session in TASK creation.

For exemple

CREATE OR REPLACE TASK mytask
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 minute'
QUERY_TAG = 'mytag'
AS CALL dummy();
user_165267480

the queries run by certain role cannot be viewed in history using another role lower in hierarchy. To fix this, we gave below grant on the warehouse being used by task: GRANT MONITOR ON WAREHOUSE CUSTOM_WH TO ROLE "DEVELOPER ROLE";

After this, we were able to see queries ran by task and getting the query tag set.

user_135848200
Related