GSP621

Overview

BigQuery is Google's serverless, highly scalable enterprise data warehouse that is designed to make data analysts more productive with unmatched price-performance.

In this lab, you use BigQuery to examine sample Cloud Billing records. After you gain some familiarity with the tool and the dataset, you run SQL queries to gain insights from your billing data.

What you'll learn

In this lab, you learn how to do the following:

• Sign in to BigQuery from the Google Cloud console.
• Examine the sample dataset and table.
• Compose and run simple queries on the billing data.
• Run queries on the data and answer pertinent billing questions.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details panel.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details panel.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left.

Task 1. Locate your dataset and table in BigQuery

In this task you locate your billing dataset in the Cloud console. You use BigQuery to look at billing data associated with your project.

1. In the Google Cloud console, in the Navigation menu (), click BigQuery.

2. In the Welcome dialog, click Done.

3. In the Explorer pane, for your project ID, click Expand node ().

   The billing_dataset is displayed.

4. Expand the billing_dataset.

   The enterprise_billing dataset is displayed.

Task 2. Examine the billing data

In this task you examine the billing data in BigQuery.

1. In the Google Cloud console, in the Explorer pane, click the enterprise_billing table.

   This displays three tabs that provide information on the enterprise_billing table. The Schema tab is open by default. The other tabs are Details and Preview.

BigQuery automatically created this schema based on the sample Cloud Billing records. Notice that there are strings, integers, timestamps, and floating values.

2. Click the Details tab.

   A table with 415,602 rows is displayed.

3. Click the Preview tab.

4. Look at the header row of the table to see what information the data provides, and then answer the following questions:

5. Find the Results per page field. You can set the number of rows displayed per page from 10 to 200.

Task 3. Analyze data using SQL queries

In this task you run SQL queries in BigQuery to analyze your data to obtain information such as, which services were used and what were their associated costs?; which projects incurred the most cost?; and are the costs as expected?

In BigQuery, you use SQL queries to pull and process data from a table to answer your questions. To reference a table in a query, you specify the dataset and table; the project is optional.

Note: If you do not specify the project, BigQuery defaults to the current project.

1. In the Google Cloud console, in the Explorer pane, click the enterprise_billing table.

2. Click + (Create SQL query).

3. In the Query editor, clear the current query in preparation for the next step.

   You enter and run your SQL queries in the Query editor.

Query 1: Analyze your data based on costs

In this step you perform some analysis based on costs. You construct a simple query based on the Cost field.

1. In the Query editor, type the following, and then click Run:

SELECT * FROM `billing_dataset.enterprise_billing` WHERE Cost > 0

This script queries data in the enterprise_billing table for records with a Cost of greater than zero.

Expected output:

Although this shows you how to run a query in BigQuery, the resulting table is not clear or helpful. For a more useful query, run the following script to see how much was spent for services.

2. In the Query editor, clear the current query.

3. In the Query editor, type the following, and then click Run:

SELECT project.name as Project_Name, service.description as Service, location.country as Country, cost as Cost FROM `billing_dataset.enterprise_billing`;

Notice that you've reduced the number of columns by selecting what information (project.name, service.description, location.country, and cost) you want to see.

Expected output:

Complete the next steps to check your progress to verify an objective and then answer a question using the SQL query output.

4. In the Explorer pane, click enterprise_billing, and then click Schema.

Answer this question:

Click Check my progress to verify that you completed the objective.

Construct a simple query based on the Cost field.

Query 2: Examine key information

In the previous step, you queried for specific information, also known as key information, to reduce the amount of data in the table. You used parameters to identify key information. In this section, you'll list key information.

For this example, the number of unique services that are available is the key information you want. Run a query that combines the service description and the SKU description and then lists that as line items.

1. Click + (Create SQL query).

2. In the Query editor, clear the current query in preparation for the next step.

3. In the Query editor, type the following, and then click Run:

SELECT CONCAT(service.description, ' : ',sku.description) as Line_Item FROM `billing_dataset.enterprise_billing` GROUP BY 1

Note that GROUP BY 1 means to group the list by the first column.

Expected output:

Answer the following questions:

Click Check my progress to verify that you completed the objective.

List unique services available from the sample bill.

Query 3: Analyze service usage

In this step you look at service usage to find out the number of times a resource used a service/SKU.

1. In the Query editor, clear the current query in preparation for the next step.

2. In the Query editor, type the following, and then click Run:

SELECT CONCAT(service.description, ' : ',sku.description) as Line_Item, Count(*) as NUM FROM `billing_dataset.enterprise_billing` GROUP BY CONCAT(service.description, ' : ',sku.description)

Expected output:

Answer the following questions:

Click Check my progress to verify that you completed the objective.

Get the count of logs generated for each service from the sample bill.

Query 4: Determine which project has the most records

In this query you find the Google Cloud project with the most records.

1. In the Query editor, clear the current query in preparation for the next step.

2. In the Query editor, type the following, and then click Run:

SELECT project.id, count(*) as count from `billing_dataset.enterprise_billing` GROUP BY project.id

This query counts how many times a project.id appears in a record and groups the results by project.id.

Expected output:

Answer the following question:

Click Check my progress to verify that you completed the objective.

Find the Google Cloud project with the most records in the billing data.

Query 5. Find the cost per project

In this step you find the cost breakdown for each project:

1. In the Query editor, clear the current query in preparation for the next step.

2. In the Query editor, type the following, and then click Run:

SELECT ROUND(SUM(cost),2) as Cost, project.name from `billing_dataset.enterprise_billing` GROUP BY project.name

This query adds the cost per project.name and then returns the results grouped by project.name.

Expected output:

Answer the following question:

Click Check my progress to verify that you completed the objective.

Find the cost breakdown per project.

Congratulations!

In this lab, you explored a sample of Cloud Billing records in BigQuery. After examining the sample dataset and table, you composed and ran queries on the billing data. You then used those queries to answer pertinent billing questions. You are now ready to take more labs in this series.

Finish your quest

This self-paced lab is part of the Understanding Your Google Cloud Costs quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in this quest and get immediate completion credit. Refer to the Google Cloud Skills Boost catalog for all available quests.

Take your next lab

Take another lab in the Understanding Your Google Cloud Costs Quest:

• Visualizing Billing Data with Google Data Studio
• Examining BigQuery Billing Data in Google Sheets

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated February 7, 2024

Lab Last Tested February 7, 2024

Copyright 2024 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.