Chapter 1: Overview of BigQuery's Regional Limitations

For many users of BigQuery, managing data or datasets stored in various regions has been a persistent challenge. This issue is particularly pronounced for international companies utilizing Google Cloud. Specific problems arise, such as:

• Location Mismatch Errors: You may encounter messages like "Cannot read in location: us" when dealing with multi-region buckets and regional datasets. The key principle is that both the external data and dataset must share the same region.
• Copying Restrictions: To replicate a table, both source and destination datasets must reside in the same region. Furthermore, if you wish to transfer an entire dataset, your destination table for scheduled queries must align with the region of the queried data.

Photo by Nejc Sokli? on Unsplash

Chapter 2: New Features in BigQuery

In a recent update, Google has addressed the long-standing issue of cross-region table copying in BigQuery. This enhancement alleviates a significant pain point, allowing users to transfer data for improved security or to rectify previous errors. Currently, this feature is available in preview mode, but it holds the potential to be a game-changer, even if not frequently utilized in production environments.

Chapter 3: How to Copy Tables Across Regions

To facilitate table copying between BigQuery regions or multi-regions, including those using customer-managed Cloud KMS (CMEK), you can execute the following command in the cloud shell:

bq cp

-f -n

PROJECT_ID:DATASET.SOURCE_TABLE

PROJECT_ID:DATASET.DESTINATION_TABLE

This development is a significant relief for many BigQuery users who have faced difficulties in this area. So, here’s to hassle-free copying in the near future!