Manage search indexes

A search index is a data structure designed to enable veryefficient search with theSEARCH function. A searchindex can also optimize some queries that usesupported functions and operators.

Much like the index you'd find in the back of a book, a searchindex for a column of string data acts like an auxiliary table that has onecolumn for unique words and another for where in the data those words occur.

Create a search index

To create a search index, use theCREATE SEARCH INDEXDDL statement. To specify primitive data types to be indexed, seeCreate a search index and specify the columns and data types. If you don't specify any data types, then by default, BigQuery indexes columns of the following types that containSTRING data:

• STRING
• ARRAY<STRING>
• STRUCT containing at least one nested field of typeSTRING orARRAY<STRING>
• JSON

When you create a search index, you can specify the type oftext analyzerto use. The text analyzer controls how data is tokenized for indexing andsearching. The default isLOG_ANALYZER. This analyzer works well for machinegenerated logs and has special rules around tokens commonly found inobservability data, such as IP addresses or emails. Use theNO_OP_ANALYZERwhen you have pre-processed data that you want to match exactly.PATTERN_ANALYZER extracts tokens from text using a regular expression.

Create a search index with the default text analyzer

In the following example, a search index is created on columnsa andc ofsimple_table and uses theLOG_ANALYZER text analyzer by default:

CREATETABLEdataset.simple_table(aSTRING,bINT64,cJSON);CREATESEARCHINDEXmy_indexONdataset.simple_table(a,c);

Create a search index on all columns with theNO_OP_ANALYZER analyzer

When you create a search index onALL COLUMNS, allSTRING orJSON data inthe table is indexed. If the table contains no such data, for example ifall columns contain integers, the index creation fails. When you specify aSTRUCT column to be indexed, all nested subfields are indexed.

In the following example, a search index is created ona,c.e, andc.f.g,and uses theNO_OP_ANALYZER text analyzer:

CREATETABLEdataset.my_table(aSTRING,bINT64,cSTRUCT<dINT64,eARRAY<STRING>,fSTRUCT<gSTRING,hINT64>>)ASSELECT'hello'ASa,10ASb,(20,['x','y'],('z',30))ASc;CREATESEARCHINDEXmy_indexONdataset.my_table(ALLCOLUMNS)OPTIONS(analyzer='NO_OP_ANALYZER');

Since the search index was created onALL COLUMNS, any columns added to thetable are automatically indexed if they containSTRING data.

Create a search index and specify the columns and data types

When you create a search index, you can specify data types to use. Data typescontrol types of columns and subfields ofJSON andSTRUCT columns forindexing. The default data type for indexing isSTRING. To create asearch index with more data types (for example, numeric types), use theCREATE SEARCH INDEX statement with thedata_typesoption included.

In the following example, a search index is created on columnsa,b,c andd of a table namedsimple_table. The supported column data types areSTRING,INT64, andTIMESTAMP.

CREATETABLEdataset.simple_table(aSTRING,bINT64,cJSON,dTIMESTAMP);CREATESEARCHINDEXmy_indexONdataset.simple_table(a,b,c,d)OPTIONS(data_types=['STRING','INT64','TIMESTAMP']);

Create a search index on all columns and specify the data types

When you create a search index onALL COLUMNS with thedata_types optionspecified, any column that matches one of the specified data types is indexed.ForJSON andSTRUCT columns, any nested subfield that matches one of thespecified data types is indexed.

In the following example, a search index is created onALL COLUMNS withdata types specified. Columnsa,b,c,d.e,d.f,d.g.h,d.g.iof a table namedmy_table is indexed:

CREATETABLEdataset.my_table(aSTRING,bINT64,cTIMESTAMP,dSTRUCT<eINT64,fARRAY<STRING>,gSTRUCT<hSTRING,iINT64>>)AS(SELECT'hello'ASa,10ASb,TIMESTAMP('2008-12-25 15:30:00 UTC')ASc,(20,['x','y'],('z',30))ASd;)CREATESEARCHINDEXmy_indexONdataset.my_table(ALLCOLUMNS)OPTIONS(data_types=['STRING','INT64','TIMESTAMP']);

Since the search index was created onALL COLUMNS, any columns added to thetable are automatically indexed if they match with any of the specified datatypes.

