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 are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials 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 (recommended) or private browser window to run this lab. This prevents 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: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that 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 dialog opens for you to select your payment method. On the left is the Lab Details pane 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 pane.

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 pane.

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 access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

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.

2. Click through the following windows:

   • Continue through the Cloud Shell information window.
   • Authorize Cloud Shell to use your credentials to make Google Cloud API calls.

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 {{{project_0.project_id | "PROJECT_ID"}}}

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

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

gcloud auth list

4. Click Authorize.

Output:

ACTIVE: * ACCOUNT: {{{user_0.username | "ACCOUNT"}}} 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_0.project_id | "PROJECT_ID"}}} 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.

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 November 05, 2024

Lab Last Tested November 05, 2024

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