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