- Categories:
TASK_HISTORY¶
This table function can be used to query the history of task usage within a specified date range. The function returns the history of task usage for your entire Snowflake account or a specified task.
Note
This function returns task activity within the last 7 days or the next scheduled execution within the next 8 days.
Syntax¶
TASK_HISTORY(
[ SCHEDULED_TIME_RANGE_START => <constant_expr> ]
[, SCHEDULED_TIME_RANGE_END => <constant_expr> ]
[, RESULT_LIMIT => <integer> ]
[, TASK_NAME => '<string>' ]
[, ERROR_ONLY => { TRUE | FALSE } ] )
Arguments¶
All the arguments are optional.
SCHEDULED_TIME_RANGE_START => constant_expr
, .SCHEDULED_TIME_RANGE_END => constant_expr
Time range (in TIMESTAMP_LTZ format), within the last 7 days, in which the task execution was scheduled.
If
SCHEDULED_TIME_RANGE_END
is not specified, the function returns those tasks that have already completed, are currently running, or are scheduled in the future.If
SCHEDULED_TIME_RANGE_END
is CURRENT_TIMESTAMP, the function returns those tasks that have already completed or are currently running. Note that a task that is executed immediately prior to the current time may still be identified as scheduled.To query only those tasks that have already completed or are currently running, include
WHERE query_id IS NOT NULL
as a filter. The QUERY_ID column in the TASK_HISTORY output is populated only when a task has started running.
Note
If no start or end time is specified, the most recent tasks are returned, up to the specified RESULT_LIMIT value.
If the time range does not fall within the last 7 days, an error is returned.
RESULT_LIMIT => integer
A number specifying the maximum number of rows returned by the function.
If the number of matching rows is greater than this limit, the task executions with the most recent timestamp are returned, up to the specified limit.
Range:
1
to10000
Default:
100
.TASK_NAME => string
A case-insensitive string specifying a task. Only non-qualified task names are supported. Only executions of the specified task are returned. Note that if multiple tasks have the same name, the function returns the history for each of these tasks.
ERROR_ONLY => TRUE | FALSE
When set to TRUE, this function returns only task runs that failed or were cancelled.
Usage Notes¶
Returns results only for the ACCOUNTADMIN role, the task owner (i.e. the role with the OWNERSHIP privilege on the task) or a role with the global MONITOR EXECUTION privilege. Note that unless a role with the MONITOR EXECUTION privilege also has the USAGE privilege on the database and schema that store the task, the DATABASE_NAME and SCHEMA_NAME values in the output are NULL.
This function returns a maximum of 10,000 rows, set in the
RESULT_LIMIT
argument value. The default value is100
. To avoid this limitation, use the TASK_HISTORY view (Account Usage).Note that when the TASK_HISTORY function is queried, its task name, time range, and result limit arguments are applied first followed by the WHERE and LIMIT clause, respectively, if specified. In addition, the TASK_HISTORY function returns records in descending SCHEDULED_TIME order. Tasks that are completed (i.e. with a SUCCEEDED, FAILED, or CANCELLED state) tend to be scheduled earlier, so they are generally returned later in order in the search results.
In practice, if you have many tasks running in your account, the results returned by the function could include fewer than expected completed tasks or only scheduled tasks, especially if the RESULT_LIMIT value is relatively low. To query the history of tasks that have already run, Snowflake recommends using a combination of the
SCHEDULED_TIME_RANGE_START => constant_expr
and/orSCHEDULED_TIME_RANGE_END => constant_expr
arguments.When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.
This function can return all executions run in the past 7 days or the next scheduled execution within the next 8 days.
Output¶
The function returns the following columns:
Column Name |
Data Type |
Description |
---|---|---|
QUERY_ID |
TEXT |
ID of the SQL statement executed by the task. Can be joined with the QUERY_HISTORY view for additional details about the execution of the statement or stored procedure. |
NAME |
TEXT |
Name of the task. |
DATABASE_NAME |
TEXT |
Name of the database that contains the task. |
SCHEMA_NAME |
TEXT |
Name of the schema that contains the task. |
QUERY_TEXT |
TEXT |
Text of the SQL statement. |
CONDITION_TEXT |
TEXT |
Text of WHEN condition the task evaluates when determining whether to run. |
STATE |
TEXT |
Status of the task:
|
ERROR_CODE |
NUMBER |
Error code, if the statement returned an error. |
ERROR_MESSAGE |
TEXT |
Error message, if the statement returned an error. |
SCHEDULED_TIME |
TIMESTAMP_LTZ |
Time when the task is/was scheduled to start running. Note that we make a best effort to ensure absolute precision, but only guarantee that tasks do not execute before the scheduled time. |
QUERY_START_TIME |
TIMESTAMP_LTZ |
Time when the query in the task definition started to run, or NULL if SCHEDULED_TIME is in the future or the current scheduled run has not started yet. This timestamp aligns with the start time for the query returned by QUERY_HISTORY. |
NEXT_SCHEDULED_TIME |
TIMESTAMP_LTZ |
Time when the standalone or root task (in a DAG of tasks) is next scheduled to start running, assuming the current run of the standalone task or DAG started at the SCHEDULED_TIME time completes in time. |
COMPLETED_TIME |
TIMESTAMP_LTZ |
Time when the task completed, or NULL if SCHEDULED_TIME is in the future or if the task is still running. |
ROOT_TASK_ID |
TEXT |
Unique identifier for the root task in a DAG. This ID matches the ID column value in the SHOW TASKS output for the same task. |
GRAPH_VERSION |
NUMBER |
Integer identifying the version of the DAG that was run, or is scheduled to be run. Each incremental increase in the value represents one or more modifications to tasks in the DAG. If the root task is recreated (using CREATE OR REPLACE TASK), then the version number restarts from 1. |
RUN_ID |
NUMBER |
Time when the standalone or root task in a DAG is/was originally scheduled to start running. Format is epoch time (in milliseconds). The combination of the ROOT_TASK_ID and RUN_ID values identifies a specific run of a DAG. . Original scheduled time refers to rare instances when the system may reschedule the same task to run at a different time to retry it or rebalance the load. If that happens, RUN_ID shows the original scheduled run time and SCHEDULED_TIME shows the rescheduled run time. |
RETURN_VALUE |
TEXT |
Value set for the predecessor task in a DAG. The return value is explicitly set by calling the SYSTEM$SET_RETURN_VALUE function by the predecessor task. |
SCHEDULED_FROM |
TEXT |
Mechanism that prompted the task run: SCHEDULE indicates that the task run was initiated by the schedule in the task definition. EXECUTE TASK indicates that the task run was initiated by an EXECUTE TASK statement execution. For runs of child tasks in a DAG, the column returns the same value as the root task run. |
ATTEMPT_NUMBER |
NUMBER |
Integer representing the number of attempts to run this task. Initially one. |
QUERY_HASH |
TEXT |
The hash value computed based on the canonicalized SQL text. 1 |
QUERY_HASH_VERSION |
NUMBER |
The version of the logic used to compute |
QUERY_PARAMETERIZED_HASH |
TEXT |
The hash value computed based on the parameterized query. 1 |
QUERY_PARAMETERIZED_HASH_VERSION |
NUMBER |
The version of the logic used to compute |
- 1(1,2,3,4)
This column is present only when the 2023_06 behavior change bundle is enabled. This column is part of the query hash feature.
Examples¶
Retrieve the 100 most recent task executions (completed, still running, or scheduled in the future) in the account. Note that the maximum number of rows returned by the function is limited to 100 by default. To change the number of rows returned, modify the RESULT_LIMIT argument value:
select * from table(information_schema.task_history()) order by scheduled_time;
Retrieve the execution history for tasks in the account within a specified 30 minute block of time within the past 7 days:
select * from table(information_schema.task_history( scheduled_time_range_start=>to_timestamp_ltz('2018-11-9 12:00:00.000 -0700'), scheduled_time_range_end=>to_timestamp_ltz('2018-11-9 12:30:00.000 -0700')));
Retrieve the 10 most recent executions of a specified task (completed, still running, or scheduled in the future) scheduled within the last hour:
select * from table(information_schema.task_history( scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()), result_limit => 10, task_name=>'MYTASK'));Note
To retrieve only tasks that are completed or still running, filter the query using
WHERE query_id IS NOT NULL
. Note that this filter is applied afterRESULT_LIMIT
already reduces the results returned, so the query could return 9 tasks if 1 task was scheduled but had not started yet.