Index with column granularity

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.

When you create a search index, you can specify the column granularity for anindexed column. Column granularity lets BigQuery optimizecertain kinds of search queries by storing additional column information inyour search index. To set the column granularity for an indexed column, use theindex_granularity option in theindex_column_option_list when you run aCREATE SEARCH INDEX statement.

Internally, BigQuery tables are organized into files. When youcreate an index, BigQuery creates a mapping from tokens to thefiles that contain those tokens. When you run a search query,BigQuery scans all of the files that contain the tokens. Thismight be inefficient if your search token rarely appears in thecolumn that you're searching but is common in a different column.

For example, suppose you have the following table that containsjob postings:

CREATETABLEmy_dataset.job_postings(job_idINT64,company_nameSTRING,job_descriptionSTRING);

The wordskills probably appears frequently in thejob_description column,but rarely in thecompany_name column. Suppose you run the following query:

SELECT*FROMmy_dataset.job_postingsWHERESEARCH(company_name,'skills');

If you created a search index on thecolumnscompany_name andjob_description without specifying columngranularity, then BigQuery would scan every file in which thewordskills appears in either thejob_description orcompany_name column.To improve the performance of this query, you could set the column granularityforcompany_name toCOLUMN:

CREATESEARCHINDEXmy_indexONmy_dataset.job_postings(company_nameOPTIONS(index_granularity='COLUMN'),job_description);

Now when you run the query, BigQuery only scans the files inwhich the wordskills appears in thecompany_name column.

To see information about which options are set on the columns of an indexedtable, query theINFORMATION_SCHEMA.SEARCH_INDEX_COLUMN_OPTIONS view.

There are limits to the number of columns that you can index with columngranularity. For more information, seeQuotas and limits.

Understand index refresh

Search indexes are fully managed by BigQuery and automaticallyrefreshed when the table changes. An index full refresh can happen in thefollowing cases:

• Thepartition expirationis updated.
• An indexed column is updated due to atable schema change.
• The index is stale due to a lack ofBACKGROUND reservation slots forincremental refreshes. To prevent staleness, you can useautoscalingand monitor the workload to determine the best baseline and max reservationsize.

In case an indexed column's data is updated in every row, such as during abackfill operation, the whole index needs to be updated, equivalent to a fullrefresh. We recommend that you perform backfills slowly, such as partition bypartition, to minimize potential negative impact.

If you make any schema change to the base table that prevents an explicitlyindexed column from being indexed, then the index is permanently disabled.

If you delete the only indexed column in a table or rename the table itself,then the search index is deleted automatically.

Search indexes are designed for large tables. If you create a search index ona table that is smaller than 10GB, then the index is not populated. Similarly,if you delete data from an indexed table and the table size falls below 10GB,then the index is temporarily disabled. In this case, search queries do notuse the index and theIndexUnusedReason codeisBASE_TABLE_TOO_SMALL. This happens whether or not you use your ownreservation for your index-management jobs. When an indexed table's size exceeds10GB, then its index is populated automatically. You are not charged for storageuntil thesearch index is populated and active. Queries that use theSEARCHfunctionalways return correct results even if some data is not yet indexed.

Get information about search indexes

You can verify the existence and the readiness of a search index by queryingINFORMATION_SCHEMA. There are three views that contain metadata on searchindexes.

• TheINFORMATION_SCHEMA.SEARCH_INDEXES viewhas information on each search index created on a dataset.
• TheINFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS viewhas information on which columns of each table in the dataset are indexed.
• TheINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION viewhas information on search indexes for the whole organization associated with the current project.

INFORMATION_SCHEMA.SEARCH_INDEXES view examples

This section includes example queries of theINFORMATION_SCHEMA.SEARCH_INDEXES view.

The following example shows all active search indexes on tables in the datasetmy_dataset, located in the projectmy_project. It includes their names, theDDL statements used to create them, their coverage percentage, and theirtext analyzer. If an indexed base table isless than 10GB, then its index is not populated, in which casecoverage_percentage is 0.

SELECTtable_name,index_name,ddl,coverage_percentage,analyzerFROMmy_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXESWHEREindex_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   |+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view examples

This section includes example queries of theINFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view.

