SEARCH function and search indexes in BigQuery

SEARCH checks whether every term of a query appears as a token in your text or JSON. It works on any table, and a search index lets it skip almost all of the data. We built an index on a 12 GB table and measured the bytes, plus the escaping rules that trip people.

SEARCH(data, 'query') returns TRUE when every term in the query appears as a whole token in the data. It splits text at delimiters, lowercases it, and compares tokens, so SEARCH('Error: timeout', 'TIMEOUT') is TRUE and SEARCH('Error: timeout', 'time') is FALSE. It works on any table, but it only gets cheap with a search index: on a 12 GB table, a query for one rare token read 127 MB with the index, against an upper bound of 11.7 GB without it. It is for point lookups, such as finding the log lines that mention one request ID.

How a SEARCH query is matched

The default LOG_ANALYZER lowercases the text and cuts it at delimiters. The delimiters include space, . , : ; = @ - _ / \ ( ) [ ] { } < > | ! ? + * & ' " $ % and a few URL-encoded forms. A term matches only a whole token:

SELECT * FROM (
  SELECT 'TIMEOUT' AS term, SEARCH('Error: timeout on host-42', 'TIMEOUT') AS matches
  UNION ALL
  SELECT 'time' AS term, SEARCH('Error: timeout on host-42', 'time') AS matches
  UNION ALL
  SELECT 'host' AS term, SEARCH('Error: timeout on host-42', 'host') AS matches
  UNION ALL
  SELECT '42' AS term, SEARCH('Error: timeout on host-42', '42') AS matches
  UNION ALL
  SELECT 'timeout host' AS term, SEARCH('Error: timeout on host-42', 'timeout host') AS matches
)
ORDER BY term

TIMEOUT matches because case is folded. time doesn’t match timeout, host matches host-42 because - is a delimiter, and a query of several terms needs all of them. Since _ and . are delimiters too, user_id_42 and user.id match the terms user and id, but camel case is not split:

SELECT value, SEARCH(value, 'id') AS finds_id, SEARCH(value, 'user') AS finds_user
FROM UNNEST(['user_id_42', 'userid42', 'user.id', 'userId']) AS value
ORDER BY value

SEARCH is not a substring or pattern match. If you need LIKE '%abc%' behavior, SEARCH will not give it to you, and Google’s intro lists fuzzy search, typo correction and wildcards as unsupported.

The query language

A query is terms joined with AND, OR and parentheses, and a space between terms is an implicit AND. Double quotes make a phrase, which must appear in that order as adjacent tokens:

SELECT * FROM (
  SELECT 'error timeout' AS query, SEARCH('Error: timeout on host-42', 'error timeout') AS matches
  UNION ALL
  SELECT 'error AND nope' AS query, SEARCH('Error: timeout on host-42', 'error AND nope') AS matches
  UNION ALL
  SELECT 'error OR nope' AS query, SEARCH('Error: timeout on host-42', 'error OR nope') AS matches
  UNION ALL
  SELECT 'error (nope OR host)' AS query, SEARCH('Error: timeout on host-42', 'error (nope OR host)') AS matches
  UNION ALL
  SELECT '"timeout on"' AS query, SEARCH('Error: timeout on host-42', '"timeout on"') AS matches
  UNION ALL
  SELECT '"on timeout"' AS query, SEARCH('Error: timeout on host-42', '"on timeout"') AS matches
)
ORDER BY query

Reserved characters and a hyphen trap

The query string has its own grammar, and the hyphen is part of it. Searching for an ID with a hyphen fails:

SELECT SEARCH('Error: timeout on host-42', 'host-42') AS matches

Escape reserved characters with a double backslash in a normal string ('host\\-42'), a single backslash in a raw string (r'host\-42'), or put the term in backticks or double quotes. The reserved characters are [ ] < > ( ) { } | ! ' " * & ? + / : = - \ ~ ^. So 'trace_id=abc' is an error as well.

