Console
    Contact Us Start free

    SEARCH_INDEXES_BY_ORGANIZATION view

    BigQuery search indexes provide free index management until your organization reaches the limit in a given region. You can use the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view to understand your current consumption towards that limit, broken down by projects and tables. The INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view contains one row for each search index for the whole organization associated with the current project.

    Required permissions

    To query the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view, you need the following Identity and Access Management (IAM) permissions for your organization:

    • bigquery.tables.get
    • bigquery.tables.list

    Each of the following predefined IAM roles includes the preceding permissions:

    • roles/bigquery.admin
    • roles/bigquery.dataViewer
    • roles/bigquery.dataEditor
    • roles/bigquery.metadataViewer

    This schema view is only available to users with defined Google Cloud organizations .

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

    Schema

    The INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view has the following schema:

    Column name
    Data type
    Value
    project_id
    STRING
    The name of the project that contains the dataset.
    project_number
    STRING
    The project number that contains the dataset.
    index_catalog
    STRING
    The name of the project that contains the dataset.
    index_schema
    STRING
    The name of the dataset that contains the index.
    table_name
    STRING
    The name of the base table that the index is created on.
    index_name
    STRING
    The name of the search index.
    index_status
    STRING
    The status of the index can be one of the following:
    • ACTIVE : the index is usable or being created.
    • PENDING DISABLEMENT : the total size of indexed base tables exceeds your organization's limit ; the index is queued for deletion. While in this state, the index is usable in search queries and you are charged for the search index storage.
    • TEMPORARILY DISABLED : either the total size of indexed base tables exceeds your organization's limit , or the base indexed table is smaller than 10 GB. While in this state, the index is not used in search queries and you are not charged for the search index storage.
    • PERMANENTLY DISABLED : there is an incompatible schema change on the base table, such as changing the type of an indexed column from STRING to INT64 .
    index_status_details
    RECORD
    The record contains following fields:
    • throttle_status : indicates the throttle status of the search index, possible values are as follows:
      • UNTHROTTLED : the index is usable.
      • BASE_TABLE_TOO_SMALL : the base table size is smaller than 10 GB. This limit applies whether or not you use your own reservation for your index-management jobs. In this case, the index is temporarily disabled and search queries don't use the index.
      • BASE_TABLE_TOO_LARGE : the base table size exceeds your organization's limit .
      • ORGANIZATION_LIMIT_EXCEEDED : the total size of indexed base tables in your organization exceeds your organization's limit .
    • message : detailed message that describes the index status.
    use_background_reservation
    BOOL
    Indicates whether the index maintenance uses the BACKGROUND reservation. This is set to FALSE when the index maintenance uses the limit.

    Scope and syntax

    Queries against this view must include a region qualifier . The following table explains the region scope for this view:

    View name Resource scope Region scope
    [` PROJECT_ID `.]`region- REGION `.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION
    		Organization that contains the specified project REGION

    Replace the following:

    • Optional: PROJECT_ID : the ID of your Google Cloud project. If not specified, the default project is used.

    • REGION : the region for your project. For example, `myproject`.`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION .

    Index throttling

    If an index is throttled, its table size is not counted towards your organization's limit. This throttling occurs when the base table size falls under 10 GB or exceeds your organization's limit . When an index is throttled, its management jobs are paused, causing the index to become stale and eventually temporarily disabled. Consequently, search queries are unable to use the index.

    You can set up alerts to get notified when a certain threshold is exceeded, similar to setting up alerts for scheduled queries . For example, set up an alert when the table size exceeds 70% of the quota limit, so that you have time to act.

    Examples

    This section includes example queries of the INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view.

    Find if the consumption exceeds the limit in a given region

    The following example illustrates if the total indexed base table size across an organization, utilizing shared slots within the US multi-region, exceeds 100 TB:

     WITH 
  
 indexed_base_table_size 
  
 AS 
  
 ( 
  
 SELECT 
  
 SUM 
 ( 
 base_table 
 . 
 total_logical_bytes 
 ) 
  
 AS 
  
 total_logical_bytes 
  
 FROM 
  
 ` 
 region 
 - 
 us 
 ` 
 . 
 INFORMATION_SCHEMA 
 . 
 SEARCH_INDEXES_BY_ORGANIZATION 
  
 AS 
  
 search_index 
  
 JOIN 
  
 ` 
 region 
 - 
 us 
 ` 
 . 
 INFORMATION_SCHEMA 
 . 
 TABLE_STORAGE_BY_ORGANIZATION 
  
 AS 
  
 base_table 
  
 ON 
  
 ( 
 search_index 
 . 
 table_name 
  
 = 
  
 base_table 
 . 
 table_name 
  
 AND 
  
 search_index 
 . 
 project_id 
  
 = 
  
 base_table 
 . 
 project_id 
  
 AND 
  
 search_index 
 . 
 index_schema 
  
 = 
  
 base_table 
 . 
 table_schema 
 ) 
  
 WHERE 
  
 TRUE 
  
 -- Excludes search indexes that are permanently disabled. 
  
 AND 
  
 search_index 
 . 
 index_status 
  
 != 
  
 'PERMANENTLY DISABLED' 
  
 -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is 
  
 -- less than 10 GB. These tables don't count toward the limit. 
  
 AND 
  
 search_index 
 . 
 index_status_details 
 . 
 throttle_status 
  
 != 
  
 'BASE_TABLE_TOO_SMALL' 
  
 -- Excludes search indexes whose project has BACKGROUND reservation purchased 
  
 -- for search indexes. 
  
 AND 
  
 search_index 
 . 
 use_background_reservation 
  
 = 
  
 false 
  
 -- Outputs the total indexed base table size if it exceeds 100 TB, 
  
 -- otherwise, doesn't return any output. 
 ) 
 SELECT 
  
 * 
  
 FROM 
  
 indexed_base_table_size 
 WHERE 
  
 total_logical_bytes 
  
 >= 
  
 109951162777600 
  
 -- 100 TB

    The result is similar to the following:

    +---------------------+
| total_logical_bytes |
+---------------------+
|     109951162777601 |
+---------------------+

    Find total indexed base table size by projects in a region

    The following example gives the breakdown on each project in a US multi-region with the total size of indexed base tables:

     SELECT 
  
 search_index 
 . 
 project_id 
 , 
  
 search_index 
 . 
 use_background_reservation 
 , 
  
 SUM 
 ( 
 base_table 
 . 
 total_logical_bytes 
 ) 
  
 AS 
  
 total_logical_bytes 
 FROM 
  
 ` 
 region 
 - 
 us 
 ` 
 . 
 INFORMATION_SCHEMA 
 . 
 SEARCH_INDEXES_BY_ORGANIZATION 
  
 AS 
  
 search_index 
 JOIN 
  
 ` 
 region 
 - 
 us 
 ` 
 . 
 INFORMATION_SCHEMA 
 . 
 TABLE_STORAGE_BY_ORGANIZATION 
  
 AS 
  
 base_table 
 ON 
  
 ( 
 search_index 
 . 
 table_name 
  
 = 
  
 base_table 
 . 
 table_name 
  
 AND 
  
 search_index 
 . 
 project_id 
  
 = 
  
 base_table 
 . 
 project_id 
  
 AND 
  
 search_index 
 . 
 index_schema 
  
 = 
  
 base_table 
 . 
 table_schema 
 ) 
 WHERE 
  
 TRUE 
  
 -- Excludes search indexes that are permanently disabled. 
  
 AND 
  
 search_index 
 . 
 index_status 
  
 != 
  
 'PERMANENTLY DISABLED' 
  
 -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is 
  
 -- less than 10 GB. These tables don't count toward limit. 
  
 AND 
  
 search_index 
 . 
 index_status_details 
 . 
 throttle_status 
  
 != 
  
 'BASE_TABLE_TOO_SMALL' 
 GROUP 
  
 BY 
  
 search_index 
 . 
 project_id 
 , 
  
 search_index 
 . 
 use_background_reservation

    The result is similar to the following:

    +---------------------+----------------------------+---------------------+
|     project_id      | use_background_reservation | total_logical_bytes |
+---------------------+----------------------------+---------------------+
| projecta            |     true                   |     971329178274633 |
+---------------------+----------------------------+---------------------+
| projectb            |     false                  |     834638211024843 |
+---------------------+----------------------------+---------------------+
| projectc            |     false                  |     562910385625126 |
+---------------------+----------------------------+---------------------+

    Find throttled search indexes

    This following example returns all search indexes that are throttled within the organization and region:

     SELECT 
  
 project_id 
 , 
  
 index_schema 
 , 
  
 table_name 
 , 
  
 index_name 
 FROM 
  
 ` 
 region 
 - 
 us 
 ` 
 . 
 INFORMATION_SCHEMA 
 . 
 SEARCH_INDEXES_BY_ORGANIZATION 
 WHERE 
  
 -- Excludes search indexes that are permanently disabled. 
  
 index_status 
  
 != 
  
 'PERMANENTLY DISABLED' 
  
 AND 
  
 index_status_details 
 . 
 throttle_status 
  
 IN 
  
 ( 
 'ORGANIZATION_LIMIT_EXCEEDED' 
 , 
  
 'BASE_TABLE_TOO_LARGE' 
 )

    The result is similar to the following:

    +--------------------+--------------------+---------------+----------------+
|     project_id     |    index_schema    |  table_name   |   index_name   |
+--------------------+--------------------+---------------+----------------+
|     projecta       |     dataset_us     |   table1      |    index1      |
|     projectb       |     dataset_us     |   table1      |    index1      |
+--------------------+--------------------+---------------+----------------+
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: