슬롯 자동 확장 소개
슬롯 자동 확장을 사용하도록 구성한 예약은 워크로드 수요를 수용하도록 할당된 용량을 자동으로 확장합니다. 워크로드가 증가하거나 감소함에 따라 BigQuery는 슬롯을 적절한 수준으로 동적으로 조정합니다. 슬롯 자동 확장이 포함된 예약은 BigQuery 버전에서만 사용할 수 있습니다.
자동 확장 예약 사용
자동 확장 예약을 만들기 전에 슬롯 약정을 구매할 필요가 없습니다. 슬롯 약정은 일관되게 사용되는 슬롯에 대해 할인된 요금을 제공하지만 자동 확장 예약의 경우에는 선택사항입니다. 자동 확장 예약을 만들려면 예약에 최대 슬롯 수(최대 예약 크기)를 할당합니다. 예약에 할당된 선택적 기준 슬롯에서 최대 예약 크기를 빼서 최대 자동 확장 슬롯 수를 확인할 수 있습니다.
자동 확장 예약을 만들 때 다음 사항을 고려하세요.
- BigQuery는 작업을 실행하는 데 필요한 슬롯 수에 도달하거나 예약에 사용 가능한 최대 슬롯 수에 도달할 때까지 100의 배수로 예약을 확장합니다.
- 수직 확장은 추정치를 기반으로 하며 프로비저닝이 초과되거나 부족할 수 있습니다. 예를 들어 자동 확장 처리에서 슬롯이 400개만 필요할 때 500개로 확장하거나 확장 없이 워크로드를 처리할 수 있음에도 소량의 확장을 시도할 수 있습니다.
- 자동 확장된 슬롯은 수직 확장 중에 연결된 버전의 용량 컴퓨팅 가격에 따라 청구됩니다. 사용된 슬롯 수가 아닌 확장된 슬롯 수에 대한 요금이 청구됩니다. BigQuery 수직 확장의 원인이 된 작업이 실패해도 이 요금이 적용됩니다.
- 슬롯 수는 항상 100의 배수로 확장되지만 한 단계에 슬롯 100개를 초과하여 확장될 수도 있습니다. 예를 들어 워크로드에 추가 슬롯 450개가 필요한 경우 BigQuery는 용량 요구사항을 충족하기 위해 한 번에 슬롯 500개로 확장하려고 시도할 수 있습니다.
- BigQuery는 할당된 슬롯이 필요 슬롯 수를 초과하고 일정 시간 동안 자동 확장 용량이 안정되면 축소됩니다.
자동 확장을 사용하여 작업하는 방법을 알아보려면 슬롯 자동 확장을 사용한 작업을 참조하세요.
기준 및 자동 확장 슬롯이 있는 예약 사용
최대 예약 크기를 지정하는 것 외에도 원하는 경우 예약당 기준 슬롯 수를 지정할 수 있습니다. 기준은 항상 예약에 할당되는 최소 슬롯 수이며 항상 요금이 청구됩니다. 자동 확장 슬롯은 모든 기준 슬롯(해당하는 경우 유휴 슬롯)이 사용된 후에만 추가됩니다. 한 예약의 유휴 기준 슬롯을 용량이 필요한 다른 예약과 공유할 수 있습니다.
몇 분 단위로 예약의 기준 슬롯 수를 늘릴 수 있습니다. 최근에 기준 슬롯 용량을 변경하였고 기준 슬롯이 약정 슬롯을 초과하는 경우 기준 슬롯을 줄이는 것은 1시간에 한 번으로 제한됩니다. 그렇지 않은 경우 몇 분마다 기준 슬롯을 줄일 수 있습니다.
기준 슬롯 및 자동 확장 슬롯은 최근 워크로드를 기준으로 용량을 제공하기 위한 것입니다. 최근 워크로드와 크게 다른 대규모 워크로드가 예상되는 경우 워크로드 용량을 처리하기 위해 자동 확장 슬롯을 사용하는 대신 이벤트에 앞서 기준 용량을 늘리는 것이 좋습니다.
예약에 기준 슬롯이 없거나 다른 예약에서 유휴 슬롯을 대여하도록 구성되지 않은 경우 BigQuery는 확장을 시도합니다. 그렇지 않으면 확장 전에 기준 슬롯을 완전히 활용해야 합니다.
예약은 다음 우선순위로 슬롯을 사용하고 추가합니다.
- 기준 슬롯
- 유휴 슬롯 공유(사용 설정된 경우). 예약은 동일한 버전 및 동일한 리전으로 생성된 다른 예약의 유휴 기준 또는 약정 슬롯만 공유할 수 있습니다.
- 자동 확장 슬롯
다음 예시에서는 지정된 기준 수량에서 슬롯을 확장합니다. etl 및 dashboard 예약의 기준 크기는 각각 700개, 300개입니다.
이 예시에서 etl 예약은 슬롯 1,300개(기준 슬롯 700개 및 자동 확장 슬롯 600개)로 확장할 수 있습니다. dashboard 예약이 사용되지 않는 경우 etl 예약은 dashboard 예약에서 슬롯 300개를 사용할 수 있으며(실행 중인 작업이 없는 경우) 사용 가능한 슬롯은 최대 1,600개입니다.
dashboard 예약은 슬롯 1,100개(기준 슬롯 300개 및 자동 확장 슬롯 800개)까지 확장될 수 있습니다. etl 예약이 완전히 유휴 상태이면 dashboard 예약은 최대 1,800개의 슬롯(기준 슬롯 300개, 자동 확장 슬롯 800개, etl 예약의 유휴 슬롯 700개)까지 확장될 수 있습니다.
etl 예약에서 항상 사용 가능한 700개의 기준 슬롯보다 더 많은 슬롯을 필요로 하는 경우 예약에서는 다음 방법을 순서대로 사용하여 슬롯을 추가하려고 시도합니다.
- 기준 슬롯 700개
dashboard예약의 기준 슬롯 300개와 유휴 슬롯 공유. 예약은 유휴 기준 슬롯만 동일한 버전으로 생성된 다른 예약과 공유합니다.- 600개의 추가 슬롯을 최대 예약 크기로 확장
슬롯 약정 사용
다음 예시에서는 용량 약정을 사용하여 슬롯 자동 확장을 보여줍니다.
예약 기준과 마찬가지로 슬롯 약정을 사용하면 모든 예약에 사용 가능한 고정된 개수의 슬롯을 할당할 수 있습니다. 기준 슬롯과 달리 약정 슬롯은 약정 기간 동안 줄일 수 없습니다. 슬롯 약정은 선택사항이지만 장기간 기준 슬롯이 필요한 경우 비용을 절약할 수 있습니다.
이 예시에서는 용량 약정 슬롯에 대해 사전 정의된 요금이 청구됩니다. 자동 확장이 활성화되고 예약이 확장 상태인 경우 자동 확장 슬롯 수에 대한 자동 확장 요금이 청구됩니다. 자동 확장 요율에는 사용된 슬롯 수가 아닌 확장 슬롯 수에 대한 요금이 청구됩니다.
사용 가능한 최대 슬롯
기준 슬롯, 최대 자동 확장 슬롯 수, 동일한 버전으로 생성되었지만 기준 슬롯에 포함되지 않는 약정의 모든 슬롯을 더하여 예약에서 사용할 수 있는 최대 슬롯 수를 계산할 수 있습니다. 이전 이미지의 예시는 다음과 같이 설정됩니다.
- 연간 슬롯 1,000개의 용량 약정. 이러한 슬롯은
etl예약 및dashboard예약에서 기준 슬롯으로 할당됩니다. etl예약에 할당된 기준 슬롯 700개dashboard예약에 할당된 기준 슬롯 300개etl예약의 자동 확장 슬롯 600개dashboard예약의 자동 확장 슬롯 800개
etl 예약의 경우 가능한 최대 슬롯 수는 etl 기준 슬롯(700)과 dashboard 기준 슬롯(모든 슬롯이 유휴 상태인 경우 300개)에 자동 확장 슬롯의 최대 개수(600개)를 더한 값입니다. 따라서 이 예시에서 etl 예약이 사용할 수 있는 최대 슬롯 수는 1,600개입니다. 이 숫자는 용량 약정의 수를 초과합니다.
다음 예시에서는 연간 약정이 할당된 기준 슬롯을 초과합니다.
이 예시의 조건은 다음과 같습니다.
- 연간 슬롯 1,600개의 용량 약정
- 최대 예약 크기 1,500개(자동 확장 슬롯 500개 포함)
etl예약에 할당된 기준 슬롯 1,000개
예약에 사용할 수 있는 최대 슬롯 수는 기준 슬롯(1,000개)과 기준 슬롯 외 약정 유휴 슬롯(약정 슬롯 1,600개 - 기준 슬롯 1,000개 = 600개)에 자동 확장 슬롯(500개)을 더한 값입니다. 따라서 이 예약에서 최대 잠재적 슬롯은 2,100개입니다. 자동 확장된 슬롯은 용량 약정보다 큰 추가 슬롯입니다.
자동 확장 권장사항
자동 확장 처리를 처음 사용하는 경우 자동 확장 슬롯 수를 과거 및 예상 성능을 기준으로 의미 있는 숫자로 설정합니다. 예약이 생성되면 실패율, 성능, 청구 요금을 적극적으로 모니터링하고 필요에 따라 자동 확장 슬롯 수를 조정합니다.
사용된 슬롯 수가 아닌 확장 슬롯 수에 대한 요금이 청구됩니다. 자동 확장 슬롯의 최대 개수가 너무 크면 BigQuery에서 필요한 것보다 많이 확장되어 추가 비용이 발생할 수 있습니다.
예를 들어 작업에서 특정 시점에 많은 수의 슬롯을 사용한 이후에 훨씬 적은 개수만 필요할 수 있습니다. BigQuery는 미사용 슬롯을 즉시 해제하지 않으므로 예약의 다른 작업에서 해당 슬롯을 사용할 수 없으면 사용률이 저하될 수 있습니다. 예약의 최댓값을 줄이면 최대 할당 슬롯 수가 낮아지므로 작업 슬롯 사용량의 급증 현상이 감소하여 할당된 슬롯의 작업 사용률이 개선됩니다.
슬롯 사용량은 간혹 기준 슬롯과 확장된 슬롯의 합계를 초과할 수 있습니다. 기준 및 확장 슬롯을 초과하는 슬롯 사용량에 대한 요금은 청구되지 않습니다.
자동 확장 처리는 여러 개의 동시 쿼리를 포함하는 워크로드와 같이 장기 실행되는 많은 워크로드에 가장 효율적입니다. 이러한 쿼리가 캐시를 사용하는지 여부에 관계없이 한 번에 하나씩 쿼리를 백엔드로 전송하지 마세요. 기준 슬롯이 없으면 예상치 못한 확장과 높은 비용이 발생할 수 있습니다.
BigQuery 자동 확장에는 용량 가용성이 적용됩니다. BigQuery는 이전 사용량을 기준으로 고객 용량 수요를 충족하려고 시도합니다. 용량 보장을 달성하기 위해서는 예약에 있는 보장 슬롯 수인 선택적 슬롯 기준을 설정할 수 있습니다. 기준을 사용하면 슬롯을 즉시 사용할 수 있으며 사용 여부에 관계없이 비용이 청구됩니다. 트래픽이 많은 공휴일과 같은 대규모의 비자연적 수요에 대해 충분한 용량을 갖추려면 몇 주 전에 BigQuery팀에 문의하세요.
기준 슬롯에는 항상 요금이 청구됩니다. 용량 약정이 만료되면 원치 않는 요금이 청구되지 않도록 예약에 있는 기준 슬롯 양을 수동으로 조정해야 할 수 있습니다. 예를 들어 슬롯 100개와 기준 슬롯 100개가 포함된 1년 약정이 있다고 가정해보겠습니다. 약정이 만료되며 갱신 요금제가 없습니다. 약정이 만료되면 사용한 만큼만 지불로 기준 슬롯 100개에 대한 비용을 지불합니다.
자동 확장 모니터링
관리 리소스 차트로 슬롯 사용량을 모니터링할 때 차트는 정렬 기간 동안 사용된 슬롯 수를 평활화하므로 슬롯 사용량보다 훨씬 많은 확장된 슬롯이 표시될 수 있습니다. 보다 정확한 세부정보로 자동 확장 슬롯 사용량을 보려면 기간 옵션을 줄이세요. 이렇게 하면 정렬 기간이 더 작은 단위로 자동 업데이트됩니다.
다음 예시의 차트에는 확장된 슬롯이 워크로드에서 요구하는 것보다 훨씬 많이 표시됩니다.
하지만 정렬 기간이 2초가 되도록 기간 옵션을 줄이면 자동 확장 처리가 워크로드 수요에 맞게 확장되고 더 정확한 데이터가 표시됩니다. 기간 옵션의 시작과 끝 범위를 드래그하여 기간 옵션을 조정할 수 있습니다. 가장 정확한 워크로드 수요 데이터를 표시하려면 측정항목 목록에서 p90 또는 p99를 선택합니다.
자동 확장 사용량을 가장 정확하게 확인하려면 1~15초의 정렬 기간을 사용합니다. 관리 리소스 차트의 정렬 기간에 대한 자세한 내용은 기간 옵션을 참조하세요.
슬롯 사용량 보기에 대한 자세한 내용은 관리 리소스 차트 보기를 참조하세요.
정보 스키마로 자동 확장 모니터링
다음 SQL 스크립트를 사용하여 특정 버전의 청구 슬롯 시간(초)을 확인할 수 있습니다. 이러한 스크립트는 예약이 생성된 동일한 프로젝트에서 실행되어야 합니다. 첫 번째 스크립트는 commitment_plan이 적용되는 청구 슬롯 시간(초)을 표시하고 두 번째 스크립트는 약정이 적용되지 않는 청구 슬롯 시간(초)을 표시합니다.
이러한 스크립트를 실행하려면 3가지 변수의 값만 설정하면 됩니다.
start_timeend_timeedition_to_check
이러한 스크립트에서는 다음 사항에 주의해야 합니다.
삭제된 예약 및 용량 약정은 데이터 보관 기간이 끝나면 정보 스키마 뷰에서 삭제됩니다. 올바른 결과를 얻기 위해 삭제된 예약 및 약정이 포함되지 않은 최근 기간을 지정합니다.
작은 차이의 반올림 오류로 인해 스크립트 결과가 청구서와 정확하게 일치하지 않을 수 있습니다.
다음 스크립트는 특정 버전의 약정이 적용되는 슬롯 사용량을 확인합니다.
해당 시나리오의 스크립트를 보려면 펼치기
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
다음 스크립트는 특정 버전의 약정에 포함되지 않는 슬롯 사용량을 확인합니다. 이 사용량에는 확장된 슬롯과 약정이 적용되지 않는 기준 슬롯이라는 두 가지 유형의 슬롯이 포함됩니다.
다루지 않는 시나리오의 스크립트를 보려면 펼치기
/*
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
할당량
최대 예약 크기의 합계는 슬롯 할당량을 초과하면 안 됩니다.
할당량에 대한 자세한 내용은 할당량 및 한도를 참조하세요.
다음 단계
- BigQuery 버전에 대한 자세한 내용은 BigQuery 소개를 참조하세요.
- 슬롯에 대한 자세한 내용은 슬롯 이해를 참조하세요.
- 예약에 대한 자세한 내용은 예약 소개를 참조하세요.