Creating and using clustered tables

This document describes how to create and use clustered tables in BigQuery.

Limitations

Clustered tables in BigQuery are subject to the following limitations:

• Currently, clustering is supported only for partitioned tables.
• Only standard SQL is supported for querying clustered tables and for writing query results to clustered tables.
• You can only specify clustering columns when a table is created.
• After a clustered table is created, clustering columns cannot be modified.
• Clustering columns must be top-level, non-repeated columns of one of the following types: INT64, STRING, DATE, TIMESTAMP, BOOL, NUMERIC, or GEOGRAPHY. For more information on data types, see Standard SQL data types.
• You can specify up to four clustering columns.

Creating clustered tables

Currently, you can only cluster a partitioned table. This includes both ingestion-time partitioned tables and tables partitioned by a TIMESTAMP or DATE column.

You can create a clustered table in BigQuery:

• From query results
  • By using a DDL CREATE TABLE AS SELECT statement
  • By running a query that creates a clustered destination table
• By using a DDL CREATE TABLE statement with a CLUSTER BY clause containing a clustering_column_list
• Manually using the command line tool's bq mk command
• Programmatically by calling the tables.insert API method
• When you load data

When you create a clustered table in BigQuery, the table name must be unique per dataset. The table name can:

• Contain up to 1,024 characters
• Contain letters (upper or lower case), numbers, and underscores

Required permissions

To create a clustered table, you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.create permissions. The following predefined, project-level IAM roles include bigquery.tables.create permissions:

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can create clustered tables in any dataset that user creates. By default, when a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Creating an empty clustered table with a schema definition

You specify clustering columns when you create a table in BigQuery. After the table is created, you cannot modify the clustering columns. Currently, you can only specify clustering columns for a partitioned table.

Clustering columns must be top-level, non-repeated columns, and they must be one of the following simple data types: INTEGER, STRING, DATE, TIMESTAMP, BOOLEAN, NUMERIC, or GEOGRAPHY.

You can specify up to four clustering columns. When you specify multiple columns, the order of the columns determines how the data is sorted. For example, if the table is clustered by columns a, b and c, the data is sorted in the same order — first by column a, then by column b, and then by column c. As a best practice, the most frequently filtered or aggregated column should appear first.

The order of your clustering columns also affects query performance and pricing. For more information on query best practices for clustered tables, see Querying clustered tables.

To create an empty clustered table with a schema definition:

Console

1. Open the BigQuery web UI in the GCP Console.
   Go to the BigQuery web UI

2. In the navigation panel, in the Resources section, expand your project and select a dataset.

3. On the right side of the window, in the details panel, click Create table. The process for loading data is the same as the process for creating an empty table.

   

4. On the Create table page, in the Source section, for Create table from, select Empty table.

   

5. On the Create table page, in the Destination section:

   • For Dataset name, choose the appropriate dataset, and in the Table name field, enter the name of the table you're creating in BigQuery.

     

   • Verify that Table type is set to Native table.

6. In the Schema section, enter the schema definition.

   • Enter schema information manually by one of the following methods:

     • Enable Edit as text and enter the table schema as a JSON array.

       Note: You can view the schema of an existing table in JSON format by entering the following command: bq show --format=prettyjson [DATASET].[TABLE].

     • Use Add field to manually input the schema.

7. In the Partition and cluster settings section:

   • For Partitioning, select Partition by ingestion time.

     

   • For Clustering order, enter between one and four comma-separated column names.

8. Click Create table.

Note: When you create an empty clustered table using the BigQuery web UI, you cannot add a label, description, table expiration, or partition expiration. You can add these optional properties when you create a table using the CLI or API.

After the table is created, you can update the clustered table's table expiration, description, and labels. You cannot add a partition expiration after a table is created using the BigQuery web UI.

Classic UI

1. Go to the classic BigQuery web UI.

   Go to the classic BigQuery web UI

2. Click the down arrow icon next to your dataset name in the navigation and click Create new table.

3. On the Create Table page, in the Source Data section, click Create empty table.

4. On the Create Table page, in the Destination Table section:

   • For Table name, choose the appropriate dataset, and in the table name field, enter the name of the table you're creating.
   • Verify that Table type is set to Native table.

5. In the Schema section, manually enter the schema definition.

   • You can enter schema information manually by:

     • Clicking Edit as text and entering the table schema as a JSON array.

       Note: You can view the schema of an existing table in JSON format by entering the following command: bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE].

     • Using Add Field to input the schema.

