Setting up custom cost controls for BigQuery

1. Introduction

BigQuery can process enormous amounts of data. Additional controls can help protect you from unexpected query costs, whether you are working solo or in an environment with multiple BigQuery projects and users.

You can manage query costs for on-demand usage of BigQuery by setting a custom daily quota on the amount of query data processed. This quota resets at midnight Pacific Time and acts as an aggregate hard cap, preventing users from running queries that exceed the specified limit.

In this tutorial, you will learn how to set your own custom quotas for BigQuery.

Prerequisites

• A basic understanding of the Google Cloud Console
• Experience with BigQuery is a plus

What you'll learn

• How to navigate to the quotas menu
• How to set up a project-level custom quota
• How to set up a user-level custom quota

What you'll need

• A Google Cloud Account and Google Cloud Project, with billing enabled
• A web browser such as Chrome

2. Before you begin

Required permissions

To set or modify custom quotas, you will need one of the following Cloud IAM roles on the project:

• The Owner or Editor basic role
• The Quota Administrator Service Management role
• The Service Usage Admin role

Note that the required permissions for setting custom quota are not included in the BigQuery Admin and BigQuery User roles. If you do not have the required permissions, you will have to reach out to a quota administrator for your organization. For more information about Google Cloud permissions, see Understanding roles in Cloud IAM.

If you are unsure if you have the required permissions, you may still continue to the next step which will help test if you have access to manage quota in your project.

3. Navigate to the Quotas menu

In this task, you will navigate to the quotas menu.

1. In the Google Cloud Console, click on the Navigation menu ( ).

2. Then select IAM & Admin > Quotas & System Limits.

This page contains all quotas for products and services across the platform. If you receive an access error, return to the previous page and review the permissions requirements.

Next you'll narrow the view to BigQuery-related quotas.

3. Using the Filter tool drop-down, choose the Service heading.

4. Then, select BigQuery API. The resulting list includes only quotas related to the BigQuery API service.

4. Set your project-level quota

In this task, you will set a custom quota at the project-level. Project-level custom quotas limit the aggregate usage of all users in that project per day.

Select your quota

1. Check the box for the line that specifies "Query usage per day" under the Name field in the table. You can use the filter box to help search for this name.

2. Click Edit. A pop-up dialog appears in the console.
3. Uncheck the Unlimited box if it is currently selected.
4. Enter 1 into the New value field to indicate a quota limit of 1 TiB (tebibyte).

You can also use the Google Cloud Pricing Calculator to estimate on-demand query costs for a specific number of tebibytes and enter your own custom quota limit.

5. After you've adjusted the quota, you can set it by clicking Done and then Submit Request.

You will receive confirmation in the dialog box when the quota has been updated.

Once the quota is set, if the project-level custom quota is exceeded, BigQuery will return an error.

Note: If the new value you set for custom quota exceeds an existing default value, the edit quota pop-up dialog will ask for a request description (see below image). As noted in the dialog, Query Usage Per Day quota increase requests should be automatically approved.

5. Set your user-level quota

Now that you've set quota at the project-level, it's time to set quota at the user-level. User-level custom quotas are separately applied to each user or service account within a project.

In practice, you may choose to use either of these quota options, or both in tandem. When project-level and user-level quotas are used in tandem, usage counts against both quotas and adheres to the stricter of the two limits. An example scenario can be found at the documentation linked at the end of this tutorial.

Select your quota

1. Check the box for the line that specifies "Query usage per day per user" under the Name field in the table. You can use the filter box to help search for this name.

2. Click Edit. A pop-up dialog appears in the console.
3. Uncheck the Unlimited box if it is currently selected.
4. Enter 0.5, or your own desired per-user daily query limit, into the New value field to indicate a quota limit of 0.5 TiB (tebibyte).

5. After you've adjusted the quota, you can set it by clicking Done and then Submit Request.

You will receive confirmation in the dialog box when the quota has been updated.

Once the quota is set, if the user-level custom quota is exceeded, BigQuery will return an error.

6. Congratulations

You have successfully created project-level and user-level custom quotas for query processing!

What we've covered

• How to navigate to the quotas menu
• How to set up a project-level custom quota
• How to set up a user-level custom quota

Next steps / learn more

• Documentation: Creating custom cost controls
• Documentation: How custom quotas are enforced
• Documentation: Example custom cost control scenario

7. Survey

How will you use this tutorial?

Only read through it Read it and complete the exercises