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  |
  +---------------+---------+