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. For example, if you specify that the start date is 2019-05-03 and the end date 2019-05-05, you’ll get data for May 3, May 4, and May 5. (The endpoints are included.)

  • If neither a start date nor an end date is specified, the default will be the last 12 hours.

  • If an end date is not specified, but a start date is specified, then CURRENT_DATE at midnight is used as the end of the range.

  • If a start date is not specified, but an end date is specified, then the range starts 12 hours prior to the start of DATE_RANGE_END.

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 results includes the data for each materialized view maintained within the specified 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.

  • The history is displayed in increments of 1 hour.

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 one-hour range for your account:

select *
  from table(information_schema.materialized_view_refresh_history(
    date_range_start=>'2019-05-22 19:00:00.000',
    date_range_end=>'2019-05-22 20:00:00.000'));

Here is sample output:

+-------------------------------+-------------------------------+--------------+-----------------------------------------+
| START_TIME                    | END_TIME                      | CREDITS_USED | MATERIALIZED_VIEW_NAME                  |
|-------------------------------+-------------------------------+--------------+-----------------------------------------|
| 2019-05-22 19:00:00.000 -0700 | 2019-05-22 20:00:00.000 -0700 |  0.223276651 | TEST_DB.TEST_SCHEMA.MATERIALIZED_VIEW_1 |
+-------------------------------+-------------------------------+--------------+-----------------------------------------+

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