Categories:
Information Schema , Table Functions

MATERIALIZED_VIEW_REFRESH_HISTORY

This table function is used for querying the materialized views refresh history for a specified materialized view within a specified date range. The information returned by the function includes the view name and credits consumed each time a materialized view is refreshed.

Syntax

MATERIALIZED_VIEW_REFRESH_HISTORY(
      [ DATE_RANGE_START => <constant_expr> ]
      [ , DATE_RANGE_END => <constant_expr> ]
      [ , MATERIALIZED_VIEW_NAME => '<string>' ] )

Arguments

All the arguments are optional.

DATE_RANGE_START => constant_expr , . DATE_RANGE_END => constant_expr

The date/time range to display the materialized view maintenance history:

  • 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 history).
  • If an end date is not specified, then CURRENT_DATE at midnight is used as the end of the range.

For example, if DATE_RANGE_END is CURRENT_DATE and no DATE_RANGE_START is specified, then the default is 11:50 PM on the previous day.

MATERIALIZED_VIEW_NAME => string

Materialized view name. If specified, only shows the history for the specified materialized view. The name can include the schema name and the database name.

If a name is not specified, then the materialized_view_name column in the results displays NULL, and each row includes the totals for all materialized views maintained within the time range for that row.

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.
  • The output is displayed in increments of 5 minutes, 1 hour, or 24 hours, depending on the length of the specified date range.

Output

The function returns the following columns:

Column Name Data Type Description
START_TIME TIMESTAMP_LTZ Start of the specified time range.
END_TIME TIMESTAMP_LTZ End of the specified time range.
CREDITS_USED TEXT Number of credits billed for materialized view maintenance during the START_TIME and END_TIME window.
MATERIALIZED_VIEW_NAME TEXT Name of the materialized view.

Examples

Retrieve the refresh history for a 30 minute range for your account:

select *
  from table(information_schema.materialized_view_refresh_history(
    date_range_start=>'2018-04-10 12:00:00.000 -0700',
    date_range_end=>'2018-04-10 12:30:00.000 -0700'));

Retrieve the history for the last 12 hours for your account:

select *
  from table(information_schema.materialized_view_refresh_history(
    date_range_start=>dateadd(H, -12, current_timestamp)));

Retrieve the history for the past week for your account:

select *
  from table(information_schema.materialized_view_refresh_history(
    date_range_start=>dateadd(D, -7, current_date),
    date_range_end=>current_date));

Retrieve the maintenance history for the past week for a specified materialized view in your account:

select *
  from table(information_schema.materialized_view_refresh_history(
    date_range_start=>dateadd(D, -7, current_date),
    date_range_end=>current_date,
    materialized_view_name=>'mydb.myschema.my_materialized_view'));