FELIPE HOFFA: Hello, everyone.
I'm Felipe Hoffa from the Google Cloud Platform team.
Today we're going to review the latest BigQuery news.
Now we have streaming.
And I'm here today with Aleem Mawani, the founder of Streak.
Hello, Aleem.
How are you doing?
ALEEM MAWANI: Thanks for having me.
FELIPE HOFFA: Oh, I'm very happy to have you.
You've been using BigQuery for a long time.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: Yeah.
ALEEM MAWANI: About a year and a half now.
FELIPE HOFFA: Yeah.
I know you met our PM at a conference,
and you asked him for us to have streaming.
ALEEM MAWANI: Yeah.
That was the first feature request I had from BigQuery--
is once I saw how powerful it was,
I wanted to be able to throw single rows of data at it.
FELIPE HOFFA: Yeah.
So you really wanted the streaming, and now you have it.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: Yeah.
But before we go to see how you're using it,
what is Streak?
ALEEM MAWANI: Sure.
FELIPE HOFFA: Please tell us.
ALEEM MAWANI: So Streak is-- it lets
you do business process software right inside of Gmail.
And so things like sales processes,
hiring, fundraising, deal flow.
Even stuff like bug tracking, we let
you do that right inside of Gmail.
And we do that by having a Chrome extension that
injects our UI right inside of Gmail.
Yeah, and that's Streak.
FELIPE HOFFA: That's cool.
And I also know that once you started using BigQuery,
you did something similar to Big Query?
ALEEM MAWANI: Oh, yes.
We're fans of taking existing software and making it better.
Like we're doing with Streak, building on top of Gmail.
And so for BigQuery we wrote another extension
that makes BigQuery better.
And we called it the BigQuery Developer Tools.
And we put that on the Chrome Web Store,
and it basically adds a few bells and whistles
to the BigQuery UI.
FELIPE HOFFA: And it's available for free?
ALEEM MAWANI: Yes, yes, this is free.
It's just for everyone to use.
We open sourced it as well.
FELIPE HOFFA: Oh, that's wonderful.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: So you will be showing us how--
ALEEM MAWANI: Yeah, I'll show you
a few examples of what it can do and how we built it.
FELIPE HOFFA: Awesome.
What do you use BigQuery for?
ALEEM MAWANI: The reason we wanted streaming so much is
because we use BigQuery two main things, logging
and event tracking.
So application logging.
Basically, all of our server logs
from App Engine are pushed to BigQuery.
So we use App Engine as our server platform for Streak.
And debugging errors, seeing the performance
of our application-- it's much better
to view that log data in BigQuery
than it is in the App Engine log console.
FELIPE HOFFA: Oh, yes.
Can you show me how you do it?
ALEEM MAWANI: Sure.
FELIPE HOFFA: Let's do a demo, please.
ALEEM MAWANI: Sure.
So the first query I want to show you
guys is just a basic query that shows the number of errors
that we have on our server, broken down by the endpoint.
And so the great part about using BigQuery
to do log analysis is that you can
write a full on query, and then group it,
sort it, order by whatever you want,
as opposed to just doing regexes in the App Engine log console.
So to make this work, we take our App Engine logs.
And every minute or two minutes, we
parse the last two minutes worth of logs.
We split it up into different columns,
so we extract different amounts of information--
whether there was an error at the host, the endpoint,
the latency.
Just all of the relevant information,
split it up into columns, and then put it into BigQuery.
And so this first query that I'm showing you,
we're basically taking all the logs that have errors,
grouping them by the endpoint, and then sorting
by how many errors there are.
So if I run this query-- and these errors
are sort of streamed right to BigQuery.
So you'll see these are a bunch of difference
endpoints on our server.
And then the number of errors that we've
received on those endpoints since this table started
logging.
Now the good part is I can restrict this.
I can add another WHERE clause that
says in the last hour or the last 24 hours.
And we can also use decorators as part of the streaming
to make it even cheaper.
So this is kind of stuff that we do, just that a high level,
to see-- monitoring general application health.
FELIPE HOFFA: That's awesome.
So now you know what errors are happening today,
in the last hour, whenever you want.
What do you do next if you have an error?
ALEEM MAWANI: Yeah.
So if we have an error, then-- so for example,
if we've noticed an error that we've never seen before,
or an endpoint that's failing higher than normal,
we'll run another query that looks something like this one.
Basically, this just says show me all the details of the logs.
Show me the full set of columns.
Because in this case, we want to see the actual log
entries themselves and not do any grouping.
So in this case, I'll run this query.
It's just going to take a second to load.
The good part here is that we can
see a full application stack trace inside
of BigQuery because we export the full log.
We extract those columns, but we also
export the full stack trace.
FELIPE HOFFA: Can you see the stack trace there?
ALEEM MAWANI: Yeah.
So for example, this first row here.
We have the path.
And the first line of the stack trace is right here.
Now BigQuery doesn't like multi-line text.
So as part of the extension I mentioned earlier,
we added this feature where you could click on the row number,
and it would let you see multi-line text right inside
of BigQuery.
FELIPE HOFFA: On the web UI?
ALEEM MAWANI: Yes, in the web UI.
Yes.
So you can see here, this is the full stack trace
of the entire request.
And then there's an error here.
In this case, it happens to be a concurrent modification error.
So we can debug using BigQuery as opposed to some other tool.
FELIPE HOFFA: But you were telling me
you are getting errors all the time.
ALEEM MAWANI: Oh yeah.
FELIPE HOFFA: How do you know what is normal, what is--
ALEEM MAWANI: I see.
So one thing we do is we power our dashboards-- our monitoring
dashboards, and also dashboards in our office--
that show the error rates of our server over time.
So that's another query, the number of errors at that time.
And what this does is just shows a time series of errors.
And so you see this query here breaks down
the number of shares by hour in the day.
And not on this specific path, but just in general.
And so if we see a spike, then we know to sort of take action.
So we have a dashboard that has that ability.
But we also, as part of the BigQuery extension,
again, that we wrote, we can graph this kind
of stuff right inside of BigQuery.
So if you saw that but I basically we
had a button here called the Timeseries Graph.
And it takes whatever data is in this result table
and it makes a time series out of it.
So right here, we can see-- last night, we had a ton of errors,
and then they've sort of settled down.
And again this morning, we saw a few more.
And so we monitor them on the dashboard,
but also we can see the graph here.
FELIPE HOFFA: Oh, that's so cool.
Other than errors, performance is important.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: Yes.
ALEEM MAWANI: Yeah.
So we want to make sure our application is fast for users.
And so one way to do that is just by hand, playing
with the application.
But another way is obviously measurement.
And so what we do in all of our endpoints
is we log to our App Engine logs how long
certain operations took.
So for example, our application queries
a lot of data from the data store.
And then it has a bunch of results,
and it wants us to ship it down to the client.
In order to do that, it has to serialize it to JSON.
So that operation, we measure the time
it takes to do that operation, and then
we just output it to the logs.
Now once it's outputed to the logs, it ends up in BigQuery.
And now we query over it and do all sorts of stuff with it.
You can find mins, maxes, means, averages,
all that kind of stuff.
So in this case, what we're doing is the serialization
time, we output it to the logs.
But it's just in a plain text field.
It's not in a column.
So we're using regular expressions
to extract it from one of the columns,
convert it to an integer, and then run some analysis over it.
So you'll see that the results in this case, there
are a bunch of different endpoints here.
First we show the number of times
that endpoint was requested.
But then the second one is, how long on average
it took to do that stabilization.
So right away, we see 1,400 milliseconds.
It's a crazy long amount time.
And then some are really short, 11 milliseconds.
And then some, on average, are really, really long.
So what should we focus on Next what we really
do is look at the number of hits times the mean
to know the total time spent.
And that's where we focus our efforts
on performance optimization.
FELIPE HOFFA: Yeah.
So you don't want to focus on slow things.
You want to focus on slow things that happen a lot.
ALEEM MAWANI: Exactly, exactly.
FELIPE HOFFA: That's cool.
I also want to highlight what you're doing here.
The ability to run a regular expression over a full table,
and transforming them to integers,
and then doing all the math?
ALEEM MAWANI: Exactly.
FELIPE HOFFA: That's one of the reasons I like BigQuery
so much.
ALEEM MAWANI: Yeah.
Yeah, there's no other way we could
have done that using the typical App Engine logging system.
FELIPE HOFFA: Yeah.
Oh, that's cool.
Tell me, how many customers do you have?
ALEEM MAWANI: So we have hundreds of thousands
of users in Streak.
FELIPE HOFFA: Users.
ALEEM MAWANI: And it's a lot users,
and so there's a lot of traffic.
And so the streaming ingestion actually
helps so much because we can just
push all this data to BigQuery as it happens.
FELIPE HOFFA: Yeah.
To my needs, 100,000 users is not easy.
ALEEM MAWANI: No.
FELIPE HOFFA: You're using BigQuery, then,
to know what they're doing?
ALEEM MAWANI: Yeah.
The second use case that we use BigQuery for
is to understand what our users are doing in the application.
And so literally every click, every page they go to-- I mean,
this is typical analytics stuff.
But instead of putting the event tracking data
into something like Google Analytics or Mixpanel
or some of these other services, we actually
put it straight into BigQuery.
And there's a few reasons.
One is we can write arbitrary queries against our data.
We're not limited to some UI that doesn't do what we want.
And then also, we can join those event tracking tables
to other tables that we have in BigQuery, such as the logs.
But also, we have a mirror of our App Engine Datastore
in BigQuery as well.
So we can do a join across that and see four users
to meet this profile, what's their behavior?
Or for users who live in this country, how do they perform?
FELIPE HOFFA: Can you show me that?
ALEEM MAWANI: Sure.
FELIPE HOFFA: I love it.
ALEEM MAWANI: Yeah.
I'll show you a little bit of a toy example.
So one thing we do is, because we're inside of Gmail, we also
record events that Gmail users do.
So when they go to a conversation
or when they go to search or when
they go to a label-- of course, anonymously.
And so this query I want to show you
is how people use Gmail for people who live in Boston
versus people who live in Miami.
Do they use Gmail or any differently?
And so I just did a query here.
This is a very simple query.
All it's doing is looking at all of our Gmail events.
So whether they open an email, compose an email
go to search, whatever-- for every hour
of the day for the last 24 hours.
And then broken out by Miami and Boston.
And so here it is by hour.
Actually, the graph but didn't work.
Let me just refresh that.
Sorry, just give me a second.
So I'll run it again.
FELIPE HOFFA: I see what it counts.
Looking for people between Boston and Miami
during the same timezone.
ALEEM MAWANI: Exactly.
Same time zone.
And we just want to see how their usage differs.
And so I have the results here, broken out by hour of the day.
And I can use the time series graph
that I showed you guys earlier.
And the red line is Boston and the blue line is Miami.
And so in this case, we can see that in Miami, people actually
prefer working in the mornings much more.
In general, it follows a pretty similar curve.
But you'll notice that around 8:00 or 9:00
AM, people in Miami actually work more than--
or do more email than-- people in Boston.
And that could be for various reasons.
Maybe the way people commute in Boston is different.
And so this kind of stuff helps us
find different usage patterns of our users.
This is a toy example, but we use it quite--
FELIPE HOFFA: So if you want to call them, for example,
you know when is the best time to call them
based on where they are-- even if it's the same time zone?
ALEEM MAWANI: Exactly, exactly.
Right now, we're just using the data to analyze our users.
But eventually, we can give the same data to our users
to help them be more effective in what they do.
FELIPE HOFFA: That's perfect.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: I have another question,
because I know you can do this and I love how you do it.
BigQuery queries can get very complicated.
That one is a very long run.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: How do you manage to write them?
ALEEM MAWANI: Yes.
So I want to show you another query that's
really complicated.
And basically, what we did here is build a funnel.
So if I could just show you our home page.
This is the home page for Streak.
This is how our users get started with streak.
And basically, they come to our site
and they see one big play button.
And that Play button basically takes
them to watch various different videos
based on what their use case is.
So our goal is, a user comes our site, they watch a video.
And then they like it, and so they click this big orange
install button.
And so we want to know how many people are making it
through that funnel, and what the drop off is at each stage.
FELIPE HOFFA: You have to visit your website to get the--
ALEEM MAWANI: Exactly.
FELIPE HOFFA: Once you get them here, what should you show them
what's the--
ALEEM MAWANI: Exactly.
What's the best way for them to end up installing Streak.
And so we wrote this query that basically uses those events.
So each one of those clicks on the website-- whether they
click on this, or the install button, or a video-- each
one of those clicks is an event that we create, and we just
stream it right to BigQuery.
So we have all these events inside of BigQuery.
Now we have to write a query that
actually does that analysis.
And that query is actually nontrivial
because it's kind of like a funnel.
And so we can run the query.
And it's kind of a pain to write.
I mean, you can see how long it is here.
It doesn't even fit on the screen.
And so we wanted to make it easier
to write funnels, specifically.
So first of all, here's the results
of the query, which is if you watch this video,
then here's the percentage chance
that you actually installed it.
If you watch this video, then here's the percentage chance.
So it's clear that if you watch this video--
we want users to watch this video because they
have a higher chance installing.
But this query a pain to write.
And so we did something else, which was we
created a sort of mini syntax.
A syntax that let us write funnels easier.
So I can just walk you through it.
The first line is funnel.
So you're just telling us that hey, you're
going to be making a final query.
You tell us what table you want to do this funnel over.
And so in our case, it's the events table.
And then you give us a column that specifies that session
ID or some unique identifier in the table,
such that we can track the same user over time.
You give us a few other columns.
And then the interesting part is you just
give us a comma- separated list of the event names you want.
So in this case it was a user visited the home page
without the extension installed.
The next event was they pressed the Download button.
The next event was the download was successful.
And the next event was they logged in
and a new account was created, was the file event.
So that's our sign-up funnel.
And so we want to see how many people make it
through that funnel.
So instead of writing this crazy nested query,
we added a functionality to the BigQuery extension
that we wrote, where you just specify it like this
and then you press the Expand Templates button.
And now you can see it auto-generated this giant query
for you to do that funnel.
And so now I can just run the query,
and I didn't have to write this big, monstrous thing.
So this is sort of an alternative
to making your own page that has your own interface
and stuff like that.
We just used BigQuery's interface,
but added a little bit of functionality to it.
FELIPE HOFFA: I love that.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: Oh, that's great.
Thank you very much, Aleem.
ALEEM MAWANI: No problem
FELIPE HOFFA: I think we will leave it here now.
ALEEM MAWANI: Great.
FELIPE HOFFA: If people want to install the BigQuery
extension--
ALEEM MAWANI: Yeah.
So the BigQuery extension you can
get in the Chrome Web Store.
Just search for Streak Developer Tools.
If you want to use Streak itself--
it's great for bug tracking, product development--
you can get that in the Chrome Web Store as well,
and just search for Streak or go to streak.com.
FELIPE HOFFA: And my last question
is, how are you getting the appending logs to BigQuery?
ALEEM MAWANI: So we wrote it in open source framework
that pushes logs from App Engine to BigQuery automatically.
And you just have to install this one library.
It's really easy.
And that's open source, it's on GitHub.
It's github.com/streakyc/mache.
And I'm sure they'll put up the URL.
FELIPE HOFFA: Yes, the URL will be here.
ALEEM MAWANI: Yeah.
FELIPE HOFFA: Yes.
Well, thank you very much, Aleem.
ALEEM MAWANI: No problem.
FELIPE HOFFA: Everybody, please check out Streak.
It's a great product.
They take the extensions they're writing--
ALEEM MAWANI: And we're hiring.
FELIPE HOFFA: They're hiring, yes.
It's a great time to be a developer, so yeah.
You are based in San Francisco?
ALEEM MAWANI: Based in San Francisco,
and we love Google Cloud Platform.
And so if you're into that, we're always hiring.
FELIPE HOFFA: Awesome.
ALEEM MAWANI: Yep.
FELIPE HOFFA: Thank you very much.
I'm Felipe Hoffa, with Aleem Mawani, the founder of Streak.
ALEEM MAWANI: Thanks.