SELECT * FROM (
  SELECT 'backslash in a normal string' AS how, SEARCH('Error: timeout on host-42', 'host\\-42') AS matches
  UNION ALL
  SELECT 'raw string', SEARCH('Error: timeout on host-42', r'host\-42')
  UNION ALL
  SELECT 'backticks', SEARCH('Error: timeout on host-42', '`host-42`')
  UNION ALL
  SELECT 'double quotes (phrase)', SEARCH('Error: timeout on host-42', '"host-42"')
)
ORDER BY how

Backticks force an exact, case-sensitive match of the text inside, which has to start and end at delimiters in the data. The words AND, OR, NOT, IN and NEAR are reserved and need backticks:

SELECT SEARCH('retry AND fail', 'AND') AS matches

Constants only, and no empty queries

The query must be a constant expression. We tried to take it from a column with UNNEST and got “must be a constant expression”, so you can’t join a table of search terms to a table of text. A NULL query, an empty string, or a query that analyzes to no tokens is an error, not a FALSE:

SELECT SEARCH('abc', CAST(NULL AS STRING)) AS matches

SELECT SEARCH('abc', '...') AS matches

What can be searched

SEARCH accepts STRING, ARRAY<STRING>, STRUCT, ARRAY<STRUCT> and JSON. For any other type it returns FALSE without an error, which is easy to miss:

SELECT label, matches FROM (
  SELECT 'STRING' AS label, SEARCH('order 42', '42') AS matches
  UNION ALL SELECT 'INT64', SEARCH(42, '42')
  UNION ALL SELECT 'ARRAY<STRING>', SEARCH(['order', '42'], '42')
  UNION ALL SELECT 'STRUCT', SEARCH(STRUCT('order' AS a, '42' AS b), '42')
)
ORDER BY label

You can pass a whole table row, and SEARCH(t, 'printer') looks at every searchable column of t:

WITH tickets AS (
  SELECT 1 AS id, 'Ana' AS owner, 'printer offline' AS title
  UNION ALL SELECT 2, 'Boris', 'VPN timeout'
  UNION ALL SELECT 3, 'Printer Corp', 'invoice question'
)
SELECT id FROM tickets WHERE SEARCH(tickets, 'printer') ORDER BY id

For JSON, json_scope chooses what is searched: JSON_VALUES (default), JSON_KEYS or JSON_KEYS_AND_VALUES:

