SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view

TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view containsinformation about schemata replicas associated with a failover reservation. TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view is scoped tothe project of the failover reservation, as opposed to theINFORMATION_SCHEMA.SCHEMATA_REPLICASview that is scoped to theproject that contains the dataset.

Required role

To get the permissions that you need to query theINFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view, ask your administrator to grant you theBigQuery Resource Viewer (roles/bigquery.resourceViewer) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Schema

TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATIONview has the following schema:
ColumnTypeDescription
failover_reservation_project_idSTRINGThe project ID of the failover reservation admin project if it's associated with the replica.
failover_reservation_nameSTRINGThe name of the failover reservation if it's associated with the replica.
catalog_nameSTRINGThe project ID of the project that contains the dataset.
schema_nameSTRINGThe dataset ID of the dataset.
replica_nameSTRINGThe name of the replica.
locationSTRINGThe region or multi-region the replica was created in.
replica_primary_assignedBOOLIf the value isTRUE, the replica has the primary assignment.
replica_primary_assignment_completeBOOLIf the value isTRUE, the primary assignment is complete. If the value isFALSE, the replica is not (yet) the primary replica, even ifreplica_primary_assigned equalsTRUE.
creation_timeTIMESTAMPThe replica's creation time. When the replica is first created, it is not fully synced with the primary replica untilcreation_complete equalsTRUE. The value ofcreation_time is set beforecreation_complete equalsTRUE.
creation_completeBOOLIf the value isTRUE, the initial full sync of the primary replica to the secondary replica is complete.
replication_timeTIMESTAMP

The value forreplication_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 ofreplication_time will not be accurate.

sync_statusJSONThe status of the sync between the primary and secondary replicas forcross-region replication anddisaster recovery datasets. ReturnsNULL if the replica is a primary replica or the dataset doesn't use replication.
replica_primary_assignment_timeTIMESTAMPThe time at which the primary switch to the replica was triggered.
replica_primary_assignment_completion_timeTIMESTAMPThe time at which the primary switch to the replica was completed.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.The following table explains the region scope for this view:

View nameResource scopeRegion scope
[RESERVATION_PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION[_BY_PROJECT]Project levelREGION
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Examples

This section lists example queries of theINFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view.

Example: List all replicated datasets in a region

The following example lists all the replicated datasets in theUS region:

SELECT*FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATIONWHEREfailover_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} |+--------------+--------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+---------------------+---------------------------------+---------------------------+-------------------------------------------------------------------------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.