SEARCH_INDEX_COLUMNS view

The INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view contains one row for each search-indexed column on each table in a dataset.

Required permissions

To see search index metadata, you need the bigquery.tables.get or bigquery.tables.list Identity and Access Management (IAM) permission on the table with the index. Each of the following predefined IAM 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, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view, the query results contain one row for each indexed column on each table in a dataset.

The INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view has the following schema:

Column name	Data type	Value
`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 index.
`index_column_name`	`STRING`	The name of the top-level indexed column.
`index_field_path`	`STRING`	The full path of the expanded indexed field, starting with the column name. Fields are separated by a period.

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

Scope and syntax

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

View Name	Resource scope	Region scope
`[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS`	Dataset level	Dataset location

Replace the following:

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, see Dataset qualifier.

Example

-- Returns metadata for search indexes in a single dataset.
SELECT*FROMmyDataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS;

Examples

The following example creates a search index on all columns of my_table.

CREATETABLEdataset.my_table(
aSTRING,
bINT64,
cSTRUCT<dINT64,
eARRAY<STRING>,
fSTRUCT<gSTRING,hINT64>>)AS
SELECT'hello'ASa,10ASb,(20,['x','y'],('z',30))ASc;
CREATESEARCHINDEXmy_index
ONdataset.my_table(ALLCOLUMNS);

The following query extracts information on which fields are indexed. The index_field_path indicates which field of a column is indexed. This differs from the index_column_name only in the case of a STRUCT, where the full path to the indexed field is given. In this example, column c contains an ARRAY<STRING> field e and another STRUCT called f which contains a STRING field g, each of which is indexed.

SELECTtable_name,index_name,index_column_name,index_field_path
FROMmy_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

The result is similar to the following:

+------------+------------+-------------------+------------------+
| table_name | index_name | index_column_name | index_field_path |
+------------+------------+-------------------+------------------+
| my_table | my_index | a | a |
| my_table | my_index | c | c.e |
| my_table | my_index | c | c.f.g |
+------------+------------+-------------------+------------------+

The following query joins the INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS view with the INFORMATION_SCHEMA.SEARCH_INDEXES and INFORMATION_SCHEMA.COLUMNS views to include the search index status and the data type of each column:

SELECT
index_columns_view.index_catalogASproject_name,
index_columns_view.index_SCHEMAASdataset_name,
indexes_view.TABLE_NAMEAStable_name,
indexes_view.INDEX_NAMEASindex_name,
indexes_view.INDEX_STATUSASstatus,
index_columns_view.INDEX_COLUMN_NAMEAScolumn_name,
index_columns_view.INDEX_FIELD_PATHASfield_path,
columns_view.DATA_TYPEASdata_type
FROM
mydataset.INFORMATION_SCHEMA.SEARCH_INDEXESindexes_view
INNERJOIN
mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNSindex_columns_view
ON
indexes_view.TABLE_NAME=index_columns_view.TABLE_NAME
ANDindexes_view.INDEX_NAME=index_columns_view.INDEX_NAME
LEFTOUTERJOIN
mydataset.INFORMATION_SCHEMA.COLUMNScolumns_view
ON
indexes_view.INDEX_CATALOG=columns_view.TABLE_CATALOG
ANDindexes_view.INDEX_SCHEMA=columns_view.TABLE_SCHEMA
ANDindex_columns_view.TABLE_NAME=columns_view.TABLE_NAME
ANDindex_columns_view.INDEX_COLUMN_NAME=columns_view.COLUMN_NAME
ORDERBY
project_name,
dataset_name,
table_name,
column_name;

The result is similar to the following:

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| project | dataset | table | index_name | status | column_name | field_path | data_type |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| my_project | my_dataset | my_table | my_index | ACTIVE | a | a | STRING |
| my_project | my_dataset | my_table | my_index | ACTIVE | c | c.e | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
| my_project | my_dataset | my_table | my_index | ACTIVE | c | c.f.g | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+