BigQuery locations and regions

Every BigQuery dataset has one location, a region like europe-west1 or a multi-region like US or EU, and a query runs in the location of the data it reads. How locations are chosen, why a query across two locations fails, and how global queries change that.

Every BigQuery dataset has one location, and the tables in it are stored there. A location is either a region (europe-west1, asia-northeast1) or a multi-region (US, EU). A query runs in the location of the datasets it reads, so a query that reads datasets in two locations does not run unless global queries are enabled. You choose the location when you create the dataset and can’t change it afterwards.

Regions and multi-regions

A region is a specific geographic place, such as London (europe-west2) or Tokyo (asia-northeast1). A multi-region is a large area that contains many regions. BigQuery has two, US and EU. Choosing one lets BigQuery pick a single region inside it for storage and compute. Google documents the possible regions: US data is stored in one of us-central1, us-west1 or us-central2, and EU data in europe-west1 (Belgium) or europe-west4 (Netherlands). BigQuery picks the region automatically.

A multi-region is not a replicated copy across its regions. Data is stored in a single region, and compute runs only in that region, so a multi-region gives you no extra protection from a regional outage. For cross-region redundancy, Google has managed disaster recovery. For any location, BigQuery keeps copies of your data in two different zones inside the one region.

Multi-regions can provide larger quotas than a single region.

Google’s locations page also carries a note that BigQuery will stop using the term “multi-region” later, listing US and EU as regions colocated with us-central1 and europe-west4, with no change to where existing data lives. The page’s own footnote still lists three possible regions for US. Until that is sorted out, treat the footnote as the current behavior.

A location also sets prices. Google defines storage and analysis pricing per location. See how queries are billed.

A query runs where its data is

You don’t normally say where a query runs. BigQuery reads the locations of the datasets in the query and runs it there. These two queries are run from the same project with no location set, and each finds its own location:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`

SELECT name
FROM `bigquery-public-data.london_bicycles.cycle_stations`

samples is in US and london_bicycles is in EU. Both estimates came back without a location setting.

The same applies to writing. You can’t query a dataset in one location and write the results to a table in another. The location of every dataset in the request, read or written, must match the location of the job. DML runs where its target table lives.

A query across two locations

Put tables from the two locations in one query and BigQuery can’t pick a single location for it:

SELECT s.word, c.name
FROM `bigquery-public-data.samples.shakespeare` AS s
JOIN `bigquery-public-data.london_bicycles.cycle_stations` AS c
  ON s.word = c.name

The error says the table samples.shakespeare is not accessible “or perhaps it does not exist”. It does exist and the permissions are fine. BigQuery chose the EU as the location for the job and looked for samples.shakespeare there. Swap the join order and the error is the same. With the location set to US, the error names london_bicycles.cycle_stations instead. The message never says “location”, so this is a hard one to diagnose the first time. If a query says a table is missing or access is denied and you know it exists, compare the datasets’ locations.

A single region and the multi-region that contains it count as different locations. A job in US that reads a dataset in us-central1 is a cross-location query.

Global queries

A global query reads datasets in more than one location. It runs a subquery in each remote location, copies the result to the location the query runs in, keeps it there in a temporary table for 24 hours, and then runs the final query. The temporary data copy means that the data leaves its original location, which matters if you have data residency rules.

Global queries are switched off by default. They need the enable_global_queries_execution option on the project running the query, the enable_global_queries_data_access option on the project that holds the data, and the bigquery.jobs.createGlobalQuery permission, which only the BigQuery Admin role includes among predefined roles.

BigQuery pushes filters and column selection to the remote location where it can, so only the needed columns and matching rows are copied. Google says this depends on the query structure and that the data transferred can still be large. Start with a small subset and check how much moves.

To limit what is transferred, run the query in the location where most of the queried data is stored.

Choosing the location of a job

When you don’t set a location, BigQuery uses the destination table’s location for queries with a destination table, the target table for DML, and the location where the resource is created for DDL. For other queries that touch several locations the choice is arbitrary. To pin the location, use one of these:

-- Not run: must be the first statement of the query
SET @@location = 'EU';
SELECT name FROM `bigquery-public-data.london_bicycles.cycle_stations`;

In the bq tool use --location=EU, and in the API set location in the job reference. If you don’t specify a location, BigQuery may temporarily store the query in its router logs while it works out where to run it.

A dataset created with CREATE SCHEMA takes the location of the query unless you give one:

-- Not run: creates a dataset in Belgium
CREATE SCHEMA my_dataset
OPTIONS (location = 'europe-west1');

Locations and reservations

Capacity commitments and reservations are tied to a location. If you run a job outside the location of your reservation, the job is priced on-demand. A project with an EU reservation that queries a US dataset runs that query on-demand and not on the EU slots. See reservations.

INFORMATION_SCHEMA is per location

Metadata views are qualified by a region. The project’s job history in the US multi-region is `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, and the EU one is `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT. To list where your datasets are:

-- Not run: reads your project's metadata
SELECT schema_name, location
FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA;

Run it once per location you use. Without SQL, a table’s Details tab in Querylab.io shows its Data location (see Table information), and the Info view of a result shows the location the job ran in. Jobs are listed in the same way, see jobs.

Data around BigQuery has to be in the same place

External data must be colocated with the dataset: Cloud Storage buckets queried through external tables, Bigtable instances, and the Cloud SQL and Spanner instances behind federated queries. For a dataset in the US multi-region, a Cloud SQL or Spanner instance can be in any single region in the US.