Overview

Storing and querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. Google BigQuery is an enterprise data warehouse that solves this problem by enabling super-fast SQL queries using the processing power of Google's infrastructure. Simply move your data into BigQuery and let us handle the hard work. You can control access to both the project and your data based on your business needs, such as giving others the ability to view or query your data.

You can access BigQuery in the Console, the classic Web UI or a command-line tool, or by making calls to the BigQuery REST API using a variety of client libraries such as Java, .NET, or Python. There are also a variety of third-party tools that you can use to interact with BigQuery, such as visualizing the data or loading the data.

This hands-on lab shows you how to use the Web UI to query public tables and load sample data into BigQuery.

Setup and Requirements

Qwiklabs setup

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Activate Google Cloud Shell

Google Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud.

Google Cloud Shell provides command-line access to your Google Cloud resources.

1. In Cloud console, on the top right toolbar, click the Open Cloud Shell button.

   

2. Click Continue.

It takes a few moments to provision and connect to the environment. When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. For example:

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

• You can list the active account name with this command:

gcloud auth list

Output:

Credentialed accounts: - @.com (active)

Example output:

Credentialed accounts: - google1623327_student@qwiklabs.net

• You can list the project ID with this command:

gcloud config list project

Output:

[core] project =

Example output:

[core] project = qwiklabs-gcp-44776a13dea667a6 Note: Full documentation of gcloud is available in the gcloud CLI overview guide .

Open BigQuery

The BigQuery console provides an interface to query tables, including public datasets offered by BigQuery. The query you will run accesses a table from a public dataset that BigQuery provides. It uses standard query language to search the dataset, and limits the results returned to 10.

Open BigQuery Console

1. In the Google Cloud Console, select Navigation menu > BigQuery.

The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and lists UI updates.

2. Click Done.

Query a public dataset

1. Copy and paste the following query into the BigQuery Query editor,:

#standardSQL SELECT weight_pounds, state, year, gestation_weeks FROM `bigquery-public-data.samples.natality` ORDER BY weight_pounds DESC LIMIT 10;

This data sample holds information about US natality (birth rates). Click the circular check icon to activate the query validator.

A green or red check displays above the query editor depending on whether the query is valid or invalid. If the query is valid, the validator also describes the amount of data to be processed after you run the query. This information is helpful for determining the cost to run a query.

2. Click the Run button.

Your query results should resemble the following:

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully query against publlic dataset, you'll see an assessment score.

Query a public dataset (dataset: samples, table: natality) You can browse the schema of other public datasets in BigQuery under the Explorer pane by clicking + ADD DATA > Explore pubic datasets, then search for "bigquery public data" in the Search field.

Load custom data into a table

Now you'll create a custom table, load data into it, and run a query against it. You'll create a dataset to hold your table, add data to your project then make the data table you'll query against.

Create a dataset

Datasets help you control access to tables and views in a project. This lab will only use one table, but you still need a dataset to hold the table.

1. In the left pane of the BigQuery console, click on the three dots next to your project ID.

2. Click Create dataset.

3. Enter babynames in the Dataset ID.

4. Leave all of the other default settings and click Create dataset. Now you have a dataset.

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully created BigQuery dataset, you'll see an assessment score.

Create a new dataset

Add custom data

The custom data file you'll use contains approximately 7 MB of data about popular baby names, provided by the US Social Security Administration. You'll add the zip file to your project then create a storage bucket for the specific file that you'll need to query against.

1. In Cloud Shell, run the following commands to add the data files to your project:

gsutil cp gs://spls/gsp072/baby-names.zip . unzip baby-names.zip

Create a Cloud Storage bucket

Now that you have the files downloaded, you create a Cloud Storage bucket to hold specific data.

1. In the Cloud Console, select Navigation menu > Cloud Storage > Buckets, and then click Create.

2. Give your bucket a universally unique name, then click Create.

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully created a storage bucket, you'll see an assessment score.

Create a bucket

1. In Cloud Shell, run the following to move file yob2014.txt into your bucket. Replace <your_bucket> with the name of the bucket you just created:

gsutil cp yob2014.txt gs://<your_bucket>

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully uploaded object in cloud storage bucket, you'll see an assessment score.

Copy file in your bucket

Now you can tell BigQuery where to find the data to query against.

Load the data into a new table

Next you'll load the data file from your storage bucket into a new table in BigQuery. You'll create the table inside the dataset you made earlier.

Return to the BigQuery console.

1. Click on the name of your project, then the babynames dataset, then click the three dots next to your dataset and select Create table.

2. Under Source, for Create table from select Google Cloud Storage, enter the path to the data file your Google Cloud Storage bucket. Change the file format to CSV.

3. In the Destination Table section, set destination table name to names_2014

4. In the Schema section, click Edit as Text and add the following:

name:string,gender:string,count:integer

Your form should look like this:

5. Click the Create Table button.

Wait for BigQuery to create the table and load the data. While BigQuery loads the data, a (loading) string displays after your table name. You'll see the names_2014 table under the babynames dataset in the BigQuery console.

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully load data in dataset table, you'll see an assessment score.

Load data into your table

Test your Understanding

Below are multiple choice-questions to reinforce your understanding of this lab's concepts. Answer them to the best of your abilities.

Preview the table

Next you'll preview the first few rows of the data.

1. Click on the names_2014 table in the left-hand menu, then click Preview.

Your table is ready to be queried against.

Query a custom dataset

Running a query against custom data is identical to querying a public dataset that you did earlier, except that now you're querying your own table instead of a public table.

1. In BigQuery, click the + SQL query button in the top right corner to clear out your previous query.

2. Add the following query into the Query editor.

Note: If your table name is something other than babynames, update the code with your table name. #standardSQL SELECT name, count FROM `babynames.names_2014` WHERE gender = 'M' ORDER BY count DESC LIMIT 5;

3. Click the Run button. The query displays the top 5 boys names for the year of data (2014) you loaded into the table.

Test Completed Task

Click Check my progress to verify your performed task. If you have successfully query against custom dataset, you'll see an assessment score.

Query a custom dataset

End your lab

When you have completed your lab, click End Lab. Google Cloud Skills Boost removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.