Monitor BigQuery reservations

As a BigQuery administrator, you can monitor reservations in yourproject by viewing the project and reservation slot usage and also viewyour capacity-based bill.

View project and reservation slot usage

You can view the project and reservation slot usage in the following ways:

  • INFORMATION_SCHEMA views. To retrieve project and reservation usageinformation, query theINFORMATION_SCHEMA.JOBS* views.

    Thereservation_id fieldin theINFORMATION_SCHEMA.JOBS* views contains the reservation name.

  • Google Cloud console. The Google Cloud console includes charts thatdisplay slot usage. For more information, seeUse administrativeresource charts.

  • Audit logs. Useaudit logs toview metrics about slot usage.

  • TheJobs method. Use theJobs API methodto view metrics about slot usage for a job.

  • Cloud Monitoring. You can useCloud Monitoring to create dashboards to monitor your allocated slots. With a Cloud Monitoring dashboard, you can view your slot usage for each reservation and for each job type, across all projects within the reservation. For more information about the metrics available for the Cloud Monitoring dashboard, seeMetrics available for visualization.

    Reservations monitoring.

Note: The number of slots in use might appear higher than your reservation slotcount because of how BigQuery provisions resources toreservations. You are not charged for slots beyond your reservation slot count.

View your capacity-based bill

To view your capacity-based bill in real time, follow these steps:

  1. In the Google Cloud console, go to theBilling page.

    Go toBilling.

  2. Select the billing account project for which you want to view the bill.

  3. Navigate to theReports section and then in theFilters section, dothe following:

    1. From theServices list, selectBigQuery and select all that'sapplicable.
    2. SelectAll SKUs from theSKUs list.
Note: BigQuery annual and three-year commitments are priced bymonths. Your bill doesn't change due to variability in the month length.If your slot usage remains unchanged, the rate remains the same every month.

Reservation cost attribution

This feature lets you attribute reservation fees back to the specific queryusage across any projects that used the reservation. This results in moreaccurate net costs for each project basis.

AllBigQuery Reservations APIcustomers have an"Analysis Slots Attribution" line item in theirCloud Billing data. This line item is included on theBilling pageand in the Cloud Billing export.

This line item shows slot hours used per project. It incurs no cost and doesn'taffect your invoice totals.

Audit logs

Creating, deleting, and updating resources related toBigQuery reservations arerecorded in the project owner's audit logs.For more information, seeAudit log.

Monitor autoscaling with information schema

You can use the following SQL scripts to check the billed slot seconds for aparticular edition. You must run these scripts in the same project thereservations were created. The first script shows billed slot seconds covered bycommitment_plan while the second script shows billed slot seconds that aren'tcovered by a commitment.

You only need to set the value of three variables to run these scripts:

  • start_time
  • end_time
  • edition_to_check

These scripts are subject to the following caveats:

  • Deleted reservations and capacity commitments are removed from informationschema views at the end of the data retention period. Specify a recent window oftime which doesn't contain deleted reservations and commitments forcorrect results.

  • The result of the scripts may not exactly match the bill due to small roundingerrors.

The following script aggregates autoscaling slots per edition.

Expand to see the script to calculate autoscale slot seconds per edition.

SELECTedition,SUM(s.autoscale_current_slots)ASautoscale_slot_secondsFROM`region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE`mJOINm.per_second_detailssWHEREperiod_startBETWEEN'2025-09-28'AND'2025-09-29'GROUPBYeditionORDERBYedition

The following script aggregates autoscaling slots per reservation.

Expand to see the script to calculate autoscale slot seconds per reservation.

selectreservation_id,sum(s.autoscale_current_slots)asautoscale_slot_secondsfrom`region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE`mLEFTJOINm.per_second_detailssWHEREperiod_startbetween'2025-09-28'and'2025-09-29'groupbyreservation_idorderbyreservation_id
The following script checks the slot usage covered by commitments for aparticular edition.

Expand to see the script to calculate slot seconds from commitments.

