Demystifying BigQuery reservations
Learn how to mix flat-rate and on-demand pricing
--
BigQuery is the exabyte-scale data warehouse of Google Cloud. It revolutionized the market with the concept of data warehouse as a service and pay-as-you-go.
BigQuery completely decouples the computing and storage layers and this is reflected in its pricing. You pay computing and storage separately.
Focusing on the computing layer, with the on-demand pricing, you pay only for the bytes actually read by your queries and you get access to a computing power of up to 2000 burstable Slots* per project. This is a supercomputer at your fingertips.
* A Slot is the unit of measure for the computing power of BigQuery. More Slots, more speed.
However, many complex organizations have internal processes and governance rules to follow. These organizations want to allocate their budgets to the different groups and make sure that everyone stays in the allocated spending.
For these use cases, BigQuery supports the flat-rate pricing. You purchase in advance reserved computation power expressed in Slots with an annual, monthly or by-minute (Flex Slots) commitment.
The origin project of a query
You may have not noticed this but
This can be made very clear by looking at this command line
The origin project (in blue) is specified via the project_id parameter while the projects of the queried tables (in green) are expressed as part of the full name of the table.
Even when using the BigQuery web UI these concepts are visible.
Having clarified the concept of “origin project of a query” and provided a way to visualize it, we can proceed by focusing again on the flat-rate model.
Assign slots to a Project
In BigQuery you can purchase a certain number of Slots through a Commitment. It specifies the number of Slots desired and for what minimum period of time (year, month, minute). The longer the Commitment, the lower the price per Slot.
Through Reservations you divide the Slots you purchased into parts and give them a name.
Assignments assign the amount of Slots specified by a Reservation to one or more Projects.
To recap, you buy the Slots and distribute them to the Projects.
The flat-rate model requires that (at least) one Project be elected to the Admin Project role by enabling the Reservation API on it. It can be any Project but you may want to have a dedicated Project just to keep things clear and separated.
Commitments, Reservations and Assignments can be created only within Admin Projects. Assignments can point to any Project.
Commitments, Reservations and Assignments have a Location-wide scope. In other words, the Slots of a Commitment cannot be distributed over multiple Locations but it is possible to buy Commitments on multiple Locations.
Everything can be easily done from the BigQuery web UI, via CLI or API.
The possible configurations are infinite but here are some simple examples:
Change the pricing model of a query
Now that we have the Slots assigned to a Project, how can we use them?
By changing the origin Project of a query it is possible to change the resources and the pricing model.
- If the origin Project doesn’t have any Assignment, the query will use the on-demand model.
- If the origin Project has an Assignment, the query will use the flat-rate model and will have access to the Slots assigned.
In the following examples we have a Project A with Slots assigned and a Project B without Slots. In the first example, Project A is the origin and therefore the flat-rate model is applied.
In the second example, exactly the same query is executed, but this time the origin Project is Project B. Because it has no Slots, the on-demand model is used.
We can now pause a moment, do a step back and try to understand the whole picture.
The idea behind Reservations is to distribute Slots (or not) to a set of Projects. Then, allocate the workloads (queries) on these Projects based on the desired pricing model and the Slots you need.
Since the origin Project is defined at run time, this query / orgin_project allocation does not have to be static. It can be changed at any time, even just before running a query.
By combining this with the Resource Hierarchy and perhaps multiple Admin Projects, highly advanced workload management configurations are possible.
Constraining users
We can imagine a situation in which the user group X (e.g. engineering) has 20000 dedicated Slots on Project X and the user group Y (e.g. finance) has 500 dedicated Slots on Project Y. Users are asked to use only their own Project.
With what we have explained so far, nothing prevents an impatient user of group Y from using the higher power available on Project X. After all, he only has to use Project X as the origin of his queries.
The bigquery.jobs.create permission can help us in this cases. Only when a user (or group) has this permission on a Project, he can use it as the origin. The permission is included in roles/bigquery.jobUser and roles/bigquery.user.
Returning to our example, we must be sure that each group has one of these roles on all and only the Projects it can use as origin.
* Group X
* Project X: roles/bigquery.jobUser
* Project Y: -
* Group Y
* Project X: -
* Project Y: roles/bigquery.jobUserLimit on-demand use
A similar approach can be used to limit the on-demand use for a group of users. For example, we can force group Z to use only the on-demand model and up to 10 TB/day.
We create a Project Z, do not assign Slots to it, but instead configure the custom quota “BigQuery API — Query usage per day” to 10 TB. Then, we assign roles/bigquery.jobUser to group Z only in Project Z.
In this way, the group Z can only use Project Z as origin. As a result, all their queries will use the on-demand pricing and erode the configured quota.
This can be repeated to create multiple independent buckets and, if desired, may even limit usage per user with the custom quota “BigQuery API — Query usage per day per user”.
Other tips are limiting bytes per query and monitor usage as described here.
Closing
In this article we have learned that:
- Computing pricing comes in two types, on-demand and flat-rate, and both may be needed in your organization.
- You can use them both on a case by case basis by selecting the right “origin project” for your queries.
- You can purchase flat-rate computing power (Slots, Commitments) and allocate it (Reservations, Assignments) to specific users (Projects, Roles).
- On-demand use can be allocated and controlled as well.
To celebrate the 10 years anniversary of BigQuery, Google is offering Trial Slots with a 95% discount. I encourage you to start experimenting with Reservation. Just login to the Google Cloud Console and buy a per-minute Commitment (Flex Slots).
Update 20200812: now you can buy even just 100 Slots.
