For this demo, we're using a subset of the common crawl
data set that we've uploaded to Google Cloud Storage, which
is an open data set of web crawl data.
The subset we're using is approximately 3.5 terabytes
of hive-partitioned Parquet files.
Let's begin by looking at the data layout in Google Cloud
Storage.
This data set is hive-partitioned
with two partition keys, crawl and subset.
As we can see, there are many subdirectories all
beginning with the word crawl, an equal sign, and then a name
for that particular crawl.
When we click into one of these directories,
we see three subdirectories.
Each subdirectory begins with the word subset,
has an equal sign, and then a word that
is the value for that subset.
The directory structure is encoding the partition values.
Let's click into another folder.
Here we see many Parquet files.
Each is on the order of several hundred megabytes.
And that's stored gzipped compressed
in a single directory here.
So our goal with this demo is to show you
how to be able to query all of this data from BigQuery.
So to do that, we start by creating a table definition
file.
And the command being issued here
indicates that the underlying data is in Parquet format,
that the data is hive-partitioned,
and that we are requesting automatic partition key name
and type inference.
So typically, we would run this and actually
save it to a file, which is what's going to happen here.
Then once we have a table definition file,
we can make a permanent external table.
And that's what this command is showing.
In this particular case, we've already created the table
so we won't run this command.
So now we want to be able to query this data.
So we're querying a specific partition
to count the number of times Google appears as a top level
domain in the crawl, broken down by the hostname.
So while this runs, we can look at the results
from an earlier identical query.
And we can see that we've successfully
queried the parquet data in place on GCS
and that the hive-partitioned columns are recognized
and in the results set.
And here we see that if we go back to the original tab,
we have the same results.
Another use case for our new support
is importing hive-partitioned files
into BigQuery managed storage.
This command will perform a load of a single file
into a managed table while also recognizing and loading
the partition columns.
Now in this particular case, I've
chosen the file that happens to have the data for the Google
top level domain.
This is a table with the same command
that was created in advance.
If we look at the schema of the table,
we'll see a number of fields that
were picked up from the file.
At the very end, we see two additional columns,
crawl and subset, that have both been successfully imported.
Next, we want to go ahead and issue that exact same query
against this table.
And because we chose a very particular file,
we'll notice that we get very similar results.
And that is our new functionality.
[MUSIC PLAYING]