SPEAKER: For this episode of "BigQuery Spotlight,"
we're putting the you in BigQuery.
Meaning it's time to start asking
questions of your own data.
For today's episode, you'll learn how to load and analyze
your own data in BigQuery.
And by the end, you'll have the data
you need to answer the age-old question-- which
is better, cats or dogs?
For the results of that match-up,
you'll have to stick around.
[MUSIC PLAYING]
If you want to analyze data that's
not already available as part of the public data
sets program or hosted publicly by another BigQuery user,
you'll need to load your own data into BigQuery.
How you load the data depends on your analytics needs
and your data pipeline.
If your data slowly changes or needs
to be loaded in a one-time analysis,
you may be fine with loading the data into BigQuery in batch.
But if you need to ingest and analyze
data close to real time, in this case,
you may need to stream your data into BigQuery.
Luckily, BigQuery has options for loading data
that cover both of these scenarios and those in between.
Let's show you how to get started
by loading data in batch.
So what data do you need in BigQuery
in order to officially crown the winner of the cats versus dogs
battle?
We're going to determine the champion by analyzing college
basketball tournament games in order
to see who wins when dog mascots and cat
mascots go head to head.
To run this analysis, we'll need two things.
First, we'll need to have the tournament results data, which
is already available in BigQuery as part of the public NCAA
basketball public data set.
We introduced this public data set in our episode
on how to query in BigQuery, which is linked
below if you're interested.
Second, we'll need a list that has teams with dog and cat
mascots, which we have available as a local CSE file.
It's also linked below if you're following along.
To join these two data sets together for analysis,
we'll need to load the mascot CSE file into BigQuery
and create a table.
No time to waste.
Let's get started.
Let's first create a home for the mascot table in BigQuery.
BigQuery organizes data into containers called data sets.
These data sets function like top-level folders
that organize and control access to the underlying tables.
Let's head over to the web UI.
If you want to follow along, pause the video,
click on the link in the description
below to set up your own BigQuery sandbox,
where you can load and analyze data
without needing a credit card.
All right, on to the UI.
To create a new data set, select the project name
on the left-hand nav and click the Create Data Set button.
We'll give the data set a name and then decide on a location.
In this case, we need to co-locate
our data set with the NCAA public data set, which is
located in the US multi region.
We'll need to reference both tables in one query
by performing a join.
And this can only be done with tables
residing in the same geographical location.
You can always view the data set location
by clicking on the Details tab in the web UI.
Click Create Data Set and the new data set
will appear in the left-hand nav.
Now it's time to create a new table within the data set
by loading the mascot CSE file.
Highlight the data set and click Create Table.
This dialog allows us to directly upload
files from our local machine up to 10 megabytes in size
and containing less than 16,000 rows.
If you have something larger, don't worry.
You can just upload it to Cloud storage and then
select it from there.
Since our CSE is pretty small, we
can skip that and use the browse functionality
to select the file from our local machine.
Give the table a name and then define the schema.
The schema is a list of each column and its data type.
We can define the schema manually
by clicking on Add Field or check the box
to have BigQuery auto-detect it.
Click Create Table and a load job will be created.
Once the data has finished loading,
you can navigate to view the table details,
review the schema, and preview the data right in the console.
Our mascots table is ready to query.
We'll paste in a query that uses the mascot
table to analyze cat versus dog tournament game match-ups.
In our query, we've started with our table of tournament games,
and then used a series of SQL joins with the mascots table
to look up to animal classification
for the winning and losing teams.
We then sum up the number of wins for cats
and the number of wins for dogs in the specific cat versus dog
match-up games.
Time to run our query.
And there we have it-- with 43 of the wins, dog mascots reign
supreme in the realm of college basketball tournament games.
The analysis doesn't have to stop there.
You can see how dogs and cats perform on other metrics
or join this data with other data sets to test new ideas.
For example, do the types of mascots
different between warmer and colder climates?
Comment below with your ideas of exploring the mascots table.
We hope you look out for our next episode
of "BigQuery Spotlight."
And remember, stay curious.
[MUSIC PLAYING]