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

The INFORMATION_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 `replication_time` indicates the staleness of the dataset. 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 `replication_time` will not be accurate.
`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`

Replace the following:

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

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

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} |
+--------------+--------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+---------------------+---------------------------------+---------------------------+-------------------------------------------------------------------------------+