To evaluate this in your company, a query can estimate the gain per query.
with parameters as (
select 6.25 as on_demand_tb_cost, 0.04 as slot_hour_cost
)
from region-US.INFORMATION_SCHEMA.JOBS
|> join parameters on true
|> extend (total_bytes_billed/(1024*1024*1024*1024)) * on_demand_tb_cost as on_demand_cost
|> extend (total_slot_ms/3600000) * slot_hour_cost as reservation_cost
|> select creation_time, project_id, query, reservation_id, total_bytes_billed, total_slot_ms, on_demand_cost, reservation_cost
|> order by creation_time desc
You can change parameters according to your region and discounts.
Queries can also be grouped per day and per query hash (query_info.query_hashes.normalized_literals) to group similar queries, regardless of WHERE clause variables.
with parameters as (
select 6.25 as on_demand_tb_cost, 0.04 as slot_hour_cost
)
from region-US.INFORMATION_SCHEMA.JOBS
|> aggregate count(1) as nb_run, SUM(total_bytes_processed)/(1024*1024*1024*1024) as total_tb_processed, SUM(total_slot_ms)/(3600000) as total_slot_h group by date(creation_time) as date, query_info.query_hashes.normalized_literals as query_hash
|> join parameters on true
|> extend total_tb_processed * on_demand_tb_cost as on_demand_cost
|> extend total_slot_h as reservation_cost
|> select date, query_hash, nb_run, total_tb_processed, on_demand_cost, total_slot_h, reservation_cost
|> order by date desc
While automating optimization at scale might seem appealing, it’s not straightforward!
- Slot consumption can only be known after the first query run, making a priori cost optimization impossible.
- On-demand pricing is limited to 2,000 concurrent slots per project (and 20k per organization!), which can slow down previously larger queries.
- Switching to on-demand pricing removes the possibility of using idle slots from other reservations.
- Committing to many slots over three years and then using reservations less could waste the commitment.
Dynamic reservation in BigQuery is a useful addition for fine-tuning query run time and cost.
However, it must be tested, monitored for latency, and weighed against potential risks.
The optimization and risk versus money saved must be carefully considered based on your specific use cases and constraints.
Source Credit: https://medium.com/google-cloud/bigquery-dynamic-reservation-the-new-query-cost-hack-4ee4297e619e?source=rss—-e52cf94d98af—4
