SPEAKER: In this episode of "BigQuery Spotlight,"
we're going to dive deep into how to ask a question
or run a query in BigQuery.
Running queries is one of the most fundamental parts
of discovering insights from your data.
Today's question-- what is the best jersey
number you should choose in order to improve
your basketball game?
Stay tuned and find out.
[MUSIC PLAYING]
It's no surprise that big data has
made its way into the world of professional sports.
Teams at every level are starting
to gather, process, and analyze data
so that they can get the most out of their players
and find a competitive edge in the game.
To see how this would work, let's check out
the NCAA basketball public data set available in BigQuery.
The data set contains play-by-play data
for several years' worth of games.
You can get information like fouls, free throws, scores,
player numbers, timeouts, basically as much data
as the pros have.
So let's see if we can use this data for today's question
and determine which jersey number is the best
three-point shooter.
If you want to follow along, click
on the link in the description below
to set up your own BigQuery sandbox
where you can analyze data without needing a credit card.
Once you're in the BigQuery sandbox,
head over to the Google Cloud Marketplace
by clicking on Add Data and then Explore Data Sets.
Search for the NCAA basketball and click
to launch the data set in the BigQuery UI.
The NCAA basketball data set has a number of different tables.
Clicking on the play-by-play table,
you can first view the table details,
which indicates that there are over four
million rows of events.
That's a lot of basketball data.
Check the table schema, and you'll
see each column or field of data that's available for each
of those events.
But first, let's look at what those events are.
You can start to explore the data
by clicking on the Preview tab to view
a few rows of sample data.
But to get a more comprehensive list of all the event
types, the best way to find out is to run a query.
Click Query Table and a blank query
will appear in the editor with a table reference pre-populated.
Before we write our query, it's important to know
that the language used to communicate with BigQuery
is SQL, or Structured Query Language.
SQL is also the standard language
for communicating with relational databases.
BigQuery is ANSI SQL compliant, so experienced practitioners
will hit the ground running.
Don't worry if you're not SQL savvy.
There are plenty of resources for learning or brushing
up on your SQL skills just a quick web search away.
So now, let's copy in a SQL query
to explore the event types.
The key command in SQL for retrieving data
is SELECT, which we'll use to pull
in a list of all of the different event types
and number of occurrences from the play-by-play table.
Notice that the table reference is in the format of the GCP
project name, then data set name,
then table name, separated by dots
and enclosed with back ticks, not quotes.
We'll select all of the event types and group the results
so that we can get the count of each event.
Once the SQL query is written, click on the green check mark
on the right-hand side of the window
to open the query validator.
A green check mark means the query is valid
and will show the estimated amount of data
that the query will process when you run it.
If the query is invalid, a red exclamation point icon
is displayed.
You can click on it and get some guidance
on how to correct the problem.
Click Run and BigQuery gets to work.
After the query execution is complete,
the query service displays the results
as a table in the web UI.
You can see that the Event Type column has
both three point made and three point miss events.
Now we can write a new query which
selects the jersey number for each play-by-play event
and then uses the Event Types column
to determine the three point field goal
accuracy for each jersey number.
Click Run and then in less than a second we have the answer.
Looks like number 45 sinks the highest percentage
of their three-pointer attempts.
Of course, buying a new jersey doesn't
make you a better basketball player, right?
An important note-- we can see that the table we queried
has three gigabytes of data.
But BigQuery only read the columns needed and therefore
just processed 78 megabytes of data.
Well, basketball enthusiasts out there,
try out the NCAA data set for yourself
and ask questions to discover what you might
be able to do to up your game.
If you want to learn more about BigQuery,
check out the rest of our playlist.
And don't forget to subscribe to our channel
on YouTube for other great content.
Look out for the next episode of "BigQuery Spotlight."
And remember, stay curious.
[MUSIC PLAYING]