Console
    Contact Us Start free

    SCHEMATA_REPLICAS view

    The INFORMATION_SCHEMA.SCHEMATA_REPLICAS view contains information about schemata replicas.

    Required role

    To get the permissions that you need to query the INFORMATION_SCHEMA.SCHEMATA_REPLICAS view, ask your administrator to grant you the BigQuery Data Viewer ( roles/bigquery.dataViewer ) 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
    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
    [ PROJECT_ID .]`region- REGION `.INFORMATION_SCHEMA.SCHEMATA_REPLICAS[_BY_PROJECT]
    		Project level REGION
    Replace the following:
    • Optional: PROJECT_ID : the ID of your Google Cloud project. 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 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 
 ;

    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 |
+---------------------+-------------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+------------------+
| myproject           | replica1          | us-east7     | us-east7 |                     true |                                true | 2023-04-17 20:42:45 |              true |             NULL |
| myproject           | replica1          | us-east4     | us-east4 |                    false |                               false | 2023-04-17 20:44:26 |              true |             NULL |
+---------------------+-------------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+------------------+

    Example: List replicated datasets and the primary replica for each

    The following example lists all replicated datasets and their primary replica in the US region:

     SELECT 
  
 catalog_name 
 , 
  
 schema_name 
 , 
  
 replica_name 
  
 AS 
  
 primary_replica_name 
 , 
  
 location 
  
 AS 
  
 primary_replica_location 
 , 
  
 replica_primary_assignment_complete 
  
 AS 
  
 is_primary 
 , 
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 SCHEMATA_REPLICAS 
 WHERE 
  
 replica_primary_assignment_complete 
  
 = 
  
 TRUE 
  
 AND 
  
 replica_primary_assigned 
  
 = 
  
 TRUE 
 ;

    The result is similar to the following:

    +---------------------+-------------+----------------------+--------------------------+------------+
|    catalog_name     | schema_name | primary_replica_name | primary_replica_location | is_primary |
+---------------------+-------------+----------------------+--------------------------+------------+
| myproject           | my_schema1  | us-east4             | us-east4                 |       true |
| myproject           | my_schema2  | us                   | US                       |       true |
| myproject           | my_schema2  | us                   | US                       |       true |
+---------------------+-------------+----------------------+--------------------------+------------+

    Example: List replicated datasets and their replica states

    The following example lists all replicated datasets and their replica states:

     SELECT 
  
 catalog_name 
 , 
  
 schema_name 
 , 
  
 replica_name 
 , 
  
 CASE 
  
 WHEN 
  
 ( 
 replica_primary_assignment_complete 
  
 = 
  
 TRUE 
  
 AND 
  
 replica_primary_assigned 
  
 = 
  
 TRUE 
 ) 
  
 THEN 
  
 'PRIMARY' 
  
 WHEN 
  
 ( 
 replica_primary_assignment_complete 
  
 = 
  
 FALSE 
  
 AND 
  
 replica_primary_assigned 
  
 = 
  
 FALSE 
 ) 
  
 THEN 
  
 'SECONDARY' 
  
 ELSE 
  
 'PENDING' 
 END 
  
 AS 
  
 replica_state 
 , 
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 SCHEMATA_REPLICAS 
 ;

    The result is similar to the following:

    +---------------------+-------------+--------------+---------------+
|    catalog_name     | schema_name | replica_name | replica_state |
+---------------------+-------------+--------------+---------------+
| myproject           | my_schema1  | us-east4     | PRIMARY       |
| myproject           | my_schema1  | my_replica   | SECONDARY     |
+---------------------+-------------+--------------+---------------+

    Example: List when each replica was created and whether the initial backfill is complete

    The following example lists all replicas and when that replica was created. When a secondary replica is created, its data is not fully synced with the primary replica until creation_complete equals TRUE .

     SELECT 
  
 catalog_name 
 , 
  
 schema_name 
 , 
  
 replica_name 
 , 
  
 creation_time 
  
 AS 
  
 creation_time 
 , 
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 SCHEMATA_REPLICAS 
 WHERE 
  
 creation_complete 
  
 = 
  
 TRUE 
 ;

    The result is similar to the following:

    +---------------------+-------------+--------------+---------------------+
|    catalog_name     | schema_name | replica_name |    creation_time    |
+---------------------+-------------+--------------+---------------------+
| myproject           | my_schema1  | us-east4     | 2023-06-15 00:09:11 |
| myproject           | my_schema2  | us           | 2023-06-15 00:19:27 |
| myproject           | my_schema2  | my_replica2  | 2023-06-15 00:19:50 |
| myproject           | my_schema1  | my_replica   | 2023-06-15 00:16:19 |
+---------------------+-------------+--------------+---------------------+

    Example: Show the most recent synced time

    The following example shows the most recent timestamp when the secondary replica caught up with the primary replica.

    You must run this query in the region that contains the secondary replica. Some tables in the dataset might be ahead of the reported replication time.

     SELECT 
  
 catalog_name 
 , 
  
 schema_name 
 , 
  
 replica_name 
 , 
  
 -- Calculate the replication lag in seconds. 
  
 TIMESTAMP_DIFF 
 ( 
 CURRENT_TIMESTAMP 
 (), 
  
 replication_time 
 , 
  
 SECOND 
 ) 
  
 AS 
  
 replication_lag_seconds 
 , 
  
 -- RLS 
  
 -- Calculate the replication lag in minutes. 
  
 TIMESTAMP_DIFF 
 ( 
 CURRENT_TIMESTAMP 
 (), 
  
 replication_time 
 , 
  
 MINUTE 
 ) 
  
 AS 
  
 replication_lag_minutes 
 , 
  
 -- RLM 
  
 -- Show the last sync time for easier interpretation. 
  
 replication_time 
  
 AS 
  
 secondary_replica_fully_synced_as_of_time 
 , 
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 SCHEMATA_REPLICAS

    The result is similar to the following:

    +---------------------+-------------+--------------+-----+-----+-------------------------------------------+
|    catalog_name     | schema_name | replica_name | rls | rlm | secondary_replica_fully_synced_as_of_time |
+---------------------+-------------+--------------+-----+-----+-------------------------------------------+
| myproject           | my_schema1  | us-east4     |  23 |   0 |                       2023-06-15 00:18:49 |
| myproject           | my_schema2  | us           |  67 |   1 |                       2023-06-15 00:22:49 |
| myproject           | my_schema1  | my_replica   |  11 |   0 |                       2023-06-15 00:28:49 |
| myproject           | my_schema2  | my_replica2  | 125 |   2 |                       2023-06-15 00:29:20 |
+---------------------+-------------+--------------+-----+-----+-------------------------------------------+

    A value of NULL indicates that the secondary replica was never fully synced to the primary replica.
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: