how to move data between datasets in different regions?

Question

I'm using BigQuery integrated with Firebase and all the datasets are in the same Project. My analytics dataset is in useast-4 but for some reason my firebase_imported_segments dataset region is just marked as US I'd like to move data from the analytics dataset into a table in the firebase_imported_segments.

At first, I tried a simple INSERT query but I get the error firebase_imported_segments was not found in location us-east4

So then I tried building a SELECT statement and exporting the rows using "Save Results > Big Query Table" but that gives a similar error that the destination dataset is not found. Oddly enough, if I create a table in firebase_imported_segments and try to save the results using that table name, I get a "Table already exists" error. So it's not that it can't find the firebase_imported_segments dataset, it just won't create a new table in that dataset.

How can I get around this? I saw some BQ documentation that moving data between regions is possible but I didn't a simple walkthrough of how it's accomplished. I'm also confused by why firebase would put some data in one specific region (useast-4) and then other data in a multi-region (US) if they aren't compatible.

Answer 1

You can move datasets using "Copy" in the BigQuery UI then delete the old dataset. See Copy dataset documentation.

Option 1: Use the Copy button.

1. Go to the BigQuery page in the Cloud console.

2. In the Explorer panel, expand your project and select a dataset.

3. Expand the More Actions option (triple dot button) and click Open.

4. Click Copy. In the Copy dataset dialog that appears, do the following:

   a. In the Dataset field, either create a new dataset or select an existing dataset ID from the list.

   Dataset names within a project must be unique. The project and dataset can be in different regions, but not all regions are supported for cross-region dataset copying.

   b. In the Location field, the location of the source dataset is displayed.

   c. Optional: To overwrite both data and schema of the destination tables with the source tables, select the Overwrite destination tables checkbox.

   d. To copy the dataset, click Copy.

To avoid additional storage costs, consider deleting the old dataset.

Option 2: Use the BigQuery Data Transfer Service.

1. Enable the BigQuery Data Transfer Service.

2. Create a transfer for your data source.

I tested this and can confirm that it works. I created a dataset in us-east4 named analytics_us_regional and has a table named east_4_table and copied it to a dataset located in US.

Copy us-east4 to US dataset:

When copy is initiated a data transfer job is created:

Copied to US:

With regards to the data in firebase located in us-east4 based from the firebase export to BQ. When the export is enabled the first time, the user will define the location of the tables. It might be possible thatus-east4 region was selected initially.