Can't replicate datasets to different location in bigquery using DML

Question

aiming to move my dataset between the us and the EU and getting the following error message:

-- Create the primary replica in the US multi-region.
-- CREATE SCHEMA covid_938 OPTIONS(location='us');

-- Create a replica in the secondary region.
-- ALTER SCHEMA covid_938
-- ADD REPLICA `eu`
-- OPTIONS(location='eu');

ALTER SCHEMA covid_938 SET OPTIONS(primary_replica = 'eu');
ALTER SCHEMA covid_938
DROP REPLICA IF EXISTS `us`;

Invalid value: The primary replica can only be assigned from the replica region: EU.

Answer 1

Can you try the steps found in this article ?

Following steps mentioned:

1. Export the data from your BigQuery tables to a Cloud Storage bucket in either the same location as your dataset or in a location contained within your dataset's location. For example, if your dataset is in the EU multi-region location, you could export your data to the europe-west1 Belgium location, which is part of the EU.

• There are no charges for exporting data from BigQuery, but you do incur charges for storing the exported data in Cloud Storage. BigQuery exports are subject to the limits on export jobs.

2. Copy or move the data from your export Cloud Storage bucket to a new bucket you created in the destination location. For example, if you are moving your data from the US multi-region to the asia-northeast1 Tokyo region, you would transfer the data to a bucket that you created in Tokyo. For information about transferring Cloud Storage objects, see Copy, rename, and move objects in the Cloud Storage documentation.

• Transferring data between regions incurs network egress charges in Cloud Storage.

3. Copy or move the data from your export Cloud Storage bucket to a new bucket you created in the destination location. For example, if you are moving your data from the US multi-region to the asia-northeast1 Tokyo region, you would transfer the data to a bucket that you created in Tokyo. For information about transferring Cloud Storage objects, see Copy, rename, and move objects in the Cloud Storage documentation.

• Transferring data between regions incurs network egress charges in Cloud Storage.

Answer 2

The reason you are getting Invalid value: The primary replica can only be assigned from the replica region: EU. because when you are making secondary location as primary your processing location is not set to 'eu'.

When loading data, querying data, or exporting data, BigQuery determines the location to run the job based on the datasets referenced in the request. For example, if a query references a table in a dataset stored in the us-east1 region, the query job will run in that region. If a query does not reference any tables or other resources contained within datasets, and no destination table is provided, the query job will run in the US multi-region.

So after creating the replica in the secondary region. When you query data using the Google Cloud console in the query editor, click More > Query settings, expand Advanced options, and then uncheck the Automatic location selection -> select eu region -> click Save. It will run successful after this.

Refer below screenshot for reference:

Step 1:

Step 2:

You can verify your response with the below sql command:

SELECT *
FROM region-us.INFORMATION_SCHEMA.SCHEMATA_REPLICAS
WHERE schema_name = 'covid_938';

Also the document too state this clearly with this line.

Let me know if you still face any issue.