The following example creates a search index on all columns ofmy_table.

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

The following query extracts information on which fields are indexed.Theindex_field_path indicates which field of a column isindexed. This differs from theindex_column_name only in the case of aSTRUCT, where the full path to the indexed field is given. In this example,columnc contains anARRAY<STRING> fielde and anotherSTRUCT calledf which contains aSTRING fieldg, each of which is indexed.

SELECTtable_name,index_name,index_column_name,index_field_pathFROMmy_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 theINFORMATION_SCHEMA.SEARCH_INDEX_COUMNS view withtheINFORMATION_SCHEMA.SEARCH_INDEXES andINFORMATION_SCHEMA.COLUMNS viewsto include the search index status and the data type of each column:

SELECTindex_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_typeFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEXESindexes_viewINNERJOINmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNSindex_columns_viewONindexes_view.TABLE_NAME=index_columns_view.TABLE_NAMEANDindexes_view.INDEX_NAME=index_columns_view.INDEX_NAMELEFTOUTERJOINmydataset.INFORMATION_SCHEMA.COLUMNScolumns_viewONindexes_view.INDEX_CATALOG=columns_view.TABLE_CATALOGANDindexes_view.INDEX_SCHEMA=columns_view.TABLE_SCHEMAANDindex_columns_view.TABLE_NAME=columns_view.TABLE_NAMEANDindex_columns_view.INDEX_COLUMN_NAME=columns_view.COLUMN_NAMEORDERBYproject_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>> |+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view examples

This section includes example queries of theINFORMATION_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 anorganization, utilizing shared slots within the US multi-region, exceeds 100 TB:

