Joining tables from different locations in Bigquery

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.

Topic google-cloud

Category Data Science

1
answered at

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"}'

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.