Joining tables from different locations in Bigquery

Question

I have been trying to join two tables from different datasets that are in different locations but in the same project. However, I keep getting the error:

dataset not found in US location.

The datasets' locations are US and us-east1

Here is what I am doing:

select a.*, b.* from `project.dataset1.table1` a join `project.dataset2.table2`
on a.common_col = b.common_col

Please help me out on this.

Answer 1

You cannot join two tables from different datasets that are in different locations. As an alternative solution, you can copy datasets between regions using BigQuery Data Transfer Service. Here is the documentation link Copying Datasets:

bq mk --transfer_config \
      --project_id=myproject \
      --data_source=cross_region_copy \
      --target_dataset=dataset_us \
      --display_name='Copy Dataset' \
      --params='{"source_dataset_id":"dataset_eu","source_project_id":"myproject"}'

Answer 2

Too late to answer but writing here for future reference.

You cannot query data from multiple different locations at the same time. The alternate solution is as below:

1. Query the data from Location-1
2. Save its results in form of CSV or any other preferrable format (Saving CSV in Google Drive works up to 1GB of output)
3. Create a table in dataset that's lying under Location-2
4. Import the CSV file you saved into this newly created table
5. Query the data on Location-2 with your Location-2 data and saved table