Categories:

# PIPE_USAGE_HISTORY¶

This table function can be used to query the history of data loaded into Snowflake tables using Snowpipe within a specified date range. The function returns the history of data loaded and credits billed for your entire Snowflake account.

Note

This function returns pipe activity within the last 14 days.

## Syntax¶

PIPE_USAGE_HISTORY(
[ DATE_RANGE_START => <constant_expr> ]
[, DATE_RANGE_END => <constant_expr> ]
[, PIPE_NAME => '<string>' ] )


## Arguments¶

All the arguments are optional.

DATE_RANGE_START => constant_expr , . DATE_RANGE_END => constant_expr

The date/time range, within the last 2 weeks, for which to retrieve the data load history:

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

History is displayed in increments of 5 minutes, 1 hour, or 24 hours (depending on the length of the specified range).

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

PIPE_NAME => string

A string specifying a pipe. Only data loads that use the specified pipe are returned.

If a pipe name is not specified, then the PIPE_NAME column in the results displays NULL. Each row includes the totals for all pipes in use within the time range.

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

Start of the specified time range in which data loads took place.

END_TIME

TIMESTAMP_LTZ

End of the specified time range in which data loads took place.

PIPE_NAME

TEXT

Name of the pipe used for a data load. Displays NULL if no pipe name is specified in the query. Each row includes the totals for all pipes in use within the time range.

CREDITS_USED

TEXT

Number of credits billed for Snowpipe data loads during the START_TIME and END_TIME window.

BYTES_INSERTED

NUMBER

Number of bytes loaded during the START_TIME and END_TIME window.

FILES_INSERTED

NUMBER

Number of files loaded during the START_TIME and END_TIME window.

## Examples¶

Retrieve the data load history for a 30 minute range, in 5 minute periods, for your account:

select *
from table(information_schema.pipe_usage_history(
date_range_start=>to_timestamp_tz('2017-10-24 12:00:00.000 -0700'),
date_range_end=>to_timestamp_tz('2017-10-24 12:30:00.000 -0700')));


Retrieve the data load history for the last 12 hours, in 1 hour periods, for your account:

select *
from table(information_schema.pipe_usage_history(
pipe_name=>'mydb.public.mypipe'));


Retrieve the data load history for the last 14 days, in 1 day periods, for your account:

select *
from table(information_schema.pipe_usage_history(

select *