WITHindexed_base_table_sizeAS(SELECTSUM(base_table.total_logical_bytes)AStotal_logical_bytesFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONASsearch_indexJOIN`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATIONASbase_tableON(search_index.table_name=base_table.table_nameANDsearch_index.project_id=base_table.project_idANDsearch_index.index_schema=base_table.table_schema)WHERETRUE-- Excludes search indexes that are permanently disabled.ANDsearch_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.ANDsearch_index.index_status_details.throttle_status!='BASE_TABLE_TOO_SMALL'-- Excludes search indexes whose project has BACKGROUND reservation purchased-- for search indexes.ANDsearch_index.use_background_reservation=false-- Outputs the total indexed base table size if it exceeds 100 TB,-- otherwise, doesn't return any output.)SELECT*FROMindexed_base_table_sizeWHEREtotal_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-regionwith the total size of indexed base tables:

SELECTsearch_index.project_id,search_index.use_background_reservation,SUM(base_table.total_logical_bytes)AStotal_logical_bytesFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONASsearch_indexJOIN`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATIONASbase_tableON(search_index.table_name=base_table.table_nameANDsearch_index.project_id=base_table.project_idANDsearch_index.index_schema=base_table.table_schema)WHERETRUE-- Excludes search indexes that are permanently disabled.ANDsearch_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.ANDsearch_index.index_status_details.throttle_status!='BASE_TABLE_TOO_SMALL'GROUPBYsearch_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 theorganization and region:

SELECTproject_id,index_schema,table_name,index_nameFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONWHERE-- Excludes search indexes that are permanently disabled.index_status!='PERMANENTLY DISABLED'ANDindex_status_details.throttle_statusIN('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      |+--------------------+--------------------+---------------+----------------+

Index management options

To create indexes and have BigQuery maintain them,you have two options:

• Use the default shared slot pool: When the data you planto index isbelow your per-organizationlimit, youcan use the free shared slot pool for index management.
• Use your own reservation:To achieve more predictable and consistent indexingprogress on your larger production workloads, you can use your ownreservations for index management.

Use shared slots

If you have not configured your project to use adedicated reservation for indexing,index management is handled in the free, shared slot pool, subject to thefollowing constraints.

If you add data to a table which causes the total size of indexedtables to exceed your organization'slimit,BigQuery pauses index managementfor that table. When this happens, theindex_status field in theINFORMATION_SCHEMA.SEARCH_INDEXES viewdisplaysPENDING DISABLEMENT and the index is queued for deletion. Whilethe index is pending disablement, it isstill used in queries and you are charged for the index storage.After the index is deleted, theindex_status field showsthe index asTEMPORARILY DISABLED. In this state, queries don't use the index,and you are not charged for index storage. In this case, theIndexUnusedReason codeisBASE_TABLE_TOO_LARGE.

If you delete data from the table and the total size of indexed tablesfalls below the per-organization limit, then index management is resumed. Theindex_status field in theINFORMATION_SCHEMA.SEARCH_INDEXESview isACTIVE, queries can use the index, and you are charged for theindex storage.

You can use theINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION viewto understand your current consumption towards the per-organization limit in agiven region, broken down by projects and tables.

BigQuery does not make guarantees about the availablecapacity of the shared pool or the throughput of indexing you see.For production applications, you might want to usededicated slots for your index processing.

Use your own reservation

Instead of using the default shared slot pool, you can optionally designate yourown reservation to index your tables. Using your own reservation ensurespredictable and consistent performance of index-management jobs, such ascreation, refresh, and background optimizations.

• There are no table size limits when an indexing job runs in yourreservation.
• Using your own reservation gives you flexibility in your index management.If you need to create a very large index ormake a major update to an indexed table, you can temporarily add moreslots to the assignment.

To index the tables in a project with a designated reservation,create a reservationin the region where your tables are located. Then, assign the project to thereservation with thejob_type set toBACKGROUND, which shares resourcesacross background optimization jobs:

bq mk \    --project_id=ADMIN_PROJECT_ID \    --location=LOCATION \    --reservation_assignment \    --reservation_id=RESERVATION_NAME \    --assignee_id=PROJECT_ID \    --job_type=BACKGROUND \    --assignee_type=PROJECT

View your indexing jobs

A new indexing job is created every time an index is created or updated ona single table. To view information about the job, query theINFORMATION_SCHEMA.JOBS* views. Youcan filter for indexing jobs bysettingjob_type IS NULL AND SEARCH(job_id, '`search_index`') in theWHEREclause of your query. The following example lists the five most recent indexingjobs in the projectmy_project:

SELECT*FROMregion-us.INFORMATION_SCHEMA.JOBSWHEREproject_id='my_project'ANDjob_typeISNULLANDSEARCH(job_id,'`search_index`')ORDERBYcreation_timeDESCLIMIT5;

SELECTTIMESTAMP_TRUNC(job.creation_time,DAY)ASusage_date,-- Aggregate total_slots_ms used for index-management jobs in a day and divide-- by the number of milliseconds in a day. This value is most accurate for-- days with consistent slot usage.SAFE_DIVIDE(SUM(job.total_slot_ms),(1000*60*60*24))ASaverage_daily_slot_usageFROM`region-us-west1`.INFORMATION_SCHEMA.JOBSjobWHEREproject_id='my_project'ANDjob_typeISNULLANDSEARCH(job_id,'`search_index`')GROUPBYusage_dateORDERBYusage_dateDESClimit30;

When there are insufficient slots to run index-management jobs, an index canbecome out of sync with its table and indexing jobs might fail.In this case, BigQuery rebuilds the index from scratch. Toavoid having an out-of-sync index, ensure you have enough slots to support indexupdates from data ingestion and optimization. For more information onmonitoring slot usage, seeadmin resource charts.

Best practices

• Search indexes are designed for large tables. The performance gains from asearch index increase with the size of the table.
• Don't index columns that contain only a very small number of unique values.
• Don't index columns that you never intend to use with theSEARCH functionor any of the othersupported functions and operators.
• Be careful when creating a search index onALL COLUMNS. Every time youadd a column containingSTRING orJSON data, it is indexed.
• You shoulduse your own reservation for indexmanagement in production applications. If you choose to usethe default shared slot pool for your index-management jobs, then theper-organization sizinglimits apply.

Delete a search index

When you no longer need a search index or want to change which columns areindexed on a table, you can delete the index currently on that table. Use theDROP SEARCH INDEX DDL statement.

If an indexed table is deleted, its index is deleted automatically.

Example:

DROPSEARCHINDEXmy_indexONdataset.simple_table;

What's next

• For an overview of search index use cases, pricing, required permissions, andlimitations, see theIntroduction to search inBigQuery.
• For information about efficient searching of indexed columns, seeSearch with an index.