SEARCH_INDEXES view
The INFORMATION_SCHEMA.SEARCH_INDEXES
view contains one row for each search
index 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 theINFORMATION_SCHEMA.SEARCH_INDEXES
view, the query results
contain one row for each search index in a dataset.
The INFORMATION_SCHEMA.SEARCH_INDEXES
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_status |
STRING |
The status of the index: ACTIVE , PENDING
DISABLEMENT , TEMPORARILY DISABLED , or
PERMANENTLY DISABLED .
|
creation_time |
TIMESTAMP |
The time the index was created. |
last_modification_time |
TIMESTAMP |
The last time the index configuration was modified. For example, deleting an indexed column. |
last_refresh_time |
TIMESTAMP |
The last time the table data was indexed. A NULL value
means the index is not yet available. |
disable_time |
TIMESTAMP |
The time the status of the index was set to DISABLED . The
value is NULL if the index status is not
DISABLED . |
disable_reason |
STRING |
The reason the index was disabled. NULL if the index
status is not DISABLED . |
DDL |
STRING |
The DDL statement used to create the index. |
coverage_percentage |
INTEGER |
The approximate percentage of table data that has been indexed. 0%
means the index is not usable in a SEARCH query, even if
some data has already been indexed. |
unindexed_row_count |
INTEGER |
The number of rows in the base table that have not been indexed. |
total_logical_bytes |
INTEGER |
The number of billable logical bytes for the index. |
total_storage_bytes |
INTEGER |
The number of billable storage bytes for the index. |
analyzer |
STRING |
The text analyzer to use to generate tokens for the search index. |
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_INDEXES |
Dataset level | Dataset location |
- 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 * FROM myDataset.INFORMATION_SCHEMA.SEARCH_INDEXES;
Example
The following example shows all active search indexes on tables in the dataset
my_dataset
, located in the project my_project
. It includes their names, the
DDL statements used to create them, their coverage percentage, and their
text analyzer. If an indexed base table is
less than 10GB, then its index is not populated, in which case
coverage_percentage
is 0.
SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';
The results should look like the following:
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+ | table_name | index_name | ddl | coverage_percentage | analyzer | +-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+ | small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names) | 0 | NO_OP_ANALYZER | | large_table | logs_index | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100 | LOG_ANALYZER | +-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+