CODEWITHSUNDEEP
google-bigquerybusiness-intelligencelooker-studio
Aldo
Aldo

Reputation: 11

Looker Studio BigQuery Custom Query returns error Not found: Dataset -:- was not found in location US

I'm adding a new data source to a Looker Studio report; using the BigQuery connector and a Custom Query. The following custom query on a BigQuery dataset returns a location error:

EXECUTE IMMEDIATE 'SELECT * from `myProject.myDataset.accounts`'
/* LIMIT 10*/

*Sorry, we encountered an error and were unable to complete your request.

Not found: Dataset twilio-hosted-2:al_gila_3634 was not found in location US at [1:1]

Error ID: 52615300 *

(same result also omitting backticks)

I'm aware of this: Error of location with EXECUTE IMMEDIATE on Bigquery but it doesn't help in the context of a Looker Studio custom query where I wouldn't know how to set the location. My dataset is in europe-west6

To give some context, of course this is a simplified query and I know I can run it without "Execute Immediate", but I need to build a query where I can change the Bigquery dataset name based on a parameter, because the report must work with different datasets with identical structure - and I don't want to maintain an individual report for every dataset.

Any help will be greatly appreciated!

Upvotes: 0

Views: 244

Answers (1)

Matthew Medlyn
Matthew Medlyn

Reputation: 13

I had this exact problem yesterday. Looker defaults to location US for custom queries (thanks yanks!), if you're in europe-west6, your data isn't these.

There's a query you can run that will create a replica of your dataset in the US location so Looker can access it. There's probably cost and location-privacy concerns here, but I couldn't figure out another way

ALTER SCHEMA `name-of-dataset`
ADD REPLICA `name-of-dataset`
OPTIONS(location='US');

You might run into trouble further down the line, as I've discovered Looker wraps its own queries around the execute command, which then fail. Have raised a question about this.

Upvotes: 0

Related Questions