Monitorizar reservas de BigQuery
Como administrador de BigQuery, puede monitorizar las reservas de su proyecto consultando el uso de proyectos y de ranuras de reserva, así como su factura basada en la capacidad.
Ver el uso de proyectos y de franjas de reserva
Puedes ver el uso de proyectos y de franjas de reserva de las siguientes formas:
INFORMATION_SCHEMAvisualizaciones. Para obtener información sobre el uso de proyectos y reservas, consulta las vistasINFORMATION_SCHEMA.JOBS*.El campo
reservation_idde las vistasINFORMATION_SCHEMA.JOBS*contiene el nombre de la reserva.Google Cloud consola. La consola Google Cloud incluye gráficos que muestran el uso de las ranuras. Para obtener más información, consulta Usar gráficos de recursos administrativos.
Registros de auditoría. Usa los registros de auditoría para ver métricas sobre el uso de las ranuras.
El método
JobsUsa el método de APIJobspara ver las métricas sobre el uso de las ranuras de un trabajo.Cloud Monitoring. Puede usar Cloud Monitoring para crear paneles de control con los que monitorizar las ranuras asignadas. Con un panel de Cloud Monitoring, puedes ver el uso de ranuras de cada reserva y de cada tipo de trabajo en todos los proyectos de la reserva. Para obtener más información sobre las métricas disponibles en el panel de control de Cloud Monitoring, consulta Métricas disponibles para la visualización.
Ver tu factura basada en la capacidad
Para ver tu factura basada en la capacidad en tiempo real, sigue estos pasos:
En la Google Cloud consola, ve a la página Facturación.
Seleccione el proyecto de la cuenta de facturación cuya factura quiera ver.
Ve a la sección Informes y, en la sección Filtros, haz lo siguiente:
- En la lista Servicios, selecciona BigQuery y todo lo que corresponda.
- Seleccione Todos los SKUs en la lista SKUs.
Atribución de costes de reserva
Esta función te permite atribuir las tarifas de reserva a la consulta específica que se haya usado en cualquier proyecto que haya utilizado la reserva. De esta forma, se obtienen costes netos más precisos para cada base de proyecto.
Todos los clientes de la API Reservations de BigQuery tienen un elemento de línea "Atribución de ranuras de análisis" en sus datos de Facturación de Cloud. Este elemento de línea se incluye en la página Facturación y en la exportación de facturación de Cloud.
Esta línea de pedido muestra las horas de espacio publicitario utilizadas por proyecto. No tiene ningún coste y no afecta a los totales de tus facturas.
Registros de auditoría
La creación, la eliminación y la actualización de recursos relacionados con las reservas de BigQuery se registran en los registros de auditoría del propietario del proyecto. Para obtener más información, consulta el artículo Registro de auditoría.
Monitorizar el autoescalado con el esquema de información
Puede usar las siguientes secuencias de comandos SQL para comprobar los segundos de ranura facturados de una edición concreta. Debes ejecutar estas secuencias de comandos en el mismo proyecto en el que se crearon las reservas. El primer script muestra los segundos de espacio facturado cubiertos por commitment_plan, mientras que el segundo muestra los segundos de espacio facturado que no están cubiertos por un compromiso.
Solo tienes que definir el valor de tres variables para ejecutar estas secuencias de comandos:
start_timeend_timeedition_to_check
Estos scripts están sujetos a las siguientes advertencias:
Las reservas y los compromisos de capacidad eliminados se quitan de las vistas de información de esquema al final del periodo de conservación de datos. Especifica un periodo reciente que no contenga reservas ni compromisos eliminados para obtener resultados correctos.
Es posible que el resultado de las secuencias de comandos no coincida exactamente con la factura debido a pequeños errores de redondeo.
La siguiente secuencia de comandos agrega las ranuras de escalado automático por edición.
Despliega esta sección para ver la secuencia de comandos que calcula los segundos de espacio de escalado automático por edición.
SELECT edition, SUM(s.autoscale_current_slots) AS autoscale_slot_seconds FROM `region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` m JOIN m.per_second_details s WHERE period_start BETWEEN '2025-09-28' AND '2025-09-29' GROUP BY edition ORDER BY edition
La siguiente secuencia de comandos agrega las ranuras de escalado automático por reserva.
Despliega esta sección para ver la secuencia de comandos que calcula los segundos de ranura de escalado automático por reserva.
select reservation_id, sum(s.autoscale_current_slots) as autoscale_slot_seconds from `region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` m LEFT JOIN m.per_second_details s WHERE period_start between '2025-09-28' and '2025-09-29' group by reservation_id order by reservation_id
Despliega esta sección para ver la secuencia de comandos que calcula los segundos de espacio a partir de los compromisos.
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
La siguiente secuencia de comandos comprueba el uso de espacios publicitarios no cubierto por los compromisos de una edición concreta. Este uso contiene dos tipos de espacios publicitarios: espacios publicitarios escalados y espacios publicitarios de referencia que no están cubiertos por compromisos.
Despliega para ver la secuencia de comandos que calcula los segundos de espacio no cubiertos por los compromisos
/* 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
Siguientes pasos
- Consulta información sobre los planes de compromiso de capacidad.
- Consulte cómo usar los gráficos de recursos administrativos.
- Obtén más información sobre los precios de BigQuery.