CODEWITHSUNDEEP
google-bigquery
Tan Duong
Tan Duong

Reputation: 1561

BigQuery scheduled query: Cannot create a transfer in > JURISDICTION_US when destination dataset is located in > REGION_ASIA_SOUTHEAST_1

I am getting this error quite frequently while trying to create a scheduled query

Error creating scheduled query: Cannot create a transfer in JURISDICTION_US when destination dataset is located in REGION_ASIA_SOUTHEAST_1

I just need a scheduled query to overwrite data in a table.

Upvotes: 7

Views: 7007

Answers (10)

Urvah Shabbir
Urvah Shabbir

Reputation: 985

I was using bq command when I came across this issue and was able to resolve it by adding parameter --location='europe-west1

So my final query looked like this

bq query \
--use_legacy_sql=false \
--display_name='my_table' \
--location='europe-west1' \
'''create or replace table my_dataset.my_table as (select * from external_query('projects/my_mysql_connection/locations/europe-west1/connections/bi', '(select * from my_table)'))'''

Upvotes: 0

p13rr0m
p13rr0m

Reputation: 1297

I had the same issue. The way I solved it was to disable the editor tabs (there is a button at the top). Then opened the query settings and set the processing location to EU manually.

Upvotes: 0

Иван Борушев
Иван Борушев

Reputation: 1

I think trouble with time when start schedule. If it is in the past relative to local time, then bg tries to run the request on another server.

Upvotes: 0

AsmaG
AsmaG

Reputation: 507

I had the same problem while trying to create a scheduled query with python:

400 Cannot create a transfer in REGION_EUROPE_WEST_1 when destination dataset is located in JURISDICTION_EU

I figured out that even my project is located in europe-west1 but my destination dataset was located in multinational location: Europe. I had to update my parent path : parent=project_path to '{project_path}/locations/eu' so that it works.

I hope that it helps someone.

Upvotes: 4

Simon Ouellet
Simon Ouellet

Reputation: 99

It's look like as a bug from BQ. I got the same problems, with source and destination dataset located in EU both. I've change just for testing purpose the destination for an other EU dataset, and it works. I've finally update the scheduled query to use my first destination choice and now it works.. I can't explain why, but it's seem to be a workaround.

Upvotes: 3

Quest
Quest

Reputation: 11

I just scheduled query select 1 and then edited it to the needed one – it worked

Upvotes: 0

Dave
Dave

Reputation: 21

I had this error and tried many of the solutions in this thread. I tried a new session in an incognito window and it worked so I believe this is a transient issue as suggested.

Upvotes: 1

gmatharu
gmatharu

Reputation: 81

I faced the same issue recently.I tried 2 things and they worked:

  1. try setting the query location to destination dataset/table location, then try scheduling the query.
  2. If that does not work try to run the query and save results to the intended table in bigquery i.e. try creating the destination table with storing the results of the query you are trying to schedule first. Then try scheduling the query.

Both cases worked for me in different cases.

Upvotes: 0

Magda Kiwi
Magda Kiwi

Reputation: 511

Maybe, you can try with starting from the Scheduled Queries BigQuery UI and click on "+ create schedule query" button, then I don't get error. If I start directly in BigQuery UI I get the same error.

Upvotes: 1

Tan Duong
Tan Duong

Reputation: 1561

As I tried, it may happen because I have existed table with the same id as the destination table. This happens even if the table is the result of manually running that query and saved.

Upvotes: 0

Related Questions