Design a table for its queries
How a table is stored decides how many bytes a query can skip, and partitioning can only be chosen when the table is created, so it should come from the queries the table will answer. This lesson builds tables from learn.shop.orders, the 200 orders of the online shop, which has order_id, customer_id, product_id, order_date, quantity and status.
How it works
A partitioned table is stored in pieces, one for each value of its partition column. A filter on that column picks pieces before the query runs, so the estimate drops. A clustered table sorts the rows inside each piece by up to four columns. A filter on those columns skips blocks of rows while the query runs, so the bill can drop and the estimate cannot promise it. You cannot add partitioning to a table that exists, so you build a new table from a query with CREATE TABLE ... AS SELECT. Clustering can be added to an existing table later, but only the rows written afterward are sorted.
CREATE TABLE name PARTITION BY column CLUSTER BY columns OPTIONS (...) AS SELECT ...createsnamein the dataset and fills it with the rows of the query. The clauses go in this order, andPARTITION BY,CLUSTER BYandOPTIONScan each be left out. PutOPTIONSorCLUSTER BYbeforePARTITION BYand BigQuery refuses the statement. The new table does not copy the layout of the table it reads, and it does not refresh when that table changes.CREATE OR REPLACE TABLEover a table with a different partitioning or clustering fails withCannot replace a table with a different partitioning spec. Instead, DROP the table, and then recreate it.The same layout again is accepted, and so is addingrequire_partition_filter. To change a layout, runDROP TABLE IF EXISTS name;and thenCREATE TABLE name .... Do that only when you can rebuild the table from another one, as here fromorders. If the table is your only copy, build the new layout under a new name first. A script that starts this way runs again whatever it built before.PARTITION BYtakes one expression. ADATEcolumn such asorder_dategives one piece for each day.DATE_TRUNC(order_date, MONTH)gives one piece for each month, andYEARone for each year.CLUSTER BYtakes up to four columns, and their order matters. Put first the column most queries filter on.- The effect shows in the estimate. Reading
order_idfor one day ofordersis estimated at 3,200 bytes. The same query on a copy partitioned byorder_dateis estimated at 48 bytes, because it reads 1 of the 185 pieces. A filter oncustomer_idalone is estimated at 3,200 bytes on that copy and on the plain table. On a copy that is also clustered bycustomer_idit is still 3,200 bytes, marked an upper bound. A real run is billed at least 10 MiB for each table, so on tables this small each of these queries is billed 10 MiB. OPTIONS (require_partition_filter = TRUE)makes the table refuse a query with no usable filter on the partition column. BigQuery accepts the option only together withPARTITION BY.INFORMATION_SCHEMA.PARTITIONShas one row for each piece of a table.COLUMNSshows which column partitions it and the place of each clustering column, andTABLE_OPTIONSshows the option. Check reads these views, with a comparison of the rows and columns againstorders, to grade how your table is stored.
Common mistake
A layout helps only the queries that filter on its columns. A table partitioned by order_date does nothing for a query that filters only on customer_id: that query reads every piece, and its estimate equals the plain table. Clustering by a column no query filters on does nothing either. List the filters of the queries first, then choose the columns.
Worked example
DROP TABLE IF EXISTS learn.shop.orders_by_month;
CREATE TABLE learn.shop.orders_by_month
PARTITION BY DATE_TRUNC(order_date, MONTH)
CLUSTER BY product_id
AS SELECT order_id, product_id, order_date, quantity
FROM learn.shop.orders;
SELECT partition_id, total_rows
FROM `learn.shop`.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'orders_by_month'
ORDER BY partition_id
LIMIT 3;The first statement removes an earlier copy, so the script runs again. The second builds a table of four columns of orders, with one piece for each month and the rows of each piece sorted by product_id.
The last statement lists the first three pieces. The partition_id is the month as YYYYMM, and total_rows counts the orders in it: 7 in January 2024, 11 in February and 9 in March.