google-bigquerygoogle-analyticsdatasetlocationreplication

promote Bigquery table replica to primary


I have a dataset in Bigquery which has a connection to Google Analytics so that the GA-data is loaded into a Bigquery sharded table called events_YYYYMMDD. This happens automatically in the background. I want to join the data from this table with data from other tables in a different location. To do so I created a replica of the table which I can use for my join. When working in Bigquery Studio it works when I start writing a query from the other table, but when starting from the GA-table I get a 'dataset cannot be found in location...' error. So it is depending on the location. When I want to create a view on the GA-table it doesn´t work as it is a secondary replica. To get this working I would have to promote the replica to a primary replica. But now my question: would promoting the replica to primary in any way affect the existing connection between Google Analytics and Bigquery? Thank you for reading and probably answering. :-)


Solution

  • Dataset replication If you replicate a dataset, BigQuery stores the data in the region that you specify.

    Primary region. When you first create a dataset, BigQuery places the dataset in the primary region.

    Secondary region. When you add a dataset replica, BigQuery places the replica in the secondary region.

    Initially, the replica in the primary region is the primary replica, and the replica in the secondary region is the secondary replica.

    The primary replica is writeable, and the secondary replica is read-only. Writes to the primary replica are asynchronously replicated to the secondary replica. Within each region, the data is stored redundantly in two zones. Network traffic never leaves the Google Cloud network.

    Read this cross region Documentation for better understanding

    so for your Key Question Promoting the secondary replica to primary could affect the existing connection between Google Analytics and BigQuery. Since the original primary replica would become read-only, Google Analytics might not be able to write data to it anymore.