Database Reference
In-Depth Information
Partition Decorators
Partition decorators allow you to run a query that runs only over a subset
of the rows in your table. Unlike HASH partitioning, you are billed only for
accessing the rows in one partition. Using partition decorators can be faster
than using HASH partitioning as well because your query has to read only a
small portion of the table.
To use a partition decorator, just add
$<index>-of-<count>
to the name
of the table you use, where
<index>
is the index of the partition you use,
starting with 0, and
<count>
is how many pieces you want to divide the
table into. For example, to access the first 10
th
of the Wikipedia sample
table,
use
the
table
name
[publicdata:samples.wikipedia$0-of-10]
.
Here's the query used for the
HASH
partitioning example but with partition
decorators instead:
SELECT title, COUNT(*)
FROM [publicdata:samples.wikipedia$0-of-10]
GROUP BY title
Although the
HASH
partition example scanned 6.79 GB, this one scans only
690 MB—a much less expensive query. The results, however, are subtly
different. Although the
HASH
partitioning example filtered by title, this is
filtering by row; it will run over approximately 10 percent of rows, but that
might not mean only 10 percent of titles are returned. This may sound
somewhat confusing now, but it is generally obvious what you want from
the context. In the case of the Wikipedia edits data, since each title is edited
many times, it is possible that 90 percent of the titles appear in a 10 percent
slice of the edit records.
Partition decorators have some limitations; you can't partition a table any
further than its underlying granularity. Internally, BigQuery stores tables in
shards; these are discrete chunks of data that can be processed in parallel.
If you have a 100 GB table, it might be stored in 5000 shards, which allows
it to be processed by up to 5000 workers in parallel. You shouldn't make
any assumptions about the size of number of shards in a table. BigQuery will
repartition data periodically to optimize the storage and query behavior.
