Categories:
Information Schema , Table Functions

AUTOMATIC_CLUSTERING_HISTORY

This table function is used for querying the Automatic Clustering history for given tables within a specified date range. The information returned by the function includes the credits consumed, bytes updated, and rows updated each time a table is reclustered.

Syntax

AUTOMATIC_CLUSTERING_HISTORY(
      [ DATE_RANGE_START => <constant_expr> ]
      [ , DATE_RANGE_END => <constant_expr> ]
      [ , TABLE_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 Automatic Clustering 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 Automatic Clustering history).

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

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

TABLE_NAME => string

Table name. If specified, only shows the history for the specified table. The table name can include the schema name and the database name.

If a table name is not specified, then the TABLE_NAME column in the results displays NULL, and each row includes the totals for all tables maintained by Automatic Clustering 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.

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.
TABLE_NAME TEXT Name of the table. Displays NULL if no table name is specified in the function, in which case either row includes the totals for all tables in use within the time range.
CREDITS_USED TEXT Number of credits billed for automatic clustering during the START_TIME and END_TIME window.
NUM_BYTES_RECLUSTERED NUMBER Number of bytes reclustered during the START_TIME and END_TIME window.
NUM_ROWS_RECLUSTERED NUMBER Number of rows reclustered loaded during the START_TIME and END_TIME window.

Examples

Retrieve the automatic clustering history for a 30 minute range, in 5 minute periods, for your account:

select *
  from table(information_schema.automatic_clustering_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 automatic clustering history for the last 12 hours, in 1 hour periods, for your account:

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

Retrieve the automatic clustering history for the past week, in 1 day periods, for your account:

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

Retrieve the automatic clustering history for the past week, in 1 day periods, for a specified table in your account:

select *
  from table(information_schema.automatic_clustering_history(
    date_range_start=>dateadd(D, -7, current_date),
    date_range_end=>current_date,
    table_name=>'mydb.myschema.mytable'));