6. In the Options section:

   • For Partitioning Type, click None and choose Day.
   • For Partitioning Field, choose one of the following:
     • Choose timestamp to create a table partitioned by a DATE or TIMESTAMP column.
     • Choose _PARTITIONTIME to create an ingestion-time partitioned table.
   • For Clustering columns, enter between one and four field names.

   • For Encryption Type, leave the Default option. This property is for customer-managed encryption keys. By default, BigQuery encrypts customer content stored at rest.

     

7. Click Create Table.

Note: When you create an empty clustered table using the BigQuery web UI, you cannot add a label, description, table expiration, or partition expiration. You can add these optional properties when you create a table using the CLI or API.

After the table is created, you can update the clustered table's table expiration, description, and labels. You cannot add a partition expiration after a table is created using the BigQuery web UI.

CLI

Use the mk command with the following flags:

• --table (or the -t shortcut).
• --schema &mdash: You can supply the table's schema definition inline or via a JSON schema file.
• Either --time_partitioning_type (for ingestion-time partitioned tables) or --time_partitioning_field (for partitioned tables). Currently, DAY is the only supported value for --time_partitioning_type.
• --clustering_fields to specify up to four clustering columns.

Optional parameters include --expiration, --description, --time_partitioning_expiration, --destination_kms_key, and --label.

If you are creating a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

--destination_kms_key is not demonstrated here. For more information on using this flag, see customer-managed encryption keys.

Enter the following command to create an empty clustered table with a schema definition:

bq mk --table --expiration [INTEGER1] \
--schema [SCHEMA] \
--time_partitioning_type=DAY \
--time_partitioning_field [COLUMN] \
--clustering_fields [COLUMNS] \
--time_partitioning_expiration [INTEGER2] \
--description "[DESCRIPTION]" \
--label [KEY:VALUE, KEY:VALUE] \
[PROJECT_ID]:[DATASET].[TABLE]

Where:

• [INTEGER1] is the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current UTC time plus the integer value. If you set the table's expiration time when you create a time-partitioned table, the dataset's default table expiration setting is ignored. Setting this value deletes the table and all partitions after the specified time.
• [SCHEMA] is an inline schema definition in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] or the path to the JSON schema file on your local machine.
• [COLUMN] is the name of the TIMESTAMP or DATE column used to create a partitioned table. If you create a partitioned table, you do not need to specify the --time_partitioning_type=DAY flag.
• [COLUMNS] is a comma-separated list of up to four clustering columns.
• [INTEGER2] is the default lifetime (in seconds) for the table's partitions. There is no minimum value. The expiration time evaluates to the partition's date plus the integer value. The partition expiration is independent of the table's expiration but does not override it. If you set a partition expiration that is longer than the table's expiration, the table expiration takes precedence.
• [DESCRIPTION] is a description of the table in quotes.
• [KEY:VALUE] is the key:value pair that represents a label. You can enter multiple labels using a comma-separated list.
• [PROJECT_ID] is your project ID.
• [DATASET] is a dataset in your project.
• [TABLE] is the name of the partitioned table you're creating.

When you specify the schema on the command line, you cannot include a RECORD (STRUCT) type, you cannot include a column description, and you cannot specify the column's mode. All modes default to NULLABLE. To include descriptions, modes, and RECORD types, supply a JSON schema file instead.

Examples:

Enter the following command to create a clustered table named myclusteredtable in mydataset in your default project. The table is a partitioned table (partitioned by a TIMESTAMP column). The partitioning expiration is set to 86,400 seconds (1 day), the table's expiration is set to 2,592,000 (1 30-day month), the description is set to This is my clustered table, and the label is set to organization:development. The command uses the -t shortcut instead of --table.

The schema is specified inline as: timestamp:timestamp,customer_id:string,transaction_amount:float. The specified clustering field customer_id is used to cluster the partitions.

bq mk -t --expiration 2592000 \
--schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' \
--time_partitioning_field timestamp \
--clustering_fields customer_id \
--time_partitioning_expiration 86400  \
--description "This is my clustered table" \
--label org:dev \
mydataset.myclusteredtable

Enter the following command to create a clustered table named myclusteredtable in myotherproject, not your default project. The table is an ingestion-time partitioned table. The partitioning expiration is set to 259,200 seconds (3 days), the description is set to This is my partitioned table, and the label is set to organization:development. The command uses the -t shortcut instead of --table. This command does not specify a table expiration. If the dataset has a default table expiration, it is applied. If the dataset has no default table expiration, the table will never expire, but the partitions will expire in 3 days.

The schema is specified in a local JSON file: /tmp/myschema.json. The customer_id field is used to cluster the partitions.

bq mk -t --expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_type=DAY \
--clustering_fields=customer_id \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.myclusteredtable

