Database Reference
In-Depth Information
Note that
TOP()
is only approximate; however if you are just looking for the
most common values, it will virtually always give you the ones you want.
Nested and Repeated Fields
The BigQuery data model supports nested and repeated fields; neither of
these are part of standard SQL. Nested fields are simply fields that are
nested into records; for the most part they are indistinguishable from fields
with “
.
” in the name. Repeated fields are a bit funkier, however. They allow
you to have a single field contain multiple values of the same type. Fields can
be both nested and repeated; this means that a single field can have an array
of records.
Because there wasn't a standard syntax for SQL queries over nested and
repeated data, the BigQuery team opted for query simplicity rather than
language rigor. That means that BigQuery tries to “do the right thing” with
respect to your query and often makes queries over nested and repeated
data convenient. For example, when you use a repeated field in a
GROUP
BY
clause in a query, the repeated field is automatically flattened. (That is,
for each repeated value, the entire row is repeated.) However, sometimes
nested and repeated data can be awkward or surprising. For instance,
seemingly trivial changes to the query can cause an automatic flattening.
Multiple repeated fields can also be difficult to manage in a single query.
That said, having the ability to query over nested and repeated fields is
extremely powerful and can be a way to write much more convenient queries
than if you had to store your data in a less natural format. You can read
about how to query over your nested and repeated data in Chapter 10.
Summary
This chapter showed you how to run queries via the BigQuery API and
how to write queries in the BigQuery SQL language. You should now be
comfortable with the various options for executing queries; you should know
how to set destination tables and priorities; and you should know how
to get query results larger than 128 MB. This chapter also discussed the
quotas and limits imposed by BigQuery. Forthcoming chapters delve into
the architecture, which hopefully can provide some rationale behind those
limits.