WITH doc AS (SELECT JSON '{"status": "active", "tags": ["beta"]}' AS j)
SELECT * FROM (
  SELECT 'JSON_VALUES' AS scope, 'status' AS term, SEARCH(j, 'status', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_VALUES' AS scope, 'active' AS term, SEARCH(j, 'active', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_VALUES' AS scope, 'beta' AS term, SEARCH(j, 'beta', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'status' AS term, SEARCH(j, 'status', json_scope => 'JSON_KEYS') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'active' AS term, SEARCH(j, 'active', json_scope => 'JSON_KEYS') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'beta' AS term, SEARCH(j, 'beta', json_scope => 'JSON_KEYS') AS matches FROM doc
)
ORDER BY scope, term

Analyzers

analyzer is LOG_ANALYZER (default), NO_OP_ANALYZER or PATTERN_ANALYZER.

• NO_OP_ANALYZER doesn’t split or lowercase. The whole value is one token, so the query has to equal the data exactly, including case.
• PATTERN_ANALYZER tokenizes with a regular expression, set in analyzer_options as {"patterns": ["[0-9]+"]}. Only text that matches the pattern becomes tokens.
• LOG_ANALYZER accepts analyzer_options with your own delimiters and token_filters (for example a normalizer with mode NONE to keep case).

SELECT analyzer, q, matches FROM (
  SELECT 'LOG_ANALYZER' AS analyzer, 'hello' AS q, SEARCH('Hello World', 'hello') AS matches
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'hello', SEARCH('Hello World', 'hello', analyzer => 'NO_OP_ANALYZER')
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'World', SEARCH('Hello World', 'World', analyzer => 'NO_OP_ANALYZER')
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'Hello World', SEARCH('Hello World', 'Hello World', analyzer => 'NO_OP_ANALYZER')
)
ORDER BY analyzer, q

The pattern analyzer analyzes the query too, so a query term that matches no pattern produces no tokens and errors:

SELECT * FROM (
  SELECT 'PATTERN_ANALYZER, [0-9]+' AS analyzer, '12' AS query, SEARCH('ref=ab12cd34', '12', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}') AS matches
  UNION ALL
  SELECT 'PATTERN_ANALYZER, [0-9]+', '34', SEARCH('ref=ab12cd34', '34', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}')
  UNION ALL
  SELECT 'PATTERN_ANALYZER, [0-9]+', '1', SEARCH('ref=ab12cd34', '1', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}')
  UNION ALL
  SELECT 'LOG_ANALYZER', '12', SEARCH('ref=ab12cd34', '12')
  UNION ALL
  SELECT 'LOG_ANALYZER', 'ab12cd34', SEARCH('ref=ab12cd34', 'ab12cd34')
)
ORDER BY analyzer, query

SELECT SEARCH('ref=ab12cd34', 'ab', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}') AS matches

Whatever analyzer and options you use in the query must match the ones the index was built with, or the index isn’t used.

Search indexes

CREATE SEARCH INDEX logs_idx ON mydataset.logs (message);

-- or index every STRING and JSON column, now and in the future
CREATE SEARCH INDEX logs_idx ON mydataset.logs (ALL COLUMNS);

The index is built in the background. The query works the whole time and returns correct results, using the index only for the part that is indexed. You can see progress in INFORMATION_SCHEMA.SEARCH_INDEXES (coverage_percentage, index_status). An index is only built for tables of 10 GB or more. On a smaller table nothing is populated and the job reports BASE_TABLE_TOO_SMALL. You can’t index a view, a materialized view or an external table, renaming the table invalidates the index, and a DML statement that modifies the indexed table doesn’t use it.

What we measured

We built a table of 40 million log lines (12.2 GB), each about 290 bytes of text with two 128 character hex tokens, and 10 rows with a rare token. The index covered only the message column and took about 25 minutes to reach 100% coverage. It was 5.9 GB, 48% of the table, because every row carries two unique long tokens. That is a bad case for index size. All runs had the cache turned off.

The same SEARCH over message without an index has an upper bound of 11.7 GB, so the rare token was found by reading about 1% of that. Two details matter for the bill:

• The dry run doesn’t know about the index. It reported 11,740,337,306 bytes for the first query after the index was ready, and maximum_bytes_billed is checked against that estimate. A limit of 1 MB failed with “11740905472 or higher required” even though the query reads 127 MB. See controlling costs.
• The cost depends on how many tokens match. The second SEARCH found 10 rows and read 5.5 times the bytes of the first, and spent 168 slot seconds, because the hit list for E-7731 is much longer.

Google’s search guide lists the other forms the index can speed up when you compare a column with literals: =, IN, LIKE 'prefix%', STARTS_WITH, ENDS_WITH, and some JSON_VALUE and UPPER/LOWER forms. We confirmed STARTS_WITH. We didn’t test LIKE '%text%' on the indexed table: an unindexed run would read the whole 12 GB, and Google doesn’t list it. Find out from the job’s searchStatistics:

bq show --format=prettyjson -j JOB_ID

indexUsageMode is FULLY_USED, PARTIALLY_USED or UNUSED, and indexUnusedReasons says why.

What it costs

On-demand queries pay for bytes processed, as in the table above. Building and refreshing the index is free up to a per-organization size limit, after which you need your own reservation. Index storage is billed once the index is populated and active, and a partially built index is billed for all the storage it already has. Google says queries that use SEARCH or an index are not accelerated by BI Engine. To find rows by meaning, not tokens, see vector search.