sql - BigQuery create table error: dataset not found in location

Question

Here is my situation:

My colleague has a dataset located in asia-northeast3 in his BigQuery. He has already give me reader access to his dataset. I'm trying to extract some necessary data from one of his tables and save them into a new table under my dataset (location: us-central).

I wrote the following sql to do this, but BigQuery reported error:

Not found: Dataset my_project_id:dataset_in_us was not found in location asia-northeast3

CREATE OR REPLACE TABLE `my_project_id.dataset_in_us.my_tablename` AS 
SELECT 
  create_date
  , totalid -- id for article. 
  , urlpath -- format like /article/xxxx
  , article_title -- text article title
FROM `my_colleagues_project_id.dataset_in_asia_northeast3.tablename` 
ORDER BY 1 DESC
;

I can't change my dataset location or his. I need to join the data from his dataset with data from my dataset. How to solve this?

question from:https://stackoverflow.com/questions/65877809/bigquery-create-table-error-dataset-not-found-in-location

Answer 1

After 1 day of trying and failing, I found a not perfect solution. I copied my colleague's entire dataset from asia-northeast3 to us-central following this guide. After that I can run my query on the copied dataset.

This solution is time (and money) consuming. I'm still trying to figure out if there is a way to only copy a single table, instead of an entire dataset, from one location to another.