SELECTres.period_start,SUM(jobs.period_slot_ms)/1000/60ASperiod_slot_minutes,ANY_VALUE(res.slots_assigned)ASrough_slots_assigned,ANY_VALUE(res.slots_max_assigned)ASrough_slots_max_assignedFROM`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATIONjobsJOIN`region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINEresONTIMESTAMP_TRUNC(jobs.period_start,MINUTE)=res.period_startANDjobs.reservation_id=res.reservation_idWHEREjobs.job_creation_timeBETWEENTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1DAY)ANDCURRENT_TIMESTAMP()ANDres.reservation_id='YOUR_RESERVATION_ID'AND(jobs.statement_type!="SCRIPT"ORjobs.statement_typeISNULL)-- Avoid duplicate byte counting in parent and children jobs.GROUPBYperiod_startORDERBYperiod_startDESC;
SELECTres.period_start,res.reservation_id,SUM(jobs.period_slot_ms)/1000/60ASperiod_slot_minutes,ANY_VALUE(res.slots_assigned)ASrough_slots_assigned,ANY_VALUE(res.slots_max_assigned)ASrough_slots_max_assigned,FROM`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATIONjobsJOIN`region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINEresONTIMESTAMP_TRUNC(jobs.period_start,MINUTE)=res.period_startANDjobs.reservation_id=res.reservation_idWHEREjobs.job_creation_timeBETWEENTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1DAY)ANDCURRENT_TIMESTAMP()AND(jobs.statement_type!="SCRIPT"ORjobs.statement_typeISNULL)-- Avoid duplicate byte counting in parent and children jobs.GROUPBYperiod_start,reservation_idORDERBYperiod_startDESC,reservation_id;
[[["易于理解","easyToUnderstand","thumb-up"],["解决了我的问题","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["很难理解","hardToUnderstand","thumb-down"],["信息或示例代码不正确","incorrectInformationOrSampleCode","thumb-down"],["没有我需要的信息/示例","missingTheInformationSamplesINeed","thumb-down"],["翻译问题","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["最后更新时间 (UTC)：2025-08-28。"],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.RESERVATIONS_TIMELINE\u003c/code\u003e view provides near real-time, minute-by-minute slices of reservation metadata for each reservation administration project.\u003c/p\u003e\n"],["\u003cp\u003eThis view can be joined with the jobs timeline to compare slot usage against slot capacity.\u003c/p\u003e\n"],["\u003cp\u003eQueries on this view return one row per minute for every BigQuery reservation within the last 180 days and one row per minute for reservation changes older than 180 days.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_PROJECT\u003c/code\u003e view contains details such as \u003ccode\u003eautoscale\u003c/code\u003e, \u003ccode\u003eedition\u003c/code\u003e, \u003ccode\u003eignore_idle_slots\u003c/code\u003e, \u003ccode\u003eperiod_start\u003c/code\u003e, \u003ccode\u003eproject_id\u003c/code\u003e, \u003ccode\u003ereservation_id\u003c/code\u003e, and slot-related information.\u003c/p\u003e\n"],["\u003cp\u003eYou must specify a region qualifier when querying the \u003ccode\u003eINFORMATION_SCHEMA.RESERVATIONS_TIMELINE\u003c/code\u003e view, ensuring the query execution location matches the view's region.\u003c/p\u003e\n"]]],[],null,["# RESERVATIONS_TIMELINE view\n==========================\n\nThe `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` view shows near real-time\ntime slices of reservation metadata for each reservation administration project\nfor every minute.\n| **Caution:** To compare slot usage to slot capacity, you can combine the reservation information with the jobs timeline by using the reservation timeline view. However, the results from using this method can be inaccurate. The `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` view displays reservation metadata with minute-level granularity. If the reservation changes more than once within a minute, the view only displays the largest value for that minute. For example, if your [autoscaling](/bigquery/docs/slots-autoscaling-intro) reservation scales first from 0 to 100 slots, and then from 100 to 200 slots within the same minute, the value of `autoscale.current_slots` is 200 for that minute. Using this method, the value of `slot_capacity` might be considerably larger than the true value. For information about more granular monitoring of slots, see [Monitoring autoscaling with information\n| schema](/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema).\n\nRequired permission\n-------------------\n\nTo query the `INFORMATION_SCHEMA.RESERVATION_TIMELINE` view, you need\nthe `bigquery.reservations.list` Identity and Access Management (IAM) permission on the\nproject.\nEach of the following predefined IAM roles includes the required\npermission:\n\n- BigQuery Resource Admin (`roles/bigquery.resourceAdmin`)\n- BigQuery Resource Editor (`roles/bigquery.resourceEditor`)\n- BigQuery Resource Viewer (`roles/bigquery.resourceViewer`)\n- BigQuery User (`roles/bigquery.user`)\n- BigQuery Admin (`roles/bigquery.admin`)\n\nFor more information about BigQuery permissions, see\n[BigQuery IAM roles and permissions](/bigquery/docs/access-control).\n\nSchema\n------\n\nWhen you query the `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_*` views, the query\nresults contain one row for every minute of every BigQuery\nreservation in the last 180 days, and one row for every minute with reservation\nchanges for any occurrences older than 180 days. Each period starts on a whole-minute\ninterval and lasts exactly one minute.\n\nThe `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_PROJECT` view has the following schema:\n\nScope and syntax\n----------------\n\nQueries against this view must include a [region qualifier](/bigquery/docs/information-schema-intro#syntax).\nIf you don't specify a regional qualifier, metadata is retrieved from all\nregions. The following table explains the region and resource scope for this view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: any [dataset region name](/bigquery/docs/locations). For example, ```region-us```.\n\n \u003cbr /\u003e\n\n \u003cbr /\u003e\n\n | **Note:** You must use [a region qualifier](/bigquery/docs/information-schema-intro#region_qualifier) to query `INFORMATION_SCHEMA` views. The location of the query execution must match the region of the `INFORMATION_SCHEMA` view.\n\n\u003cbr /\u003e\n\nExamples\n--------\n\n#### Example: See total slot usage per minute\n\nTo run the query against a project other than your default project, add the\nproject ID in the following format: \n\n```bash\n`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION\n```\n. For example, ```myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION``.\n\n\u003cbr /\u003e\n\n| **Caution:** To compare slot usage to slot capacity, you can combine the reservation information with the jobs timeline by using the reservation timeline view. However, the results from using this method can be inaccurate. The `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` view displays reservation metadata with minute-level granularity. If the reservation changes more than once within a minute, the view only displays the largest value for that minute. For example, if your [autoscaling](/bigquery/docs/slots-autoscaling-intro) reservation scales first from 0 to 100 slots, and then from 100 to 200 slots within the same minute, the value of `autoscale.current_slots` is 200 for that minute. Using this method, the value of `slot_capacity` might be considerably larger than the true value. For information about more granular monitoring of slots, see [Monitoring autoscaling with information\n| schema](/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema).\n\nThe following example shows per-minute slot usage from projects assigned to\n\u003cvar translate=\"no\"\u003eYOUR_RESERVATION_ID\u003c/var\u003e across all jobs: \n\n```googlesql\nSELECT\n res.period_start,\n SUM(jobs.period_slot_ms) / 1000 / 60 AS period_slot_minutes,\n ANY_VALUE(res.slots_assigned) AS rough_slots_assigned,\n ANY_VALUE(res.slots_max_assigned) AS rough_slots_max_assigned\nFROM\n `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs\nJOIN\n `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res\n ON\n TIMESTAMP_TRUNC(jobs.period_start, MINUTE) = res.period_start\n AND jobs.reservation_id = res.reservation_id\nWHERE\n jobs.job_creation_time\n BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)\n AND CURRENT_TIMESTAMP()\n AND res.reservation_id = '\u003cvar translate=\"no\"\u003eYOUR_RESERVATION_ID\u003c/var\u003e'\n AND (jobs.statement_type != \"SCRIPT\" OR jobs.statement_type IS NULL) -- Avoid duplicate byte counting in parent and children jobs.\nGROUP BY\n period_start\nORDER BY\n period_start DESC;\n```\n\nThe result is similar to the following: \n\n```\n+-----------------------+---------------------+---------------------+-------------------------+\n| period_start | period_slot_minutes | rough_slots_assigned| rough_slots_max_assigned|\n+-----------------------+---------------------+---------------------+-------------------------+\n|2021-06-08 21:33:00 UTC| 100.000 | 100 | 100 |\n|2021-06-08 21:32:00 UTC| 96.753 | 100 | 100 |\n|2021-06-08 21:31:00 UTC| 41.668 | 100 | 100 |\n+-----------------------+---------------------+---------------------+-------------------------+\n```\n\n#### Example: Slot usage by reservation\n\nThe following example shows per-minute slot usage for each reservation in the\nlast day:\n| **Caution:** To compare slot usage to slot capacity, you can combine the reservation information with the jobs timeline by using the reservation timeline view. However, the results from using this method can be inaccurate. The `INFORMATION_SCHEMA.RESERVATIONS_TIMELINE` view displays reservation metadata with minute-level granularity. If the reservation changes more than once within a minute, the view only displays the largest value for that minute. For example, if your [autoscaling](/bigquery/docs/slots-autoscaling-intro) reservation scales first from 0 to 100 slots, and then from 100 to 200 slots within the same minute, the value of `autoscale.current_slots` is 200 for that minute. Using this method, the value of `slot_capacity` might be considerably larger than the true value. For information about more granular monitoring of slots, see [Monitoring autoscaling with information\nschema](/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema). \n\n```googlesql\nSELECT\n res.period_start,\n res.reservation_id,\n SUM(jobs.period_slot_ms) / 1000 / 60 AS period_slot_minutes,\n ANY_VALUE(res.slots_assigned) AS rough_slots_assigned,\n ANY_VALUE(res.slots_max_assigned) AS rough_slots_max_assigned,\nFROM\n `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs\nJOIN\n `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res\n ON TIMESTAMP_TRUNC(jobs.period_start, MINUTE) = res.period_start\n AND jobs.reservation_id = res.reservation_id\nWHERE\n jobs.job_creation_time\n BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)\n AND CURRENT_TIMESTAMP()\n AND (jobs.statement_type != \"SCRIPT\" OR jobs.statement_type IS NULL) -- Avoid duplicate byte counting in parent and children jobs.\nGROUP BY\n period_start,\n reservation_id\nORDER BY\n period_start DESC,\n reservation_id;\n```\n\nThe result is similar to the following: \n\n```\n+-----------------------+----------------+---------------------+----------------------+--------------------------+\n| period_start | reservation_id | period_slot_minutes | rough_slots_assigned | rough_slots_max_assigned |\n+-----------------------+----------------+---------------------+----------------------+--------------------------+\n|2021-06-08 21:33:00 UTC| prod01 | 100.000 | 100 | 100 |\n|2021-06-08 21:33:00 UTC| prod02 | 177.201 | 200 | 500 |\n|2021-06-08 21:32:00 UTC| prod01 | 96.753 | 100 | 100 |\n|2021-06-08 21:32:00 UTC| prod02 | 182.329 | 200 | 500 |\n+-----------------------+----------------+---------------------+----------------------+--------------------------+\n```"]]
