Categories:

Information Schema , Table Functions

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.

Syntax

TASK_HISTORY(
      [ SCHEDULED_TIME_RANGE_START => <constant_expr> ]
      [, SCHEDULED_TIME_RANGE_END => <constant_expr> ]
      [, RESULT_LIMIT => <integer> ]
      [, TASK_NAME => '<string>' ] )

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

Default: 100.

TASK_NAME => string

A string specifying a task. 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.

Usage Notes

  • Returns results only for the ACCOUNTADMIN role or the task owner (i.e. the role with the OWNERSHIP privilege on the task).

  • 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 Information Schema.

  • This function can return tasks executed in the past 7 days or scheduled in 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.

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.

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.

STATE

TEXT

Status of the task: SCHEDULED, EXECUTING, SUCCEEDED, FAILED, or CANCELLED.

QUERY_TEXT

TEXT

Text of the SQL statement.

ERROR_CODE

NUMBER

Error code, if the statement returned an error.

ERROR_MESSAGE

TEXT

Error message, if the statement returned an error.

RETURN_VALUE

TEXT

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

Examples

Retrieve the 100 most recent task executions (completed, still running, or scheduled in the future) in the current database and schema:

select *
  from table(information_schema.task_history())
  order by scheduled_time;

Retrieve the execution history for tasks in the current database and schema 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:

use schema mydb.public;

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 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.