[MUSIC PLAYING]
MICHAEL MANOOCHEHRI: Hello, data developers.
Welcome to Google Developers Live.
My name is Michael Manoochehri.
I work with the Developer Relations team, supporting
data products like BigQuery.
And today, I'm joined by Kim Cameron, a developer relations
person working in Seattle with the BigQuery team.
Hi, Kim.
How are you?
KIM CAMERON: No, I'm doing well, Michael.
How are you?
MICHAEL MANOOCHEHRI: That's great.
I'm doing great.
What's the weather like in Seattle?
KIM CAMERON: It is rainy, Michael.
I know this is a shock.
MICHAEL MANOOCHEHRI: [LAUGHS]
So cool.
It's usually raining in Seattle.
Today we're going to be talking about some new
BigQuery features.
But for those who haven't used it yet, BigQuery is an API
that allows you to ask questions, run queries, on
very large data sets.
We're talking things like gigabytes and terabytes.
Now one of the things we've tried to do with Google
BigQuery is to simplify the lives of data developers.
When you're normally working with very large data sets, you
often have to manage your own infrastructure, buy expensive
equipment, or do data transformations.
And with BigQuery, one of the advantages to using it is that
the service is hosted on our infrastructure.
And by using the SQL LIKE queries, it allows you to run
these queries very easily.
So today we're going to talk a little bit about some of the
new features that we're launching today.
And Kim, do you want to tell us some of the new features
that we're going to be talking about?
KIM CAMERON: Absolutely.
So we're going to talk about four features today.
First of all, we now have the ability to use the timestamp
data type instead of regular Unix
timestamps and date strings.
You can now add columns to your tables schemas.
And we have two really important updates to the JOIN
and GROUP BY operations that Michael is going
to talk about first.
MICHAEL MANOOCHEHRI: Yes.
So let's talk about joins.
So when you're dealing with a lot of data, very large data
sets, what we try to do with BigQuery is allow you to run
these queries very quickly on these data sets.
Imagine if you're an e-commerce site and you've got
millions or thousands of user IDs, and you have another data
set full of transactions, purchases.
And you want to gain some insight by combining these
data sets together.
Now this kind of data process is usually something in the
realm of data transformation.
You'd use something like Hadoop, MapReduce.
You may be using a lot of expensive infrastructure or
even waiting a long time for these things to happen.
But relational database people are very used to running JOINS
across different tables or grouping by
many different buckets.
So with BigQuery, we've tried to make this simpler.
We want to extend.
We've heard from our developers who have told us
they want to extend the simplicity of BigQuery to
other use cases.
And one of the most common use cases we've heard from is to
extend BigQuery's ability to run these queries, not just on
one large data set, but multiple.
In the past, BigQuery has supported what we call small
joins, which is a large table joined with a smaller table,
like a look-up table.
But today we're announcing a new feature called Big JOIN,
which allows you to actually merge data results from two
large data sets.
We're also rolling out a new feature we call Big Group
Aggregations, which allows you to group by a very large
amount of individual common records.
For example, grouping results by each of the
individual user records.
And to do this, we've added a new clause that Kim's going to
talk about.
KIM CAMERON: So that's actually the keyword, EACH.
Which you can add to your queries.
And that activates these new features.
And this isn't to say that you wouldn't want to use the
regular GROUP BY or the regular JOIN anymore.
There are use cases for using one or the other.
MICHAEL MANOOCHEHRI: Yeah.
That's right.
Like I said before, the original JOIN was really great
when you had a large table and a small table.
And when you have that actual use case, you should still use
JOIN without JOIN EACH, or GROUP BY
without GROUP EACH BY.
And I'll demonstrate some of these
queries in just a second.
When you have a lot of different keys, you're going
to have to use the JOIN EACH.
And let me demonstrate that right now.
So why don't we go to the query window, and Kim I will
demonstrate an example of this query.
So here, if you can see, our BigQuery WebUI--
when you sign up for BigQuery, you have access to this web
interface to run your own queries.
We also have public data sets that you can query.
I'm going to show you a public data set here.
It's our Google Books Ngrams data set.
It has trigrams, which are really just collections of
three words.
And this particular data set we have is 258 gigabytes.
And I'm going to run a query that matches, merges data by
common key with our Wikipedia revision data set.
That data set's about 35 gigabytes.
So we're going to do a JOIN query.
Now let me run this without JOIN EACH, and
you'll see what I mean.
JOIN is for a large query running--
joining with a smaller table.
So let's run this.
So it's going to throw an error.
It's going to say, this table is too large
for a regular JOIN.
So now let's use the JOIN EACH clause.
And we'll let this run.
And we'll talk about what's happening.
So this is adding an additional processing step,
which is actually making sure that we can join these two
tables by the common key, which is the Wikipedia title
and a particular Ngram.
I've thrown in a string function here to actually
normalize the data.
Some of the Wikipedia titles have title case.
And some of the Ngrams have lowercase.
So I'm just running that over all the records that we're
joining against.
And I'm saying, return the top five.
And there we go.
That took merely 27 seconds.
It processed all of that data.
And it's given us the five examples of Ngrams that are
also popularly revised Wikipedia titles.
So now you know.
This is just using the public data sets.
I think a better example would be what Kim was talking about
earlier with some of these e-commerce examples.
So Kim, can you tell us a little bit about what you
might do in an e-commerce example that would
make sense with this?
KIM CAMERON: So, one example would be let's say I store
user information in one data set and then I also store
order information in other data set.
So each of those orders is going to have an associated
customer ID.
And now, what happens if I want to aggregate all of that
information about a customer?
I'm going to have to join two really big data sets,
especially if I'm a really big e-commerce site, and I have
millions of users.
And that's going to be a challenge to use with the
regular JOIN.
MICHAEL MANOOCHEHRI: Yeah.
And I think a lot of people have been using things like
MapReduce or some kind of transformation tool to first
denormalize this data, so they could put it
in a tool like BigQuery.
But I think this really enables you to do a
different use case.
It's something that stems that simplistic
model in larger tables.
And I think it really opens up a lot of
new ways to use BigQuery.
KIM CAMERON: And how about the GROUP BY example?
Do you have one of those.
MICHAEL MANOOCHEHRI: Yeah.
So the GROUP BY is actually almost exactly the same.
And another example, we could do things like--
I don't actually have one queued up-- but we could
actually do things like run a query where we want to look at
every Wikipedia title and group something by each
Wikipedia title.
In the past, BigQuery didn't support this type of grouping.
The GROUP BY had a limit.
But now, if you use GROUP EACH BY, that kind of query doesn't
actually have a limit.
You can pipe out sort of any amount of bucketing that you
want, as long as it fits within our
maximum result set size.
So that's it.
You can use them in conjunction as well.
So it's really interesting.
You can create these joined table results and then run a
GROUP BY on the resulting table.
There's lots of great things you can do with this.
Really great for mobile game developers and, as you said,
e-commerce sites.
KIM CAMERON: Absolutely.
MICHAEL MANOOCHEHRI: Really interesting use cases there.
KIM CAMERON: So next let's talk about timestamp.
So in the past, BigQuery has had date and time functions to
deal with things like Unix timestamps and also date
strings, ways to parse date strings, et cetera.
And this is good.
You could technically get that information from Unix
timestamps or date strings.
But let's say you wanted to store timestamp data on your
end, and you wanted to put that into BigQuery.
In the past, you haven't been able to do this.
And you've had to do one of two things.
One, you could actually change the data in your database.
So you could start storing Unix timestamps instead of a
timestamp data type like in MySQL.
So that's option one.
Option two is you could actually transform your data
before you load it into BIgQuery.
And then that's an additional step that you'd have to do
every single time.
So again, we wanted to listen to you as developers and try
to make your lives easier with this.
So we've rolled out the addition of a
timestamp data type.
And what that means is, now you can keep your timestamps
in your database.
And you can load them into BigQuery as time stamps.
There's a few advantages to doing this.
First of all, obviously, you don't have to do that
transformation step, which is going to save you time.
Additionally, you can specify a time zone when you load your
data into Big Query.
So you can preserve that bit of metadata.
Additionally, if you ever stored your data as a date
string, it's kind of tricky to parse that information out, if
you wanted to really easily get things like, what's the
month, what's the year.
So by using BigQuery's built-in timestamp functions,
you can just say, month.
And you can get the month of the timestamp.
So that makes your life--
MICHAEL MANOOCHEHRI: Yeah, I like this use case that
supports the idea that you can have different time zones in
your timestamps.
Maybe you're collecting data from different servers, like
one on the East Coast of the United States,
one on the West Coast.
KIM CAMERON: Absolutely.
And finally, it's a lot more readable.
So I don't know about you, but unless you're a math savant
like Michael--
MICHAEL MANOOCHEHRI: Or like Kim.
KIM CAMERON: --reading Unix timestamps in records is not
very readable.
So let's say you want to query your data in BigQuery and you
want to see the date actually in the result.
By keeping it in timestamp format,
it's going to be readable.
So those are some of the reasons why timestamp, we
think, is going to be helpful.
And Michael has an example.
MICHAEL MANOOCHEHRI: Yeah.
So if we go back to the BigQuery WebUI, I'll show you
one of my favorite things about the new timestamp data
type that Kim just talked about.
One is you can cast strings to timestamp.
And I'll show you an example of that.
I'm actually going to run three things
in one quick query.
You can cast to a timestamp data type.
You can also timeshift, which I think is very cool.
You can say, give me the timestamp that is one hour or
one year away from a particular timestamp.
So here I'm just going to do that.
I picked a random timestamp.
And I'm going to show you the string turn into a timestamp.
And as you can see here, we list UTC at the bottom.
And then I've just selected a date that's one hour ahead and
a date that's one year ahead.
Now Kim, I believe if you want it to go backwards, you can
just add a negative number.
So I can re-run this query with the same date.
And I'm going to say, give me something one hour before and
one year before.
And there we go.
The query has just run.
And now I can actually do timeshift.
So I can say things like, give me everything from this date
to another date, so within a range.
You can do things like that.
It's very useful for those kind of applications.
And now let's take another look at some real data.
We're going to go back to one of our public data sets.
It's one of our most popular that was curated by a Googler
on the Chrome team, [? Ilya ?]
[? Gregorich. ?]
It's our GitHub commit history data set.
Here I'm just going to look at the timestamps there.
We have these timestamps in that data set
currently as strings.
And as Kim said, it's really useful to change these into
timestamp data type, so we can actually run the hour method,
which will pull out the hour of the day that that timestamp
pertains to.
So here I'm going to look at all of the records in GitHub,
when they were created--
I'm sorry, these are events in GitHub--
when they were created.
And we're going to take the timestamp, change it into the
timestamp data type, and then pull out the hour.
And so what we're doing here is we're trying to find which
hour of the day is most popular for GitHub events?
And of course, this is in UTC.
Look, the queries here.
Looks like 1300 hours is UTC, 14, 16 is about the most
popular time for these GitHub events.
You'd have to do a little more data analysis
to figure out why.
But you can use this kind of query to find out things like
that on your own data.
KIM CAMERON: And for any of these functions, you can look
at the query reference.
There's a new section called Date and Time Functions.
And that contains both all of the old Unix timestamp data
string functions, as well as the new timestamp functions.
And we've also added a new column that has an example
query that you can run as well as the result.
So if you want to remember how to do the negative numbers or
any of these, you can just check out the query reference
at any time.
MICHAEL MANOOCHEHRI: Yeah.
I like those samples a lot.
In fact, the samples I just ran, I literally pulled some
of those right from the new documentation.
So I'm pretty happy about those.
KIM CAMERON: Oh great.
And then let's talk about the column schema example.
So let's say you store your data in either datastore, on
App Engine, or a NoSQL solution.
And you do that, probably because you want the
flexibility to be able to add data on the fly.
So going back to the e-commerce example, let's say
you store customer data and you store zip codes.
And later on, you decide you want to add the four-digit
extension as a new column.
You can do that on your end.
In the past, that wasn't possible in BigQuery, because
once you load your data into BigQuery, you're stuck with
that table schema.
We fixed that today.
And moving forward, by letting you just update your tables
schema with the new column.
And it's just going to work.
MICHAEL MANOOCHEHRI: Yeah.
So to do this, you could actually just reuse the
BigQuery API's Tables Update method.
And all you have to do is run that method on your table and
pass in the new schema, with the additional columns added.
Records that you've already had there are unaffected.
They become null values.
And you're able to have the data in there before that
didn't have that schema and extend that schema for the new
data coming in.
It's pretty great.
Another thing we're working on is adding that functionality
to the BQ command-line tool.
This is the command-line tool for BigQuery.
So we'll have that out soon as well.
KIM CAMERON: And we'll definitely keep you updated
once that's available.
So yeah, I think those are the four features we wanted to
talk about.
One last piece of info for you--
we've also been trying to make information in the
documentation a lot more accessible, especially for
things that are important to you, like we know the query
reference is important to you.
And in the past it's been a little bit buried in the docs.
So we're rolling out some new doc changes today as well.
There is an overall plan.
It's going to get a lot better.
For now, we've launched a few new nodes in the top-level
navigation.
One is the loading data in the BigQuery node.
This contains a few new topics like preparing
your data for BigQuery.
So this gives examples of things you might want to do on
your database and before you load it.
We also have some content on loading from
various data stores.
We also have a new exporting topic.
And then, we've moved the query reference
up to the top level.
It's now called Querying Data.
So you should be able to access that a lot easier.
And if you have any feedback at all in the documentation--
on things you like, things you dislike, things you think are
hidden and you'd like them [? bundled ?] up--
there's a Send Feedback link on every documentation page.
And we do personally read that feedback.
And we use it to make your experience better.
So we really encourage you to do that.
MICHAEL MANOOCHEHRI: Yeah.
And before we go, let's talk about how you can reach us in
other ways.
If you have developer questions, you can ask them on
our official developer support area, which is Stack Overflow.
We use the tag Google-BigQuery.
So just ask your questions about BigQuery.
Tag them that way.
We have, not only us, but other Google engineers read
that thread as well.
And we'd be happy to talk to you.
Also, don't forget to follow us on the Google Cloud
Platform Developers Google+ page.
We're always posting there with new features, new things
like that, and we'd love to get your
feedback there as well.
So great.
So that wraps up our new features.
Please send us feedback.
I hope you enjoy them.
And we'll be back again for another data developers GDL.
KIM CAMERON: Indeed.
MICHAEL MANOOCHEHRI: I'm Michael Manoochehri.
KIM CAMERON: I am Kim Cameron.
MICHAEL MANOOCHEHRI: All right.
Thank you very much.
[MUSIC PLAYING]