Manage clustered tables

This document describes how to get information about and control access toclustered tables in BigQuery.

For more information, see the following:

• To learn about clustered tablesupport in BigQuery, seeIntroduction to clustered tables.
• To learn how to create clustered tables, seeCreate clustered tables.

Before you begin

To get information about tables, you must have thebigquery.tables.get permission. The following predefined IAMroles includebigquery.tables.get permissions:

• roles/bigquery.metadataViewer
• roles/bigquery.dataViewer
• roles/bigquery.dataOwner
• roles/bigquery.dataEditor
• roles/bigquery.admin

In addition, if a user has thebigquery.datasets.create permission, when thatuser creates a dataset, they are grantedbigquery.dataOwner access to it.bigquery.dataOwner access gives the user the ability to get information abouttables in a dataset.

For more information about IAM roles and permissions inBigQuery, seePredefined roles and permissions.

Control access to clustered tables

To configure access to tables and views, you can grant anIAM role to an entity at the following levels, listed inorder of range of resources allowed (largest to smallest):

• a high level in theGoogle Cloud resource hierarchysuch as the project, folder, or organization level
• the dataset level
• the table or view level

You can also restrict data access within tables, by using the followingmethods:

• column-level security
• column data masking
• row-level security

Access with any resource protected by IAM is additive. Forexample, if an entity does not have access at the high level such as a project,you could grant the entity access at the dataset level, and then the entity willhave access to the tables and views in the dataset. Similarly, if the entitydoes not have access at the high level or the dataset level, you could grant theentity access at the table or view level.

Granting IAM roles at a higher level in theGoogle Cloudresource hierarchysuch as the project, folder, or organization level gives the entity access to abroad set of resources. For example, granting a role to an entity at the projectlevel gives that entity permissions that apply to all datasets throughout theproject.

Granting a role at the dataset level specifies the operations an entity isallowed to perform on tables and views in that specific dataset, even if theentity does not have access at a higher level. For information on configuringdataset-level access controls, seeControlling access to datasets.

Granting a role at the table or view level specifies the operations an entity isallowed to perform on specific tables and views, even if the entity does nothave access at a higher level. For information on configuring table-level accesscontrols, seeControlling access to tables and views.

You can also createIAM custom roles.If you create a custom role, the permissions you grant depend on the specificoperations you want the entity to be able to perform.

You can't set a "deny" permission on any resource protected byIAM.

For more information about roles and permissions, seeUnderstanding rolesin the IAM documentation and the BigQueryIAM roles and permissions.

Get information about clustered tables

Select one of the following options:

bqshow\--schema\--format=prettyjson\PROJECT_ID:DATASET.TABLE

bq show --format=prettyjson mydataset.myclusteredtable

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

List clustered tables in a dataset

The permissions required to list clustered tables and the steps to list themare the same as for standard tables. For more information, seeListing tables in a dataset.

Modify the clustering specification

You can change or remove a table's clustering specifications, or change the setof clustered columns in a clustered table. This method of updating theclustering column set is useful for tables that use continuous streaming insertsbecause those tables cannot be easily swapped by other methods.

Follow these steps to apply a new clustering specification to unpartitioned orpartitioned tables.

1. In the bq tool, update the clustering specification of yourtable to match the new clustering:

    bq update --clustering_fields=CLUSTER_COLUMNDATASET.ORIGINAL_TABLE

   Replace the following:

   • CLUSTER_COLUMN: the column you are clustering on—for example,mycolumn
   • DATASET: the name of the dataset containing the table—for example,mydataset
   • ORIGINAL_TABLE: the name of your original table—for example,mytable

   You can also call thetables.update ortables.patch API method tomodify the clustering specification.

2. To cluster all rows according to the new clustering specification,run the followingUPDATE statement:

   UPDATEDATASET.ORIGINAL_TABLESETCLUSTER_COLUMN=CLUSTER_COLUMNWHEREtrue

   Note: If a new clustering specification is applied to a table that is inlong-term storage, then the table reverts to active storage pricing.For more information, seeStorage pricing.

What's next

• For information about querying clustered tables, seeQuery clustered tables.
• For an overview of partitioned table support in BigQuery, seeIntroduction to partitioned tables.
• To learn how to create partitioned tables, seeCreate partitioned tables.