INFORMATION_SCHEMA.BI_CAPACITIES view

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

Required permission

To query the INFORMATION_SCHEMA.BI_CAPACITIES 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_CAPACITIES view, the query results contain one row with current state of BI Engine capacity.

The INFORMATION_SCHEMA.BI_CAPACITIES view has the following schema:

Column name	Data type	Value
`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 set to `default`.
`size`	`INTEGER`	BI Engine RAM in bytes
`preferred_tables`	`REPEATED STRING`	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

Scope and 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_CAPACITIES	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.

Example

-- Returns current state of BI Engine capacity.
SELECT * FROM myproject.`region-us`.INFORMATION_SCHEMA.BI_CAPACITIES;

Examples

The following example retrieves current BI Engine capacity changes from INFORMATION_SCHEMA.BI_CAPACITIES 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_CAPACITIES.

The following example shows the current state of BI Engine in the project with id 'my-project-id':

SELECT *
FROM `my-project-id.region-us`.INFORMATION_SCHEMA.BI_CAPACITIES

The result looks similar to the following:

  +---------------+----------------+------------------+--------------+-----------------------------------------------------------------------------------------------+
  |  project_id   | project_number | bi_capacity_name |     size     |                                               preferred_tables                                |
  +---------------+----------------+------------------+--------------+-----------------------------------------------------------------------------------------------+
  | my-project-id |   123456789000 | default          | 268435456000 | "my-company-project-id.dataset1.table1","bigquery-public-data.chicago_taxi_trips.taxi_trips"] |
  +---------------+----------------+------------------+--------------+-----------------------------------------------------------------------------------------------+

The following example returns size of BI Engine capacity in gigabytes for the query project:

SELECT
  project_id,
  size/1024.0/1024.0/1024.0 AS size_gb
FROM `region-us`.INFORMATION_SCHEMA.BI_CAPACITIES

The result looks similar to the following:

  +---------------+---------+
  |  project_id   | size_gb |
  +---------------+---------+
  | my-project-id |  250.0  |
  +---------------+---------+