After the table is created, you can update the partitioned table's table expiration, partition expiration, description, and labels.

API

Call the tables.insert method with a defined table resource that specifies the timePartitioning property, the clustering.fields property, and the schema property.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "timestamp",
		Expiration: 90 * 24 * time.Hour,
	},
	Clustering: &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	},
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

Creating a clustered table from a query result

There are two ways to create a clustered table from a query result:

• Write the results to a new destination table and specify the clustering columns. This method is discussed below.
• By using a DDL CREATE TABLE AS SELECT statement. For more information on this method, see Creating a clustered table from the result of a query on the Using Data Definition Language statements page.

You can create a clustered table by querying either a partitioned table or a non-partitioned table. You cannot change an existing table to a clustered table using query results.

When you create a clustered table from a query result, you must use standard SQL. Currently, legacy SQL is not supported for querying clustered tables or for writing query results to clustered tables.

Console

You cannot specify clustering options for a destination table when you query data using the console BigQuery web UI unless you use a DDL statement. For more information, see Using Data Definition Language statements.

Classic UI

You cannot specify clustering options for a destination table when you query data using the classic BigQuery web UI unless you use a DDL statement. For more information, see Using Data Definition Language statements.

CLI

Enter the following command to create a new, clustered destination table from a query result:

    bq --location=[LOCATION] query --use_legacy_sql=false '[QUERY]'

Where:

• [LOCATION] is the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• [QUERY] is a query in standard SQL syntax. Currently, you cannot use legacy SQL to query clustered tables or to write query results to clustered tables. The query can contain a CREATE TABLE DDL statement that specifies the options for creating your clustered table. You can use DDL rather than specifying the individual command-line flags.

Examples:

Enter the following command to write query results to a clustered destination table named myclusteredtable in mydataset. mydataset is in your default project. The query retrieves data from a non-partitioned table — mytable. The table's customer_id column is used to cluster the table. The table's timestamp column is used to create a partitioned table.

bq query --use_legacy_sql=false \
'CREATE TABLE
   mydataset.myclusteredtable
 PARTITION BY
   DATE(timestamp)
 CLUSTER BY
   customer_id AS
 SELECT
   *
 FROM
   `mydataset.mytable`'

API

To save query results to a clustered table, call the jobs.insert method, configure a query job, and include a CREATE TABLE DDL statement that creates your clustered table.

Specify your location in the location property in the jobReference section of the job resource.

Creating a clustered table when you load data

You can create a clustered table by specifying clustering columns when you load data into a new table. You do not need to create an empty table before loading data into it. You can create the clustered table and load your data at the same time.

For more information on loading data, see Introduction to loading data into BigQuery.

To define clustering when defining a load job:

API

