>>Brian Vance: All right.
We're all set up.
Hi, I'm Brian Vance, like Sonal introduced me.
I've been at Google here for about seven years, I work here in the Kirkland office.
I lead a couple of test teams that we work on our distributed databases here at Google,
so both analytic and transactional processing.
Externally they're known as Dremel and Spanner.
So one of the themes I've noticed over the last few talks is we have a lot of data.
And this isn't a surprise, but in most of the talks, people tend to kind of hand wave
around this.
So we want some of the interesting ones where Gareth yesterday talked about getting code
coverage numbers in production, and I'm sort of thinking how that would work and that's
got to be a ton of data being spread out, having to be collected, having to be analyzed,
to actually make use out of it.
(Saying name) yesterday talked about sort of logging all this stuff to look for invariants;
right?
Having tons and tons of logging, being able to data mine, look for invariants.
Even today, Tony this morning talked about thousands of thousands if not hundreds or
millions of samples.
How do you collect that?
How do you analyze it?
What would you do with it?
So it's no secret that Google has a lot of data.
We've had a lot of data for quite a long time so we kind of had to solve this problem a
while ago, which is where Dremel came about.
About eight years ago we decided let's build a tool that can basically analyze the scale
of data that we have at Google but we didn't want to build sort of the traditional way
with map reduce and that type of thing, which we also have that, but our programmers were
able to sort of leverage that, and you've got mappers and reducers and there's a lot
of complexity involved.
But we wanted, you know, PMs to be able to use this data.
UX folks, pointy-haired managers that couldn't sit down and sort of build the workers to
do this.
So we wanted it to be interactive, we wanted it to be fast, we wanted it to be familiar.
So we built this tool Dremel with a SQL-like interface which was pretty easy.
Most people understand SQL.
And it's been incredibly successful for us and very, very useful.
So -- But it's not only Google now that has this problem.
It's the entire industry as we talked about and there are tons of businesses spinning
up around how to do this, and it's a pretty exciting time to be sort of involved in this
Big Data world.
But specifically in test, what I'm hoping to do is talk a little bit and maybe help
you guys think a little bit differently about how you test, the kind of data you collect,
and what's possible with the tools that are starting to come out.
And specifically what I'm going to talk about today is BigQuery.
We, about three years ago, released BigQuery on our cloud platform, and it's available
for anyone to use now.
And so I'm going to sort of go through some demos and talk about that.
Oops.
I guess I need the clicker.
All right.
So here's kind of the overview.
So I'm going to kind of tell but BigQuery, the tool that we have that's, again, available
for anyone to use.
And then I kind of want to spend a few minutes on some demos just to see what's possible
and again get your brain kind of moving there.
So first thing.
BigQuery.
Where does BigQuery fit in kind of the larger Google compute platform all together?
So on this slide down at the bottom we've got the storage services.
Google compute storage.
That's typically where you're going to dump all your data; right?
It's good for all types of data.
And then you've got these tools that you can use on top of that to be able to analyze that
data.
So the one that most people are familiar with is something like Hadoop.
So you've got BigQuery on top of Google Compute Engine.
If you want to do really, really complicated analysis with sort of multiple reductions,
you're okay with high latency, you're okay sort of building these big long, big complicated
pipelines, or you need that.
There's definitely cases where you need that.
There's things like Google Compute Engine and Hadoop to do that.
But BigQuery sort of lets you do a lot of interactive sort of real-time analysis on
the data and sort of have tools to do visualization and stuff.
So we're going to kind of talk about that more today.
So the architecture.
Obviously overly simplified but you have to have boxes and arrows.
So how does BigQuery do what it does?
There's no indexes.
Traditional databases speed things up with indexes.
Every query that runs in BigQuery is a full table scan, but we have column I/O and columnar
storage at the bottom there which allows us -- Traditional databases have to basically
read the entire row every single time they want to do the table scans.
We're able to actually only look at the data in a columnar format that you actually care
about, the things that you're looking at, which heavily speeds up a lot of the queries.
So in a nutshell, the queries come in from the root.
They're effectively split out and sharded sort of they intermediate phase and they're
sharded again, split out to the leaf phase.
And it's apt the leaf phase we're able to use thousands and thousands of machines to
look at this data in columnar format very, very quickly and then basically do some reductions
all the way back up and provide the results.
So we're able to leverage sort of the thousands of machines that Google has to be able to
do this sort of thing.
So for those that maybe understand a little bit more about database internals, kind of
from a relational algebra perspective, we have the leaf servers doing the filters.
They kind of do the selection operations down at the bottom.
The shuffles are going to happen.
Also we can do shuffles -- they're not shown here but between leaf nodes we can shuffle
for joins.
And then further up, all the aggregation operations, like grouping and ordering, happen up kind
of at the intermediate stages.
And again, kind of bring those back up.
All right.
So let's talk about sort of just a quick demo to kind of demonstrate what's possible.
And before we switch over to the demo, just want to pull up a query.
Hopefully most of you guys are familiar with sort of SQL and you can kind of -- we can
kind of make out what this means.
But one of the cool things about BigQuery is we've loaded up some of the big he is data
sets in the world and they're available for free.
They're publicly available.
They're free.
You can host them, you can join them against your own data.
So one of the things that's kind of interesting is we have Wikipedia page view.
So every single page view that's happened in Wikipedia, there's been a log entry placed.
We're been able to ingest that into BigQuery, and now we can actually do queries against
years and years of page views in Wikipedia, which obviously there's a lot of.
So if we can switch over to the demo.
Of course.
I did an extend my desktop instead of -- this will be fine.
Sorry.
All right.
Sorry about that.
So this is the BigQuery console.
Again, you can do interactive queries in here.
You can see your data on the side here.
The same query I just showed you is loaded up here.
I've turned off caching so we aren't actually going to cache anything.
And we will run this query.
So this is, again, I just picked a random sort of one of the larger months, which is
7/2010 to do this query about.
So we're looking for page views that actually match a regular expression.
So we're doing actual regular expressions on every single entry to be able to do filtering.
And let's see sort of what the top page views.
You can maybe guess based on my regular expression what might come up here.
So as we run this choir, there's over two terabytes of data that's actually being -- in
the original data set.
We're actually looking at about 1.2 terabytes.
Again, based on the columnar format.
We only actually care about a sample.
And just to give you an idea, on a sort of serial ITA3 disk drive, it would take about
25 minutes to load the data, just to read the data off the disk; right?
So we were able to search over all that data in approximately 15 seconds.
Usually it varies between that that -- of course the ultra zoomed in UIs are not that
fun.
Here.
We've got the top 100 page views for that month in 2010.
Google is at the top here but all these others sort of match the same query.
So the point here is we just process 1.23 terabytes of data in about 15 seconds.
So that opens up a world of being able to analyze data and being able to log a ton of
stuff and being able to work with it.
So we switch back to the slides.
So another idea that I wanted to sort of -- I wanted to see people's ideas with what could
we do with large amounts of data.
So I kind of came up with another example that was more test centric around performance
data.
So -- oops.
Wrong button.
There we go.
This is a typical data pipeline that most have built in some way or another.
This is often BigQuery customers do this type of thing.
So you've got servers.
They're logging all types of data.
You're uploading these to Google Cloud storage.
You're then ingesting them into BigQuery.
And in fact, in BigQuery now, you can even skip the cloud storage stage and ingest -- be
in the stream APIs directly into BigQuery.
And now you've got this data and you can analyze it in a bunch of different ways.
You can do Hadoop workflows with large complex operations, and you can use sort of BigQuery.
You can use BigQuery on really large complex queries that might take longer that aren't
interactive and then you can also do ad hoc analysis.
Tons of our customers do that.
They just want to poke around and see that.
So again, switching back over to the demo, so I've got this Google speed table over here.
Let's close this out.
And basically what this is was -- you know, theoretically speaking, once a month I've
been doing performance testing against my Web site, which in this case is Google.com.
And we've got a speed index number which is effectively the number that it takes from
the time I start load to go the time it's visually rendered.
And I've taken those samples over the last three or four years, right, and I've got all
this data.
So I start thinking I think my Web site is pretty good but I'm curious how it compares
to the rest of the Web.
So it turns out that there's also public data, this HTTP archives data, that basically does
sampling on the top million Web pages in a Lexus index, and there's all kinds of data
that gets snapshotted, but in this case we care about, again, the date they did this,
create date, and then there's this speed index item here.
So wouldn't it be interesting to see how we compare to these top million Web sites?
Instead of just using the console, we're going to use something a little more fun.
So there's basically, it's a restful API to BigQuery, and there's always kinds of -- every
language has a client library that you can connect with.
In this case we're going to use app script and sheets.
So we won't go through this query but effectively what we're doing is this is kind of the interesting
part here, we're going to look at the 25th quantile out of the million and graph it over
time, and actually down here at the bottom we're going to join it against our table.
So there's a join here.
And we have two different data sets.
We're going to join them.
And then hopefully what we're going to do is plot the results and kind of get a visualization.
So we're going to run this query.
This one is not quite as large as the last query, so it should run pretty quick.
All right.
So there's our data.
We've got the run date.
The 25th quantile and then sort of the Google speed.
So let's do a quick chart of this.
Zoomed in UIs.
There we go.
Oops.
I picked the wrong chart type.
All right.
So there's something a little more interesting.
So the blue line there, we've got the 25th quantile and then we have Google.com over
time.
Looks like we have a bit of work to do to improve speed latency.
There it is on the left in terms of milliseconds.
So really easy to be able to sort of pull out this data, manipulate data, figure out
performance data, figure out test analysis.
Within Google, we actually have all kinds of shared data sets we use.
Every single CL, all information about changes that go in, every single bug that gets filed,
information about every single machine in production are available as data sets within
Google, and we use the same exact tools to do analysis and correlations across these
things; right?
So our engineers, our PMs, anyone within the company is able to sort of do that.
And so the same tools are available for you guys.
There's actually a free tier.
You're able to do a terabyte of querying for free every month.
There's a price model that's basically use what you use, so if you don't have terabytes
of data you can still sort of load it up for very cheap and be able to do this type of
analysis.
So, again, the hope is that this kind of gets you thinking a little bit more.
Historically, we've been very restrictive on what we feel comfortable sort of logging,
and that's a big problem and how are we going to analyze it.
But the tools are there and we're in an exciting time in sort of data processing to be able
to do that.
So, hopefully, you'll be able to take that and think through what you might be able to
change in your own companies.
That's it.
[ Applause ] >>Sonal Shah: Are there any questions?
Any questions on the moderator link?
All right.
Thank you, Brian.