EXECUTE IMMEDIATE ("query string")

[marcousescloud]

hi everybody,

I'm trying to launch the following query:

DECLARE ex_table STRING;
DECLARE ex_column STRING;
DECLARE query_text STRING;
set ex_table="read_table";
set ex_column="read_column";
SET query_text = CONCAT('SELECT * FROM my_project.my_dataset.', ex_table, ' WHERE column = "', ex_column, '"');
EXECUTE IMMEDIATE query_text ;

[I've tried also this version

SET query_text = CONCAT('SELECT * FROM `my_project.my_dataset.', ex_table, '` WHERE column = "', ex_column, '"');

]

Unfortunately, when launching, BQ returns the following error:
Not found: Dataset prj-isp-ddlr0-envs-prod-001:temp was not found in location US at [1:1].

`prj-isp-ddlr0-envs-prod-001.temp.produce`

I've read that, by default, BQ looks for projects defined in the US area and that, by using a string as a select command, it isn't able to recognize that the query is launched from a different area.

Does exist any trick that helps to programmatically work around this problem?

Can someone help me?

Thanks in advance.

Marco

[sraj]

It sounds like you're encountering a common issue with BigQuery when working across different regions. Here's a trick to work around this problem:

Specify the Project and Dataset Location: Ensure that you explicitly specify the project and dataset location in your query. You can do this by using the LOCATION clause in your CREATE TABLE or SELECT statements.

Use the LOCATION Clause: Modify your query to include the LOCATION clause, which tells BigQuery the exact location of the dataset. Here's an example:
DECLARE ex_table STRING;
DECLARE ex_column STRING;
DECLARE query_text STRING;
SET ex_table = "read_table";
SET ex_column = "read_column";
SET query_text = CONCAT('SELECT * FROM `my_project.my_dataset.', ex_table, '` WHERE column = "', ex_column, '"');
EXECUTE IMMEDIATE query_text;
Check Project and Dataset Configuration: Make sure that your project and dataset are correctly configured in the Google Cloud Con

[marckevin · Accepted solution]

Hi @marcousescloud,

Welcome to Google Cloud Community!

“Not found: Dataset prj-isp-ddlr0-envs-prod-001:temp was not found in location US at [1:1]” suggest an error when executing a dynamic queries like EXECUTE IMMEDIATE where BigQuery assign a default location on US Region, as they can't be used to automatically determine the region of a query.

As quoted from this documentation, here are possible suggestions that you can try on how to specify a location when BigQuery returns an error on the location mismatched for dataset:

• When you query data using the Google Cloud console in the query editor, click settings More > Query settings, expand Advanced options, and then select your Data location.
• When you use the bq command-line tool, supply the --location global flag and set the value to your location.

You can try this bq command: bq --project_id=<project_id> query --use_legacy_sql=false --location=<location> "EXECUTE IMMEDIATE(QUERY)";

• When you use the API, specify your region in the location property in the jobReference section of the job resource.

You can also check this similar StackOverflow discussion as an additional guide.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[marcousescloud]

Thank you very much, marckevin. 😊