Introduction to search in BigQuery

Note: This feature may not be available when using reservations that are created with certain BigQuery editions. For more information about which features are enabled in each edition, seeIntroduction to BigQuery editions.

BigQuery search indexes let you use GoogleSQL toefficiently findunique data elements that are buried in unstructured text and semi-structuredJSON data, without having to know the table schemas in advance.

With search indexes, BigQuery provides a powerful columnar storeand text search in one platform, enabling efficient row lookups when you need tofind individual rows of data. A common use case is log analytics. For example,you might want to identify the rows of data associated with a user for GeneralData Protection Regulation (GDPR) reporting, or to find specific error codes ina text payload.

BigQuery stores and manages your indexes, so that when data becomesavailable in BigQuery, you can immediately retrieve it with theSEARCH functionorother operators and functions,such as the equal (=),IN, orLIKE operators and certain string and JSONfunctions. To optimize your searches, read aboutbest practices.

Important: Join theSearch discussion groupto post questions and comments, and to follow the latest updates.

Use cases

BigQuery search indexes help you perform the following tasks:

• Search system, network, or application logs stored in BigQuerytables.
• Identify data elements for deletion to comply with regulatory processes.
• Support developer troubleshooting.
• Perform security audits.
• Create a dashboard that requires highly selective search filters.
• Search pre-processed data for exact matches.

For more information, seeCreate a search index andSearch with an index.

Pricing

There is no charge for the processing required to build and refresh your searchindexes when the total size of indexed tables in your organization is belowyour region'slimit. To support indexing beyond this limit,you need toprovide your own reservationfor handling the index-management jobs.Search indexes incur storage costs when they are active.You can find the index storage size in theINFORMATION_SCHEMA.SEARCH_INDEXES view.

Roles and permissions

To create a search index, you need thebigquery.tables.createIndex IAM permissionon the table where you're creating the index. To drop a search index, you needthebigquery.tables.deleteIndex permission. Each of the following predefinedIAM roles includes the permissions that you need to work withsearch indexes:

• BigQuery Data Owner (roles/bigquery.dataOwner)
• BigQuery Data Editor (roles/bigquery.dataEditor)
• BigQuery Admin (roles/bigquery.admin)

Limitations

• You can't create a search index directly on a view or materialized view, butcalling theSEARCH functionon a view of an indexed table makes use of the underlying search index.
• You can't create a search index on an external table.
• If you rename a table after you create a search index on it, the indexbecomes invalid.
• TheSEARCH function is designed for point lookups. Fuzzy searching,typo correction, wildcards, and other types of document searches are notavailable.
• If the search index is not yet at 100% coverage, you are still charged for allindex storage that is reported in theINFORMATION_SCHEMA.SEARCH_INDEXES view.
• Queries that use theSEARCH function or are optimized by search indexesare not accelerated byBigQuery BI Engine.

• Search indexes are not used when the indexed table is modified by a DMLstatement, but they can be used when the predicate that is optimizable bysearch indexes is part of a subquery in a DML statement.

  • A search index is not used in the following query:

  DELETEFROMmy_dataset.indexed_tableWHERESEARCH(user_id,'123');

  • A search index can be used in the following query:

  DELETEFROMmy_dataset.other_tableWHEREuser_idIN(SELECTuser_idFROMmy_dataset.indexed_tableWHERESEARCH(user_id,'123'));

• Search indexes are not used when the query referencesMaterialized Views.

• Search indexes are not used in amulti-statement transaction query.

• Search indexes are not used in atime-travel query.

What's next

• Learn more aboutcreating a search index.
• Learn more aboutsearching in a table with a search index.