Querying clustered tables
When you create a clustered table in BigQuery, the table data is automatically organized based on the contents of one or more columns in the table’s schema. The columns you specify are used to colocate related data. When you cluster a table using multiple columns, the order of columns you specify is important. The order of the specified columns determines the sort order of the data.
To optimize performance when you run queries against clustered tables, use an expression that filters on a clustered column or on multiple clustered columns in the order the clustered columns are specified. Queries that filter on clustered columns generally perform better than queries that filter only on non-clustered columns.
BigQuery sorts the data in a clustered table based on the values in the clustering columns and organizes them into blocks.
When you submit a query that contains a filter on a clustered column, BigQuery uses the clustering information to efficiently determine whether a block contains any data relevant to the query. This allows BigQuery to only scan the relevant blocks — a process referred to as block pruning.
You can query clustered tables by:
- Using the Cloud Console or the classic BigQuery web UI
- Using the command-line tool's
- Calling the jobs.insert API method and configuring a query job
- Using the client libraries
Currently, you can only use standard SQL with clustered tables.
At a minimum, to query a table, you must be granted
The following predefined Cloud IAM roles include
In addition, if a user has
bigquery.datasets.create permissions, when that
user creates a dataset, they are granted
bigquery.dataOwner access to it.
bigquery.dataOwner access gives the user the ability to query tables and views
in the dataset.
You must also be granted
bigquery.jobs.create permissions to run query jobs.
The following predefined Cloud IAM roles include
For more information on Cloud IAM roles and permissions in BigQuery, see Access control.
To get the best performance from queries against clustered tables, use the following best practices.
Sample table used in the examples
The sample table used in the examples on this page is a clustered table that is
created by using a DDL statement. The DDL statement creates a table named
ClusteredSalesData. The table is clustered by the following columns: first by
customer_id then by
product_id then by
CREATE TABLE `mydataset.ClusteredSalesData` PARTITION BY DATE(timestamp) CLUSTER BY customer_id, product_id, order_id AS SELECT * FROM `mydataset.SalesData`
Filter clustered columns in the order they're specified
When you specify a filter, use expressions that filter on the clustered columns in sort order.
The following query includes a filter expression that filters on
and then on
product_id. This query optimizes performance by filtering the
clustered columns in sort order.
SELECT SUM(totalSale) FROM `mydataset.ClusteredSalesData` WHERE customer_id = 10000 AND product_id LIKE 'gcp_analytics%'
The following query does not filter the clustered columns in sort order. As a
result, the performance of the query is not optimal. This query filters on
product_id then on
SELECT SUM(totalSale) FROM `mydataset.ClusteredSalesData` WHERE product_id LIKE 'gcp_analytics%' AND order_id = 20000
Do not use clustered columns in complex filter expressions
If you use a clustered column in a complex filter expression, the performance of the query is not optimized because block pruning cannot be applied.
For example, the following query will not prune blocks because a clustered
customer_id — is used in a function in the filter
SELECT SUM(totalSale) FROM `mydataset.ClusteredSalesData` WHERE CAST(customer_id AS STRING) = "10000"
To optimize query performance by pruning blocks, use simple filter expressions
like the following. In this example, a simple filter is applied to the
clustered column —
SELECT SUM(totalSale) FROM `mydataset.ClusteredSalesData` WHERE customer_id = 10000
Do not compare clustered columns to other columns
If a filter expression compares a clustered column to another column (either a clustered column or a non-clustered column), the performance of the query is not optimized because block pruning cannot be applied.
The following query does not prune blocks because the filter expression compares
a clustered column —
customer_id to another column —
SELECT SUM(totalSale) FROM `mydataset.ClusteredSalesData` WHERE customer_id = order_id
- For more information on running queries, see Running interactive and batch queries.
- To learn how to create and use clustered tables, see Creating and using 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.