Is there a way in BigQuery to save queries from Multi-region in region

Question

we have a BigQuery table which is located in the Multi-region "EU" and another table that is located in the region "europe-west3". Our goal is to append the results of regular queries (every hour) from the EU-table to the europe-west3-table. Is there an easy way to perform this task besides always exporting the entire table to a storage bucket, copying it to europe-west3 and importing it back to a table in the correct region? Initial tests in the UI fail with "Dataset :<target_dataset> was not found in location EU" We would really appreciate your help!

aga aga · Accepted Answer · 2020-07-30T07:32:23

BigQuery returns an error if the specified location does not match the location of the datasets in the request. You cannot change the location of a dataset after it is created, but you can make a copy of the dataset. You cannot move a dataset from one location to another, but you can manually move (recreate) a dataset. The BigQuery Data Transfer Service can transfer data to a BigQuery dataset in many regions.

BigQuery determines the location to run the job based in the datasets referenced in the request, but in order to specify the location to run your data's location, please click More -> Query settings. For Processing Location, click Auto-select and choose your data's location. An example with creating temporary table and selecting EU location.

CREATE TEMP TABLE TempTable
(x INT64, y STRING);

INSERT INTO TempTable
VALUES (1, 'Big');

INSERT INTO TempTable
VALUES (2, 'Query');

SELECT * FROM TempTable;

After that, when you go to Job Information -> Destination Table, you will get details about TempTable in Table info -> Data location, which is EU.

Is there a way in BigQuery to save queries from Multi-region in region

1 Answers