INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view

The INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view contains history of changes to the BI Engine capacity. If you want to view the current state of BI Engine reservation, see the INFORMATION_SCHEMA.BI_CAPACITIES view .

Required permission

To query the INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view, you need the bigquery.bireservations.get Identity and Access Management (IAM) permission for BI Engine reservations.

For more information about BigQuery permissions, see Access control with IAM .

Schema

When you query the INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view, the query results contain one row for each update of BI Engine capacity, including the current state.

The INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view has the following schema:

Column name	Data type	Value
`change_timestamp`	`TIMESTAMP`	Timestamp when the current update to BI Engine capacity was made.
`project_id`	`STRING`	The project ID of the project that contains BI Engine capacity.
`project_number`	`INTEGER`	The project number of the project that contains BI Engine capacity.
`bi_capacity_name`	`STRING`	The name of the object. Currently there can only be one capacity per project, hence the name is always `default` .
`size`	`INTEGER`	BI Engine RAM in bytes.
`user_email`	`STRING`	Email address of the user or subject of the workforce identity federation that made the change. `google` for changes made by Google. `NULL` if the email address is unknown.
`preferred_tables`	`REPEATED STRING`	The set of preferred tables this BI Engine capacity must be used for. If set to `null` , BI Engine capacity is used for all queries in the current project.

Syntax

Queries against this view must include a region qualifier . A project ID is optional. If no project ID is specified, the project that the query runs in is used.

View name	Resource scope	Region scope
[ PROJECT_ID .]`region- REGION `.INFORMATION_SCHEMA.BI_CAPACITY_CHANGES	Project level	`REGION`

Replace the following:

Optional: PROJECT_ID : the ID of your Google Cloud project. If not specified, the default project is used.
REGION : any dataset region name . For example, `region-us` .

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

  -- Returns the history of BI Engine capacity. 
 SELECT 
  
 * 
  
 FROM 
  
 myproject 
 . 
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 BI_CAPACITY_CHANGES 
 ;

Examples

The following example retrieves the current BI Engine capacity changes from the INFORMATION_SCHEMA.BI_CAPACITY_CHANGES view.

To run the query against a project other than the project that the query is running in, add the project ID to the region in the following format: ` project_id `.` region_id `.INFORMATION_SCHEMA.BI_CAPACITY_CHANGES .

The following example gets all changes made to BI Engine capacity by a user with email email@mycompanymail.com :

  SELECT 
  
 * 
 FROM 
  
 `my-project-id.region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 BI_CAPACITY_CHANGES 
 WHERE 
  
 user_email 
  
 = 
  
 "email@mycompanymail.com"

The result looks similar to the following:

+---------------------+---------------+----------------+------------------+--------------+---------------------+----------------------------------------------------------------------------------------+
  |  change_timestamp   |  project_id   | project_number | bi_capacity_name |     size     |     user_email      |                                               preferred_tables                         |
  +---------------------+---------------+----------------+------------------+--------------+---------------------+----------------------------------------------------------------------------------------+
  | 2022-06-14 02:22:18 | my-project-id |   123456789000 | default          | 268435456000 | email@mycompany.com | ["my-project-id.dataset1.table1","bigquery-public-data.chicago_taxi_trips.taxi_trips"] |
  | 2022-06-08 20:25:51 | my-project-id |   123456789000 | default          | 268435456000 | email@mycompany.com | ["bigquery-public-data.chicago_taxi_trips.taxi_trips"]                                 |
  | 2022-04-01 21:06:49 | my-project-id |   123456789000 | default          | 161061273600 | email@mycompany.com | [""]                                                                                   |
  +---------------------+---------------+----------------+------------------+--------------+---------------------+----------------------------------------------------------------------------------------+

The following example gets BI Engine capacity changes for the last seven days:

  SELECT 
  
 change_timestamp 
 , 
  
 size 
 , 
  
 user_email 
 , 
  
 preferred_tables 
 FROM 
  
 `my-project-id.region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 BI_CAPACITY_CHANGES 
 WHERE 
  
 change_timestamp 
 > 
 TIMESTAMP_SUB 
 ( 
 CURRENT_DATE 
 (), 
  
 INTERVAL 
  
 7 
  
 DAY 
 )

The result looks similar to the following:

+---------------------+--------------+----------------------+-------------------+
  |  change_timestamp   |     size     |     user_email       |  preferred_tables |                                                                                    |
  +---------------------+--------------+----------------------+-------------------+
  | 2023-07-08 18:25:09 | 268435456000 | sundar@mycompany.com | [""]              |
  | 2023-07-09 17:47:26 | 161061273600 | pichai@mycompany.com | ["pr.dataset.t1"] |
  +---------------------+--------------+----------------------+-------------------+