Introduzione alla scalabilità automatica degli slot
Le prenotazioni configurate per l'utilizzo della scalabilità automatica degli slot scalano automaticamente la capacità allocata per soddisfare le esigenze dei carichi di lavoro. Man mano che il carico di lavoro aumenta o diminuisce, BigQuery regola dinamicamente i tuoi slot a un livello appropriato. Le prenotazioni con scalabilità automatica di slot sono disponibili solo con le versioni di BigQuery.
Utilizza le prenotazioni con scalabilità automatica
Non è necessario acquistare impegni slot prima di creare prenotazioni di scalabilità automatica. Gli impegni slot offrono una tariffa scontata per gli slot utilizzati in modo coerente, ma sono facoltativi con le prenotazioni con scalabilità automatica. Per creare una prenotazione con scalabilità automatica, assegna a una prenotazione un numero massimo di slot (la dimensione massima della prenotazione). Puoi identificare il numero massimo di slot con scalabilità automatica sottraendo la dimensione massima della prenotazione per eventuali slot di riferimento facoltativi assegnati alla prenotazione.
Quando crei prenotazioni con scalabilità automatica, considera quanto segue:
- BigQuery scala le prenotazioni di multipli di 100 fino a quando non ha raggiunto il numero di slot necessari per eseguire i job o fino al numero massimo di slot disponibili per la prenotazione.
- Lo scale up si basa su una stima e potrebbe comportare un provisioning eccessivo o insufficiente. Ad esempio, il gestore della scalabilità automatica può scalare fino a 500 slot quando ne sono necessari solo 400 o tentare di scalare una piccola quantità quando il carico di lavoro potrebbe essere gestito senza scalabilità.
- Gli slot con scalabilità automatica vengono addebitati in base ai prezzi di computing della capacità per la versione associata durante lo scale up. Ti viene addebitato il numero di slot in scala, non il numero di slot utilizzati. Questo addebito si applica anche se il job che causa lo scale up di BigQuery non riesce.
- Anche se il numero di slot viene sempre scalato per multipli di 100, può scalare più di 100 slot in un passaggio. Ad esempio, se il carico di lavoro richiede altri 450 slot, BigQuery può tentare di scalare di 500 slot contemporaneamente per soddisfare il requisito di capacità.
- BigQuery fa lo scale down quando gli slot allocati superano il numero di slot richiesti e la capacità di scalabilità automatica è stabile per un po' di tempo.
Per scoprire come utilizzare la scalabilità automatica, consulta Utilizzare la scalabilità automatica degli slot.
Utilizzo delle prenotazioni con slot di riferimento e con scalabilità automatica
Oltre a specificare la dimensione massima della prenotazione, puoi facoltativamente specificare un numero di base di slot per prenotazione. La base di riferimento è il numero minimo di slot che verranno sempre assegnati alla prenotazione e ti verranno sempre addebitati. Gli slot di scalabilità automatica vengono aggiunti solo dopo che tutti gli slot di riferimento (e quelli inattivi, se applicabile) sono stati consumati. Puoi condividere gli slot di base inattivi in una prenotazione con altre prenotazioni che richiedono capacità.
Puoi aumentare il numero di slot di riferimento in una prenotazione a intervalli di pochi minuti. Se vuoi ridurre gli slot di riferimento, il limite è una volta all'ora se di recente hai modificato la capacità slot di riferimento e gli slot di riferimento superano gli slot impegnati. In caso contrario, puoi diminuire gli slot di riferimento a intervalli di pochi minuti.
Gli slot di base e con scalabilità automatica sono destinati a fornire capacità in base al carico di lavoro recente. Se prevedi un carico di lavoro di grandi dimensioni molto diverso dai tuoi carichi di lavoro nel recente passato, ti consigliamo di aumentare la capacità di base prima dell'evento, anziché fare affidamento sugli slot di scalabilità automatica per coprire la capacità del carico di lavoro.
Se la prenotazione non ha slot di riferimento o non è configurata per prendere in prestito gli slot inattivi da altre prenotazioni, BigQuery tenterà di scalare. In caso contrario, gli slot di riferimento devono essere utilizzati completamente prima della scalabilità.
Le prenotazioni utilizzano e aggiungono slot con la priorità seguente:
- Slot di riferimento.
- Condivisione di slot inattivi (se abilitata). Le prenotazioni possono condividere solo base di riferimento inattivi o slot impegnati di altre prenotazioni create con la stessa versione e la stessa regione.
- Slot a scalabilità automatica.
Nell'esempio seguente, gli slot vengono scalati da una quantità di riferimento specificata. Le prenotazioni etl e dashboard hanno rispettivamente una dimensione di riferimento di 700 e 300 slot.
In questo esempio, la prenotazione etl può scalare fino a 1300 slot (700 slot di riferimento più 600 slot a scalabilità automatica). Se la prenotazione dashboard non è in uso,
la prenotazione etl può utilizzare i 300 slot della prenotazione dashboard
se non è in esecuzione alcun job al suo interno, il che porterà a un massimo di 1600 slot possibili.
La prenotazione dashboard può scalare fino a 1100 slot (300 slot di riferimento più
800 slot a scalabilità automatica). Se la prenotazione etl è completamente inattiva, la prenotazione dashboard può scalare fino a un massimo di 1800 slot (300 slot di riferimento più 800 slot a scalabilità automatica più 700 slot inattivi nella prenotazione etl).
Se la prenotazione etl richiede più di 700 slot di riferimento, che sono sempre disponibili, tenta di aggiungere slot utilizzando i seguenti metodi nell'ordine:
- 700 slot di riferimento.
- Condivisione di slot inattivo con i 300 slot di riferimento nella prenotazione
dashboard. La prenotazione condivide solo gli slot di riferimento inattivi con altre prenotazioni create con la stessa versione. - Fare lo scale up di 600 slot aggiuntivi fino alla dimensione massima della prenotazione.
Utilizzo degli impegni slot
L'esempio seguente mostra la scalabilità automatica degli slot utilizzando impegni di capacità.
Come le basi delle prenotazioni, gli impegni di slot consentono di allocare un numero fisso di slot disponibili per tutte le prenotazioni. A differenza degli slot di riferimento, non è possibile ridurre l'impegno durante il periodo di validità. Gli impegni slot sono facoltativi ma possono far risparmiare sui costi se sono richiesti slot di riferimento per lunghi periodi di tempo.
In questo esempio, viene addebitata una tariffa predefinita per gli slot dell'impegno di capacità. La tariffa di scalabilità automatica ti viene addebitata per il numero di slot di scalabilità automatica dopo l'attivazione della scalabilità automatica e le prenotazioni in stato di scalabilità automatica. Per la tariffa di scalabilità automatica, ti viene addebitato il numero di slot scalati, non il numero di slot utilizzati.
Numero massimo di slot disponibili
Puoi calcolare il numero massimo di slot che una prenotazione può utilizzare aggiungendo gli slot di riferimento, il numero massimo di slot a scalabilità automatica ed eventuali slot negli impegni creati con la stessa versione e non coperti dagli slot di riferimento. L'esempio nell'immagine precedente è configurato come segue:
- Un impegno di capacità di 1000 slot annuali. Questi slot vengono assegnati come slot di riferimento nella prenotazione
etle nella prenotazionedashboard. - 700 slot di riferimento assegnati alla prenotazione
etl. - 300 slot di riferimento assegnati alla prenotazione
dashboard. - Slot a scalabilità automatica di 600 per la prenotazione
etl. - Slot a scalabilità automatica di 800 per la prenotazione
dashboard.
Per la prenotazione etl, il numero massimo di slot possibile è pari a etl slot di riferimento (700) più dashboard slot di riferimento (300, se tutti gli slot sono inattivi) più al numero massimo di slot a scalabilità automatica (600). Pertanto, il numero massimo di slot che la prenotazione etl potrebbe utilizzare in questo esempio è 1600. Questo numero supera il numero indicato nell'impegno di capacità.
Nell'esempio seguente, l'impegno annuale supera gli slot di riferimento assegnati.
In questo esempio abbiamo:
- Un impegno di capacità di 1600 slot annuali.
- Una dimensione massima della prenotazione di 1500 (inclusi 500 slot con scalabilità automatica).
- 1000 slot di riferimento assegnati alla prenotazione
etl.
Il numero massimo di slot disponibili per la prenotazione è uguale agli slot di base (1000) più eventuali slot inattivi impegnati non dedicati agli slot di riferimento (1600 slot annuali - 1000 slot di riferimento = 600) più il numero di slot a scalabilità automatica (500). Il numero massimo di slot potenziali in questa prenotazione è 2100. Gli slot con scalabilità automatica sono slot aggiuntivi al di sopra dell'impegno di capacità.
Best practice per la scalabilità automatica
Quando utilizzi per la prima volta il gestore della scalabilità automatica, imposta il numero di slot con scalabilità automatica su un numero significativo in base alle prestazioni passate e previste. Una volta creata la prenotazione, monitora attivamente la percentuale di errori, le prestazioni e la fatturazione, nonché modifica il numero di slot con scalabilità automatica in base alle esigenze.
Tieni presente che ti viene addebitato l'importo dovuto per il numero di slot scalati, non per il numero di slot utilizzati. Se il numero massimo di slot a scalabilità automatica è troppo elevato, BigQuery potrebbe scalare di più del necessario, comportando costi aggiuntivi.
Ad esempio, un job potrebbe utilizzare un numero elevato di slot in un determinato punto, ma avere bisogno solo di un numero significativamente inferiore. Poiché BigQuery non rilascia immediatamente gli slot inutilizzati, ciò potrebbe comportare un minore utilizzo se nessun altro job nella prenotazione può utilizzare quegli slot. Riducendo il valore massimo per una prenotazione, si riduce il numero massimo di slot allocati, il che riduce i picchi di utilizzo degli slot del job e migliora l'utilizzo degli slot allocati per i job.
A volte l'utilizzo degli slot può superare la somma del riferimento più gli slot scalati. Non ti viene fatturato alcun costo per l'utilizzo degli slot superiore alla base di riferimento più gli slot scalati.
Il gestore della scalabilità automatica è più efficiente per carichi di lavoro impegnativi e a lunga esecuzione, come i carichi di lavoro con più query in parallelo. Evita di inviare query una alla volta al backend, indipendentemente dal fatto che queste query utilizzino o meno la cache. Senza slot di riferimento, ciò potrebbe causare una scalabilità imprevista e comportare costi elevati.
La scalabilità automatica di BigQuery è soggetta alla disponibilità della capacità. BigQuery tenta di soddisfare la domanda di capacità dei clienti sulla base dell'utilizzo storico. Per raggiungere le garanzie di capacità, puoi impostare una base di slot facoltativa, ovvero il numero di slot garantiti in una prenotazione. Con le basi, gli slot sono immediatamente disponibili e puoi pagarli indipendentemente dal loro utilizzo o meno. Per garantire che la capacità sia disponibile per richieste inorganiche elevate, ad esempio festività con traffico elevato, contatta il team di BigQuery con qualche settimana di anticipo.
Gli slot di riferimento vengono sempre addebitati. Se un impegno di capacità scade, potrebbe essere necessario modificare manualmente la quantità di slot di riferimento nelle prenotazioni per evitare addebiti indesiderati. Ad esempio, considera di avere un impegno di 1 anno con 100 slot e una prenotazione con 100 slot di riferimento. L'impegno scade e non ha un piano di rinnovo. Una volta scaduto l'impegno, paghi per 100 slot di riferimento con la tariffa di pagamento a consumo.
Monitora la scalabilità automatica
Quando monitori l'utilizzo degli slot con i grafici delle risorse amministrative, potresti notare un numero notevolmente maggiore di slot in scala rispetto all'utilizzo degli slot, perché i grafici riducono il numero di slot utilizzati nel periodo di allineamento. Per visualizzare l'utilizzo degli slot a scalabilità automatica con dettagli più precisi, riduci l'opzione dell'intervallo di tempo. In questo modo, il periodo di allineamento viene aggiornato automaticamente a un incremento minore.
Nell'esempio seguente, il grafico mostra un numero notevolmente maggiore di slot con scalabilità rispetto a quanto richiesto dal carico di lavoro.
Tuttavia, se riduci l'opzione dell'intervallo di tempo in modo che il periodo di allineamento sia di due secondi, puoi vedere che il gestore della scalabilità automatica scala in base alla domanda dei carichi di lavoro e visualizza dati più accurati. Per modificare l'opzione dell'intervallo di tempo, trascina gli intervalli di inizio e di fine dell'opzione. Per visualizzare i dati più precisi sulla domanda del carico di lavoro, seleziona p90 o p99 dall'elenco Metrica.
Per una visualizzazione più accurata dell'utilizzo della scalabilità automatica, utilizza un periodo di allineamento compreso tra 1 e 15 secondi. Per ulteriori informazioni sul periodo di allineamento dei grafici delle risorse amministrative, consulta l'opzione Intervallo di tempo.
Per informazioni sulla visualizzazione dell'utilizzo degli slot, consulta Visualizzare i grafici delle risorse amministrative
Monitora la scalabilità automatica con lo schema di informazioni
Puoi utilizzare i seguenti script SQL per controllare i secondi di slot fatturati per una determinata versione. Devi eseguire questi script nello stesso progetto in cui sono state create le prenotazioni. Il primo script mostra i secondi di slot fatturati coperti da commitment_plan, mentre il secondo mostra i secondi di slot fatturati che non sono coperti da un impegno.
Per eseguire questi script devi solo impostare il valore di tre variabili:
start_timeend_timeedition_to_check
Questi script sono soggetti alle seguenti avvertenze:
Le prenotazioni e gli impegni di capacità eliminati vengono rimossi dalle visualizzazioni dello schema di informazioni al termine del periodo di conservazione dei dati. Per ottenere risultati corretti, specifica un periodo di tempo recente che non contiene prenotazioni e impegni eliminati.
Il risultato degli script potrebbe non corrispondere esattamente alla fattura a causa di piccoli errori di arrotondamento.
Lo script seguente controlla l'utilizzo degli slot coperto dagli impegni per una determinata versione.
Espandi per visualizzare lo script per gli scenari coperti
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
Lo script seguente controlla l'utilizzo degli slot non coperto dagli impegni per una determinata versione. Questo utilizzo contiene due tipi di slot: slot scalati e slot di riferimento non coperti dagli impegni.
Espandi per visualizzare lo script per gli scenari non coperti
/*
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
Quote
La somma delle dimensioni massime della prenotazione non deve superare la quota di slot.
Per informazioni sulle quote, consulta Quote e limiti.
Passaggi successivi
- Per ulteriori informazioni sulle versioni di BigQuery, consulta Introduzione a BigQuery.
- Per ulteriori informazioni sugli slot, consulta Informazioni sugli slot.
- Per ulteriori informazioni sulle prenotazioni, consulta l'introduzione alle prenotazioni.