Introduction to search in BigQuery

BigQuery search indexes let you efficiently find unique data elements buried in unstructured text and semi-structured JSON data without knowing the table schemas in advance. Search indexes combine a columnar store with text search to let you perform efficient row lookups for tasks like log analytics, security audits, and regulatory reporting.

BigQuery stores and manages your indexes, so that when data becomes available in BigQuery, you can immediately retrieve it with the SEARCH function or other supported operators and functions. To optimize your searches, read about best practices.

Use cases

BigQuery search indexes help you perform the following tasks:

  • Search system, network, or application logs stored in BigQuery tables.
  • 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, see Create a search index and Search with an index.

Pricing

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

Roles and permissions

To create a search index, you need the bigquery.tables.createIndex IAM permission on the table where you're creating the index. To drop a search index, you need the bigquery.tables.deleteIndex permission. Each of the following predefined IAM roles includes the permissions that you need to work with search 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, but calling the SEARCH function on 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 index becomes invalid.
  • The SEARCH function is designed for point lookups. Fuzzy searching, typo correction, wildcards, and other types of document searches are not available.
  • If the search index is not yet at 100% coverage, you are still charged for all index storage that is reported in the INFORMATION_SCHEMA.SEARCH_INDEXES view.
  • Queries that use the SEARCH function or are optimized by search indexes are not accelerated by BigQuery BI Engine.

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

    • A search index is not used in the following query:
    DELETE FROM my_dataset.indexed_table
WHERE SEARCH(user_id, '123');
    • A search index can be used in the following query:
    DELETE FROM my_dataset.other_table
WHERE
  user_id IN (
    SELECT user_id
    FROM my_dataset.indexed_table
    WHERE SEARCH(user_id, '123')
  );

  • Search indexes are not used when the query references Materialized Views.

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

  • Search indexes are not used in a time-travel query.

What's next