Checkpoints
Query a public dataset in BigQuery
/ 50
Rerun your Query
/ 50
Setting Up Cost Control with Quota
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).
- Time to complete the lab---remember, once you start, you cannot pause a lab.
How to start your lab and sign in to the Google Cloud Console
-
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
-
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. -
If necessary, copy the Username from the Lab Details panel and paste it into the Sign in dialog. Click Next.
-
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. -
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.
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.
- 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:
gcloud
is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.
-
(Optional) You can list the active account name with this command:
-
Click Authorize.
-
Your output should now look like this:
Output:
-
(Optional) You can list the project ID with this command:
Output:
Example output:
gcloud
, in Google Cloud, refer to the gcloud CLI overview guide.
Open the BigQuery console
- 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.
- 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.
-
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 -
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.
- Now run the query and see how quickly BigQuery processes that size of data.
Click Check my progress to verify the objective.
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.
-
In your Cloud Shell, run this command to view your current usage quotas with the BigQuery API:
The consumerQuotaLimits display your current query per day limits. There is a separate quota for usage per project and usage per user.
-
Run this command in Cloud Shell to update your per user quota to .25 TiB per day:
-
After the quota is updated, examine your consumerQuotaLimits again:
You should see the same limits from before but also a consumerOverride with the value used in the previous step:
Next, you will re-run your query with the updated quota.
Task 4. Rerun your query
-
In the Cloud Console, click BigQuery.
-
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?
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.
- To test that the quota has changed, disable the cached query results. In the Query results pane, click More > Query settings:
-
Uncheck Use cached results and click Save.
-
Run the query again so that it counts against your daily quota.
-
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.
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:
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.