Snowflake, Tasks and Session variables problem

Question

I have a problem in Snowflake with Task that executes Stored Procedures and that SP is using a Session variable QUERY_TAG that I want to use for logging purposes. When the Task executes the SP, I'll get the error: "Session variable '$QUERY_TAG' does not exist" EXECUTE AS CALLER is there It doesn't matter where I try to set the QUERY_TAG (in the first Task precond-code or in the definition).

The Tasks and SP are created by me as SYSADMIN

When I'm executing the SP in a query editor (Snowflake, DBeaver etc) it runs fine, so no coding errors in the SP. SET QUERY_TAG = 'A nice query tag' CALL TASK_SCHEMA.SP_TASK_ONE()

This runs fine when I'm calling doing it in the Worksheet or DBeaver or similar. Both the ways in the SP works (inline SQL or by the getQueryTag function)

Here is the code for Tasks and SP

CREATE OR REPLACE TASK TASK_SCHEMA.TASK_ONE_PRECOND
    WAREHOUSE = TASK_WH
    SCHEDULE = '2 minute'
    QUERY_TAG = 'My Query Tag'
AS
    SET QUERY_TAG = 'My Query Tag 2'


CREATE OR REPLACE TASK TASK_SCHEMA.TASK_ONE
    WAREHOUSE = TASK_WH
    AFTER TASK_SCHEMA.TASK_ONE_PRECOND
AS
    CALL TASK_SCHEMA.SP_TASK_ONE()


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

function getQueryTag()
{
    var QueryTag;
    rs_QT = snowflake.execute ( { sqlText: `SELECT $QUERY_TAG;` }  );
    if( rs_QT.next())
    {
        QueryTag = rs_QT.getColumnValue(1); // get the QueryTag
    }

    return QueryTag; 
}   

    var qtag = getQueryTag();

    //rs = snowflake.execute ( { sqlText: 
    //`INSERT INTO "LOG"."TESTSESSIONLOG"
    //  ("SESSION_NAME")
    //SELECT $QUERY_TAG
    //` }  );


    snowflake.execute({
               sqlText: `INSERT INTO LOG.TESTSESSIONLOG 
               (SESSION_NAME) 
               VALUES (?)`
               ,binds: [ qtag]
    });   

    return "SESSION_OK"; 

  $$;

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-11-01T11:15:50

Edit 4 Nov 2019: My answer below is not entirely correct, there is a way to pass values between a task and its successor. See doc on SYSTEM$SET_RETURN_VALUE.

Even if you define dependencies between tasks, that doesn't mean a task inherits anything from the predecessor in the task tree.

So if you set a variable in one task, that variable is lost when the task finishes.

This is different from a normal session (like in the GUI) where the session state is preserved between the commands you execute within the session.

Between tasks, the only thing related is the end time of the predecessor and the start time of the successor(s).

When it comes to extracting the query tag, you should preferably ask the system for it:

function getQueryTag()
{
    var rs_QT = snowflake.execute ( { sqlText: `SHOW PARAMETERS LIKE 'QUERY_TAG'` }  );
    return rs_QT.next() && rs_QT.getColumnValue("value"); // get the QueryTag
}

Snowflake, Tasks and Session variables problem

1 Answers