In this lab you will complete 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.
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: 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.
- (Optional) You can list the active account name with this command:
gcloud auth list
- Click Authorize.
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 links 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:
Use the query validator to determine how many bytes of data this will process when you run.
- Now run the query and see how quickly BigQuery processes that size of data.
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:
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.
- 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
- 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
- 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?
Running the same query again may not process any data because of the automatic query, Caching feature in BigQuery.ShufflingJoiningcheck
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.
- 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
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.