8
$\begingroup$

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.

$\endgroup$

2 Answers 2

5
$\begingroup$

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"}'
$\endgroup$
1
  • 1
    $\begingroup$ Is there no other way to set jobs to read from multiple different locations? I'm facing the same issue and seems to be frustrating that data run in US and us-east1 can't be run together. Feels like we have to restructure our complete data locations $\endgroup$ Commented Jun 11, 2021 at 15:25
2
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.