Categories:

Information Schema , Table Functions

TASK_HISTORY

You can use this table function 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, a specified task, or task graph.

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 } ]
      [, ROOT_TASK_ID => '<string>'] )
Copy

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 the time range does not fall within the last 7 days, an error is returned.

  • 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 before the current time might 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.

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 to 10000

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.

ROOT_TASK_ID =>string

Unique identifier for the root task in a task graph. This ID matches the ID column value in the SHOW TASKS output for the same task. Specify the ROOT_TASK_ID to show the history of the root task and any child tasks that are part of the task graph.

Usage Notes

  • This function returns results only for the ACCOUNTADMIN role, the task owner, 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 is 100. 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 in a SUCCEEDED, FAILED, or CANCELLED state are usually scheduled earlier, so they are generally returned later 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. To query the history of tasks that have already run, use a combination of the SCHEDULED_TIME_RANGE_START => constant_expr and SCHEDULED_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 information, 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.

  • Tasks run during a cloud services failure might appear as duplicate entries in the results of this function. During a cloud services failure, Snowflake might rerun a task causing the task to have two UUIDs with different task SCHEDULED_TIME. TASK_HISTORY view only displays the final UUID of the rerun task.

  • All tasks in a task graph run show the same task history output.

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:

  • SCHEDULED: scheduled for execution.

  • EXECUTING: currently executing.

  • SUCCEEDED: execution successful.

  • FAILED: execution failed.

  • FAILED_AND_AUTO_SUSPENDED: task failed, and was automatically suspended.

  • CANCELLED: execution cancelled.

  • SKIPPED: indicates that a task run began, but the optional WHEN parameter in the task definition returned a FALSE value; therefore, the run did not resume the warehouse (if the task uses customer-managed compute resources) or execute the SQL code in the task definition.

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 Task graphs of tasks) is next scheduled to start running, assuming the current run of the standalone task or task graph 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 task graph. 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 task graph that was run, or is scheduled to be run. Each incremental increase in the value represents one or more modifications to tasks in the task graph. 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 Task graphs is/was originally scheduled to start running. Format is epoch time (in milliseconds). . . 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. . . Note that RUN_ID may not be a unique identifier for the current task/graph run prior to retry. You may use GRAPH_RUN_GROUP_ID column as a replacement for RUN_ID.

RETURN_VALUE

TEXT

Value set for the predecessor task in a task graph. The return value is explicitly set by calling the SYSTEM$SET_RETURN_VALUE function by the predecessor task.

SCHEDULED_FROM

TEXT

One of:

  • SCHEDULE: The task was scheduled to run normally, as described in SCHEDULE or AFTER clauses of CREATE TASK.

  • EXECUTE TASK: The task was scheduled to run using EXECUTE TASK.

  • MANUAL RETRY: The task was scheduled to run using EXECUTE TASK … RETRY LAST.

For runs of child tasks in a task graph, 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.

CONFIG

TEXT

Displays the graph level configuration if set for the root task, otherwise displays NULL.

QUERY_HASH

TEXT

The hash value computed based on the canonicalized SQL text.

QUERY_HASH_VERSION

NUMBER

The version of the logic used to compute QUERY_HASH.

QUERY_PARAMETERIZED_HASH

TEXT

The hash value computed based on the parameterized query.

QUERY_PARAMETERIZED_HASH_VERSION

NUMBER

The version of the logic used to compute QUERY_PARAMETERIZED_HASH.

GRAPH_RUN_GROUP_ID

NUMBER

Identifier for the graph run. When a graph run has multiple task runs, each task run will show the same GRAPH_RUN_GROUP_ID. The combination of GRAPH_RUN_GROUP_ID, and ATTEMPT_NUMBER can be used to uniquely identify a graph run.

BACKFILL_INFO

OBJECT

Reserved for future use. The returned value for all rows is NULL.

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:

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
  ORDER BY SCHEDULED_TIME;
Copy

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')));
Copy

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'));
Copy

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 after RESULT_LIMIT already reduces the results returned, so the query could return 9 tasks if 1 task was scheduled but had not started yet.

Retrieve the execution history of all tasks in the task graph of the specified root task.

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(ROOT_TASK_ID=>'d4b89013-c942-465c-bcb8-e7037a932b04'));
Copy

Retrieve the execution history of all tasks in the task graph of the most recently queried root task:

DESC TASK my_task
SET task_id=(SELECT "id" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));
SELECT *
  FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(ROOT_TASK_ID=>$task_id));
Copy