Clustered tables in BigQuery

A clustered BigQuery table keeps rows with similar values in up to four columns stored together, so a filter on those columns reads fewer blocks. How column order decides what gets pruned, which filters defeat it, and why the cost estimate is only an upper bound.

A clustered table is sorted by up to four columns you name. BigQuery stores the data in blocks, and each block records the smallest and largest value of every clustered column. A query that filters on those columns reads only the blocks whose range can match. That is block pruning, and the skipped blocks are not billed.

BigQuery has no indexes on ordinary columns (only search and vector indexes), and clustering is the closest thing. It costs nothing to maintain, because BigQuery re-sorts the data in the background without charging for it. It only helps when the filter is on the first clustered column, or on the first few in order.

Creating a clustered table

CREATE TABLE shop.orders (
  order_id INT64,
  customer_id INT64,
  country STRING,
  placed_at TIMESTAMP
)
CLUSTER BY customer_id, country;

The order of the list is the sort order. You can also cluster a partitioned table: PARTITION BY DATE(placed_at) CLUSTER BY customer_id. Each partition is then sorted separately.

The rules:

• At most four clustering columns, each named once. A fifth is an error:

CREATE TEMP TABLE page_views (
  site INT64,
  section INT64,
  page INT64,
  country INT64,
  device INT64
)
CLUSTER BY site, section, page, country, device;
SELECT * FROM page_views

• Top-level columns of type BOOL, DATE, DATETIME, TIMESTAMP, INT64, NUMERIC, BIGNUMERIC, STRING, GEOGRAPHY or RANGE. Not FLOAT64, ARRAY, STRUCT fields or JSON:

CREATE TEMP TABLE readings (
  sensor_id INT64,
  temperature FLOAT64
)
CLUSTER BY temperature;
SELECT * FROM readings

• For STRING columns BigQuery sorts on the first 1,024 characters only.
• Adding clustering to an existing table sorts only the data written afterwards. Old rows stay where they are until you rewrite them.

Google says clustering is worth it for tables above about 64 MB, and that on smaller tables the gain is negligible.

The first column does the work

The public table bigquery-public-data.covid19_genome_sequence.metadata_clustered holds 30 million records. It is clustered by acc (the record ID), then releasedate, then organism. It is not partitioned, so it shows clustering on its own. Reading the acc column without a filter costs 363 MB:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`

A filter on acc shrinks that to 395 KB:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc = 'SRR11454611'

The estimate dropped to 1/940 of the column. BigQuery compared the value with the minimum and maximum acc stored for each block and skipped nearly all of them. A range or a prefix works the same way, as long as it is a comparison on the bare column:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc LIKE 'SRR114%'

That reads 1.8 MB, because more blocks fall into a prefix than a single value.

Column order matters

A query that filters on the second clustered column alone is not helped much. Rows are sorted by acc first. releasedate only orders the rows that share an acc, so its values are spread across nearly all blocks, and most blocks can’t be skipped.

Reading acc and releasedate together costs 595 MB:

SELECT acc, releasedate
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`

Adding WHERE releasedate = '2022-01-01' (and reading only acc) costs 566 MB:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE releasedate = '2022-01-01'

That is a 5% saving, from a filter on a single date. The third column behaves the same way, only more so. A filter on organism alone costs the same as reading the whole column, 734 MB:

SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`

SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE organism = 'Severe acute respiratory syndrome coronavirus 2'

Once the first column is in the filter, the later ones can add to the pruning. This query filters on acc and organism, and reads 998 KB:

SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc = 'SRR11454611'
  AND organism = 'Severe acute respiratory syndrome coronavirus 2'

The practical rule is to list the columns in the order your queries filter on them, most-filtered first. If every query filters on country but only some also filter on customer_id, cluster by country, customer_id. If a column is never filtered on, don’t cluster by it.

A function on the column turns pruning off

Block pruning compares the filter value with each block’s stored minimum and maximum. If the column is wrapped in a function, those values say nothing about the result, and BigQuery reads every block:

SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE UPPER(acc) = 'SRR11454611'

The estimate is 363 MB again, exactly the unfiltered cost. Compare the bare column with a constant, or move the function to the constant side of the comparison.

Clustering works inside partitions

On a table that is both partitioned and clustered, pruning happens in two steps: the date filter chooses partitions, then the clustering filter chooses blocks inside them. wikipedia.pageviews_2020 is partitioned by datehour and clustered by wiki, then title. One day of the views column is 2.4 GB. Adding a filter on the language edition:

SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
  AND wiki = 'en'

brings it to 622 MB. Combining the two works well when each partition is large enough to have many blocks and your queries filter on a few columns besides the date. See Partitioning vs clustering.

The estimate is an upper bound

Every estimate above is labeled “upper bound” by BigQuery. For a clustered table it is a ceiling, and the query can read less. Google’s documentation says BigQuery can’t always estimate the bytes exactly, because the blocks that match are only known while the query runs. The final figure is the bytes in the blocks that were read.

We ran a few of these queries for real to see how far off the estimate was. For acc = 'SRR11454611' and for acc LIKE 'SRR114%', the job processed exactly what the dry run had said, 404,736 and 1,879,790 bytes. With a LIMIT the two differ. SELECT acc FROM ... LIMIT 10 has the same estimate as the full column, 363 MB, but the job stopped after reading 196 MB. Google’s docs say a LIMIT doesn’t reduce the cost on a table that is not clustered; on a clustered table it can.

Two consequences:

• An estimate can’t promise a price, only a maximum. This is one reason to partition when you need a firm number.
• Maximum bytes billed compares against the estimate. A query on a clustered table can be rejected for exceeding the limit although it would have read less.

Reclustering is automatic

New data may not be grouped with existing rows that have the same clustering values. BigQuery re-sorts in the background and doesn’t charge for it. For partitioned tables the sorting stays within each partition.

To see which columns a table is clustered by, query clustering_ordinal_position in INFORMATION_SCHEMA.COLUMNS. It is 1 for the first clustered column and NULL for columns that aren’t clustered.

In Querylab.io the estimate in the status bar changes as you add a filter on a clustered column, so you can see whether the filter prunes. It is the same upper bound.