SEARCH_INDEX_COLUMN_OPTIONS view

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

TheINFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONS view contains one row foreach option set on a search-indexed column in the tables in a dataset.

Required permissions

To seesearch index metadata, you need thebigquery.tables.get orbigquery.tables.list Identity and Access Management (IAM)permission on the table with the index. Each of the following predefinedIAM roles includes at least one of these permissions:

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

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

TheINFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONS view has the followingschema:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must have adataset qualifier. Thefollowing table explains the region scope for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

Example

-- Returns metadata for search index column options in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONS;

Example

The following example sets the default index column granularity toCOLUMN, andindividually sets the granularity forcol2 andcol3 toGLOBAL andCOLUMNrespectively. In this example, columnscol2 andcol3 appear in the resultsbecause their granularity is set explicitly. The granularity for columncol1 is not shown because it uses the default granularity.

CREATESEARCHINDEXindex1ON`mydataset.table1`(ALLCOLUMNSWITHCOLUMNOPTIONS(col2OPTIONS(index_granularity='GLOBAL'),col3OPTIONS(index_granularity='COLUMN')))OPTIONS(default_index_column_granularity='COLUMN');SELECTindex_column_name,option_name,option_type,option_valueFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONSWHEREindex_schema='mydataset'ANDindex_name='index1'ANDtable_name='table1';

The result is similar to the following:

+-------------------+-------------------+---------------+--------------+| index_column_name |  option_name      | option_type   | option_value |+-------------------+-------------------+---------------+--------------+| col2              | index_granularity | STRING        | GLOBAL       || col3              | index_granularity | STRING        | COLUMN       |+-------------------+-------------------+---------------+--------------+

The following equivalent example, which doesn't useALL COLUMNS, sets thedefault index column granularity toCOLUMN and individually sets the granularity for two columns toGLOBAL andCOLUMN respectively:

CREATESEARCHINDEXindex1ON`mydataset.table1`(col1,col2OPTIONS(index_granularity='GLOBAL'),col3OPTIONS(index_granularity='COLUMN'))OPTIONS(default_index_column_granularity='COLUMN');SELECTindex_column_name,option_name,option_type,option_valueFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONSWHEREindex_schema='mydataset'ANDindex_name='index1'ANDtable_name='table1';

The result is similar to the following:

+-------------------+-------------------+---------------+--------------+| index_column_name |  option_name      | option_type   | option_value |+-------------------+-------------------+---------------+--------------+| col2              | index_granularity | STRING        | GLOBAL       || col3              | index_granularity | STRING        | COLUMN       |+-------------------+-------------------+---------------+--------------+