CREATE TASK¶
Creates a new task in the current/specified schema or replaces an existing task.
This command also supports the following variants:
CREATE OR ALTER TASK (creates a task if it doesn’t exist or alters an existing task)
CREATE TASK … CLONE (creates a clone of an existing task)
For information about tasks, see Introduction to tasks.
- See also:
Important
Newly created or cloned tasks are created suspended. For information about running suspended tasks, see ALTER TASK … RESUME or EXECUTE TASK.
Syntax¶
CREATE [ OR REPLACE ] TASK [ IF NOT EXISTS ] <name>
[ WITH TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ { WAREHOUSE = <string> } | { USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = <string> } ]
[ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
[ CONFIG = <configuration_string> ]
[ ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE ]
[ <session_parameter> = <value> [ , <session_parameter> = <value> ... ] ]
[ USER_TASK_TIMEOUT_MS = <num> ]
[ SUSPEND_TASK_AFTER_NUM_FAILURES = <num> ]
[ ERROR_INTEGRATION = <integration_name> ]
[ COMMENT = '<string_literal>' ]
[ FINALIZE = <string> ]
[ TASK_AUTO_RETRY_ATTEMPTS = <num> ]
[ USER_TASK_MINIMUM_TRIGGER_INTERVAL_IN_SECONDS = <num>]
[ AFTER <string> [ , <string> , ... ] ]
[ WHEN <boolean_expr> ]
AS
<sql>
Variant syntax¶
CREATE OR ALTER TASK¶
Creates a new task if it doesn’t already exist, or transforms an existing task into the task defined in the statement. A CREATE OR ALTER TASK statement follows the syntax rules of a CREATE TASK statement and has the same limitations as an ALTER TASK statement.
Supported task alterations include:
Change task properties and parameters. For example, SCHEDULE, USER_TASK_TIMEOUT_MS, or COMMENT.
Set, unset, or change task predecessors.
Set, unset, or change task condition (WHEN clause).
Change the task definition (AS clause).
For more information, see CREATE OR ALTER TASK usage notes.
CREATE OR ALTER TASK <name>
[ { WAREHOUSE = <string> } | { USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = <string> } ]
[ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
[ CONFIG = <configuration_string> ]
[ ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE ]
[ USER_TASK_TIMEOUT_MS = <num> ]
[ <session_parameter> = <value> [ , <session_parameter> = <value> ... ] ]
[ SUSPEND_TASK_AFTER_NUM_FAILURES = <num> ]
[ ERROR_INTEGRATION = <integration_name> ]
[ COMMENT = '<string_literal>' ]
[ FINALIZE = <string> ]
[ TASK_AUTO_RETRY_ATTEMPTS = <num> ]
[ AFTER <string> [ , <string> , ... ] ]
[ WHEN <boolean_expr> ]
AS
<sql>
CREATE TASK … CLONE¶
Creates a new task with the same parameter values:
CREATE [ OR REPLACE ] TASK <name> CLONE <source_task> [ ... ]
For more details, see CREATE <object> … CLONE.
Note
Cloning tasks using CREATE TASK <name> CLONE, or cloning a schema containing tasks, copies all underlying task properties unless explicitly overridden.
Required parameters¶
name
String that specifies the identifier for the task; must be unique for the schema in which the task is created.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes, such as
"My object"
. Identifiers enclosed in double quotes are also case-sensitive.For more details, see Identifier requirements.
sql
Any one of the following:
Single SQL statement
Call to a stored procedure
Procedural logic using Snowflake Scripting
Snowsight doesn’t support creating or modifying tasks to use Snowflake Scripting. Instead, use SnowSQL or another command-line client.
The SQL code is executed when the task runs. Verify that the
{sql}
executes as expected before using it in a task.Note
Serverless tasks support the use of Java or Python external functions. However, a serverless task can’t invoke the following object types and functions developed inside of Snowflake:
UDFs (user-defined functions) that contain Java or Python code.
Stored procedures written in Scala (using Snowpark), or which call UDFs that contain Java or Python code.
Optional parameters¶
WAREHOUSE = string
or . USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = string
WAREHOUSE = string
Specifies the virtual warehouse that provides compute resources for task runs.
Omit this parameter to use serverless compute resources for runs of this task. Snowflake automatically resizes and scales serverless compute resources as required for each workload. When a schedule is specified for a task, Snowflake adjusts the resource size to complete future runs of the task within the specified time frame. To specify the initial warehouse size for the task, set the
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = string
parameter.USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = string
Applied only to serverless tasks.
Specifies the size of the compute resources to provision for the first run of the task, before a task history is available for Snowflake to determine an ideal size. Once a task has successfully completed a few runs, Snowflake ignores this parameter setting.
Note that if the task history is unavailable for a given task, the compute resources revert to this initial size.
Note
If a
WAREHOUSE = string
parameter value is specified, then setting this parameter produces a user error.The size is equivalent to the compute resources available when creating a warehouse (using CREATE WAREHOUSE), such as
SMALL
,MEDIUM
, orLARGE
. The largest size supported by the parameter isXXLARGE
. If the parameter is omitted, the first runs of the task are executed using a medium-sized (MEDIUM
) warehouse.You can change the initial size (using ALTER TASK) after the task is created but before it has run successfully once. Changing the parameter after the first run of this task starts has no effect on the compute resources for current or future task runs.
Note that suspending and resuming a task doesn’t remove the task history used to size the compute resources. The task history is only removed if the task is recreated (using the CREATE OR REPLACE TASK syntax).
For more information about this parameter, see USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE.
SCHEDULE ...
Specifies the schedule for periodically running the task:
Note
For Triggered Tasks, a schedule is not required. For other tasks, a schedule must be defined for a standalone task or the root task in a task graph; otherwise, the task only runs if manually executed using EXECUTE TASK.
A schedule cannot be specified for child tasks in a task graph.
USING CRON expr time_zone
Specifies a cron expression and time zone for periodically running the task. Supports a subset of standard cron utility syntax.
For a list of time zones, see the list of tz database time zones (in Wikipedia).
The cron expression consists of the following fields:
# __________ minute (0-59) # | ________ hour (0-23) # | | ______ day of month (1-31, or L) # | | | ____ month (1-12, JAN-DEC) # | | | | _ day of week (0-6, SUN-SAT, or L) # | | | | | # | | | | | * * * * *
The following special characters are supported:
*
Wildcard. Specifies any occurrence of the field.
L
Stands for “last”. When used in the day-of-week field, it allows you to specify constructs such as “the last Friday” (“5L”) of a given month. In the day-of-month field, it specifies the last day of the month.
/n
Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if
4/3
is specified in the month field, then the task is scheduled for April, July, and October, which is every 3 months, starting with the 4th month of the year. The same schedule is maintained in subsequent years. That is, the task is not scheduled to run in January (3 months after the October run).
Note
The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the task.
The cron expression defines all valid run times for the task. Snowflake attempts to run a task based on this schedule; however, any valid run time is skipped if a previous run hasn’t completed before the next valid run time starts.
When both a specific day of month and day of week are included in the cron expression, then the task is scheduled on days satisfying either the day of month or day of week. For example,
SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC'
schedules a task at 0AM on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.The shortest granularity of time in cron is minutes. If a task is resumed during the minute defined in its cron expression, the first scheduled run of the task is the next occurrence of the instance of the cron expression. For example, if task scheduled to run daily at midnight (
USING CRON 0 0 * * *
) is resumed at midnight plus 5 seconds (00:00:05
), the first task run is scheduled for the following midnight.
num MINUTE
Specifies an interval (in minutes) of wait time inserted between runs of the task. Accepts positive integers only.
Also supports
num M
syntax.To avoid ambiguity, a base interval time is set when:
When the task is resumed (using ALTER TASK … RESUME) or
When a different interval is set (using ALTER TASK … SET SCHEDULE)
The base interval time starts the interval counter from the current clock time. For example, if an INTERVAL value of
10
is set and the task is enabled at 9:03 AM, then the task runs at 9:13 AM, 9:23 AM, and so on. Note that we make a best effort to ensure absolute precision, but only guarantee that tasks don’t execute before their set interval occurs. For instance, in the current example, the task could first run at 9:14 AM, but won’t run at 9:12 AM.Note
The maximum supported value is
11520
(8 days). Tasks that have a greaternum MINUTE
value never run.
CONFIG = configuration_string
Specifies a string representation of key value pairs that can be accessed by all tasks in the task graph. Must be in JSON format. For more information about getting the configuration string for the task that is currently running, see SYSTEM$GET_TASK_GRAPH_CONFIG.
Note
This parameter can only be set on a root task. The setting applies to all tasks in the task graph.
The parameter can be set on standalone tasks but doesn’t affect the task behavior. Snowflake ensures only one instance of a standalone task is running at a given time.
ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE
Specifies whether to allow multiple instances of the task graph to run concurrently.
Note
This parameter can only be set on a root task. The setting applies to all tasks in the task graph.
The parameter can be set on standalone tasks but doesn’t affect the task behavior. Snowflake ensures only one instance of a standalone task is running at a given time.
TRUE
If the next scheduled run of the root task occurs while the current run of any child task is still in operation, another instance of the task graph begins. If a root task is still running when the next scheduled run time occurs, then that scheduled time is skipped.FALSE
The next run of a root task is scheduled only after all child tasks in the task graph have finished running. This means that if the cumulative time required to run all tasks in the task graph exceeds the explicit scheduled time set in the definition of the root task, at least one run of the Task Graph is skipped.
Default:
FALSE
session_parameter = value [ , session_parameter = value ... ]
Specifies a comma-separated list of session parameters to set for the session when the task runs. A task supports all session parameters. For the complete list, see Session parameters.
USER_TASK_TIMEOUT_MS = num
Specifies the time limit on a single run of the task before it times out (in milliseconds).
Note
Before you increase the time limit on a task significantly, consider whether the SQL statement initiated by the task could be optimized (either by rewriting the statement or using a stored procedure) or the warehouse size should be increased.
In some situations, the parameter STATEMENT_TIMEOUT_IN_SECONDS has higher precedence than USER_TASK_TIMEOUT_MS. For details, see STATEMENT_TIMEOUT_IN_SECONDS.
For more information about this parameter, see USER_TASK_TIMEOUT_MS.
Values:
0
-86400000
(1 day).Default:
3600000
(1 hour)SUSPEND_TASK_AFTER_NUM_FAILURES = num
Specifies the number of consecutive failed task runs after which the current task is suspended automatically. Failed task runs include runs in which the SQL code in the task body either produces a user error or times out. Task runs that are skipped, canceled, or that fail due to a system error are considered indeterminate and aren’t included in the count of failed task runs.
Set the parameter on a standalone task or the root task in a task graph. When the parameter is set to a value greater than
0
, the following behavior applies to runs of the standalone task or task graph:Standalone tasks are automatically suspended after the specified number of consecutive task runs either fail or time out.
The root task is automatically suspended after the run of any single task in a task graph fails or times out the specified number of times in consecutive runs.
When the parameter is set to
0
, failed tasks aren’t automatically suspended.The setting applies to tasks that rely on either serverless compute resources or virtual warehouse compute resources.
For more information about this parameter, see SUSPEND_TASK_AFTER_NUM_FAILURES.
Values:
0
- No upper limit.Default:
10
ERROR_INTEGRATION = 'integration_name'
Required only when configuring a task to send error notifications using Amazon Simple Notification Service (SNS), Microsoft Azure Event Grid, or Google Pub/Sub.
Specifies the name of the notification integration used to communicate with Amazon SNS, MS Azure Event Grid, or Google Pub/Sub. For more information, refer to Enabling error notifications for tasks.
COMMENT = 'string_literal'
Specifies a comment for the task.
Default: No value
AFTER string [ , string , ... ]
Specifies one or more predecessor tasks for the current task. Use this option to create a task graph or add this task to an existing task graph. A task graph is a series of tasks that starts with a scheduled root task and is linked together by dependencies.
Note that the structure of a task graph can be defined after all of its component tasks are created. Execute ALTER TASK … ADD AFTER statements to specify the predecessors for each task in the planned task graph.
A task runs after all of its predecessor tasks have finished their own runs successfully (after a brief lag).
Note
The root task should have a defined schedule. Each child task must have one or more defined predecessor tasks, specified using the
AFTER
parameter, to link the tasks together.A single task is limited to 100 predecessor tasks and 100 child tasks. In addition, a task graph is limited to a maximum of 1000 tasks total (including the root task) in either a resumed or suspended state.
Accounts are currently limited to a maximum of 30000 resumed tasks.
All tasks in a task graph must have the same task owner. A single role must have the OWNERSHIP privilege on all of the tasks in the task graph.
All tasks in a task graph must exist in the same schema.
The root task must be suspended before any task is recreated (using the CREATE OR REPLACE TASK syntax) or a child task is added (using CREATE TASK … AFTER or ALTER TASK … ADD AFTER) or removed (using ALTER TASK … REMOVE AFTER).
If any task in a task graph is cloned, the role that clones the task becomes the owner of the clone by default.
If the owner of the original task creates the clone, then the task clone retains the link between the task and the predecessor task. This means the same predecessor task triggers both the original task and the task clone.
If another role creates the clone, then the task clone can have a schedule but not a predecessor.
Current limitations:
Snowflake guarantees that at most one instance of a task with a defined schedule is running at a given time; however, we cannot provide the same guarantee for tasks with a defined predecessor task.
WHEN boolean_expr
Specifies a Boolean SQL expression; multiple conditions joined with AND/OR are supported. When a task is triggered (based on its
SCHEDULE
orAFTER
setting), it validates the conditions of the expression to determine whether to execute. If the conditions of the expression are not met, then the task skips the current run. Any tasks that identify this task as a predecessor also don’t run.The following are supported in a task WHEN clause:
SYSTEM$STREAM_HAS_DATA is supported for evaluation in the SQL expression.
This function indicates whether a specified stream contains change tracking data. You can use this function to evaluate whether the specified stream contains change data before starting the current run. If the result is FALSE, then the task doesn’t run.
Note
SYSTEM$STREAM_HAS_DATA is designed to avoid returning a FALSE value even when the stream contains change data. However, this function isn’t guaranteed to avoid returning a TRUE value when the stream contains no change data.
SYSTEM$GET_PREDECESSOR_RETURN_VALUE is supported for evaluation in the SQL expression.
This function retrieves the return value for the predecessor task in a task graph. The return value can be used as part of a boolean expression. When using SYSTEM$GET_PREDECESSOR_RETURN_VALUE, you can cast the returned value to the appropriate numeric, string, or boolean type if required.
Simple examples include:
WHEN NOT SYSTEM$GET_PREDECESSOR_RETURN_VALUE('task_name')::BOOLEAN
WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('task_name') != 'VALIDATION'
WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('task_name')::FLOAT < 0.2
Note
Use of PARSE_JSON in TASK … WHEN expressions isn’t supported as it requires warehouse based compute resources.
Boolean operators such as AND, OR, NOT, and others.
Casts between numeric, string and boolean types.
Comparison operators such as equal, not equal, greater than, less than, and others.
Validating the conditions of the WHEN expression does not require compute resources. The validation is instead processed in the cloud services layer. A nominal charge accrues each time a task evaluates its WHEN condition and doesn’t run. The charges accumulate each time the task is triggered until it runs. At that time, the charge is converted to Snowflake credits and added to the compute resource usage for the task run.
Generally the compute time to validate the condition is insignificant compared to task execution time. As a best practice, align scheduled and actual task runs as closely as possible. Avoid task schedules that don’t align with task runs. For example, if data is inserted into a table with a stream roughly every 24 hours, don’t schedule a task that checks for stream data every minute. The charge to validate the WHEN expression with each run is generally insignificant, but the charges are cumulative.
Note that daily consumption of cloud services that falls below the 10% quota of the daily usage of the compute resources accumulates no cloud services charges.
TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )
Specifies the tag name and the tag string value.
The tag value is always a string, and the maximum number of characters for the tag value is 256.
For information about specifying tags in a statement, see Tag quotas for objects and columns.
This parameter is not supported by the CREATE OR ALTER variant syntax.
FINALIZE = string
Specifies the name of a root task that the finalizer task is associated with. Finalizer tasks run after all other tasks in the task graph run to completion. You can define the SQL of a finalizer task to handle notifications and the release and cleanup of resources that a task graph uses. For more information, see Release and cleanup of task graphs.
A root task can only have one finalizer task. If you create multiple finalizer tasks for a root task, the task creation will fail.
A finalizer task cannot have any child tasks. Any command attempting to make the finalizer task a predecessor will fail.
A finalizer task cannot have a schedule. Creating a finalizer task with a schedule will fail.
Default: No value
TASK_AUTO_RETRY_ATTEMPTS = num
Specifies the number of automatic task graph retry attempts. If any task graphs complete in a FAILED state, Snowflake can automatically retry the task graphs from the last task in the graph that failed.
The automatic task graph retry is disabled by default. To enable this feature, set TASK_AUTO_RETRY_ATTEMPTS to a value greater than
0
on the root task of a task graph.Note that this parameter must be set to the root task of a task graph. If it’s set to a child task, an error will be returned.
Values:
0
-30
.Default:
0
USER_TASK_MINIMUM_TRIGGER_INTERVAL_IN_SECONDS = num
Defines how frequently a task can execute in seconds. If data changes occur more often than the specified minimum, changes will be grouped and processed together.
The task will run every 12 hours even if this value is set to more than 12 hours.
Values: Minimum
15
, maximum604800
.Default:
30
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
EXECUTE MANAGED TASK |
Account |
Required only for tasks that rely on serverless compute resources for runs. |
CREATE TASK |
Schema |
|
USAGE |
Warehouse |
Required only for tasks that rely on user-managed warehouses for runs. |
OWNERSHIP |
Task |
Required only when executing a CREATE OR ALTER TASK statement for an existing task. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). |
Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
Tasks run using the task owner’s privileges. For the list of minimum required privileges to run tasks, see Task security.
Run the SQL statement or call the stored procedure, as the task owner role, before you include it in a task definition to ensure the role has the required privileges on objects referenced by the SQL or stored procedure.
For serverless tasks:
Serverless compute resources for a task can range from the equivalent of
XSMALL
toXXLARGE
in warehouse sizes. To request a size increase, contact Snowflake Support.Individual tasks in a task graph can use serverless or user-managed compute resources. Using the serverless compute for all tasks in the task graph isn’t required.
If a task fails with an unexpected error, you can receive a notification about the error. For more information on configuring task error notifications, see Enabling error notifications for tasks.
By default, a DML statement executed without explicitly starting a transaction is automatically committed on success or rolled back on failure at the end of the statement. This behavior is called autocommit and is controlled with the AUTOCOMMIT parameter. This parameter must be set to TRUE. If the AUTOCOMMIT parameter is set to FALSE at the account level, then set the parameter to TRUE for the individual task (using ALTER TASK … SET AUTOCOMMIT = TRUE); otherwise, any DML statement executed by the task fails.
Only one task should consume data from a stream. Create multiple streams for the same table to be consumed by more than one task. When a task consumes the data in a stream using a DML statement, the stream advances the offset and change data is no longer available for the next task to consume.
When you use the
CREATE OR REPLACE
syntax, Snowflake drops the existing task and recreates it using the specified definition. Consider the following behaviors when you replace a task:The recreated task is suspended by default.
Any ongoing task run is completed. To stop a task run, use the SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS function.
If a standalone or root task is recreated, the next scheduled run of the task is cancelled.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
Tasks with large definitions can cause errors. If you experience an error due to task size, try using stored procedure or making your task definition less complex.
Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
CREATE OR ALTER TASK usage notes¶
All limitations of the ALTER TASK command apply.
A task cannot be resumed or suspended using the CREATE OR ALTER TASK command. To resume or suspend a task, use the ALTER TASK command.
Setting or unsetting a tag is not supported; however existing tags are not altered by a CREATE OR ALTER statement and remain unchanged.
Examples¶
Single SQL statement¶
Create a serverless task that queries the current timestamp every hour starting at 9 AM and ending at 5 PM on Sundays (America/Los_Angeles time zone).
The initial warehouse size is XSMALL:
CREATE TASK t1
SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AS
SELECT CURRENT_TIMESTAMP;
Same as the previous example, but the task relies on a user-managed warehouse to provide the compute resources for runs:
CREATE TASK mytask_hour
WAREHOUSE = mywh
SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
AS
SELECT CURRENT_TIMESTAMP;
Additional timing examples:
SCHEDULE Value |
Description |
---|---|
|
Every minute. UTC time zone. |
|
Every 5 minutes. UTC time zone. |
|
Every night at 2 AM. UTC time zone. |
|
Twice daily, at 5 AM and 5 PM (at the top of the hour). UTC time zone. |
|
In June, on the last day of the month, at 2:30 AM. UTC time zone. |
Create a serverless task that inserts the current timestamp into a table every hour. The task sets the TIMESTAMP_INPUT_FORMAT parameter for the session in which the task runs. This session parameter specifies the format of the inserted timestamp:
CREATE TASK t1
SCHEDULE = '60 MINUTE'
TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AS
INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP);
Create a task that inserts the current timestamp into a table every 5 minutes:
CREATE TASK mytask_minute
WAREHOUSE = mywh
SCHEDULE = '5 MINUTE'
AS
INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP);
Create a task that inserts change tracking data for INSERT operations from a stream into a table every 5 minutes. The task polls the
stream using the SYSTEM$STREAM_HAS_DATA function to determine whether change data exists and, if the result is FALSE
, skips the
current run:
CREATE TASK mytask1
WAREHOUSE = mywh
SCHEDULE = '5 minute'
WHEN
SYSTEM$STREAM_HAS_DATA('MYSTREAM')
AS
INSERT INTO mytable1(id,name) SELECT id, name FROM mystream WHERE METADATA$ACTION = 'INSERT';
Create a serverless child task in a task graph and add multiple predecessor tasks. The child task runs only after all specified predecessor tasks have successfully completed their own runs.
Suppose that the root task for a task graph is task1
and that task2
, task3
, and task4
are child tasks of task1
. This example adds child task task5
to the task graph and specifies
task2
, task3
, and task4
as predecessor tasks:
-- Create task5 and specify task2, task3, task4 as predecessors tasks.
-- The new task is a serverless task that inserts the current timestamp into a table column.
CREATE TASK task5
AFTER task2, task3, task4
AS
INSERT INTO t1(ts) VALUES(CURRENT_TIMESTAMP);
Stored procedure¶
Create a task named my_copy_task
that calls a stored procedure to unload data from the mytable
table to the named mystage
stage (using COPY INTO <location>) every hour:
-- Create a stored procedure that unloads data from a table
-- The COPY statement in the stored procedure unloads data to files in a path identified by epoch time (using the Date.now() method)
CREATE OR REPLACE PROCEDURE my_unload_sp()
returns string not null
language javascript
AS
$$
var my_sql_command = ""
var my_sql_command = my_sql_command.concat("copy into @mystage","/",Date.now(),"/"," from mytable overwrite=true;");
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();
return my_sql_command; // Statement returned for info/debug purposes
$$;
-- Create a task that calls the stored procedure every hour
CREATE TASK my_copy_task
WAREHOUSE = mywh
SCHEDULE = '60 minute'
AS
CALL my_unload_sp();
Multiple SQL statements using SnowSQL¶
Create a task that executes multiple SQL statements. In this example, the task modifies the TIMESTAMP_OUTPUT_FORMAT for the session and then queries the CURRENT_TIMESTAMP function.
Note
The SQL code in the task definition includes multiple statements. To execute the CREATE TASK statement, you must temporarily set a
character other than a semicolon as the delimiter for SQL statements; otherwise, the CREATE TASK statement would return a user
error. The command to change the SQL delimiter in SnowSQL is !set sql_delimiter = <character>
.
!set sql_delimiter=/
CREATE OR REPLACE TASK test_logging
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE = 'USING CRON 0 * * * * America/Los_Angeles'
AS
BEGIN
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SELECT CURRENT_TIMESTAMP;
END;/
!set sql_delimiter=";"
Procedural logic using Snowflake Scripting¶
Create a task that declares a variable, uses the variable, and returns the value of the variable every 2 minutes:
CREATE TASK t1
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE = '2 minute'
AS
EXECUTE IMMEDIATE
$$
DECLARE
radius_of_circle float;
area_of_circle float;
BEGIN
radius_of_circle := 3;
area_of_circle := pi() * radius_of_circle * radius_of_circle;
return area_of_circle;
END;
$$;
Root task with configuration¶
Create a task that specifies configuration, and then reads that configuration.
CREATE OR REPLACE TASK root_task_with_config
WAREHOUSE=mywarehouse
SCHEDULE='10 m'
CONFIG=$${"output_dir": "/temp/test_directory/", "learning_rate": 0.1}$$
AS
BEGIN
LET OUTPUT_DIR STRING := SYSTEM$GET_TASK_GRAPH_CONFIG('output_directory')::string;
LET LEARNING_RATE DECIMAL := SYSTEM$GET_TASK_GRAPH_CONFIG('learning_rate:v1')::DECIMAL;
...
END;
Finalizer task¶
Create a finalizer task, associated with the root task of a task graph, that sends an email alert after task completion. For more information about finalizer tasks, see Release and cleanup of task graphs.
CREATE TASK finalize_task
WAREHOUSE = my_warehouse
FINALIZE = my_root_task
AS
CALL SYSTEM$SEND_EMAIL(
'my_email_int',
'first.last@example.com, first2.last2@example.com',
'Email Alert: Task A has finished.',
'Task A has successfully finished.\nStart Time: 10:10:32\nEnd Time: 12:15:45\nTotal Records Processed: 115678'
);
Triggered task¶
Create a triggered task, associated with a stream, that inserts data from the specified stream into the table every time there is new data in the stream. For more information, see Triggered Tasks.
CREATE TASK triggeredTask WAREHOUSE = my_warehouse
WHEN system$stream_has_data('my_stream')
AS
INSERT INTO my_downstream_table
SELECT * FROM my_stream;
ALTER TASK triggeredTask RESUME;
Create and alter a simple task using the CREATE OR ALTER TASK command¶
Create a task my_task
to execute every hour in warehouse my_warehouse
:
CREATE OR ALTER TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = '60 minute'
AS
SELECT PI();
Alter task my_task
to execute after task my_other_task
and update the task definition:
CREATE OR ALTER TASK my_task
WAREHOUSE = regress
AFTER my_other_task
AS
SELECT 2 * PI();