Query partitioned tables

This document describes some specific considerations for queryingpartitioned tables in BigQuery.

For general information on running queries in BigQuery, seeRunning interactive and batch queries.

Overview

If a query uses a qualifying filter on the value of the partitioning column,BigQuery can scan the partitions that match the filter and skipthe remaining partitions. This process is calledpartition pruning.

Partition pruning is the mechanism BigQuery uses to eliminateunnecessary partitions from the input scan. The pruned partitions are notincluded when calculating the bytes scanned by the query. In general, partitionpruning helps reduce query cost.

Pruning behaviors vary for the different types of partitioning, so you couldsee a difference in bytes processed when querying tables that are partitioneddifferently but are otherwise identical. To estimate how many bytes aquery will process, perform adry run.

Query a time-unit column-partitioned table

To prune partitions when you query atime-unit column-partitioned table,include a filter on the partitioning column.

In the following example, assume thatdataset.table is partitioned on thetransaction_date column. The example query prunes dates before2016-01-01.

SELECT*FROMdataset.tableWHEREtransaction_date>='2016-01-01'

Query an ingestion-time partitioned table

Ingestion-time partitioned tablescontain a pseudocolumn named_PARTITIONTIME, which is the partitioningcolumn. The value of the column is the UTC ingestion time for each row,truncated to the partition boundary (such as hourly or daily), as aTIMESTAMPvalue.

For example, if you append data on April 15, 2021, 08:15:00 UTC, the_PARTITIONTIME column for those rows contains the following values:

• Hourly partitioned table:TIMESTAMP("2021-04-15 08:00:00")
• Daily partitioned table:TIMESTAMP("2021-04-15")
• Monthly partitioned table:TIMESTAMP("2021-04-01")
• Yearly partitioned table:TIMESTAMP("2021-01-01")

If the partition granularity is daily, the table also contains a pseudocolumnnamed_PARTITIONDATE. The value is equal to_PARTITIONTIME truncated to aDATE value.

Both of these pseudocolumn names are reserved. You can't create a column witheither name in any of your tables.

To prune partitions, filter on either of these columns. For example, thefollowing query scans only the partitions between the dates January 1, 2016 andJanuary 2, 2016:

SELECTcolumnFROMdataset.tableWHERE_PARTITIONTIMEBETWEENTIMESTAMP('2016-01-01')ANDTIMESTAMP('2016-01-02')

To select the_PARTITIONTIME pseudocolumn, you must use an alias. For example,the following query selects_PARTITIONTIME by assigning the aliaspt tothe pseudocolumn:

SELECT_PARTITIONTIMEASpt,columnFROMdataset.table

For daily partitioned tables, you can select the_PARTITIONDATE pseudocolumnin the same way:

SELECT_PARTITIONDATEASpd,columnFROMdataset.table

The_PARTITIONTIME and_PARTITIONDATE pseudocolumns are not returned by aSELECT * statement. You must select them explicitly:

SELECT_PARTITIONTIMEASpt,*FROMdataset.table

Handle time zones in ingestion-time partitioned tables

The value of_PARTITIONTIME is based on the UTC date when the field ispopulated. If you want to query data based on a time zone other than UTC, chooseone of the following options:

• Adjust for time zone differences in your SQL queries.
• Usepartition decoratorsto load data into specific ingestion-time partitions, based on a differenttime zone than UTC.

Better performance with pseudocolumns

To improve query performance, use the_PARTITIONTIME pseudocolumn by itselfon the left side of a comparison.

For example, the following two queries are equivalent. Depending on the tablesize, the second query might perform better, because it places_PARTITIONTIMEby itself on the left side of the> operator. Both queries process the sameamount of data.

-- Might be slower.SELECTfield1FROMdataset.table1WHERETIMESTAMP_ADD(_PARTITIONTIME,INTERVAL5DAY)>TIMESTAMP("2016-04-15");-- Often performs better.SELECTfield1FROMdataset.table1WHERE_PARTITIONTIME>TIMESTAMP_SUB(TIMESTAMP('2016-04-15'),INTERVAL5DAY);

To limit the partitions that are scanned in a query, use a constant expressionin your filter. The following query limits which partitions are pruned based onthe first filter condition in theWHERE clause. However, the second filtercondition doesn't limit the scanned partitions, because it uses table values,which are dynamic.

SELECTcolumnFROMdataset.table2WHERE-- This filter condition limits the scanned partitions:_PARTITIONTIMEBETWEENTIMESTAMP('2017-01-01')ANDTIMESTAMP('2017-03-01')-- This one doesn't, because it uses dynamic table values:AND_PARTITIONTIME=(SELECTMAX(timestamp)fromdataset.table1)

