Setting Query Location in GCP BigQuery SQL Script

[mohamedsithik]

Hello Everyone,

Greetings!

I need assistance with setting the query location in my SQL script for GCP BigQuery.

My GCP BigQuery dataset, project, and GCS bucket are all located in the me-central2 region (Saudi Arabia, Dammam). I also have SQL queries saved in the me-central2 region. However, when running the script, GCP BigQuery throws an error indicating that it cannot find the dataset or table in the US location. This suggests that the script is executing in the US region by default.

To resolve this, I currently have to manually go to the settings, select the advanced options, and set the region to me-central2. How can I specify the me-central2 region permanently in the script to avoid this issue?

Thank you for your support.  

Mdsithik

[TimotiSburv]

Hello, 

To address your issue, you can set the location in the query job configuration. If you're running the query programmatically whether using Python, Java, or the bq command-line tool you can specify the location directly within the query configuration.

For scripts executed through the BigQuery API or SDKs, the location parameter can be defined in the request payload to ensure the query runs in the correct region.

Although there isn’t a direct way to permanently embed the location in the SQL script itself, you can manually select the correct region in the BigQuery Console UI before running the script. Keep in mind, though, that this step will need to be repeated if you frequently switch between regions.

For the bq command-line tool, you can set a default region by configuring it in your settings file, which eliminates the need to specify the region each time you run a query.

Finally, make sure your datasets are explicitly created in the intended region (me-central2 in this case). This helps prevent BigQuery from defaulting to the US location.

Hope this helps

[Nandha100]

Hi @mohamedsithik ,
While saving the Query script, it will ask the region option where to save the query. For your reference added the screenshot do save the query where you want.

[kubaptak]

There is now a @@location variable (in Public Preview) that you can use for this purpose:
SET @@location = "me-central2"; -- add at the top of the script.