Categories:
Information Schema , Table Functions

WAREHOUSE_LOAD_HISTORY

This table function can be used to query the activity history (defined as the “query load”) for a single warehouse within a specified date range.

Note

This function returns warehouse activity within the last 14 days.

See also:
WAREHOUSE_METERING_HISTORY

Syntax

WAREHOUSE_LOAD_HISTORY(
      [ DATE_RANGE_START => <constant_expr> ]
      [, DATE_RANGE_END => <constant_expr> ]
      [, WAREHOUSE_NAME => '<string>' ] )

Arguments

All the arguments are optional.

DATE_RANGE_START => constant_expr , . DATE_RANGE_END => constant_expr

The date range, within the last 14 days, for which to retrieve warehouse load history data:

  • If an end date is not specified, then CURRENT_DATE is used as the end of the range.
  • If a start date is not specified, then the range starts 10 minutes prior to the start of DATE_RANGE_END (i.e. the default is to show the previous 10 minutes of load history). For example, if DATE_RANGE_END is CURRENT_DATE, then the default DATE_RANGE_START is 11:50 PM on the previous day.

If the range falls outside the last 15 days, an error is returned.

Note

If the selected period is less than 8 hours, load is shown in 5-second intervals; otherwise, 5-minute intervals are used.

WAREHOUSE_NAME => 'string'

The name of the warehouse to retrieve usage load history for. Note that the warehouse name must be enclosed in single quotes. Also, if the warehouse name contains any spaces, mixed-case characters, or special characters, the name must be double-quoted within the single quotes (e.g. '"My Warehouse"' vs 'mywarehouse').

Default: CURRENT_WAREHOUSE

Usage Notes

  • Returns results only for the ACCOUNTADMIN role or any role that has been explicitly granted the MONITOR USAGE global privilege.
  • 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.

Output

The function returns the following columns:

Column Name Data Type Description
START_TIME TIMESTAMP_LTZ The start of the specified time range in which the warehouse usage took place.
END_TIME TIMESTAMP_LTZ The end of the specified time range in which the warehouse usage took place.
WAREHOUSE_NAME TEXT Name of the warehouse.
AVG_RUNNING NUMBER(38,2) Average number of queries executed.
AVG_QUEUED_LOAD NUMBER(38,2) Average number of queries queued because the warehouse was overloaded.
AVG_QUEUED_PROVISIONING NUMBER(38,2) Average number of queries queued because the warehouse was being provisioned.
AVG_BLOCKED NUMBER(38,2) Average number of queries blocked by a transaction lock.

Examples

Retrieve the load history for the last hour, in 5-second intervals, for the warehouse currently in use for your session:

use warehouse mywarehouse;

select *
from table(information_schema.warehouse_load_history(date_range_start=>dateadd('hour',-1,current_timestamp())));

Retrieve the load history for the last 14 days, in 5-minute intervals, for the warehouse currently in use for your session:

use warehouse mywarehouse;

select *
from table(information_schema.warehouse_load_history(date_range_start=>dateadd('day',-14,current_date()), date_range_end=>current_date()));