CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Hakan54
Hakan54

Reputation: 3861

Join BigQuery tables from different reqions fails

I have two datasets which I want to join, but when I try to join them GCP gives me an error that it cannot find the other table within the same region which is correct because it is hosted in a different region. So one is hosted at EU and the other one is hosted at europe-west1.

I thought of changing the region of one dataset but that is not possible and the documentation also mentions that:

Moving BigQuery data between locations

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.

Ideally I would like to specify the region within the query editor when specifying the dataset/table path but I couldn't find such a thing. So I wanted to ask if there is a way to still join the tables without the need of migration one dataset to the same region.

Upvotes: 3

Views: 5719

Answers (2)

Justin Albinet
Justin Albinet

Reputation: 11

It is now possible to replicate the dataset in the region you would like to perform your join from.

Let's say your first dataset primary location is in the EU and the second in europe-west1. If you want to join both of them, you should add a replica (so a secondary location) on one of those two datasets.

If the majority of your datasets are in the multi-region EU, I'd advise you to replicate the second one so that you can use it with all your other datasets.

ALTER SCHEMA `my_second_dataset`
ADD REPLICA `my_second_dataset_EU`
OPTIONS(location='EU');

Once the replication is over (you can follow it in the Dataset replica info in your dataset description) you can perform the join between your two datasets

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5503

Currently, there is no way you can join 2 tables from different regions. Moving the dataset into same region is the only way.

Regarding the dataset location, BigQuery knows location of each dataset. So if you don't do cross region join, your query is always routed to the region where your data sits.

Upvotes: 6

Related Questions