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!
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.