To define clustering configuration when creating a table via a load job, you can populate the configuration.load.clustering message, populating the configuration.load.clustering.Fields property with up to four clustering columns in prioritized order.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field: "timestamp",
}
loader.Clustering = &bigquery.Clustering{
	Fields: []string{"origin", "destination"},
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

Controlling access to clustered tables

You cannot assign access controls directly to clustered tables or to partitions. You can control table access by configuring access controls at the dataset level or at the project level.

Dataset-level access controls specify the operations users, groups, and service accounts are allowed to perform on tables in that specific dataset. If you assign only dataset-level permissions, you must also assign a primitive or predefined, project-level role that provides access to the project, for example, bigquery.user.

Instead of granting access to individual datasets, you can assign predefined, project-level IAM roles that grant permissions to all table data in all datasets in a project.

You can also create IAM custom roles. If you create a custom role, the permissions you grant depend on the table operations you want the user, group, or service account to be able to perform.

For more information on roles and permissions, see:

• Understanding roles in the IAM documentation
• BigQuery Access controls
• Controlling access to datasets.

Using clustered tables

Getting information about clustered tables

You can get information about tables by:

• Using the GCP Console or the classic BigQuery web UI
• Using the bq show CLI command
• Calling the tables.get API method
• Querying the INFORMATION_SCHEMA views (beta)

Required permissions

To get information about tables, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.get permissions. If you are granted bigquery.tables.get permissions at the project level, you can get information about all tables in the project. All predefined, project-level IAM roles include bigquery.tables.get permissions except for bigquery.jobUser and bigquery.user.

In addition, a user assigned the bigquery.user role has bigquery.datasets.create permissions. This allows a user assigned to the bigquery.user role to get information about tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Getting clustered table information

To view information about a clustered table:

Console

1. In the Resources pane, click your dataset name to expand it, then click the table name you want to view.

2. Click Details. This page displays the table's details including the clustering columns.

   

Classic UI

1. In the navigation pane, down arrow icon to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

2. Click the table name.

3. Click Details. The Table Details page displays the table's details including the clustering columns.

   

CLI

Issue the bq show command to display all table information. Use the --schema flag to display only table schema information. The --format flag can be used to control the output.

If you are getting information about a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Where:

• [PROJECT_ID] is your project ID.
• [DATASET] is the name of the dataset.
• [TABLE] is the name of the table.

Examples:

Enter the following command to display all information about myclusteredtable in mydataset. mydataset is in your default project.

bq show --format=prettyjson mydataset.myclusteredtable

The output should look like the following:

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

Call the bigquery.tables.get method and provide any relevant parameters.

Getting clustered table information using INFORMATION_SCHEMA (beta)

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, tables, and views.

You can query the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_OPTIONS views to retrieve metadata about tables and views in a project. You can also query the INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.COLUMN_FIELD_PATHS views to retrieve metadata about the columns (fields) in a table.

For clustered tables, you can query the CLUSTERING_ORDINAL_POSITION column in the INFORMATION_SCHEMA.COLUMNS view to retrieve information about your clustering columns.

TABLES view

When you query the INFORMATION_SCHEMA.TABLES view, the query results contain one row for each table or view in a dataset.

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables or views.

The INFORMATION_SCHEMA.TABLES view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	STRING	The name of the project that contains the dataset
TABLE_SCHEMA	STRING	The name of the dataset that contains the table or view also referred to as the datasetId
TABLE_NAME	STRING	The name of the table or view also referred to as the tableId
TABLE_TYPE	STRING	The table type: BASE TABLE: A BigQuery table VIEW: A BigQuery view EXTERNAL: A table that references an external data source
IS_INSERTABLE_INTO	STRING	YES or NO depending on whether the table supports DML INSERT statements
IS_TYPED	STRING	The value is always NO
CREATION_TIME	TIMESTAMP	The table's creation time

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.TABLES view except for is_typed which is reserved for future use. The metadata returned is for all tables in mydataset in your default project — myproject.

mydataset contains the following tables:

• mytable1: a standard BigQuery table
• myview1: a BigQuery view

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    * EXCEPT(is_typed)
   FROM
    mydataset.INFORMATION_SCHEMA.TABLES
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES'

The results should look like the following:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

Example 2:

The following example retrieves all tables of type BASE TABLE from the INFORMATION_SCHEMA.TABLES view. The is_typed column is excluded. The metadata returned is for tables in mydataset in your default project — myproject.

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    * EXCEPT(is_typed)
   FROM
    mydataset.INFORMATION_SCHEMA.TABLES
   WHERE
    table_type="BASE TABLE"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

The results should look like the following:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS view

When you query the INFORMATION_SCHEMA.TABLE_OPTIONS view, the query results contain one row for each table or view in a dataset.

Queries against the INFORMATION_SCHEMA.TABLE_OPTIONS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables or views.

The INFORMATION_SCHEMA.TABLE_OPTIONS view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	STRING	The name of the project that contains the dataset
TABLE_SCHEMA	STRING	The name of the dataset that contains the table or view also referred to as the datasetId
TABLE_NAME	STRING	The name of the table or view also referred to as the tableId
OPTION_NAME	STRING	One of the name values in the options table
OPTION_TYPE	STRING	One of the data type values in the options table
OPTION_VALUE	STRING	One of the value options in the options table

Options table

OPTION_NAME	OPTION_TYPE	OPTION_VALUE
partition_expiration_days	FLOAT64	The default lifetime, in days, of all partitions in a partitioned table
expiration_timestamp	FLOAT64	The default lifetime, in days, of the table
kms_key_name	STRING	The name of the Cloud KMS key used to encrypt the table
friendly_name	STRING	The table's descriptive name
description	STRING	A description of the table
labels	ARRAY<STRUCT<STRING, STRING>>	An array of STRUCT's that represent the labels on the table

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

Example 1:

The following example retrieves the default table expiration times for all tables in mydataset in your default project (myproject) by querying the INFORMATION_SCHEMATA.TABLE_OPTIONS view.

Queries against the INFORMATION_SCHEMA.TABLE_OPTIONS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    option_name="expiration_timestamp"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="expiration_timestamp"'

The results should look like the following:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Note: Tables without an expiration time are excluded from the query results.

Example 2:

The following example retrieves metadata about all tables in mydataset that contain test data. The query uses the values in the description option to find tables that contain "test" anywhere in the description. mydataset is in your default project — myproject.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    option_name="description" AND option_value LIKE "%test%"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="description" AND option_value LIKE "%test%"'

The results should look like the following:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS view

When you query the INFORMATION_SCHEMA.COLUMNS view, the query results contain one row for each column (field) in a table.

Queries against the INFORMATION_SCHEMA.COLUMNS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

The INFORMATION_SCHEMA.COLUMNS view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	STRING	The name of the project that contains the dataset
TABLE_SCHEMA	STRING	The name of the dataset that contains the table also referred to as the datasetId
TABLE_NAME	STRING	The name of the table or view also referred to as the tableId
COLUMN_NAME	STRING	The name of the column
ORDINAL_POSITION	INT64	The 1-indexed offset of the column within the table; if it's a pseudo column such as _PARTITIONTIME or _PARTITIONDATE, the value is NULL
IS_NULLABLE	STRING	YES or NO depending on whether the column's mode allows NULL values
DATA_TYPE	STRING	The column's standard SQL data type
IS_GENERATED	STRING	The value is always NEVER
GENERATION_EXPRESSION	STRING	The value is always NULL
IS_STORED	STRING	The value is always NULL
IS_HIDDEN	STRING	YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_UPDATABLE	STRING	The value is always NULL
IS_SYSTEM_DEFINED	STRING	YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_PARTITIONING_COLUMN	STRING	YES or NO depending on whether the column is a partitioning column
CLUSTERING_ORDINAL_POSITION	STRING	The 1-indexed offset of the column within the table's clustering columns; the value is NULL if the table is not a clustered table

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMNS view for the population_by_zip_2010 table in the census_bureau_usa dataset. This dataset is part of the BigQuery public dataset program.

Because the table you're querying is in another project, the bigquery-public-data project, you add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

The following columns are excluded from the query results because they are currently reserved for future use:

• IS_GENERATED
• GENERATION_EXPRESSION
• IS_STORED
• IS_UPDATABLE

Queries against the INFORMATION_SCHEMA.COLUMNS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
   FROM
    `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
   WHERE
    table_name="population_by_zip_2010"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
 FROM
   `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
 WHERE
   table_name="population_by_zip_2010"'

The results should look like the following. For readability, table_catalog and table_schema are excluded from the results:

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

COLUMN_FIELD_PATHS view

When you query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, the query results contain one row for each column nested within a RECORD (or STRUCT) column.

Queries against the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	>STRING	The name of the project that contains the dataset
TABLE_SCHEMA	STRING	The name of the dataset that contains the table also referred to as the datasetId
TABLE_NAME	STRING	The name of the table or view also referred to as the tableId
COLUMN_NAME	STRING	The name of the column
FIELD_PATH	STRING	The path to a column nested within a `RECORD` or `STRUCT` column
DATA_TYPE	STRING	The column's standard SQL data type
DESCRIPTION	STRING	The column's description

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for the commits table in the github_repos dataset. This dataset is part of the BigQuery public dataset program.

Because the table you're querying is in another project, the bigquery-public-data project, you add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW]; for example, `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

The commits table contains the following nested and nested and repeated columns:

• author: nested RECORD column
• committer: nested RECORD column
• trailer: nested and repeated RECORD column
• difference: nested and repeated RECORD column

Your query will retrieve metadata about the author and difference columns.

Queries against the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    *
   FROM
    `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
   WHERE
    table_name="commits"
    AND column_name="author"
    OR column_name="difference"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
 WHERE
   table_name="commits"
   AND column_name="author"
   OR column_name="difference"'

The results should look like the following. For readability, table_catalog and table_schema are excluded from the results.

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

Listing clustered tables in a dataset

You can list clustered tables in datasets by using the GCP Console, the classic BigQuery web UI, the bq ls CLI command or by calling the tables.list API method.

The permissions required to list clustered tables, and the steps to list them are the same as for partitioned tables. For more information on listing tables, see Listing partitioned tables in a dataset.

Features under development

The following features are being developed but are not currently available in the alpha release:

• Support for clustering native (non-partitioned) tables.
• Reducing the costs for particular types of queries that use filters on clustering columns.

Next steps

• For an overview of clustered table support in BigQuery, see Introduction to clustered tables.
• For information on querying clustered tables, see Querying clustered tables.
• For an overview of partitioned table support in BigQuery, see Introduction to partitioned tables.
• To learn how to create and use ingestion-time partitioned tables, see Creating and using ingestion-time partitioned tables.
• To learn how to create and use partitioned tables, see Creating and using partitioned tables.
• To see an overview of INFORMATION_SCHEMA, go to Introduction to BigQuery INFORMATION_SCHEMA.