Introduction to slots autoscaling
Reservations that you configure to use slots autoscaling automatically scale the allocated capacity to accommodate your workload demands. As your workload increases or decreases, BigQuery dynamically adjusts your slots to an appropriate level. Reservations with slots autoscaling are only available with BigQuery editions.
Use autoscaling reservations
You don't need to purchase slot commitments before creating autoscaling reservations. Slot commitments provide a discounted rate for consistently used slots but are optional with autoscaling reservations. To create an autoscaling reservation, you assign a reservation a maximum number of slots (the max reservation size). You can identify the maximum number of autoscaling slots by subtracting the max reservation size by any optional baseline slots assigned to the reservation.
When you create autoscaling reservations, consider the following:
- BigQuery scales reservations by multiples of 100 until it has reached the number of slots needed to execute the jobs, or it reaches the maximum number of slots available to the reservation.
- Scaling up is based on an estimation, and might over or under provision. For example, autoscaler may scale to 500 slots when only 400 are needed, or attempt to scale a small amount when the workload might be handled without scaling.
- Your autoscaled slots are charged at capacity compute pricing for your associated edition while scaling up. You are charged for the number of scaled slots, not the number of slots used. This charge applies even if the job that causes BigQuery to scale up fails.
- While the number of slots always scales by multiples of 100, it may scale more than 100 slots within one step. For example, if your workload requires an additional 450 slots, BigQuery can attempt to scale by 500 slots at once to meet the capacity requirement.
- BigQuery scales down when the slots that are allocated exceed the number of slots required, and the autoscaling capacity has been stable for some time.
To learn how to work with autoscaling, see Work with slots autoscaling.
Using reservations with baseline and autoscaling slots
In addition to specifying the maximum reservation size, you can optionally specify a baseline number of slots per reservation. The baseline is the minimum number of slots that will always be allocated to the reservation, and you will always be charged for them. Autoscaling slots are only added after all of the baseline slots (and idle slots if applicable) are consumed. You can share idle baseline slots in one reservation with other reservations that need capacity.
You can increase the number of baseline slots in a reservation every few minutes. If you want to decrease your baseline slots, you are limited to once an hour if you have recently changed your baseline slot capacity and your baseline slots exceed your committed slots. Otherwise, you can decrease your baseline slots every few minutes.
Baseline and autoscaling slots are intended to provide capacity based on your recent workload. If you anticipate a large workload that is very different from your workloads in the recent past, we recommend increasing your baseline capacity ahead of the event rather than rely on autoscaling slots to cover the workload capacity.
If the reservation doesn't have baseline slots or is not configured to borrow idle slots from other reservations, then BigQuery attempts to scale. Otherwise, baseline slots must be fully utilized before scaling.
Reservations use and add slots in the following priority:
- Baseline slots.
- Idle slot sharing (if enabled). Reservations can only share idle baseline or committed slots from other reservations that were created with the same edition and the same region.
- Autoscale slots.
In the following example, slots scale from a specified baseline amount. The
etl and dashboard reservations have a baseline size of 700 and 300 slots
respectively.
In this example, the etl reservation can scale to 1300 slots (700 baseline
slots plus 600 autoscale slots). If the dashboard reservation is not in use,
the etl reservation can use the 300 slots from the dashboard reservation
if no job is running there, leading to a maximum of 1600 possible slots.
The dashboard reservation can scale to 1100 slots (300 baseline slots plus
800 autoscale slots). If the etl reservation is totally idle, the
dashboard reservation can scale to a maximum of 1800 slots (300 baseline
slots plus 800 autoscale slots plus 700 idle slots in the etl reservation).
If the etl reservation requires more than 700 baseline slots, which are
always available, it attempts to add slots by using the following methods in
order:
- 700 baseline slots.
- Idle slot sharing with the
300 baseline slots in the
dashboardreservation. Your reservation only shares idle baseline slots with other reservations that are created with the same edition. - Scaling up 600 additional slots to the maximum reservation size.
Using slot commitments
The following example shows slots autoscaling using capacity commitments.
Like reservation baselines, slot commitments allow you to allocate a fixed number of slots that are available to all reservations. Unlike baseline slots, a commitment cannot be reduced during the term. Slot commitments are optional but can save costs if baseline slots are required for long periods of time.
In this example, you are charged a predefined rate for the capacity commitment slots. You are charged at the autoscaling rate for the number of autoscaling slots after autoscaling activates and reservations are in an upscaled state. For autoscaling rate, you are charged for the number of scaled slots, not the number of slots used.
Maximum available slots
You can calculate the maximum number of slots a reservation can use by adding the baseline slots, the maximum number of autoscale slots, and any slots in commitments that were created with the same edition and are not covered by the baseline slots. The example in the previous image is set up as follows:
- A capacity commitment of 1000 annual slots. Those slots are assigned as
baseline slots in the
etlreservation and thedashboardreservation. - 700 baseline slots assigned to the
etlreservation. - 300 baseline slots assigned to the
dashboardreservation. - Autoscale slots of 600 for the
etlreservation. - Autoscale slots of 800 for the
dashboardreservation.
For the etl reservation, the maximum number of slots possible is equal to
the etl baseline slots (700) plus the dashboard baseline slots (300, if
all slots are idle) plus the maximum number of autoscale slots (600). So the
maximum number of slots the etl reservation could use in this example is
1600. This number exceeds the number in the capacity commitment.
In the following example, the annual commitment exceeds the assigned baseline slots.
In this example, we have:
- A capacity commitment of 1600 annual slots.
- A maximum reservation size of 1500 (including 500 autoscaling slots).
- 1000 baseline slots assigned to the
etlreservation.
The maximum number of slots available to the reservation is equal to the baseline slots (1000) plus any committed idle slots not dedicated to the baseline slots (1600 annual slots - 1000 baseline slots = 600) plus the number of autoscaling slots (500). So the maximum potential slots in this reservation is 2100. The autoscaled slots are additional slots above the capacity commitment.
Autoscaling best practices
When first using autoscaler, set the number of autoscaling slots to a meaningful number based on past and expected performance. Once the reservation is created, actively monitor the failure rate, performance, and bill and adjust the number of autoscaling slots as needed.
Be aware that you are charged for the number of scaled slots, not the number of slots used. If the maximum number of autoscale slots is too large, then BigQuery might scale more than you need, incurring additional costs.
For example, a job might use a large number of slots at one point, but then only need a significantly smaller number. Since BigQuery doesn't release unused slots immediately, this might result in lower utilization if no other jobs in the reservation can use those slots. By reducing the max value for a reservation, the maximum number of slots allocated is reduced, which causes the job slot usage to be less spiky and improves the job utilization of allocated slots.
Slot usage can occasionally exceed the sum of your baseline plus scaled slots. You are not billed for slot usage that is greater than your baseline plus scaled slots.
Autoscaler is most efficient for heavy, long-running workloads, such as workloads with multiple concurrent queries. Avoid sending queries one at a time to the backend, whether or not those queries use the cache. Without baseline slots, this might cause unexpected scaling and incur high costs.
BigQuery autoscaling is subject to capacity availability. BigQuery attempts to meet customer capacity demand based on historical usage. To achieve capacity guarantees, you can set an optional slot baseline, which is the number of guaranteed slots in a reservation. With baselines, slots are immediately available and you pay for them whether you use them or not. To ensure capacity is available for large, inorganic demands, such as high-traffic holidays, contact the BigQuery team several weeks in advance.
Baseline slots are always charged. If a capacity commitment expires, you might need to manually adjust the amount of baseline slots in your reservations to avoid any unwanted charges. For example, consider that you have a 1-year commitment with 100 slots and a reservation with 100 baseline slots. The commitment expires and doesn't have a renewal plan. Once the commitment expires, you pay for 100 baseline slots at the pay as you go rate.
Monitor autoscaling
When you monitor slot usage with administrative resource charts, you might see significantly more scaled slots than your slot usage because the charts smooth the number of used slots over the alignment period. To view autoscale slot usage with more precise details, reduce the time frame option. This automatically updates the alignment period to a smaller increment.
In the following example, the chart displays significantly more scaled slots than the workload demands.
However, if you shorten the time frame option so that the alignment period is two seconds, you can see that the autoscaler scales to the workload demand and displays more accurate data. You can adjust the time frame option by dragging the start and end ranges of the time frame option. To display the most accurate workload demand data, select p90 or p99 from the Metric list.
For the most accurate view of autoscale usage, use an alignment period between 1 and 15 seconds. For more information about the alignment period of administrative resource charts, see Time frame option.
For information about viewing your slot usage, see View administrative resource charts
Monitor autoscaling with information schema
You can use the following SQL scripts to check the billed slot seconds for a
particular edition. You must run these scripts in the same project the
reservations were created. The first script shows billed slot seconds covered by
commitment_plan while the second script shows billed slot seconds that aren't
covered by a commitment.
You only need to set the value of three variables to run these scripts:
start_timeend_timeedition_to_check
These scripts are subject to the following caveats:
Deleted reservations and capacity commitments are removed from information schema views at the end of the data retention period. Specify a recent window of time which doesn't contain deleted reservations and commitments for correct results.
The result of the scripts may not exactly match the bill due to small rounding errors.
The following script checks the slot usage covered by commitments for a particular edition.
Expand to see the script for covered scenarios
DECLARE start_time,end_time TIMESTAMP;
DECLARE
edition_to_check STRING;
/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */
/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.
During daylight savings time, the start_time and end_time variables should
follow this format: 2024-02-20 00:00:00-08. */
SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_to_check = 'ENTERPRISE';
/* The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023-06-01
10:00:00, 2023-06-01 11:00:00), then during that window the total slot seconds
will be 100 * 3600.
This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */
CREATE TEMP FUNCTION
GetSlotSecondsBetweenChanges(
slots FLOAT64,
range_begin_timestamp_unix_millis FLOAT64,
range_end_timestamp_unix_millis FLOAT64,
script_start_timestamp_unix_millis FLOAT64,
script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis > range_end_timestamp_unix_millis) {
return 0;
}
var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
return slots * Math.ceil((end - begin) / 1000.0)
""";
/*
Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023-07-20 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
| 2023-07-27 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
| 2023-07-27 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
| 2023-07-27 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE |
The last row indicates a special change from MONTHLY to FLEX, which happens
because of commercial migration.
*/
WITH
/*
Information containing which commitment might have plan
updated (e.g. renewal or commercial migration). For example:
+------------------------+------------------+--------------------+--------+------------+--------+-----------+----------------------------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action | next_plan | next_plan_change_timestamp |
+---------------------+------------------------+-----------------+--------+------------+--------+-----------+----------------------------+
| 2023-07-20 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE | ANNUAL | 2023-07-20 19:30:27 |
| 2023-07-27 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE | FLEX | 2023-07-27 22:29:21 |
| 2023-07-27 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE | FLEX | 2023-07-27 23:11:06 |
| 2023-07-27 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE | FLEX | 2023-07-27 23:11:06 |
*/
commitments_with_next_plan AS (
SELECT
*,
IFNULL(
LEAD(commitment_plan)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
),
commitment_plan)
next_plan,
IFNULL(
LEAD(change_timestamp)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
),
change_timestamp)
next_plan_change_timestamp
FROM
`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
),
/*
Insert a 'DELETE' action for those with updated plans. The FLEX commitment
'7341455530498381779' is has no 'CREATE' action, and is instead labeled as an
'UPDATE' action.
For example:
+---------------------+------------------------+-----------------+--------+------------+--------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023-07-20 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
| 2023-07-27 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
| 2023-07-27 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
| 2023-07-27 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE |
| 2023-07-27 23:11:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | DELETE |
*/
capacity_changes_with_additional_deleted_event_for_changed_plan AS (
SELECT
next_plan_change_timestamp AS change_timestamp,
project_id,
project_number,
capacity_commitment_id,
commitment_plan,
state,
slot_count,
'DELETE' AS action,
commitment_start_time,
commitment_end_time,
failure_status,
renewal_plan,
user_email,
edition,
is_flat_rate,
FROM commitments_with_next_plan
WHERE commitment_plan <> next_plan
UNION ALL
SELECT * FROM `region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
),
/*
The committed_slots change the history. For example:
+---------------------+------------------------+------------------+-----------------+
| change_timestamp | capacity_commitment_id | slot_count_delta | commitment_plan |
+---------------------+------------------------+------------------+-----------------+
| 2023-07-20 19:30:27 | 12954109101902401697 | 100 | ANNUAL |
| 2023-07-27 22:29:21 | 11445583810276646822 | 100 | FLEX |
| 2023-07-27 23:10:06 | 7341455530498381779 | 100 | MONTHLY |
| 2023-07-27 23:11:06 | 7341455530498381779 | -100 | MONTHLY |
| 2023-07-27 23:11:06 | 7341455530498381779 | 100 | FLEX |
*/
capacity_commitment_slot_data AS (
SELECT
change_timestamp,
capacity_commitment_id,
CASE
WHEN action = "CREATE" OR action = "UPDATE"
THEN
IFNULL(
IF(
LAG(action)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
)
IN UNNEST(['CREATE', 'UPDATE']),
slot_count - LAG(slot_count)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
),
slot_count),
slot_count)
ELSE
IF(
LAG(action)
OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
IN UNNEST(['CREATE', 'UPDATE']),
-1 * slot_count,
0)
END
AS slot_count_delta,
commitment_plan
FROM
capacity_changes_with_additional_deleted_event_for_changed_plan
WHERE
state = "ACTIVE"
AND edition = edition_to_check
AND change_timestamp <= end_time
),
/*
The total_committed_slots history for each plan. For example:
+---------------------+---------------+-----------------+
| change_timestamp | capacity_slot | commitment_plan |
+---------------------+---------------+-----------------+
| 2023-07-20 19:30:27 | 100 | ANNUAL |
| 2023-07-27 22:29:21 | 100 | FLEX |
| 2023-07-27 23:10:06 | 100 | MONTHLY |
| 2023-07-27 23:11:06 | 0 | MONTHLY |
| 2023-07-27 23:11:06 | 200 | FLEX |
*/
running_capacity_commitment_slot_data AS (
SELECT
change_timestamp,
SUM(slot_count_delta)
OVER (
PARTITION BY commitment_plan
ORDER BY change_timestamp
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
AS capacity_slot,
commitment_plan,
FROM
capacity_commitment_slot_data
),
/*
The slot_seconds between each changes, partitioned by each plan. For example:
+---------------------+--------------+-----------------+
| change_timestamp | slot_seconds | commitment_plan |
+---------------------+--------------+-----------------+
| 2023-07-20 19:30:27 | 64617300 | ANNUAL |
| 2023-07-27 22:29:21 | 250500 | FLEX |
| 2023-07-27 23:10:06 | 6000 | MONTHLY |
| 2023-07-27 23:11:06 | 0 | MONTHLY |
| 2023-07-27 23:11:06 | 5626800 | FLEX |
*/
slot_seconds_data AS (
SELECT
change_timestamp,
GetSlotSecondsBetweenChanges(
capacity_slot,
UNIX_MILLIS(change_timestamp),
UNIX_MILLIS(
IFNULL(
LEAD(change_timestamp)
OVER (PARTITION BY commitment_plan ORDER BY change_timestamp ASC),
CURRENT_TIMESTAMP())),
UNIX_MILLIS(start_time),
UNIX_MILLIS(end_time)) AS slot_seconds,
commitment_plan,
FROM
running_capacity_commitment_slot_data
WHERE
change_timestamp <= end_time
)
/*
The final result is similar to the following:
+-----------------+--------------------+
| commitment_plan | total_slot_seconds |
+-----------------+--------------------+
| ANNUAL | 64617300 |
| MONTHLY | 6000 |
| FLEX | 5877300 |
*/
SELECT
commitment_plan,
SUM(slot_seconds) AS total_slot_seconds
FROM
slot_seconds_data
GROUP BY
commitment_plan
The following script checks the slot usage not covered by commitments for a particular edition. This usage contains two types of slots, scaled slots and baseline slots not covered by commitments.
Expand to see the script for non-covered scenarios
/*
This script has several parts:
1. Calculate the baseline and scaled slots for reservations
2. Calculate the committed slots
3. Join the two results above to calculate the baseline not covered by committed
slots
4. Aggregate the number
*/
-- variables
DECLARE start_time, end_time TIMESTAMP;
DECLARE
edition_to_check STRING;
/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */
/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.
During daylight savings time, the start_time and end_time variables should
follow this format: 2024-02-20 00:00:00-08. */
SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_to_check = 'ENTERPRISE';
/*
The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023-06-01
10:00:00, 2023-06-01 11:00:00), then during that window the total slot seconds
will be 100 * 3600.
This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */
CREATE TEMP FUNCTION GetSlotSecondsBetweenChanges(
slots FLOAT64,
range_begin_timestamp_unix_millis FLOAT64,
range_end_timestamp_unix_millis FLOAT64,
script_start_timestamp_unix_millis FLOAT64,
script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis > range_end_timestamp_unix_millis) {
return 0;
}
var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
return slots * Math.ceil((end - begin) / 1000.0)
""";
/*
Sample RESERVATION_CHANGES data (unrelated columns ignored):
+---------------------+------------------+--------+---------------+---------------+
| change_timestamp | reservation_name | action | slot_capacity | current_slots |
+---------------------+------------------+--------+---------------+---------------+
| 2023-07-27 22:24:15 | res1 | CREATE | 300 | 0 |
| 2023-07-27 22:25:21 | res1 | UPDATE | 300 | 180 |
| 2023-07-27 22:39:14 | res1 | UPDATE | 300 | 100 |
| 2023-07-27 22:40:20 | res2 | CREATE | 300 | 0 |
| 2023-07-27 22:54:18 | res2 | UPDATE | 300 | 120 |
| 2023-07-27 22:55:23 | res1 | UPDATE | 300 | 0 |
Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023-07-20 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
| 2023-07-27 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
| 2023-07-27 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
*/
WITH
/*
The scaled_slots & baseline change history:
+---------------------+------------------+------------------------------+---------------------+
| change_timestamp | reservation_name | autoscale_current_slot_delta | baseline_slot_delta |
+---------------------+------------------+------------------------------+---------------------+
| 2023-07-27 22:24:15 | res1 | 0 | 300 |
| 2023-07-27 22:25:21 | res1 | 180 | 0 |
| 2023-07-27 22:39:14 | res1 | -80 | 0 |
| 2023-07-27 22:40:20 | res2 | 0 | 300 |
| 2023-07-27 22:54:18 | res2 | 120 | 0 |
| 2023-07-27 22:55:23 | res1 | -100 | 0 |
*/
reservation_slot_data AS (
SELECT
change_timestamp,
reservation_name,
CASE action
WHEN "CREATE" THEN autoscale.current_slots
WHEN "UPDATE"
THEN
IFNULL(
autoscale.current_slots - LAG(autoscale.current_slots)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
),
IFNULL(
autoscale.current_slots,
IFNULL(
-1 * LAG(autoscale.current_slots)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
),
0)))
WHEN "DELETE"
THEN
IF(
LAG(action)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
)
IN UNNEST(['CREATE', 'UPDATE']),
-1 * autoscale.current_slots,
0)
END
AS autoscale_current_slot_delta,
CASE action
WHEN "CREATE" THEN slot_capacity
WHEN "UPDATE"
THEN
IFNULL(
slot_capacity - LAG(slot_capacity)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
),
IFNULL(
slot_capacity,
IFNULL(
-1 * LAG(slot_capacity)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
),
0)))
WHEN "DELETE"
THEN
IF(
LAG(action)
OVER (
PARTITION BY project_id, reservation_name
ORDER BY change_timestamp ASC, action ASC
)
IN UNNEST(['CREATE', 'UPDATE']),
-1 * slot_capacity,
0)
END
AS baseline_slot_delta,
FROM
`region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`
WHERE
edition = edition_to_check
AND change_timestamp <= end_time
),
-- Convert the above to running total
/*
+---------------------+-------------------------+----------------+
| change_timestamp | autoscale_current_slots | baseline_slots |
+---------------------+-------------------------+----------------+
| 2023-07-27 22:24:15 | 0 | 300 |
| 2023-07-27 22:25:21 | 180 | 300 |
| 2023-07-27 22:39:14 | 100 | 300 |
| 2023-07-27 22:40:20 | 100 | 600 |
| 2023-07-27 22:54:18 | 220 | 600 |
| 2023-07-27 22:55:23 | 120 | 600 |
*/
running_reservation_slot_data AS (
SELECT
change_timestamp,
SUM(autoscale_current_slot_delta)
OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS autoscale_current_slots,
SUM(baseline_slot_delta)
OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS baseline_slots,
FROM
reservation_slot_data
),
/*
The committed_slots change history. For example:
+---------------------+------------------------+------------------+
| change_timestamp | capacity_commitment_id | slot_count_delta |
+---------------------+------------------------+------------------+
| 2023-07-20 19:30:27 | 12954109101902401697 | 100 |
| 2023-07-27 22:29:21 | 11445583810276646822 | 100 |
| 2023-07-27 23:10:06 | 7341455530498381779 | 100 |
*/
capacity_commitment_slot_data AS (
SELECT
change_timestamp,
capacity_commitment_id,
CASE
WHEN action = "CREATE" OR action = "UPDATE"
THEN
IFNULL(
IF(
LAG(action)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
)
IN UNNEST(['CREATE', 'UPDATE']),
slot_count - LAG(slot_count)
OVER (
PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
),
slot_count),
slot_count)
ELSE
IF(
LAG(action)
OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
IN UNNEST(['CREATE', 'UPDATE']),
-1 * slot_count,
0)
END
AS slot_count_delta
FROM
`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
WHERE
state = "ACTIVE"
AND edition = edition_to_check
AND change_timestamp <= end_time
),
/*
The total_committed_slots history. For example:
+---------------------+---------------+
| change_timestamp | capacity_slot |
+---------------------+---------------+
| 2023-07-20 19:30:27 | 100 |
| 2023-07-27 22:29:21 | 200 |
| 2023-07-27 23:10:06 | 300 |
*/
running_capacity_commitment_slot_data AS (
SELECT
change_timestamp,
SUM(slot_count_delta)
OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS capacity_slot
FROM
capacity_commitment_slot_data
),
/* Add next_change_timestamp to the above data,
which will be used when joining with reservation data. For example:
+---------------------+-----------------------+---------------+
| change_timestamp | next_change_timestamp | capacity_slot |
+---------------------+-----------------------+---------------+
| 2023-07-20 19:30:27 | 2023-07-27 22:29:21 | 100 |
| 2023-07-27 22:29:21 | 2023-07-27 23:10:06 | 200 |
| 2023-07-27 23:10:06 | 2023-07-31 00:14:37 | 300 |
*/
running_capacity_commitment_slot_data_with_next_change AS (
SELECT
change_timestamp,
IFNULL(LEAD(change_timestamp) OVER (ORDER BY change_timestamp ASC), CURRENT_TIMESTAMP())
AS next_change_timestamp,
capacity_slot
FROM
running_capacity_commitment_slot_data
),
/*
Whenever we have a change in reservations or commitments,
the scaled_slots_and_baseline_not_covered_by_commitments will be changed.
Hence we get a collection of all the change_timestamp from both tables.
+---------------------+
| change_timestamp |
+---------------------+
| 2023-07-20 19:30:27 |
| 2023-07-27 22:24:15 |
| 2023-07-27 22:25:21 |
| 2023-07-27 22:29:21 |
| 2023-07-27 22:39:14 |
| 2023-07-27 22:40:20 |
| 2023-07-27 22:54:18 |
| 2023-07-27 22:55:23 |
| 2023-07-27 23:10:06 |
*/
merged_timestamp AS (
SELECT
change_timestamp
FROM
running_reservation_slot_data
UNION DISTINCT
SELECT
change_timestamp
FROM
running_capacity_commitment_slot_data
),
/*
Change running reservation-slots and make sure we have one row when commitment changes.
+---------------------+-------------------------+----------------+
| change_timestamp | autoscale_current_slots | baseline_slots |
+---------------------+-------------------------+----------------+
| 2023-07-20 19:30:27 | 0 | 0 |
| 2023-07-27 22:24:15 | 0 | 300 |
| 2023-07-27 22:25:21 | 180 | 300 |
| 2023-07-27 22:29:21 | 180 | 300 |
| 2023-07-27 22:39:14 | 100 | 300 |
| 2023-07-27 22:40:20 | 100 | 600 |
| 2023-07-27 22:54:18 | 220 | 600 |
| 2023-07-27 22:55:23 | 120 | 600 |
| 2023-07-27 23:10:06 | 120 | 600 |
*/
running_reservation_slot_data_with_merged_timestamp AS (
SELECT
change_timestamp,
IFNULL(
autoscale_current_slots,
IFNULL(
LAST_VALUE(autoscale_current_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
AS autoscale_current_slots,
IFNULL(
baseline_slots,
IFNULL(LAST_VALUE(baseline_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
AS baseline_slots
FROM
running_reservation_slot_data
RIGHT JOIN
merged_timestamp
USING (change_timestamp)
),
/*
Join the above, so that we will know the number for baseline not covered by commitments.
+---------------------+-----------------------+-------------------------+------------------------------------+
| change_timestamp | next_change_timestamp | autoscale_current_slots | baseline_not_covered_by_commitment |
+---------------------+-----------------------+-------------------------+------------------------------------+
| 2023-07-20 19:30:27 | 2023-07-27 22:24:15 | 0 | 0 |
| 2023-07-27 22:24:15 | 2023-07-27 22:25:21 | 0 | 200 |
| 2023-07-27 22:25:21 | 2023-07-27 22:29:21 | 180 | 200 |
| 2023-07-27 22:29:21 | 2023-07-27 22:39:14 | 180 | 100 |
| 2023-07-27 22:39:14 | 2023-07-27 22:40:20 | 100 | 100 |
| 2023-07-27 22:40:20 | 2023-07-27 22:54:18 | 100 | 400 |
| 2023-07-27 22:54:18 | 2023-07-27 22:55:23 | 220 | 400 |
| 2023-07-27 22:55:23 | 2023-07-27 23:10:06 | 120 | 400 |
| 2023-07-27 23:10:06 | 2023-07-31 00:16:07 | 120 | 300 |
*/
scaled_slots_and_baseline_not_covered_by_commitments AS (
SELECT
r.change_timestamp,
IFNULL(LEAD(r.change_timestamp) OVER (ORDER BY r.change_timestamp ASC), CURRENT_TIMESTAMP())
AS next_change_timestamp,
r.autoscale_current_slots,
IF(
r.baseline_slots - IFNULL(c.capacity_slot, 0) > 0,
r.baseline_slots - IFNULL(c.capacity_slot, 0),
0) AS baseline_not_covered_by_commitment
FROM
running_reservation_slot_data_with_merged_timestamp r
LEFT JOIN
running_capacity_commitment_slot_data_with_next_change c
ON
r.change_timestamp >= c.change_timestamp
AND r.change_timestamp < c.next_change_timestamp
),
/*
The slot_seconds between each changes. For example:
+---------------------+--------------------+
| change_timestamp | slot_seconds |
+---------------------+--------------+
| 2023-07-20 19:30:27 | 0 |
| 2023-07-27 22:24:15 | 13400 |
| 2023-07-27 22:25:21 | 91580 |
| 2023-07-27 22:29:21 | 166320 |
| 2023-07-27 22:39:14 | 13200 |
| 2023-07-27 22:40:20 | 419500 |
| 2023-07-27 22:54:18 | 40920 |
| 2023-07-27 22:55:23 | 459160 |
| 2023-07-27 23:10:06 | 11841480 |
*/
slot_seconds_data AS (
SELECT
change_timestamp,
GetSlotSecondsBetweenChanges(
autoscale_current_slots + baseline_not_covered_by_commitment,
UNIX_MILLIS(change_timestamp),
UNIX_MILLIS(next_change_timestamp),
UNIX_MILLIS(start_time),
UNIX_MILLIS(end_time)) AS slot_seconds
FROM
scaled_slots_and_baseline_not_covered_by_commitments
WHERE
change_timestamp <= end_time AND next_change_timestamp > start_time
)
/*
Final result for this example:
+--------------------+
| total_slot_seconds |
+--------------------+
| 13045560 |
*/
SELECT
SUM(slot_seconds) AS total_slot_seconds
FROM
slot_seconds_data
Quotas
The sum of your maximum reservation size should not exceed your slot quota.
For information about quotas, see Quotas and limits.
What's next
- For more information about BigQuery editions, see Introduction to BigQuery.
- For more information about slots, see Understand slots.
- For more information about reservations, see Introduction to reservations.