FELIPE HOFFA: Hello, everyone.
I'm Felipe Hoffa.
I'm a Google Cloud Platform Developer Advocate.
And today I'm at the GitHub offices
to learn about how they are using the BigQuery.
You might already know that BigQuery's great
when you went to analyze terabytes of data in seconds.
And that there are a lot of open data
sets around that you can query whenever you want.
But BigQuery is not only good for open data,
a lot of corporations, enterprises,
companies are using BigQuery to understand
their own data, their users, their own metrics, et cetera.
And today at GitHub I'm going to look at both worlds.
Because we have a lot of, at least three open
datasets about GitHub that they're
using to understand their own company.
So today, Alyson La, from the data science team at GitHub
will join me.
We will explore how they are using BigQuery and other tools
and open data to understand their organization.
So Alyson, thank you so much for inviting me.
ALYSON LA: Yeah, thanks for being here.
FELIPE HOFFA: Yes I know you've done great stuff here
at GitHub, and before we get started,
I wanted to mention to everyone how
you've been doing awesome stuff like, I went to the GitHub
Universe Conference.
You published these GitHub Octoverse.
ALYSON LA: Yeah.
FELIPE HOFFA: And you were behind it.
ALYSON LA: Yeah, so I was part of a cross-departmental team
that helped publish this infographic, if you will,
all about the state of the Octoverse.
And it has a few different sections,
but one of the sections is focused on open source.
And it was really important to me
to be able to publish those open source metrics using
the open data set, and be able to publish
the queries behind it for the community to reuse.
FELIPE HOFFA: Nice, so you have that Octoverse here
and a lot of different open source analysis.
But before we get to how you made it,
I would love to know how you got started in this world.
ALYSON LA: Absolutely.
So I got started about four years ago, I
was one of the first accountants at GitHub actually.
And when I started, I had never seen a terminal before
or seen code at all.
But I was really curious.
So I asked a lot of questions to my coworkers
and started going to meet-ups.
And eventually I got to learn how to code a little bit.
And using my analysis skills as an accountant
in Excel and hacking skills that I
learned from going to all these meet-ups,
I was able to parlay that into a position on our data science
team as an analyst.
FELIPE HOFFA: Nice, so you start as an accountant,
you started going to meet-ups like--
ALYSON LA: So, all around the Bay Area there's meet-ups,
for example, Women Who Code.
And there's RailsBridge on the weekends and Rails Girls
meet-ups where I was able to make some apps.
And then also by searching GitHub and seeing projects,
I was able to fork them and clone them and learn
how to hack that way.
FELIPE HOFFA: So working within a company,
you were able to find open datasets about GitHub
and that turned you into a data person, data analyst.
That is really cool.
What kind of open datasets do you have available?
ALYSON LA: So the open datasets that I've used on BigQuery,
there's a few different ones.
So first thing there's the GitHub archive,
which is all of the event data.
So every single time someone stars or pushes or creates
a issue comment or a pull request,
that's recorded as an event.
And so that's one dataset.
It's called the GitHub archive.
FELIPE HOFFA: Started by Ilya Grigorik some years ago.
ALYSON LA: Yeah, yeah, exactly.
A Googler.
And then another dataset is the GitHub public dataset
that you helped publish.
FELIPE HOFFA: Yes, we recently released that one.
ALYSON LA: Yeah, that's a great one.
It's all the contents of all of the repositories.
And that one's different from the events data,
it's only repositories that have a detectable open source
license.
FELIPE HOFFA: So for open source projects
that we know to be open source, we
are taking them and putting them on BigQuery.
All of the content, the code.
So I've been able to run analysis
like, hey, do we have more [INAUDIBLE] or the spaces.
What is more public?
That's been really cool.
And we are now having a third dataset-- GHTorrent.
That's a different project.
It does pretty nice job of making all of the data,
like you have archive, we have all the events.
But with GHTorrent we're able to see everything.
Like different tables, normalized,
to see, for example, where are people come from?
What countries do they come from?
ALYSON LA: Yeah, so the GHTorrent
includes the geographic data.
And all of the datasets are really complementary,
so it's really nice that they're all in one place on BigQuery.
FELIPE HOFFA: If anyone can come to BigQuery,
we find these datasets, start doing jobs between them.
Just to show everyone what kind of queries you run,
can you show a little bit of how you work with BigQuery?
Your tools?
ALYSON LA: Yeah, so we can start by looking
at the Octoverse and the queries that powered the open source
section.
FELIPE HOFFA: Nice.
ALYSON LA: So here's the Octoverse.
FELIPE HOFFA: Yeah.
ALYSON LA: So for example, this graph was pretty popular.
Of the 15 most popular languages on GitHub by open port request.
And the percent change from the previous period.
I published the queries that go along with this.
So here's the example of the most popular languages
by port quest using JSON extract of the events payload.
FELIPE HOFFA: So that's an important element.
On the GitHub archive dataset we have
a lot of data encoded as JSON.
ALYSON LA: That's right.
FELIPE HOFFA: So we can do that with unstructured data.
We can store JSON.
So we don't have to think about columns
when we're storing data.
And now you need to write queries
like this, that extract data from JSON.
ALYSON LA: Yeah.
FELIPE HOFFA: It seems pretty easy to do.
ALYSON LA: Yeah.
So just by using the JSON extract
and then using a time stamp and choosing the date range.
And this was a 12-month date range,
was able to get this output of say the top 25 languages.
And then changing the date range to compare it
against the previous time period.
FELIPE HOFFA: As you publish these queries,
anyone can come to GitHub take these queries,
copy them into the query and start
running their own analysis.
ALYSON LA: Exactly.
FELIPE HOFFA: Starting with your queries.
ALYSON LA: Exactly.
FELIPE HOFFA: So OK, we have to these queries.
They're exposed.
And these queries run actually inside the query.
The Web UI.
What can you tell me about the Web UI?
How do you use it?
ALYSON LA: Oh, yes.
So going to the Google BigQuery, there's
a couple of different places where you
can see the different datasets.
So here is the GitHub archive dataset
and it has these different table structures.
And then the BigQuery public dataset is here
and under GitHub repos is where you
can find all of the tables that are the repositories that we
talked about the file contents.
FELIPE HOFFA: Yeah, so here you have repos that
have in the contents table.
That's a huge table.
And if you click on it.
I think it has more if you click on the Details.
Yeah, it has 1.7, 1.9 terabytes of data now.
1.7.
And then on GitHub archive we have all of the events.
And we have them organized by day, month, year.
The query you were using?
ALYSON LA: Yeah, so if we go back to this gist,
you can just copy and paste this into the UI here on BigQuery.
And it'll know to pull up the CAB archive day table.
So we can run that query.
FELIPE HOFFA: I see you're running everything between,
looking between 2015.
ALYSON LA: So one year.
FELIPE HOFFA: One year of data.
By day, And this query goes over a lot of days.
ALYSON LA: Yeah.
And especially with the extract of the payload
to pull out the repo base language.
And also, we're doing an extract of the JSON payload
to make sure that it's only the action equals open.
So it's only counting the pull request once
by when they're opened.
FELIPE HOFFA: Nice.
And here we already found what were the most popular languages
last year.
ALYSON LA: Yeah, for this date range then,
we have the output of the language name
and the count of the total pull requests by language.
FELIPE HOFFA: So a reminder for everyone
that wants to play with this data, here
we looked at the full year of data,
but BigQuery, we give you a free terabyte of query to run
your queries every month.
Now if you want to make those queries, [INAUDIBLE]
last longer, you're still waiting the full year
when you're playing, go for a few days,
and then just go over the full year.
ALYSON LA: Exactly, so when doing exploratory analysis,
I usually limit it to maybe seven days or two weeks.
And then when I'm ready to look over the full date range
that I want to analyze, then I'll
open it up to the full date range.
FELIPE HOFFA: That's pretty cool.
We analyze about, how many gigabytes of data?
ALYSON LA: 600.
FELIPE HOFFA: 600 gigabytes of data in 20 seconds,
parsing JSON in the meantime.
That's pretty cool.
ALYSON LA: Well, yeah.
That's a great tool.
FELIPE HOFFA: Then, you were able to take
this laptop, these queries and visualize them.
ALYSON LA: Yeah.
That was a visual designer that all of the pretty
visualizations in Octoverse.
But I've done visualizations separately
in other side projects using the GitHub archive public
dataset in Tableau.
And then also, more recently, I'm using Looker.
FELIPE HOFFA: Nice.
Can we take a look on how you use Tableau
and then we can jump to what we're doing with Looker.
ALYSON LA: The project that I did
using Tableau was called Open Source Monthly.
And internally we wanted to just take
a look at what was going on in our organization
and the repository is within the GitHub organization.
The public repository, that is.
So I was able to connect Tableau directly with BigQuery
and pull in the events data and start
to do some analysis looking at what
are the most active repositories within the whole org, who
are the most active contributors, contributing
to the org.
And what is the most common event type.
Whether that's an issue being created or a star or a fork.
And just kind of get the overall view
of what's going on within the organization in open source.
FELIPE HOFFA: You're publishing this with Tableau public now.
ALYSON LA: Yeah, that's right.
So this is all public.
So taking a look at the GitHub organization,
so this is connected on a monthly basis.
So November 2015 is when I did this project.
It's showing that the most common event
type is star for our org.
HUBOT, our robot, is the most active contributor.
And then we can scroll it down. [INTERPOSING VOICES]
And then this is a, they call them tree maps or tree graphs.
The rectangle equals the size of the amount of events going on
in that repository.
Our gitignore repository is super active as far as events.
And then Git LFS and then our git HUBOT for this month.
FELIPE HOFFA: How do with these reports with Tableau?
ALYSON LA: So I create it using the Tableau workbook,
which I have on my desktop.
And I can do a direct connect to Google BigQuery.
I just have to click Google BigQuery
and enter my credentials.
FELIPE HOFFA: Let's keep your password safe.
ALYSON LA: And sign in, and then allow access.
And then so once I've connected my Tableau workbook
with BigQuery, I will be able to see that it's
connected the server here.
And I can select a project here or the public
dataset we can at.
But to access the GitHub Archive I just type in GitHub Archive.
FELIPE HOFFA: The name of the project
that contains the dataset.
ALYSON LA: Yeah.
And then scroll down and look at the dataset that I want to use.
So I want to use the month.
And I can scroll down to get the most recent complete month,
which would be September here.
And I can drag that over and update.
And this is like doing, you know, select star limit 10,
where I can see the output of the table.
And just kind of overview what's in the table contents.
And then from here, I can go to the worksheet
and start dragging and dropping to do some exploratory analysis
using Tableau.
FELIPE HOFFA: Si Tableau understands all of the columns
you have here and--
ALYSON LA: Yeah.
It already knows the dates should be in a date format,
booleans are true, false, numbers, strings.
FELIPE HOFFA: So for example, if you
want to know the most frequent event.
ALYSON LA: Yeah.
So I could just select type and number of records
and do show me bar chart.
And just to get an overview, I can see, if I filter this down,
that push events are the most common events
for this month, followed by create events, watch
events and issue comments.
FELIPE HOFFA: And if you want to filter these events by any way.
ALYSON LA: Yeah, so you can slice and dice it
a couple of different ways.
You can drag the org log in here to Filter.
And from there it will allow you to choose,
say I can choose Google as the name
of the organization on GitHub.
FELIPE HOFFA: So those are projects
hosted under the Google organization.
GitHub organization.
ALYSON LA: That's correct.
FELIPE HOFFA: Now we can see how people are
interacting with this project.
ALYSON LA: Yeah, exactly.
So these are the different events triggered,
filtered specifically on the log-in for Google organization.
You can see it's a different shape here.
Before it was mostly push events and the most common event
type for the Google organization was a watch event,
which is considered starring.
FELIPE HOFFA: How many stars did we get?
ALYSON LA: 15,000.
FELIPE HOFFA: Nice.
And here you can keep drilling in.
You can look at the most popular projects,
what people are doing.
ALYSON LA: Yeah.
FELIPE HOFFA: But you are using not only Tableau here.
ALYSON LA: That's right.
So recently I got started using Looker,
which is also an analysis tool similar to Tableau.
But the nice thing about Looker is
it allows you to do a data connection to BigQuery.
But then build a model layer, where
it allows you to basically do pre-joins,
if you want to do it.
You can do the JSON extract directly in the model layer.
And we love it because it's all version-controlled
using can GitHub.
So taking a look, here is an example dashboard
using the GitHub web archive latest events.
So again, using a short date range.
This is filtered to just the last two days.
This is an example of the dashboard
that you can use to see the number
of active users, the number of active organizations,
repositories.
You know, the event types, developers by commit counts.
And again, most popular languages here by pull request
account.
And then you can filter it to any organization you want.
So say, we can look at Angular here, and see on the dashboard
that in this time period of two days
there were 530 active users.
Anyone that interacted with any repositories on Angular
organization, 65 active repositories.
And then you can see that the most popular languages
by pull request are a little bit different.
JavaScript, TypeScript, HTML and Java.
FELIPE HOFFA: And the interesting thing about Looker
is it adds a layer where Looker understands what the underlying
data is.
ALYSON LA: Yeah, I can show you that model layer.
FELIPE HOFFA: So it's a model that someone has to write?
ALYSON LA: That's right.
FELIPE HOFFA: But only one person at the organization
needs to understand.
ALYSON LA: I think helps for other people
to understand the model and how it's built.
But not everyone needs to know how to write the model layer.
FELIPE HOFFA: So this is GitHub's model.
ALYSON LA: Yeah, so this is the model
for all of the public events.
And Looker's really neat because it
has this ID where it tells you what
you have not committed yet.
So these green lines are things that I still need to push up.
FELIPE HOFFA: Using GitHub?
ALYSON LA: Using GitHub, yeah.
And I can just press commit changes
and it will push it up to the hosted Git
Repository on GitHub.
But for example, this pull request action here,
I can do this JSON extract here.
And then I can filter on the action.
So when I go to do the analysis, I can just
pick action equals opened or closed
and it allows me to pick out the pre-filter
and do the JSON extract here.
FELIPE HOFFA: So anyone that's working with Looker
doesn't need to understand that if some data is
[INAUDIBLE] JSON.
The model takes care of extracting that away.
ALYSON LA: Yeah
FELIPE HOFFA: That's really cool.
What a lot of people ask me is how
to translate these interesting metrics into actionable advice.
Like what kind of things can you do that translate into action?
ALYSON LA: Yeah, so something we're thinking about
internally is open source KPIs, or Key Performance Indicators
using the BigQuery datasets.
So things like open source monthly active users
or monthly active repositories, and tracking that over time.
FELIPE HOFFA: And we can look at people by region,
how they are interacting with GitHub.
ALYSON LA: Yeah, so another way to measure user engagement
is looking at monthly active users and daily active users,
and seeing the ratio by region to see where some users are
less engaged than others.
And just really by region how different regions
are using GitHub differently.
FELIPE HOFFA: So this is really, really awesome.
I love how anyone at home can start
writing their own queries, finding this status
of other public datasets.
Any advice for someone that wants to start on this path?
ALYSON LA: Yeah.
So my advice would be to check out the published queries.
You have some out floating around the Internet.
There are some in the gist that I linked in the Octoverse.
FELIPE HOFFA: We're putting all of those links here.
ALYSON LA: Yeah, check out the links below.
And then also check out the developer docs
where you can learn about the different event
types and the payload.
So you can understand exactly what's going on there.
FELIPE HOFFA: So we have everything documented.
We have sample queries.
We have sample analyses.
Now it's up to you to start working with this.
And as for you, please start trying out BigQuery.
Find open datasets.
Remember that you have a monthly terabyte of no cost
to run your own queries.
Check all of the resources we left on this video description.
And I'm out for today.
Thank you very much and stay curious.
Thank you, Alyson.
ALYSON LA: Thank you.
FELIPE HOFFA: Woo!