Snowflake Tasks
Today we take a look at a feature in Snowflake called Tasks. Tasks allows you to create a SQL script or procedure and schedule it to run on your Snowflake instance. The Snowflake task engine is a CRON variant and should look familiar syntactically if you are an avid Linux user. CRON or CRONTAB is the Linux version of windows task schedule. It is extremely simplified in regards to how it runs a job. It supports a few parameters and points to a .sh or other script file. The parameters, days of week and time, control the frequency of the job being run.
Snowflake has recently introduced this functionality so lets take a quick look at how to create our first task on our Snowflake instance.
First let’s create a table to store some data into. This will be the table targeted in our Snowflake task.
CREATE DATABASE MYTASKEXAMPLES;
USE DATABASE MYTASKEXAMPLES;
CREATE OR REPLACE TABLE LOGTABLE
(
LogDT datetime
)
Next, let’s create and run an insert statement.
INSERT INTO LOGTABLE(LogDT) VALUES(CURRENT_TIMESTAMP);
Now let’s create the task that will use the insert SQL statement.
CREATE OR REPLACE TASK MINUTEINSERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON * * * * * America/New_York'
TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
INSERT INTO LOGTABLE(LogDT) VALUES(CURRENT_TIMESTAMP);
This will create the task in Snowflake, but it will be initiated in a suspended state. To turn the command on we must alter the task.
ALTER TASK MINUTEINSERT RESUME;
Now all we need to do is wait and scan the table. Here we can see every minute the task has ran and inserted our data.
SELECT * FROM LOGTABLE
Turn off the task so it does not run up compute charges!
ALTER TASK MINUTEINSERT SUSPEND;
This is an extremely basic example. I will do some more complicated implementations of Snowflake tasks using Snowpipe and stored procedures soon.
You can also do preceding steps, notifications and many other nifty things with Snowflake tasks.