To limit the partitions scanned, don't include any other columns in a_PARTITIONTIME filter. For example, thefollowing query does not limit the scanned partitions, becausefield1is a column in the table.

-- Scans all partitions of table2. No pruning.SELECTfield1FROMdataset.table2WHERE_PARTITIONTIME+field1=TIMESTAMP('2016-03-28');

If you often query a particular range of times, consider creating a view thatfilters on the_PARTITIONTIME pseudocolumn. For example, the followingstatement creates a view that includes only the most recent seven days of datafrom a table nameddataset.partitioned_table:

-- This view provides pruning.CREATEVIEWdataset.past_weekASSELECT*FROMdataset.partitioned_tableWHERE_PARTITIONTIMEBETWEENTIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP,INTERVAL7*24HOUR),DAY)ANDTIMESTAMP_TRUNC(CURRENT_TIMESTAMP,DAY);

For information about creating views, seeCreating views.

Query an integer-range partitioned table

To prune partitions when you query aninteger-range partitioned table,include a filter on the integer partitioning column.

In the following example, assume thatdataset.table is an integer-rangepartitioned table with a partitioning specification ofcustomer_id:0:100:10The example query scans the three partitions that start with 30, 40, and 50.

SELECT*FROMdataset.tableWHEREcustomer_idBETWEEN30AND50+-------------+-------+|customer_id|value|+-------------+-------+|40|41||45|46||30|31||35|36||50|51|+-------------+-------+

Partition pruning is not supported for functions over an integer rangepartitioned column. For example, the following query scans the entire table.

SELECT*FROMdataset.tableWHEREcustomer_id+1BETWEEN30AND50

Query data in the write-optimized storage

The__UNPARTITIONED__ partition temporarily holds data that is streamed to apartitioned table while it is in thewrite-optimized storage.Data that is streamed directly to a specific partition of a partitioned tabledoes not use the__UNPARTITIONED__ partition. Instead, the data is streameddirectly to the partition.

Data in the write-optimized storage hasNULL values in the_PARTITIONTIME and_PARTITIONDATE columns.

To query data in the__UNPARTITIONED__ partition, use the_PARTITIONTIMEpseudocolumn with theNULL value. For example:

SELECTcolumnFROMdataset.tableWHERE_PARTITIONTIMEISNULL

For more information, seeStreaming into partitioned tables.

Best practices for partition pruning

Use a constant filter expression

To limit the partitions that are scanned in a query, use a constant expressionin your filter. If you use dynamic expressions in your query filter,BigQuery must scan all of the partitions.

For example, the following query prunes partitions because the filter contains aconstant expression:

SELECTt1.name,t2.categoryFROMtable1ASt1INNERJOINtable2ASt2ONt1.id_field=t2.field2WHEREt1.ts=CURRENT_TIMESTAMP()

However, the following query doesn't prune partitions, because the filter,WHERE t1.ts = (SELECT timestamp from table where key = 2), is nota constant expression; it depends on the dynamic values of thetimestamp andkey fields:

SELECTt1.name,t2.categoryFROMtable1ASt1INNERJOINtable2ASt2ONt1.id_field=t2.field2WHEREt1.ts=(SELECTtimestampfromtable3wherekey=2)

Isolate the partition column in your filter

Isolate the partition column when expressing a filter. Filters that require datafrom multiple fields to compute will not prune partitions. For example, a querywith a date comparison using the partitioning column and a second field, orqueries containing some field concatenations will not prune partitions.

For example, the following filter does not prune partitions because itrequires a computation based on the partitioningts field and a second fieldts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Require a partition filter in queries

When you create a partitioned table, you can require the use of predicatefilters by enabling theRequire partition filter option. When this option isapplied, attempts to query the partitioned table without specifying aWHEREclause produce the following error:

Cannot query over table 'project_id.dataset.table' without a filter that can beused for partition elimination.

This requirement also applies to queries on views and materialized views thatreference the partitioned table.

There must be at least one predicate that only references a partition column forthe filter to be considered eligible for partition elimination. For example, fora table partitioned on columnpartition_id with an additional columnf inits schema, both of the followingWHERE clauses satisfy the requirement:

WHERE partition_id = "20221231"WHERE partition_id = "20221231" AND f = "20221130"

However,WHERE (partition_id = "20221231" OR f = "20221130") is not sufficient.

For ingestion-time partitioned tables, use either the_PARTITIONTIME or_PARTITIONDATE pseudocolumn.

For more information about adding theRequire partition filter option whenyou create a partitioned table, seeCreating partitioned tables.You can alsoupdatethis setting on an existing table.

What's next

• For an overview of partitioned tables, seeIntroduction to partitioned tables.
• To learn more about creating partitioned tables, seeCreating partitioned tables.