Hello, and welcome to another episode of GDL-IL--
GDL Israel.
Today, we're going to speak about Big Query and Apps
Script, and how you could leverage those two amazing,
powerful technologies in order to get some clever insights on
your own big data.
Now I didn't prepare any slides today, because I
thought code would be a much more appropriate tool for a
crowd of developers.
So without further ado, let me share my screen with you, and
show you what we are going to do today.
So first of all, we're jumping right into the things.
But maybe we'll take a step back.
And we'll open another tab here, just to show you what we
are talking about.
Yes.
And when we're talking about Big Query, the best source to
start and try to understand what we have on our heads is
the ability to gain insights from your big data.
And when we're saying "big data," we're speaking about
gigabytes, or terabytes, or petabytes of data.
And anyone that works in, let's say, a startup, mid-size
company, or a bigger organization, knows that
there's lots of logs, lots of data,
that is sitting somewhere.
And there are true gems in this data.
And basically, it's quite hard, in most of the cases, to
ask the right questions, and to get a response in a time
that will make sense.
Luckily, Google Big Query is a technology that lets you do
exactly that, and analyze your massive data sets.
And the nice thing about it is that you have this browser
tool-- that I'll show in a minute-- and a command line
tool that let you play, quite quickly, with the data, even
before you start approaching the API and start building
your project that will create some useful tool for your
organization.
Luckily for us, you could go today-- and I'll
make it a bit bigger--
and the sign up is just right here.
And it's open, now, for everyone.
So you don't need to have any invite, or any betavite.
In our little case, you chose the [INAUDIBLE]
--that's importing the HTTP archives, where we have the
ability to ask some [INAUDIBLE] on the web, and
how we could get insights about what's
going on with web data.
So when we're coming to this browser tool, you can see
quite quickly that we have the ability to go and to have
those public samples.
I'll make it a bit bigger.
It will be easier to read.
And in our case, I'll just choose Wikipedia, but you have
lots of other things.
And first thing, we're getting the schema.
If we go to the details, immediately, you could have,
first, just the grasp of what is the size of this data, and
when it was created, how many rows we are talking here--
in our case, north of 300 million rows--
and how this table is composed--
so all the different characteristics that you could
later run a query on.
Here, I prepared and we have, here, a query history.
A simple query, that if I'll click on it--
let's make it a bit prettier, and make it larger.
So this query is getting the top 10 sites--
sorry, 10 articles--
that where revised frequently.
And we want to see just what are those articles that people
have changed them again, and again, and again.
When we are running, you see here, we're getting how many
seconds it's taking.
And here in our case, to go and to count over north of 300
million rows took 2.4 seconds.
So, quite impressive.
And here, if I just take this line and make it bigger, you
can see a few of the popular terms.
Quite funny that the list of World Wrestling is very, very
high, considerably.
What we want to show today, and that's something pretty
cool, is that the HTTP archive is basically a site that just
gathers what is going on the web.
As we know, it's important to see how things are
progressing, how things are moving, what are the trends,
if it's going forward or backwards.
We want to make sure that we know, and we have a way to
analyze it.
And this great initiative is an open source project that I
was lucky enough to contribute to in the past.
And all the data here is, of course, downloadable.
So you could take it with you, and analyze it on
your own free time.
The only problem is that it's more than 400
gigabytes of data--
so, like a magnitude of 10 times what we saw just a
minute ago with Wikipedia.
There are a few interesting trends, and slides, and stats
that you can see here, on the site, itself.
But of course, it's much more powerful to analyze and to
have the ability to query it on our own.
So if we go into the browser tool, we have here-- and
you'll need to edit.
And I'll have a post that gives you all the little
details of how you could edit to your project, later on--
But you have, here, basically, all the runs and all the data
that you would want to query later.
And again, in our case, I prepared a short query that--
it's a bit longer, as you can see here.
But let's make it a bit more readable.
Basically, I'm querying on June and July.
But for our example, let's keep it just for
June, and run it.
And what I want to see in this query is which are the most
popular sites--
basically, APIs--
that are being used by other sites.
So in this case, you can see quite quickly--
let's make it a bit bigger.
And now let's--
--quite well that Facebook is in the lead.
And then, you have Google, and Doubleclick, and Google
Syndication, and Google Analytics leading the chart.
Of course, you could save this table.
You could download it, and work with it off this browser
tool two, and then other tools.
And it's a quite nice way just to see what we are doing, and
how we are going to approach the data.
Now the big, nice thing about it is that, with App Script--
and this is the example that I prepared--
we could take the same thing that we saw just now, but
automate it, and automate it quite quickly and easily.
And how we are going to do it--
I'll just show the code [INAUDIBLE]
maybe we'll just jump in and see what the
results look like.
So I composed this data from the code that we have.
And as you can see here, I have, here, a new menu item
that allows me to run those queries, and
get the data here.
And after I'm getting the data-- for instance, in our
case, I just wanted to see which are the top 10 sites
that are being used by others.
But I wanted to run it on the previous 12 months.
So I wanted to see some sort of a trend.
Is the usage going to a certain direction?
Does it move up?
Does it move down?
And here, I just used the charting to take a few bold
examples like Twitter, Google Analytics, and Facebook.
And as you can see here, it's taken just a bit of time.
But you can see quite nicely that, maybe here at the
beginning of 2012, the HTTP archive wasn't crawling the
same amount of sites that it's crawling today.
So we see quite a nice ramp up.
But you could definitely say something about the end of
last year-- so around November, December, 2012.
And then up to today, we see that, in February, we had a
bit of a drop down.
And then, you can see the trend.
Another interesting aspect is Google Analytics, of course.
You can see it very steadily going up.
And now, it's a bit flat.
And Facebook--
and here, it's mostly Facebook CDN--
it's quite different, because I just moved the bar here.
Why?
I think, because of the way the data was sorted.
But you can see clearly that it's going up.
Then we had some drop at the beginning of this year.
And now, the trend is quite flat.
So it's quite nice and interesting to see
trends that are going.
Let's jump into the code, and just go over it.
So the first function here is a quite basic initializing
function that will trigger when we open the Google sheet
that we have right here.
And it will just add a menu item that we saw right here.
So this is the menu item, as you can see here.
Now I hope that you can still-- yeah, OK.
You can still see it.
So we have this menu item.
And let's jump into the code.
So this code just added this menu item.
And it's letting us run, basically, all the different
queries that we want to run.
And here, I have another query that just allows me to run all
the queries that I have under my hand.
So it's a very useful way to just--
let's say you have a daily dashboard that you want to get
insights, and to run your several queries, and then, get
the results back.
It will be quite nice to have a function to just gather all
the queries, and make sure that you are generating them
with one entry point.
And here in our case, it's the query, or the function, that
will give us the results of which are the top 10 sites--
or APIs, actually--
that are being used by third parties, over time.
You can see, here, that I'm getting a default of where I
want this data to be output on the sheet, itself-- so what is
the starting row, and the starting column.
Then, I have this unique identifier to my project.
And if you want to mimic it, you'll need to go to the
console on Google APIs, and make sure that you're creating
a new project, and you're allowing
yourself to run Big Query.
I'll have, again, all the details on how to do it in the
blog post that I'll publish just after this GDL session.
Then, we get the handler on that active
sheet that we have.
In our case, it's this guy-- third parties.
And here, we just dump all the raw data.
Here, I have all the dates that I want to, basically,
query against.
Another fun part is here, where I'm just building the
SQL and making sure that this SQL is composed from several
SQLs, that each and every one of them is just going on the
certain file, and fetching the information from
the specific file.
If you saw, before that, we were gathering
the files per month.
So pages and requests would be gathered on a monthly basis.
And then, when we query them, it's a much more efficient way
to go month by month, and get the results back.
So after we compose this nice SQL variable, we'll create
another query results object.
And in this try/catch block, we'll just go to Big Query
object, jobs, and set the query with the project
identifier in our SQL.
The beauty, here, is that this object, now, will run the job
in the background.
And once its completed, it will return us
getJobComplete() == True.
What we want to do, here, is just run on this loop, and
make sure that we are fetching the results just
when this guy is true.
And if it's, of course, not, we'll just keep trying.
Now we are just setting some boilerplate metadata on the
query, itself-- how many rows we go back, etc.
We could definitely dump them into another sheet, and get
some insights of what was the metadata of the query, itself.
And last but not least, we're just building the object of
dumping the result's values.
We get all the content that we want to put on our sheet.
And then, we're just getting the right range, and then
setting the values with results' values.
Last but not least, we'll just jump here, and, with the
message box, inform the user that everything is done.
Let's see how it's run.
It might take a bit of time, but we'll run it.
I'm just choosing the function that I want to run here--
in our case, it's this function--
and then, I'm clicking run.
As you can see, it's now running it.
And if we come here, basically, we're getting a
notification that something is working.
And now you can see that all is done.
We have all the answers.
And you see here, we have fresh data that was just
produced from this specific function.
The code will be, of course, live.
Actually, part of this code is already live on my GitHub
repository, and it's public.
So of course, I'll have a link here.
But you could check out greenido on GitHub, and get
different queries here, and of course, all the boilerplate
code that I just showed a second ago.
--case use for these types of things is the ability to
create your own dashboard, quite quickly.
And if you think about it, when you have this big data
inside Big Query, you could quite quickly go with App
Script, and create your daily dashboard, get a trigger.
And here is a nice ability to just--
sorry, just here, you could put what your triggers are.
Make sure that, for instance, we want to add a new trigger
that will run this specific query on a specific event.
And this powerful thing will give us the ability to
basically create new dashboards that, once we are
fetching the data from the query, we can emulate, we
could update the sheet, and of course,
share it with our coworkers.
So that's what I had this time.
And if someone would like to ask questions on the chat, or
live, here, please feel free.
So I see one thing from a developer.
They're asking if they could run Big Query with other
languages and technologies.
And the answer, of course, is yes.
We actually have lots of libraries and clients that are
supporting almost, I think, any popular
language that is out there.
A great way to start and check how you could write with it,
and how you could work with Java, Python, and others would
be to go to this page, and just check on Big Query's
APIs, Big Query client libraries.
And from here, you could see, quite quickly, that we have
lots of client libraries for almost any popular technology
that is out there.
So if there are other questions, this is the time.
If not, thank you for joining us, and we'll be here next
week with interesting startups.
So be strong, and bye bye.