SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view
The INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION
view contains
information about schemata replicas associated with a failover reservation. The
INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION
view is scoped to
the project of the failover reservation, as opposed to the
INFORMATION_SCHEMA.SCHEMATA_REPLICAS
view that is scoped to the
project that contains the dataset.
Required role
To get the permissions that you need to query the INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION
view,
ask your administrator to grant you the
BigQuery Resource Viewer (roles/bigquery.resourceViewer
) IAM role on the project.
For more information about granting roles, see Manage access to projects, folders, and organizations.
You might also be able to get the required permissions through custom roles or other predefined roles.
Schema
TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS
view contains information about
dataset replicas.
The INFORMATION_SCHEMA.SCHEMATA_REPLICAS
view has the following schema:
Column | Type | Description |
---|---|---|
failover_reservation_project_id |
STRING |
The project ID of the failover reservation admin project if it's associated with the replica. |
failover_reservation_name |
STRING |
The name of the failover reservation if it's associated with the replica. |
catalog_name |
STRING |
The project ID of the project that contains the dataset. |
schema_name |
STRING |
The dataset ID of the dataset. |
replica_name |
STRING |
The name of the replica. |
location |
STRING |
The region or multi-region the replica was created in. |
replica_primary_assigned |
BOOL |
If the value is TRUE , the replica has the primary assignment. |
replica_primary_assignment_complete |
BOOL |
If the value is TRUE , the primary assignment is complete.
If the value is FALSE , the replica is not (yet) the primary
replica, even if replica_primary_assigned equals
TRUE . |
creation_time |
TIMESTAMP |
The replica's creation
time. When the replica is first created, it is not fully synced with the primary replica until
creation_complete equals TRUE . The value of
creation_time is set before creation_complete equals
TRUE . |
creation_complete |
BOOL |
If the value is TRUE , the initial full sync of the
primary replica to the secondary replica is complete. |
replication_time |
TIMESTAMP |
The value for Some tables in the replica might be ahead of this timestamp. This value is only visible in the secondary region. If the dataset contains a table with streaming data, the value of |
sync_status |
JSON |
The status of the sync
between the primary and secondary replica. Returns NULL if the replica is a
primary replica. |
Scope and syntax
Queries against this view must include a region qualifier. The following table explains the region scope for this view:
View name | Resource scope | Region scope |
---|---|---|
[RESERVATION_PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION[_BY_PROJECT] |
Project level | REGION |
Optional:
RESERVATION_PROJECT_ID
: the ID of the administration project of the reservation. If not specified, the default project is used.REGION
: any dataset region name. For example,`region-us`
.
Examples
This section lists example queries of the
INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION
view.
Example: List all replicated datasets in a region
The following example lists all the replicated datasets in the US
region:
SELECT * FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION WHERE failover_reservation_name = "failover_reservation";
The result is similar to the following:
+--------------+--------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+---------------------+---------------------------------+---------------------------+-------------------------------------------------------------------------------+ | catalog_name | schema_name | replica_name | location | replica_primary_assigned | replica_primary_assignment_complete | creation_time | creation_complete | replication_time | failover_reservation_project_id | failover_reservation_name | sync_status | +--------------+--------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+---------------------+---------------------------------+---------------------------+-------------------------------------------------------------------------------+ | project2 | test_dataset | us-east4 | us-east4 | true | true | 2024-05-09 20:34:06 | true | NULL | project1 | failover_reservation | NULL | | project2 | test_dataset | us | US | false | false | 2024-05-09 20:34:05 | true | 2024-05-10 18:31:06 | project1 | failover_reservation | {"last_completion_time":"2024-06-06 18:31:06","error_time":null,"error":null} | +--------------+--------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+---------------------+---------------------------------+---------------------------+-------------------------------------------------------------------------------+