Projects, datasets and tables in BigQuery

A BigQuery table is named project.dataset.table. The project is also what a query is billed to, the dataset holds the location and access rules, and names are case-sensitive. How to write the path, when backticks are needed, and what each level controls.

A BigQuery table is addressed as project.dataset.table. The project is a Google Cloud project, the dataset is a container inside it, and the table is inside the dataset. The project also decides who pays: a query is billed to the project that runs it, which does not have to be the project that holds the data.

What each level is for

A project is the Google Cloud project. It has a billing account, quotas and IAM policies. Jobs (queries, loads, exports) always belong to a project, and so do connections. Storage is charged to the project where the dataset lives. With on-demand pricing, a query is billed to the project that runs it. In Querylab.io the billing project is set per tab in the status bar’s ⚙ popover (cost limits).

A dataset is a container for tables, views, functions and procedures. It is close to what other databases call a schema. A dataset has a location, and every table in it is stored there. You can’t change the location after you create the dataset. Datasets are also where access control is usually set, and where you choose whether storage is billed on logical or physical bytes. See locations.

A table holds the rows. Other kinds of tables, such as views and external tables, are covered in table types.

Above projects there are folders and an organization. They group projects for access policies and nothing in a query path refers to them.

Writing the path

A table in a query can be written these ways:

-- Not run: fully qualified, works from any project you can access
SELECT word FROM `bigquery-public-data.samples.shakespeare`;

-- Project in its own quotes: same table
SELECT word FROM `bigquery-public-data`.samples.shakespeare;

-- Without a project: BigQuery uses the project the query runs in
SELECT word FROM samples.shakespeare;

The third form is where people get stuck. Run from your own project, it looks for a dataset named samples in that project and fails with “Not found: Dataset your-project:samples was not found in location US”. The table is public, but the name was resolved against your project. Name the project and it works from anywhere.

A table name with no dataset at all is an error:

SELECT word
FROM shakespeare

BigQuery asks for the dataset. The one exception is a temporary table, which has no dataset and is referenced by its bare name:

CREATE TEMP TABLE orders AS
SELECT 1 AS id, 40 AS total UNION ALL
SELECT 2, 15;

SELECT id, total
FROM orders
ORDER BY id;

CREATE TEMP TABLE makes a table that lives for the duration of the script or session. See temporary tables.

Backticks and dashes

Project IDs often contain dashes, and the rules for where a dash is allowed are in how BigQuery SQL differs from other SQL. In short: in a FROM clause only the first part of the path may contain dashes unquoted, a dataset name can never contain one, and a table name with a dash needs backticks. Backticks can wrap the whole path or one part of it.

The separator between project and dataset is a dot. The project:dataset.table form that the bq command-line tool uses is rejected inside a query: “Project name needs to be separated by dot from dataset name, not by colon”.

Repeated dots collapse. Google documents that duplicate dots in a path are stripped, so this reads the same table:

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

The estimate is the same 1,332,943 bytes as a plain bigquery-public-data.samples.shakespeare.

Naming rules

Dataset names can be up to 1,024 characters of letters, numbers and underscores. A dataset name can’t contain spaces, dashes or other special characters. A dataset whose name starts with an underscore is hidden: it is left out of the Explorer panel and of every INFORMATION_SCHEMA view, though you can still query its tables.

Table names can be up to 1,024 UTF-8 bytes and may contain Unicode letters, marks, numbers, connectors such as underscore, dashes and spaces. table 01, étudiant-01 and 00_お客様 are all valid names. A name with a space or a dash needs backticks in SQL.

Names are case-sensitive

Dataset and table names are case-sensitive by default, so mytable and MyTable can exist side by side in one dataset. A query that gets the case wrong doesn’t find the table:

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

Column names are not case-sensitive, and neither are keywords or aliases; the full list is in how BigQuery SQL differs from other SQL. A dataset can be created with case-insensitivity turned on, and then mydataset and MyDataset can’t coexist and the names match regardless of case.

Listing what is in a project

INFORMATION_SCHEMA views list datasets and tables. They are queried, not run here, because they read your own metadata. Datasets are listed per location:

-- Not run: lists the datasets in your project that are in the US multi-region
SELECT schema_name, location
FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA;

-- Not run: lists the tables in one dataset
SELECT table_name, table_type
FROM `my-project.my_dataset`.INFORMATION_SCHEMA.TABLES;

In INFORMATION_SCHEMA a dataset is called a schema, the same word other databases use. For the whole set of views, see INFORMATION_SCHEMA.

In Querylab.io the sidebar shows the same hierarchy of projects, datasets and tables. See navigating resources. Right-click a dataset and choose SQL Templates, then Information Schema, to get its INFORMATION_SCHEMA views as ready SELECT statements (DDL operations).