Categories:: Information Schema , Table functions

LISTING_REFRESH_PROGRESS, LISTING_REFRESH_PROGRESS_BY_JOB¶

The LISTING_REFRESH_PROGRESS family of functions can be used to understand the replication progress of listings. They allows users to query the status of a refresh for cross-cloud auto-fulfillment listings.

LISTING_REFRESH_PROGRESS returns data about the status of the current in-progress or most recently completed refresh for a listing by name.
LISTING_REFRESH_PROGRESS_BY_JOB returns phase-by-phase data about listing refresh events specified by refresh UUID. Refresh UUIDs can be obtained as one of the columns returned by LISTING_REFRESH_HISTORY.

This function is available to providers of listings who have any privilege on the listing. The information returned contains replication details for the synchronization of data added to the listing to the specified target region. These functions return detailed information about a single refresh event. For a summary of all refresh events in the past 14 days, refer to LISTING_REFRESH_HISTORY.

Syntax¶

LISTING_REFRESH_PROGRESS('<listing_name>', '<target_region>'[, '<target_region_group>')
LISTING_REFRESH_PROGRESS_BY_JOB('<listing_name>', '<query_id>', '<target_region>'[, '<target_region_group>')

Copy

Arguments¶

listing_name

SQL identifier of a cross-cloud auto-fulfillment listing in this account. The SQL identifier for listings can be found in the name column returned by show listings in data exchange <exchange_name>. Similarly, the SQL identifier for data exchanges can be found in the name column returned by SHOW DATA EXCHANGES.

exchange_name.

SQL identified for the data exchange. SQL identifiers for data exchanges can be found in the name column returned by SHOW DATA EXCHANGES. This function argument must be enclosed in single quotes.

query_id

UUID of the refresh job for the listing. This can be obtained as one of the columns returned by LISTING_REFRESH_HISTORY.

target_region

The Snowflake region the listing is replicated to for which to view refresh history. This follows the same formatting as the column snowflake_region returned by SHOW REGIONS.

target_region_group

The Snowflake region group the listing is replicated to for which to view refresh history.

PUBLIC by default. This argument only needs to be specified if the target region being monitored is in a US government or Virtual Private Snowflake region.

Output¶

The function returns the following columns:

Column Name	Data Type	Description
PHASE_NAME	TEXT	Name of the replication phases completed (or in progress) so far. For the list of phases, see usage notes.
START_TIME	TIMESTAMP_LTZ	Time when the replication phase began.
END_TIME	TIMESTAMP_LTZ	Time when the phase finished, if applicable. NULL if the phase is in progress or is the terminating phase (`COMPLETED/FAILED/CANCELED`).
PROGRESS	TEXT	PRIMARY_UPLOADING_DATA: Percentage of total bytes replicated. SECONDARY_DOWNLOADING_METADATA: Percentage of the total number of objects replicated. SECONDARY_DOWNLOADING_DATA: Percentage of total bytes replicated. Empty for remaining phases.
DETAILS	VARIANT	For phase PRIMARY_UPLOADING_METADATA: `primarySnapshotTimestamp`: Time when the primary snapshot was created. Format is epoch time. For phase PRIMARY_UPLOADING_DATA: `totalBytesToReplicate`: Total number of bytes expected to be uploaded. `totalBytesToUpload`: Total number of bytes to required to be uploaded. `bytesUploaded`: Total number of bytes uploaded so far. `databases`: List of JSON objects containing the following fields for each member database: `name`: Database name. `totalBytesToReplicate`: Total bytes expected to be uploaded for the database. For phase SECONDARY_DOWNLOADING_DATA: `totalBytesToReplicate`: Total number of bytes expected to be downloaded. `totalBytesToDownload`: Actual number of bytes required to be downloaded. `bytesDownloaded`: Actual number of bytes downloaded so far. `databases`: List of JSON objects containing the following fields for each member database: `name`: Database name. `totalBytesToReplicate`: Total bytes expected to be downloaded for the database. For phase SECONDARY_DOWNLOADING_METADATA: `totalObjects`: Total number of objects to download. `completedObjects`: Total number of objects downloaded so far. `objectTypes`: List of JSON objects containing the following fields for each object type: `objectType`: Type of object (for example,users, roles, grants, warehouses,schemas, tables, columns, etc). `totalObjects`: Total number of objects of this type. `completedObjects`: Number of completed objects of this type. For phase FAILED: `errorCode`: Error code of the failure. `errorMessage`: Error message of the failure.

Usage notes¶

LISTING_REFRESH_PROGRESS only returns the most recent refresh activity if it occurred within the last 14 days.
LISTING_REFRESH_PROGRESS_BY_JOB returns refresh activity within the last 14 days.
Only returns rows for a role with any privilege on the listing.
Only returns rows for a listing in the current account.
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 information, see Information Schema.

Phase list in the order processed:
1. SECONDARY_SYNCHRONIZING_MEMBERSHIP
2. SECONDARY_UPLOADING_INVENTORY
3. PRIMARY_UPLOADING_METADATA
4. PRIMARY_UPLOADING_DATA
5. SECONDARY_DOWNLOADING_METADATA
6. SECONDARY_DOWNLOADING_DATA
7. COMPLETED / FAILED / CANCELED

In the PRIMARY_UPLOADING_DATA and SECONDARY_DOWNLOADING_DATA phases, the totalBytesToReplicate value is estimated prior to the replication operation. This value may differ from the totalBytesToUpload or totalBytesToDownload value in the respective phase.

For example, if during the PRIMARY_UPLOADING_DATA phase, a previous replication operation uploaded some bytes but was canceled before the operation completed, those bytes would not be uploaded again. In that case, totalBytesToUpload would be lower than totalBytesToReplicate.

Examples¶

Retrieve the current progress of the listing my_listing replicating to AWS US East-1:

select * from table(information_schema.listing_refresh_progress('my_listing','AWS_US_EAST_1'));

Copy

Retrieve the phase-by-phase progress of a specific data refresh event into AWS US East-1 for the listing my_listin by query ID:

select * from table(information_schema.listing_refresh_progress_by_job('my_listing','012a3b45-1234-a12b-0000-1aa200012345','AWS_US_EAST_1'));

Copy