1
0
During this post we will discuss an interesting use case related to TASKS. Say we have two TASKS i.e. TASK_A and TASK_B exists in our database where TASK_B is depend on TASK_A. Two procedures, PROC_A and PROC_B are coupled with these TASKS respectively. On successful completion of TASK_A we want to set some status or custom defined message in a variable. At the execution of TASK_B we need to refer the above Parent Task variable and fetch respective value which we will insert into some table for Audit purpose.
So question comes: How we can return the value from TASKS.
Snowflake has provided below two system function.
- SYSTEM$SET_RETURN_VALUE.
- GET_PREDECESSOR_RETURN_VALUE
In a DAG of tasks, a task can call SYSTEM$SET_RETURN_VALUE function to set a return value. Another task that identifies this task as the predecessor task (using the AFTER keyword in the task definition) can retrieve the return value using SYSTEM.GET_PREDECESSOR_RETURN_VALUE function.
Implementation:
To simplify this, Consider the below real time use case and the implementation.
Presently, We have a stored procedure which identifies the Long runner query in the Database. This procedure determines the query which are running more than 45 minutes and fetch the Query ID. In next part procedure kills these long runner and frees the resources. Procedure is scheduled to run via TASKS at 6 hours interval. Now Business wants if any long runner is there at the time of execution then track the time and append with some custom message. Second TASK which is dependent on this parent task should capture the msg generated by First TASK and can devise logic accordingly.
Below Stored procedure to set the variable value:
Second procedure to get the value set in Parent task:
Create two task and associates the Procedure respectively.
alter task LONG_RUNNER_TASK resume;
alter task GET_LONG_RUNNER_TASKresume;
After the execution of task see the values in table
select * from TASK_LONG_RUNNER;
