Categories:

Information Schema , Table functions

AUTO_REFRESH_REGISTRATION_HISTORY¶

This table function can be used to query the history of data files registered in the metadata of specified objects and the credits billed for these operations. The table function returns the billing history within a specified date range for your entire Snowflake account.

Note

This function returns billing activity within the last 14 days.

Syntax¶

AUTO_REFRESH_REGISTRATION_HISTORY(
      [ DATE_RANGE_START => <constant_expr> ]
      [, DATE_RANGE_END => <constant_expr> ]
      [, OBJECT_TYPE => '<string>' [, OBJECT_NAME => '<string>'] ])
Copy

Arguments¶

All of the arguments are optional.

DATE_RANGE_START => constant_expr , . DATE_RANGE_END => constant_expr

The date/time range of the billing window:

  • 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 the billing 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).

OBJECT_TYPE => string

Type of object for which credits are billed. The following value is supported:

DIRECTORY_TABLE

Directory tables that are configured for automatic metadata refreshes.

EXTERNAL_TABLE

External tables that are configured for automatic metadata refreshes.

OBJECT_NAME => string

A string specifying the name of the object for which credits are billed.

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

Output¶

The function returns the following columns:

Column Name

Data Type

Description

START_TIME

TIMESTAMP_LTZ

Start of the specified billing window.

END_TIME

TIMESTAMP_LTZ

End of the specified billing window.

OBJECT_NAME

TEXT

Name of the object for which credits are billed.

OBJECT_TYPE

TEXT

Type of object for which credits are billed.

CREDITS_USED

TEXT

Number of credits billed for data files registered in the metadata of the specified object or object type during the START_TIME and END_TIME window.

FILES_REGISTERED

NUMBER

Number of files registered during the START_TIME and END_TIME window.

Examples¶

Note that all of the examples in this topic reference external table metadata. To retrieve similar history records for other object types, edit the OBJECT_TYPE => string value in the query.

Retrieve the billing history for all external tables in your account that are configured for automatic metadata refreshes. The query retrieves the history for a 30 minute range, in 5 minute periods:

select *
  from table(information_schema.auto_refresh_registration_history(
    date_range_start=>to_timestamp_tz('2021-06-17 12:00:00.000 -0700'),
    date_range_end=>to_timestamp_tz('2021-06-17 12:30:00.000 -0700'),
    object_type=>'external_table'));
Copy

Same as the previous example, but retrieves the billing history for the last 14 days, in 1 day periods:

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

Same as the first example, but retrieves the billing history for the last 14 days, in 1 day periods:

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

Retrieve the billing history for an external table named myexttable in the active schema in the session for the last 12 hours, in 1 hour periods:

select *
  from table(information_schema.auto_refresh_registration_history(
    date_range_start=>dateadd('hour',-12,current_timestamp()),
    object_type=>'external_table',
    object_name=>'myexttable'));
Copy

Retrieve the billing history for an external table named myexttable in the mydb.myschema schema for the last 12 hours, in 1 hour periods:

select *
  from table(information_schema.auto_refresh_registration_history(
    date_range_start=>dateadd('hour',-12,current_timestamp()),
    object_type=>'external_table',
    object_name=>'mydb.myschema.myexttable'));
Copy
Language: English