DECLAREstart_time,end_timeTIMESTAMP;DECLAREedition_to_checkSTRING;/* 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 thebilling report. Change the start_time and end_time values to match the desiredwindow. *//* 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 shouldfollow this format: 2024-02-20 00:00:00-08. */SETstart_time="2023-07-20 00:00:00-07";SETend_time="2023-07-28 00:00:00-07";SETedition_to_check='ENTERPRISE';/* The following function returns the slot seconds for the time window betweentwo capacity changes. For example, if there are 100 slots between (2023-06-0110:00:00, 2023-06-01 11:00:00), then during that window the total slot secondswill 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_millisand script_end_timestamp_unix_millis. */CREATETEMPFUNCTIONGetSlotSecondsBetweenChanges(slotsFLOAT64,range_begin_timestamp_unix_millisFLOAT64,range_end_timestamp_unix_millisFLOAT64,script_start_timestamp_unix_millisFLOAT64,script_end_timestamp_unix_millisFLOAT64)RETURNSINT64LANGUAGEjsASr"""    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 happensbecause 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_planAS(SELECT*,IFNULL(LEAD(commitment_plan)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC),commitment_plan)next_plan,IFNULL(LEAD(change_timestamp)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC),change_timestamp)next_plan_change_timestampFROM`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_planAS(SELECTnext_plan_change_timestampASchange_timestamp,project_id,project_number,capacity_commitment_id,commitment_plan,state,slot_count,'DELETE'ASaction,commitment_start_time,commitment_end_time,failure_status,renewal_plan,user_email,edition,is_flat_rate,FROMcommitments_with_next_planWHEREcommitment_plan<>next_planUNIONALLSELECT*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_dataAS(SELECTchange_timestamp,capacity_commitment_id,CASEWHENaction="CREATE"ORaction="UPDATE"THENIFNULL(IF(LAG(action)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),slot_count-LAG(slot_count)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC),slot_count),slot_count)ELSEIF(LAG(action)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),-1*slot_count,0)ENDASslot_count_delta,commitment_planFROMcapacity_changes_with_additional_deleted_event_for_changed_planWHEREstate="ACTIVE"ANDedition=edition_to_checkANDchange_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_dataAS(SELECTchange_timestamp,SUM(slot_count_delta)OVER(PARTITIONBYcommitment_planORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScapacity_slot,commitment_plan,FROMcapacity_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_dataAS(SELECTchange_timestamp,GetSlotSecondsBetweenChanges(capacity_slot,UNIX_MILLIS(change_timestamp),UNIX_MILLIS(IFNULL(LEAD(change_timestamp)OVER(PARTITIONBYcommitment_planORDERBYchange_timestampASC),CURRENT_TIMESTAMP())),UNIX_MILLIS(start_time),UNIX_MILLIS(end_time))ASslot_seconds,commitment_plan,FROMrunning_capacity_commitment_slot_dataWHEREchange_timestamp<=end_time)/*The final result is similar to the following:+-----------------+--------------------+| commitment_plan | total_slot_seconds |+-----------------+--------------------+| ANNUAL          |           64617300 || MONTHLY         |               6000 || FLEX            |            5877300 |*/SELECTcommitment_plan,SUM(slot_seconds)AStotal_slot_secondsFROMslot_seconds_dataGROUPBYcommitment_plan

The following script checks the slot usage not covered by commitments for aparticular edition. This usage contains two types of slots, scaled slots andbaseline slots not covered by commitments.

Expand to see the script to calculate slot seconds not covered by commitments

/*This script has several parts:1. Calculate the baseline and scaled slots for reservations2. Calculate the committed slots3. Join the two results above to calculate the baseline not covered by committed   slots4. Aggregate the number*/-- variablesDECLAREstart_time,end_timeTIMESTAMP;DECLAREedition_to_checkSTRING;/* 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 thebilling report. Change the start_time and end_time values to match the desiredwindow. *//* 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 shouldfollow this format: 2024-02-20 00:00:00-08. */SETstart_time="2023-07-20 00:00:00-07";SETend_time="2023-07-28 00:00:00-07";SETedition_to_check='ENTERPRISE';/*The following function returns the slot seconds for the time window betweentwo capacity changes. For example, if there are 100 slots between (2023-06-0110:00:00, 2023-06-01 11:00:00), then during that window the total slot secondswill 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_millisand script_end_timestamp_unix_millis. */CREATETEMPFUNCTIONGetSlotSecondsBetweenChanges(slotsFLOAT64,range_begin_timestamp_unix_millisFLOAT64,range_end_timestamp_unix_millisFLOAT64,script_start_timestamp_unix_millisFLOAT64,script_end_timestamp_unix_millisFLOAT64)RETURNSINT64LANGUAGEjsASr"""    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_dataAS(SELECTchange_timestamp,reservation_name,CASEactionWHEN"CREATE"THENautoscale.current_slotsWHEN"UPDATE"THENIFNULL(autoscale.current_slots-LAG(autoscale.current_slots)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC),IFNULL(autoscale.current_slots,IFNULL(-1*LAG(autoscale.current_slots)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC),0)))WHEN"DELETE"THENIF(LAG(action)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),-1*autoscale.current_slots,0)ENDASautoscale_current_slot_delta,CASEactionWHEN"CREATE"THENslot_capacityWHEN"UPDATE"THENIFNULL(slot_capacity-LAG(slot_capacity)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC),IFNULL(slot_capacity,IFNULL(-1*LAG(slot_capacity)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC),0)))WHEN"DELETE"THENIF(LAG(action)OVER(PARTITIONBYproject_id,reservation_nameORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),-1*slot_capacity,0)ENDASbaseline_slot_delta,FROM`region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`WHEREedition=edition_to_checkANDchange_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_dataAS(SELECTchange_timestamp,SUM(autoscale_current_slot_delta)OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASautoscale_current_slots,SUM(baseline_slot_delta)OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASbaseline_slots,FROMreservation_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_dataAS(SELECTchange_timestamp,capacity_commitment_id,CASEWHENaction="CREATE"ORaction="UPDATE"THENIFNULL(IF(LAG(action)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),slot_count-LAG(slot_count)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC),slot_count),slot_count)ELSEIF(LAG(action)OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)INUNNEST(['CREATE','UPDATE']),-1*slot_count,0)ENDASslot_count_deltaFROM`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`WHEREstate="ACTIVE"ANDedition=edition_to_checkANDchange_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_dataAS(SELECTchange_timestamp,SUM(slot_count_delta)OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScapacity_slotFROMcapacity_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_changeAS(SELECTchange_timestamp,IFNULL(LEAD(change_timestamp)OVER(ORDERBYchange_timestampASC),CURRENT_TIMESTAMP())ASnext_change_timestamp,capacity_slotFROMrunning_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_timestampAS(SELECTchange_timestampFROMrunning_reservation_slot_dataUNIONDISTINCTSELECTchange_timestampFROMrunning_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_timestampAS(SELECTchange_timestamp,IFNULL(autoscale_current_slots,IFNULL(LAST_VALUE(autoscale_current_slotsIGNORENULLS)OVER(ORDERBYchange_timestampASC),0))ASautoscale_current_slots,IFNULL(baseline_slots,IFNULL(LAST_VALUE(baseline_slotsIGNORENULLS)OVER(ORDERBYchange_timestampASC),0))ASbaseline_slotsFROMrunning_reservation_slot_dataRIGHTJOINmerged_timestampUSING(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_commitmentsAS(SELECTr.change_timestamp,IFNULL(LEAD(r.change_timestamp)OVER(ORDERBYr.change_timestampASC),CURRENT_TIMESTAMP())ASnext_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)ASbaseline_not_covered_by_commitmentFROMrunning_reservation_slot_data_with_merged_timestamprLEFTJOINrunning_capacity_commitment_slot_data_with_next_changecONr.change_timestamp>=c.change_timestampANDr.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_dataAS(SELECTchange_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))ASslot_secondsFROMscaled_slots_and_baseline_not_covered_by_commitmentsWHEREchange_timestamp<=end_timeANDnext_change_timestamp>start_time)/*Final result for this example:+--------------------+| total_slot_seconds |+--------------------+|           13045560 |*/SELECTSUM(slot_seconds)AStotal_slot_secondsFROMslot_seconds_data

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.