Categories:

Information Schema , Table functions

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

The DATABASE_REFRESH_PROGRESS family of functions can be used to query the status of a database refresh along various dimensions:

  • DATABASE_REFRESH_PROGRESS returns a JSON object indicating the current refresh status for a secondary database by name.

  • DATABASE_REFRESH_PROGRESS_BY_JOB returns a JSON object indicating the current refresh status for a secondary database by refresh query.

Each function is optimized for querying along the specified dimension.

Note

  • DATABASE_REFRESH_PROGRESS only returns the database refresh activity for the most recent database refresh if it occurred within the last 14 days.

  • DATABASE_REFRESH_PROGRESS_BY_JOB returns database refresh activity within the last 14 days.

See also:

DATABASE_REFRESH_HISTORY

Syntax

DATABASE_REFRESH_PROGRESS( '<secondary_db_name>' )

DATABASE_REFRESH_PROGRESS_BY_JOB( '<query_id>' )
Copy

Arguments

secondary_db_name

Name of the secondary database. This argument is optional if the secondary database is the active database in the current session.

Note that the entire name must be enclosed in single quotes.

query_id

ID of the database refresh query. The query ID can be obtained from the History History tab page in the web interface.

Usage notes

  • Only returns results for account administrators (users with the ACCOUNTADMIN role).

  • 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.

  • Following is the list of phases in the order processed:

    1. SECONDARY_UPLOADING_INVENTORY

    2. PRIMARY_UPLOADING_METADATA

    3. PRIMARY_UPLOADING_DATA

    4. SECONDARY_DOWNLOADING_METADATA

    5. SECONDARY_DOWNLOADING_DATA

    6. COMPLETED / FAILED / CANCELED

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 the usage notes.

RESULT

TEXT

Status of the replication phase. Valid statuses are EXECUTING, SUCCEEDED, CANCELLED, FAILED.

START_TIME

NUMBER

Time when the replication phase began. Format is epoch time.

END_TIME

NUMBER

Time when the phase finished, if applicable. Format is epoch time.

DETAILS

VARIANT

Returned by the DATABASE_REFRESH_PROGRESS function only. A JSON object that provides detailed information for the following phases: . - Primary uploading data: The timestamp of the current snapshot of the primary database. . - Primary uploading data and Secondary downloading data: Total number of bytes in the database refresh as well as the number of bytes copied so far in the phase. . - Secondary downloading metadata: The number of tables, table columns, and all database objects (including tables and table columns) in the latest snapshot of the primary database.

Examples

Retrieve the current progress of the database refresh for the mydb1 database:

select *
from table(information_schema.database_refresh_progress(mydb1));
Copy

Retrieve the current progress of a database refresh by query ID:

select *
from table(information_schema.database_refresh_progress_by_job('012a3b45-1234-a12b-0000-1aa200012345'));
Copy
Language: English