Optimize BigQuery costs with Flex Slots
(Update 5/13/2020: Code now uses the Reservations client libraries)
Google Cloud recently added Flex Slots as a new pricing option for BigQuery. Users on Flat Rate commitments no longer pay for queries by bytes scanned and instead pay for reserved compute resources; and using Flex Slots commitments, users can now cancel the reservation anytime after 60 seconds. At $20/500 slot-hours, billed per second, Flex Slots can offer significant cost savings for On-Demand customers whose query sizes exceed 1TiB.
In this article I will show you how your applications can run an hour’s worth of queries on a 500 slot reservation for the same price as a single 4TiB on-demand query (currently priced at $5/TiB), using the Reservations APIs. Equivalent functionality is also available in the Reservations UI.
Admin Project Setup
The recommended best practice for BigQuery Reservations is to maintain a dedicated project for administering the reservations. In this guide, this project will be called the “admin project”.
In order to create reservations, your user will need the bigquery.resourceAdmin role on a project and an available quota of at least 500 Reservation API slots.
User Project Setup
The BigQuery “user project”, will need to be created. To make things simple, your user should have the bigquery.admin role in the user project, allowing you to attach reservations and run queries.
From cloud shell set to your user project.
$gcloud projects add-iam-policy-binding $DEVSHELL_PROJECT_ID member='user:myuser@org.com' role='roles/bigquery.admin'Testing Behavior in a Notebook
To show the APIs, I am going to be using a Colaboratory (Colab) notebook which will allow you to run Python in your browser. (Another option, as demonstrated in BigQuery quickstart documentation, would be to use an authenticated Python client.)
Configure the Colab environment, and start with the New Notebook option when prompted.
Enter this code in the initial cell.
You will need to replace #’YOUR_ADMIN_PROJECT’ and #’YOUR_USER_PROJECT’ with the projects you created earlier.
Hint: Press Shift-Enter to execute the code in the cell.
The colab project is now logged in as your user, with a base project set to your user project.
You will now create a series of single-purpose functions to demonstrate the Flex Slots feature.
First, create the run_query function which will, by default, estimate the size of the sample query. To execute the query set the dry_run flag to False.
When called, you will see that run_query estimates approximately 3.2TiB scanned or over $15 to run with on-demand pricing.
Now, create a reservations client.
Next, create the function to purchase a Commitment. Commitments are the mechanism to buy slots. Flex commitment purchases are charged in increments of 500 slot hours for $20; this equates to ~33¢ / slot-minute. Customers wishing faster queries or more concurrency can increase their slot commitment.
Now, create a function to create a Reservation. Reservations create a named allocation of slots, and are necessary to assign slots to a project.
Next, create a function to create Assignments. Assignments assign reservations to Organizations, Folders, or Projects. This function assigns the reservation created in the admin project to the user project.
Finally, create a cleanup function. The slot commitment can not be deleted until it’s 60 seconds commitment, end time has been reached; a simple retry is used until the resource can be deleted.
Now that all of the utility functions have been created, you will put everything together and observe the results. Creating the commitment below will charge your project for the 5–10 minute it is expected to run.
Note: as of the time of this writing, the BigQuery Reservations API will indicate the slot assignment is active, before it is ready. The script includes a 3 minute wait to allow time for the reservation to attach. You can get a snack now if you’d like while you wait.
Confirming Query Reservations
You can see the query statistics, nicely formatted in the BigQuery Query History tab within the BigQuery Console. The Reservation name will be indicated with a property for queries that used the reserved slots.
Interpreting the Results
The charts compare the query times and costs of on-demand runs, soft capped at 2000 slots, with runs at increments of 500 slots up to 2000. It’s important to remember that Flex Slots customers will also pay for idle time and those costs can add up quickly for larger reservations. However, even padded with three minutes of idle time, Flex Slots cost 60% to 80% less than the cost of on-demand pricing for this tutorial’s large example query.
