Optimize BigQuery costs with Flex Slots

Editor’s note: This is one installment in a series about managing BigQuery costs. Check out the other posts on choosing between BigQuery pricing models and using Reservations effectively.Google Cloud’s enterprise data warehouse BigQuery offers some flexible pricing options so you can get the most out of your resources. Our recently added Flex Slots can save you money by switching your billing to flat-rate pricing for defined time windows to add maximum efficiency. Flex Slots lets you take advantage of flat-rate pricing when it’s most advantageous, rather than only using on-demand pricing.This is particularly useful for those of you querying large tables—those above 1 terabyte. Flex Slots lets you switch to flat-rate pricing to save money on these larger queries. We often hear, for example, that running data science or ELT jobs over large tables can benefit from using Flex Slots. And companies with teams of AI Notebook users running analytics jobs for several hours or more a day can benefit as well. In this blog post, you’ll see how you can incorporate Flex Slots programmatically into your BigQuery jobs to meet querying spikes or scale on demand to meet data science needs, without going over budget or using a lot of management overhead. Users on Flat Rate commitments no longer pay for queries by bytes scanned and instead pay for reserved compute resources; using Flex Slots commitments, you can cancel anytime after 60 seconds. At the time of this writing, an organization can run an hour’s worth of queries in BigQuery’s U.S. multi-region using Flex Slots for the same price as a single 4TiB on-demand query.  Setting up for Flex SlotsThe recommended best practice for BigQuery Reservations is to maintain a dedicated project for administering the reservations. In order to create reservations, the user account will need the bigquery.resourceAdmin role on the project and Reservations API slots quota.Understanding the conceptsFlex Slot commitments are purchases charged in increments of 500 slot hours for $20, or ~$0.33/minute. You can increase your slot commitments if you need faster queries or more concurrency.  Reservations create a named allocation of slots, and are necessary to assign purchased slots to a project. Find details on reservations in this documentation.Assignments assign reservations to Organizations, Folders, or Projects. All queries in a project will switch from on-demand billing to purchased slots after the assignment is made.You can manage your Flex Slots commitments from the Reservations UI in the Google Cloud Console. In this post, though, we’ll show how you can use the Python client library to apply Flex Slots reservations to your jobs programmatically, so that you can schedule slots when you need them and reduce any unnecessary idle time. This means you can run jobs at any hour, without an admin needing to click a button, and automatically remove that slot commitment when it’s no longer needed (no admin needed).  Check out the BigQuery Quickstart documentation for details on how to authenticate your client session. Here’s a look at a simple script that purchases Flex Slots for the duration of an ELT job:Confirming query reservationsYou can see your 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, as shown here:Interpreting the run times and costsThe charts compare the query times and costs of on-demand runs,soft-capped at 2,000 slots, with runs at increments of 500 slots up to 2,000 for a single 3.15 TB on-demand query. It’s important to remember that Flex Slot customers will also pay for idle time and those costs can add up for larger reservations. Even padded with three minutes of idle time, Flex Slots cost 60% to 80% less than the cost of on-demand pricing for large queries.There’s a near-linear performance increase as slots are added.60% to 80% cost savings using Flex SlotsUsing Flex Slots and the Reservation APIs together lets you fine-tune your organization’s cost and performance profile with flexibility that is unprecedented among data warehouse solutions. For more details on how to get started with BigQuery or developing with the Reservations APIs, check out these resources:Get an introduction to BigQuery ReservationsLearn more about BigQuery slotsCheck out the  Python Client for Cloud BigQuery Reservation docsSee the details on Flex Slots pricing
Quelle: Google Cloud Platform