NICK ORLOVE: Hey, we're late for the small data meetup.
Are you coming?
STEPHANIE WONG: I can't.
I'm waiting for this BigQuery job to finish.
Can you wait 18.2 seconds?
NICK ORLOVE: But then all the sparkling water's
going to be gone.
Come on.
STEPHANIE WONG: Well, at that point,
is it even worth going then?
NICK ORLOVE: Well, I bet if you use BigQuery's clustering
and partitioning features, your queries
would run much faster and cheaper.
STEPHANIE WONG: That'd be great.
Can you show me how?
NICK ORLOVE: Yeah, let's do it.
See, BigQuery's a great tool for analyzing huge amounts of data
quickly, but there are plenty of times
where you only care to analyze a subset of your data.
For example, you may only want to look
at data over a particular period of time
or over a particular column.
If you check out the illustration on the screen,
you can see that I'm converting my unordered table
on the left into a date partition table on the right
and clustering my tags closer together.
This is really going to optimize my queries
when I analyze my data.
Now looking at my GCB project, you
can see that I have a Stack Overflow data
set, which contains a table with 10.8 million Stack Overflow
questions which have been gathered since 2014.
This was pulled from the BigQuery Stack Overflow
public data set by the way, which everyone has access to.
From the schema, you can see that this table contains
a bunch of great information from each Stack Overflow
question such as the ID, title, body, creation date,
number of comments, what it's been tagged with,
view count, et cetera.
That's a lot of data across 10.8 million Stack Overflow
questions.
So let's say you want to gather some information
on the types of questions being asked about Android
in the last six months.
To accomplish this, I wrote this simple SQL query.
We can see that we only care about dates in the last six
months and anything that has a tag Android in it.
When running this query against my standard table,
it has to scan all of the data and all
of the tags and the creation date column,
so you can see that it takes a few seconds
and scans the full 18 gigabytes of data.
Now if we set up the table to be partitioned,
BigQuery will only scan the parts of the table that
fall into this date range.
Ideally, you should start using partitioning when you first
create your table.
However, I'll run a DDL, or Data Definition Language,
query to create a new table from this existing table
and use the creation underscore date column as my custom
partitioning column.
Now let's run that query against my partition table.
You can see that it returns in about a quarter of the time
and scans about a tenth of the amount of data.
Impressive.
STEPHANIE WONG: So let me get this straight.
BigQuery is taking care of all of the hard work?
NICK ORLOVE: Yep.
You just tell it what date field you want to partition by,
and BigQuery knows exactly what partition ranges
to use to maximize efficiency.
STEPHANIE WONG: But what if I want
to partition my data on something like a text column?
NICK ORLOVE: That's where clustering comes in.
Let me show you.
Looking at this query, we're taking
advantage of partitioning because we're
filtering by creation date.
However, the query still needs a scan across all the tags
to find out which ones are Android, even though we're only
looking for a single keyword.
Clustering is a way for BigQuery to optimize
the placement of your data and allows
it to store alike data closer together
to make queries more efficient.
Typically clustering works best on high cardinality fields,
which maximizes the amount of benefit you'll get.
By the way, you can cluster on multiple columns
so you aren't limited to just one.
Just like partitioning, you should set up
clustering at the get go.
So I'll run a similar DDL as before to create a new table,
but this time we'll cluster our data using the column tags.
So now if we run these queries with clustering,
we can see that we get our results slightly faster,
and we're now scanning megabytes of data instead of gigabytes.
STEPHANIE WONG: So if I use BigQuery's partitioning
and clustering, I'll only scan the data
that I need to save both time and money.
NICK ORLOVE: Exactly.
It takes a little bit more work upfront,
but it's definitely worth it.
STEPHANIE WONG: And for those of you watching at home,
check out the link in the description
to try it out for yourselves.
Don't forget to like, comment, and subscribe for more great
Google Cloud Platform content.
[MUSIC PLAYING]