SPEAKER: This demo highlights one of our newest BigQuery
features, Time Unit Partitioning.
This feature allows you to organize time-based data
using flexible units of time, ranging from an hour to a year.
To see this feature in action, let's work
with the BigQuery public data set on Wikipedia page views.
This data set contains a timestamp column called
"datehour."
If you are familiar with creating daytime partition
tables, you know that you can use "daytime_trunc" and specify
a daytime column and a parameter for the time component,
in this case, hour.
For this data set, the Query Editor
warns us that the dataset does not contain a column called
"daytime."
Recall from our review of the dataset that it actually
contains a timestamp column called "datehour."
To modify this query for Time Unit Partitioning,
you can simply replace "daytime_trunc"
with "timestamp_trunc," and "daytime"
column with the "datehour" column.
With the time component set as "hour,"
the query will create an hourly partition table.
You can easily create a daily partition table
by replacing "HOUR" with "DAY."
Last, you can create a daily partition table with clustering
by adding "CLUSTER BY datehour."
Using these three versions of the query,
I have created the tables listed under a dataset called
"timeunit."
Taking a look at the details of the hourly partition table,
you can see that it contains just over 12.7 billion records
and that it is partitioned by hour on the "datehour" column.
The details for the daily partition table show the same
number of rows and that it is partitioned by day
on the "datehour" column.
The daily partition table with clustering shows similar
information, the difference being that it is also clustered
on the "datehour" column.
To compare the performance of these Time Unit Partitioning
schemes, let's run a few queries to select some hourly data.
First, let's select some data for a specific
one-and-a-half-hour window from the daily partition table.
Note that the estimated amount of data processed
for this query is 6 gigs.
While the query may not take long to run,
selecting all data for that one-and-a-half-hour window from
the daily partition table ended up processing the entire 6 gigs
of data.
Let's run the same selection on the hourly partition table
to see the difference in performance.
On the hourly partition table, the same query
is estimated to process noticeably less data,
about 575 megs of data.
Running the query shows that, indeed, it only processed
about 575 megs of data.
For comparison, let's run the same query
on the daily partition table with clustering.
Notice that the estimated amount of data
processed for this query is again 6 gigs.
Running this query highlights that the hourly partition table
performed similarly to a daily partition table
with clustering, but with a more straightforward implementation.
For example, recall that the estimate of data processed
for the daily partition table was
6 gigs, the same as the estimate for the daily partition
table with clustering, even though the query on the cluster
table ended up processing less data.
Limiting the selection further to a 45-minute window
shows the true benefit of hourly partitioning.
The query on the daily partition table
still has to process 6 gigs of data.
However, the hourly partition table
only has to process 281 megs of data.
Running the query shows that it indeed only processed 281 megs.
Now that you have seen Time Unit Partitioning in action,
we hope that you are excited to try it out
on your time-based data.
Be sure to check out the BigQuery docs to learn more.
And keep an eye out for the release
notes to stay informed about other exciting features headed
your way.