Database Reference
In-Depth Information
the service offers to deal with this abundance of tables. The unifying theme
is that these features allow tables (in contrast to the contents of tables) to
be treated a bit like data and to be included in expressions rather than just
static references.
Time Travel
Tables in BigQuery are mutable, and you have seen how you can grow tables
over time using jobs with the
WRITE_APPEND
disposition or by inserting
individual records. In this chapter you have also seen how to save a snapshot
of a table prior to modifying it so that you can retain a historical copy. The
ability to retrieve earlier versions of data is a useful feature in any data
storage system. It is quite likely that you are familiar with filesystems that
allow you to access historical views of the filesystem or the ability in Google
Docs to access revisions (
https://support.google.com/drive/
answer/190843
)
. BigQuery supports similar access to table revisions.
Almost anywhere that you can pass a table name, you can instead pass
a reference to a slice (in the time dimension) of the table. This section
discusses this type of metatable and how to use it to implement common
data management solutions.
You Need Your Own Copy of the Sample
Table
In most cases we provide sample tables for queries in a shared dataset
so that you can test queries without building your own version. In this
case it is not possible because BigQuery collapses revisions older than 7
days. Instead, you have to build your own fresh copy to try the
examples. It also means that you have to adjust all the timestamps
appropriately.
First create a table that is particularly amenable to time slicing. The table
may seem a bit contrived, but it clearly illustrates how the slicing
expressions function. Here is the recipe for building the sample table:
$
bq query --destination_table=ch11.time_lapse \
'SELECT 0 index, 0 millis'
