bigquery search index for log analytics

TL;DR

A BigQuery search index makes SEARCH() queries over text and JSON fast by pre-tokenizing the data, the way a book index beats reading every page. Create it with CREATE SEARCH INDEX on the columns you search, then use SEARCH(column, 'term') instead of LIKE '%term%'. It pays off when you repeatedly search large log tables; for one-off greps it is overhead.

The query

bigquery search index for log analytics

Use this when

• LIKE '%error%' over log tables is slow, expensive, or timing out
• You search tokenized text across JSON or semi-structured log fields regularly
• You are choosing between a search index, brute-force scans, and a dedicated log search tool

Not for

• Exact equality or range filters, which partitioning and clustering handle better
• Complex regex extraction, search indexes are token-based, not pattern-based
• Small tables or rare searches, where a full scan is cheaper than maintaining an index

Steps

1. Confirm the pain: check how much your current text search scans:

SELECT query, total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND query LIKE '%SEARCH%error%'
ORDER BY total_bytes_processed DESC LIMIT 10;

Expected output: your heaviest text-search queries and their byte counts. If the same log table keeps showing up, it is a search-index candidate.

1. Create the search index on the columns you actually search. The index stores tokens, so pick text, JSON, or the specific JSON paths:

CREATE SEARCH INDEX logs_search_idx
ON `project.dataset.app_logs`(message, OPTIONS(json_scope = 'JSON_VALUES'));

Expected output: CREATE SEARCH INDEX succeeds. Index creation takes time proportional to table size; check INFORMATION_SCHEMA.SEARCH_INDEXES for build status before relying on it.

1. Rewrite the slow query to use SEARCH instead of LIKE:

-- before: full scan
SELECT * FROM `project.dataset.app_logs`
WHERE message LIKE '%payment failed%';

-- after: index-assisted
SELECT * FROM `project.dataset.app_logs`
WHERE SEARCH(message, 'payment failed');

Expected output: same logical rows, far fewer bytes scanned. SEARCH matches tokens, so word order and stemming behave differently than LIKE; verify result counts match on a sample before switching dashboards.

1. For JSON logs, scope the search to the fields that matter. Searching the whole document tokenizes everything including noise:

CREATE SEARCH INDEX logs_json_idx
ON `project.dataset.app_logs`(payload, OPTIONS(json_scope = 'JSON_VALUES'));

Expected output: an index over JSON values only, not keys. Combine with a filter like WHERE SEARCH(payload, 'timeout') AND severity = 'ERROR'; the structured filter still benefits from partitioning.

1. Check the index is being used and measure the win:

SELECT * FROM `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE table_name = 'app_logs';

Expected output: index status and size. Compare bytes scanned before and after on the same query; a healthy search index typically cuts text-search scans by an order of magnitude or more on large tables.

1. Know the maintenance cost: the index updates as data lands, which adds write cost and a small freshness lag. For streaming inserts the lag is usually seconds, fine for analytics, not for real-time alerting. If you need sub-second alerting on logs, that is a job for a dedicated log pipeline, not BigQuery.

Expected output: a conscious tradeoff, not a surprise bill. Drop indexes on tables nobody searches anymore; stale indexes are pure cost.

Variant phrasings

bigquery SEARCH function vs LIKE performance

SEARCH uses the search index and is token-based; LIKE is a full scan with substring matching. They are not interchangeable for exact substring needs, but for keyword search SEARCH wins by a lot.

bigquery full text search on json column

Create the search index with jsonscope JSONVALUES and use SEARCH on the JSON column. For per-key search, extract the key with JSON_VALUE and index that path specifically.

bigquery search index pricing

Index storage and maintenance cost extra on top of the table. Price it against the bytes your LIKE scans were burning; the index usually wins when searches are frequent.

Why it matters

Log analytics in BigQuery dies on LIKE '%...%' because every search reads every byte. A search index is the standard fix, but teams either dont know it exists or create one and keep writing LIKE queries that ignore it. The rewrite in step 3 is the whole game.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yGqGANI6h3bX1Sehxt5ZPg