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.
-- 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"] |
  +---------------------+--------------+----------------------+-------------------+