[MUSIC PLAYING]
SPEAKER: Welcome to the quickstart tutorial
for BigQuery, Google Cloud's large-scale data warehouse
for storage and analysis.
In this video, we're going to walk
through how to analyze data stored in BigQuery using the BQ
command-line tool.
To start, visit console.cloud.google.com
to open the Google Cloud console.
This web portal allows you to manage and explore
your Cloud resources directly from a browser.
Create a new project by clicking the Project Viewer button
at the top of your screen.
Once it's ready, you'll get a notification saying so.
Click the Notifications icon, and then
click Select Project to open your new project.
Next, activate Cloud Shell by clicking its icon
to the right of the search bar.
Cloud Shell is an online development environment
that comes preloaded with helpful tools
for interacting with your Cloud resources.
Once it launches, click the Maximize button
to increase the size of the Cloud Shell terminal.
Now you'll use the BQ command-line tool
to explore one of the sample tables
provided in BigQuery, the Shakespeare table, which
contains an entry for every word in every Shakespeare play.
You can use the bq show command to display information
about the table.
Type bq show plus the table reference and the command
prompt.
If prompted, make sure to click Authorize to authorize
Cloud Shell to make API calls.
You will then see information about the table returned,
including a schema and size.
You can learn more about how to use the BQ command-line tool
by typing in bq help.
You can also view information about a specific command.
Here, we can ask for information about the bq query command.
Now let's run an SQL query over the Shakespeare table
using the bq query command.
This query, which is available following
the link in the description below,
calculates how many times the substring raisin
appears in Shakespeare's works.
The output shows that while raisins appears once,
the raisin substring also appears in words
like praising and raising.
We can also query to see how many times the substring
huzzah appears.
Since the substring does not appear in Shakespeare's works,
no results are returned.
Now let's create a new table and place it in a new data set.
Our sample data will come from the US Social Security
Administration, and contains about seven megabytes of data
about popular baby names.
First, visit the link in the description
below and download the baby names
zip file to your local device.
Open the file YOB2010.txt to see what it looks like.
It contains comma-separated values for three columns--
name, sex, and number of children with that name.
Next, upload the YOB2010.txt file from your local device
to Cloud Shell.
Using the three-dotted More menu,
you can confirm that the file was successfully
uploaded to the Cloud Shell environment by running ls.
Run bq ls to check whether your project has existing data sets.
Now we can run the bq mk command to create a new data set called
baby names for our own project.
If we run bq ls again, we can see that our new data set
appears as part of our project.
In the baby names data set that you created, use the bq load
command to load your source file,
YOB2010.txt, into a new table called Names 2010.
This command creates a table and loads data in a single step.
You can provide the data set ID, table ID, source, and schema.
Once the upload is complete, confirm that the table now
appears in the data set by running bq ls babynames.
You can also view the table schema
by running bq show, babynames.names2010.
Now that the table is loaded, we can
run some queries on the data, again using the bq query
command.
For example, this query returns the most popular girls' names.
And this one returns some unusual boys' names.
Now that you've successfully run your queries,
make sure to complete a few quick cleanup steps.
To avoid incurring storage charges to your Google Cloud
account for the resources used in this quickstart,
run this bq rm command and confirm the Delete command
by typing in y.
To finish cleanup, type rm YOB2010.txt
to remove the file that you uploaded to your Cloud Shell
environment.
And that's it.
You're now equipped to run queries and load data
using the BQ command-line tool.
Make sure to check out the rest of our BigQuery documentation
at cloud.google.com/BigQuery/Docs
to learn more about how to use BigQuery to gain insights about
your data.
[MUSIC PLAYING]