Calling a stored procedure using a task in Snowflake

Question

I wrote a task that calls the procedure every weekend.

Here is my procedure that inserts the values into a table

CREATE OR REPLACE TABLE TABLE1(DATABASE_ VARCHAR, TABLE_ VARCHAR); // Table to store values

CREATE OR REPLACE PROCEDURE TASK_()   //Procedure to insert values into table
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var str = '';
        var stmt = snowflake.createStatement({sqlText: "INSERT INTO TABLE1 VALUES ('DB1','TB1')"});
        stmt.execute(); 
        return str;
    $$;

Here is my task that calls the above procedure every weekend.

CREATE TASK mytask_hour
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 0 0 * 1-12 SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
as
  call TASK_();

But when I checked, the above task didn't run and the values were not inserted into the table.

So, I tried to debug my task and wrote a task that calls the above procedure every minute.

create task mytask_hour
  warehouse = COMPUTE_WH
  schedule = '1 minute'
as
  call TASK_();

Even this task didn't work. Don't understand where I'm doing wrong

@Marcel I checked by querying the table which gave me an empty table — R0bert
@R0bert adding one more link for troubleshooting tasks, check this docs.snowflake.com/en/user-guide/tasks-ts.html — Monem_منعم

Marcel Marcel · Accepted Answer · 2020-10-05T19:13:49

Creating a task is not enough. The next step is to resume it as tasks are having the status "suspended" by default.

In your case the statement is

ALTER TASK mytask_hour resume;

Consequence: The tasks runs to your schedule.

On top of that you have to keep in mind that

resuming/suspending a task requires OWNERSHIP or OPERATE privilege on the task
the OWNERSHIP-role has the EXECUTE TASK privilege, which can be assigned by ACCOUNT ADMIN

For more infos see here: https://docs.snowflake.com/en/sql-reference/sql/alter-task.html

Calling a stored procedure using a task in Snowflake

2 Answers