Database Reference
In-Depth Information
decorators because the partition boundaries will change when data is added
to the table, so you could miss some rows entirely.
If you use either
HASH
partitioning or partition decorators on tables that
may be changing, it is recommended that you use a snapshot decorator as
well. A
snapshot decorator
can be used to read the table of a particular
timestamp. To use a snapshot decorator, just append
@<timestamp>
to the
table name, where
<timestamp>
is the time of the snapshot in milliseconds
since 1970. The snapshot time must be within the last 7 days; detailed table
history is kept only for a week.
For example, to use partition decorators with snapshot decorators to read
the first third of a stable snapshot of the Wikipedia sample table at
timestamp 1390581599000, you can use:
SELECT title, COUNT(*)
FROM
[publicdata:samples.wikipedia
@1390581599000
$0-of-3]
GROUP BY title
Note that the snapshot decorator must come before the partition decorator.
Describing the table this way is equivalent to reading the table at the
snapshot time and then partitioning it into thirds.
Snapshot decorators have some extra tricks and options. To find out more,
check out the table decorators' section in Chapter 11, “Managing Data Stored
in BigQuery,” which discusses them in more detail.
Repeated Fields
Relational databases encourage us to think of data as flat records spread
across different tables with suitable foreign key relations. In practice, it is
often more natural to have records with a rich structure. For example, an
order placed on a website consists of top-level information such as the time
of the transaction and the customer together with additional details like the
list of items along with their quantities. This is a natural unit of data and the
ability to represent it as a single large record would be convenient. Similarly,
the application described in Chapter 8, “Putting It Together,” stores all the
data coming from devices in a single table with a large number of fields.
These types of complex schemas are well supported in BigQuery.
