GSP651

Overview

In this lab, you explore controlling your BigQuery costs by modifying quota.

What you'll do

• Query a public dataset and explore associated costs.

• Modify quota.

• Try to rerun the query after quota has been modified.

BigQuery pricing

BigQuery offers scalable, flexible pricing options to meet your technical needs and your budget.

With BigQuery, you can incur storage and query costs. In this lab, you explore query costs. For more information, see BigQuery pricing.

There are two pricing models for query costs in BigQuery:

• On-demand: On-demand pricing is based on the amount of data processed by each query you run. This is the most flexible option.

• Flat-rate: Flat-rate customers purchase dedicated resources for query processing and are not charged for individual queries. This option is predictable and is best for customers with fixed budgets.

Setup

In this section, you access the Google Cloud and BigQuery consoles.

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 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 Console. 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 from the Lab Details panel and paste it into the Sign in dialog. Click Next.

4. Copy the Password from the Lab Details panel and paste it into the Welcome dialog. Click Next.

   Important: You must use the credentials from the left panel. Do not use your Google Cloud Skills Boost credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

5. 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 Cloud Console opens in this tab.

Note: You can view the menu with a list of Google Cloud Products and Services by clicking the Navigation menu at the top-left.

Activate Cloud Shell

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. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click Activate Cloud Shell at the top of the Google Cloud console.

When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. The output contains a line that declares the PROJECT_ID for this session:

Your Cloud Platform project in this session is set to YOUR_PROJECT_ID

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

2. (Optional) You can list the active account name with this command:

gcloud auth list

3. Click Authorize.

4. Your output should now look like this:

Output:

ACTIVE: * ACCOUNT: student-01-xxxxxxxxxxxx@qwiklabs.net To set the active account, run: $ gcloud config set account `ACCOUNT`

5. (Optional) You can list the project ID with this command:

gcloud config list project

Output:

[core] project = <project_ID>

Example output:

[core] project = qwiklabs-gcp-44776a13dea667a6 Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.

Open the 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 the release notes.

2. Click Done.

The BigQuery console opens.

Task 1. Query a public dataset in BigQuery

In this lab, you query the bigquery-public-data:wise_all_sky_data_release public dataset. Learn more about this dataset from the blog post Querying the Stars with BigQuery GIS.

1. In the Query editor paste the following query:

   SELECT w1mpro_ep, mjd, load_id, frame_id FROM `bigquery-public-data.wise_all_sky_data_release.mep_wise` ORDER BY mjd ASC LIMIT 500

2. Do not run the query. Instead, please answer the following question:

Processing large amounts of data without proper cost controls, even with simple queries like the above, can lead to unanticipated charges on your bill. To manage this, examine how BigQuery pricing works and how you can setup custom quotas for your teams.

3. Now run the query and see how quickly BigQuery processes that size of data.

Click Check my progress to verify the objective.

Query a public dataset in BigQuery

Task 2. Explore query cost

The first 1 TB of query data processed per month is free.

• Learn more about cost from the BigQuery pricing guide.

• For more information on calculating and estimating costs in Google Cloud, use the Google Cloud Pricing Calculator.

Task 3. Update BigQuery quota

In this task, you update the BigQuery API quota to restrict the data processed in queries in your project.

1. In your Cloud Shell, run this command to view your current usage quotas with the BigQuery API:

gcloud alpha services quota list --service=bigquery.googleapis.com --consumer=projects/${DEVSHELL_PROJECT_ID} --filter="usage"

The consumerQuotaLimits display your current query per day limits. There is a separate quota for usage per project and usage per user.

2. Run this command in Cloud Shell to update your per user quota to .25 TiB per day:

gcloud alpha services quota update --consumer=projects/${DEVSHELL_PROJECT_ID} --service bigquery.googleapis.com --metric bigquery.googleapis.com/quota/query/usage --value 262144 --unit 1/d/{project}/{user} --force

3. After the quota is updated, examine your consumerQuotaLimits again:

gcloud alpha services quota list --service=bigquery.googleapis.com --consumer=projects/${DEVSHELL_PROJECT_ID} --filter="usage"

You should see the same limits from before but also a consumerOverride with the value used in the previous step:

--- consumerQuotaLimits: - metric: bigquery.googleapis.com/quota/query/usage quotaBuckets: - defaultLimit: '9223372036854775807' effectiveLimit: '9223372036854775807' unit: 1/d/{project} - metric: bigquery.googleapis.com/quota/query/usage quotaBuckets: - consumerOverride: name: projects/33699896259/services/bigquery.googleapis.com/consumerQuotaMetrics/bigquery.googleapis.com%2Fquota%2Fquery%2Fusage/limits/%2Fd%2Fproject%2Fuser/consumerOverrides/Cg1RdW90YU92ZXJyaWRl overrideValue: '262144' defaultLimit: '9223372036854775807' effectiveLimit: '262144' unit: 1/d/{project}/{user} displayName: Query usage metric: bigquery.googleapis.com/quota/query/usage unit: MiBy

Next, you will re-run your query with the updated quota.

Task 4. Rerun your query

1. In the Cloud Console, click BigQuery.

2. The query you previously ran should still be in the query editor, but if it isn't, paste the following query in the Query editor and click Run:

   SELECT w1mpro_ep, mjd, load_id, frame_id FROM `bigquery-public-data.wise_all_sky_data_release.mep_wise` ORDER BY mjd ASC LIMIT 500

   Note the validator still mentions This query will process 1.36 TB when run. However, the query has run successfully and hasn't processed any data. Why do you think that is?

Note: If your query is already blocked by your custom quota, don't worry. It's likely that you set the custom quota and re-run the query before the first query had time to cache the results.

Queries that use cached query results are at no additional charge and do not count against your quota. For more information on using cached query results, see Using cached query results.

In order for us to test the newly set quota, you must to disable query cache to process data using the previous query.

3. To test that the quota has changed, disable the cached query results. In the Query results pane, click More > Query settings:

4. Uncheck Use cached results and click Save.

5. Run the query again so that it counts against your daily quota.

6. Once the query has run successfully and processed the 1.36 TB, run the query once more.

   What happened? Were you able to run the query? You should have received an error like the following:

   Custom quota exceeded: Your usage exceeded the custom quota for QueryUsagePerUserPerDay, which is set by your administrator. For more information, see https://cloud.google.com/bigquery/cost-controls

Click Check my progress to verify the objective.

Rerun your Query

Task 5. Explore BigQuery best practices

Quotas can be used for cost controls but it's up to your business to determine which quotas make sense for your team. This is one example of how to set quotas to protect from unexpected costs. One way to reduce the amount of data queried is to optimize your queries.

Learn more about optimizing BigQuery queries from the Control costs in BigQuery guide.

Congratulations!

In this lab you completed the following tasks:

• Queried a public dataset and explore associated costs.

• Modified BigQuery API quota.

• Tried to rerun the query after quota had been modified.

Finish your quest

This self-paced lab is part of the Qwiklabs Optimizing 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 or any quest that contains this lab and get immediate completion credit. See the Google Cloud Skills Boost catalog to see all available quests.

Take your next lab

Continue your Quest with Cloud Monitoring: Qwik Start or check out these suggestions:

• Cloud Functions: Qwik Start - Command Line
• Clean Up Unused IP Addresses

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 July 20, 2022

Lab Last Tested May 13, 2022

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