What Is Task In Snowflake

What Is Task In Snowflake

Summary

Why every snowflake developer must know about task object in snowflake.

What Does Task Do In Snowflake

Snowflake Tasks & Task Tree are two important components in snowflake to automate your SQL script as well as automate your workflow. Using task and task tree, you can achive lot of automation and data movement from ingestion to consumption layer. These two features are snowflake native components and works very well if designed correctly. This episode is a quick 30min introduction as well as feature elaboration to the task objects and it will help you to answer the following questions

  1. How snowflake task works?
  2. How snowflake task tree works?
  3. What are the limitations of snowflake task objects?
  4. Snowflake Task Example to understand how task DDL works?
  5. Task & Task Tree Limitations?

Task & Task Tree Tutorial

You can watch Chapter-18 video tutorial which covers the snowflake task and task tree.

SQL Used in Task & Task Tree Chapter

use role sysadmin;
use warehouse compute_wh;
use database tipsdb;
use schema ch18;


-- lets create a customer table called customer_dim
create or replace table customer_dim(
        id number,
        first_name varchar,
        last_name varchar, 
        date_of_birth date,
        active_flag boolean,
        city varchar,
        insert_time timestamp default current_timestamp()
)
comment = 'my customer dimension table';
;

select * from customer_dim;
-- lets create a sequence object
create or replace sequence customer_seq
  start 2 
  increment 2
  comment = 'my demo sequence for customer dimension table';

-- check the sequence object
select customer_seq.nextval;

-- now create task named task_01
create or replace task task_01
    warehouse = compute_wh
    schedule = '1 minute'
        as
   insert into customer_dim (id,first_name,last_name,date_of_birth,active_flag,city) 
   values (customer_seq.nextval,'F-Name','L-Name',current_date(),TRUE,'My-City');

-- You can assign execute task to any role and 
-- that role can alter the task to resume or suspend
use role accountadmin;
grant execute task, execute managed task on account to role sysadmin;

-- We can alter task and change attributes associated with it.
alter task task_01 set warehouse  = 'etl_wh';
alter task task_01 set schedule  = '2 minute';

-- lets clone the task and it works
create or replace task task_clone clone task_01;
-- the task will be suspended and it has to be resumed

How to interact with task - SQL Example

-- Lets show tasks
show tasks;
desc task task_01;

Task Scheduling

create or replace task task_seconds
    warehouse = compute_wh
    schedule = '60 second'
        as
insert into customer_dim values (customer_seq.nextval,'F-Name','L-Name',current_date(),TRUE,'My-City');

create or replace task task_hours
    warehouse = compute_wh
    schedule = '1 hour'
        as
insert into customer_dim values (customer_seq.nextval,'F-Name','L-Name',current_date(),TRUE,'My-City');

create or replace task task_half_min
    warehouse = compute_wh
    schedule = '0.5 minute'
        as
insert into customer_dim values (customer_seq.nextval,'F-Name','L-Name',current_date(),TRUE,'My-City');

create or replace task task_with_minus
    warehouse = compute_wh
    schedule = '-1 minute'
        as
insert into customer_dim values (customer_seq.nextval,'F-Name','L-Name',current_date(),TRUE,'My-City');

Task History Table Function - SQL Example

-- How to check if it is running
-- task_history() is a table function
select *  from table(information_schema.task_history()) where name ='TASK_01' order by scheduled_time;

Calling Stored Procedure from Task - SQL Example


/* =======================================
 Change the context via SQL statements
 ========================================== */
--set the context    
use role sysadmin;
use warehouse compute_wh;
use database tipsdb;
use schema ch18;

-- creating a customer table, having a column called task name
create or replace table customer_sproc(
        id integer autoincrement,
        task_name varchar,
        insert_time timestamp default current_timestamp(),
        first_name varchar,
        last_name varchar, 
        date_of_birth date,
        active_flag boolean,
        city varchar

) comment = 'my customer table for task with stored proc demo';

-- here is my stored procedure with single insert statement.
create or replace procedure insert_row_in_customer_sproc()
  returns boolean 
  language javascript
  execute as caller
  as
  $$
    sql_insert_command = `insert into customer_sproc (level,first_name,last_name,date_of_birth,active_flag,city)   
                        values ('TASK_WITH_SPROC','F-Name','L-Name',current_date(),TRUE,'My-City');` 
    result_insert = snowflake.execute(
    { 
      sqlText: sql_insert_command   
    }); 
    return result_insert;
  $$
;

-- call stored procedure to check if it runs 
call insert_row_in_customer_sproc();

-- task calling a stored procedure
create or replace task task_with_sproc
    warehouse = compute_wh
    schedule = '1 minute'
        as
    call insert_row_in_customer_sproc();

-- resume the task so it becomes functional
alter task task_with_sp resume;

-- check how task entry looks like
show tasks like 'TASK_WITH_SPROC';

-- lets check task history table function
select *  from table(information_schema.task_history()) 
    where name ='TASK_WITH_SPROC' 
    order by scheduled_time;