Use AI indexes
You can use the
AI.SEARCH
function to query your data while BigQuery automatically
handles column selection, index optimization, and ongoing maintenance.
This document explains how to create, monitor, and delete AI indexes, and how to query your indexed data.
Roles and permissions
To get the permissions that you need to Use AI indexes, ask your administrator to grant you the following IAM roles on the project:
-
Create or drop an AI index:
BigQuery Data Owner (
roles/bigquery.dataOwner) -
Query a table:
- BigQuery Data Viewer (
roles/bigquery.dataViewer) - BigQuery Job User (
roles/bigquery.jobUser)
- BigQuery Data Viewer (
-
View AI index metadata:
BigQuery Data Viewer (
roles/bigquery.dataViewer)
For more information about granting roles, see Manage access to projects, folders, and organizations.
You might also be able to get the required permissions through custom roles or other predefined roles.
Create an AI index
You can create an AI index on a standard BigQuery table by
using the CREATE AI INDEX
data definition language (DDL) statement. You can specify the columns to
index, or you can have BigQuery automatically select candidate
columns by using the AUTO COLUMNS option.
Limitations on creating AI indexes
When you create an AI index, the following limitations apply:
- Only top-level, non-repeated STRING columns are supported.
ARRAY<STRING>,STRUCT,JSON,ObjectRef, nested fields, non-STRINGcolumns, and pseudocolumns aren't supported. - You can index up to 50 columns in a single AI index.
- You can create an AI index only on a standard BigQuery table. Views, materialized views, external tables, BigLake tables, Apache Iceberg tables, and table snapshots aren't supported.
- You can't create AI indexes in datasets that have cross-region replicas.
- When you run
CREATE AI INDEX, the base table can have at most 1,000,000 rows for on-demand projects, or up to 100,000,000 rows if the project that contains the table has aBACKGROUNDreservation assignment. AUTO COLUMNSselects columns when theCREATE AI INDEXstatement runs. If you add columns to the table later, they aren't automatically indexed. To include new columns, drop the AI index and create it again.- Each table supports at most one AI index. A table with an AI index can't also have a vector index or an automatically generated embedding column.
For a full list of limitations, see Limitations on AI indexes.
Create an AI index with AUTO COLUMNS
When you create an AI index with AUTO COLUMNS, BigQuery
automatically scans the table schema and selects up to 50 eligible columns.
To create an AI index with AUTO COLUMNS, use the CREATE AI INDEX DDL
statement:
Go to BigQuery Studio in the Cloud de Confiance console.
In the query editor, run the following SQL statement:
CREATE AI INDEX INDEX_NAME ON DATASET_NAME.TABLE_NAME(AUTO COLUMNS);
Replace the following:
INDEX_NAME: the name of the AI index that you're creating. Because the index is always created in the same project and dataset as the base table, you don't need to specify the project or dataset in the index name. The index name must be unique among all indexes on the table.DATASET_NAME: the name of the dataset that contains the table.TABLE_NAME: the name of the standard table to index.
In the following example, an AI index is created with AUTO COLUMNS on
mydataset.products:
CREATE AI INDEX products_ai_index ON mydataset.products(AUTO COLUMNS);
Create an AI index on specific columns
You can create an AI index on a single column or a specific subset of columns. For example, when your table has more than 50 eligible columns, or when you want to exclude sensitive or high-noise columns, such as opaque IDs, UUIDs, hashes, or raw logs. If you specify multiple columns, place the most informative columns, such as a title or name column, first in the column list.
In the following example, an AI index is created only on the name and
description columns of mydataset.products:
CREATE AI INDEX products_ai_index ON mydataset.products(name, description);
In the following example, an AI index is created only on the description
column of mydataset.products:
CREATE AI INDEX products_ai_index ON mydataset.products(description);
Search a table with an AI index
You can query a table that has an AI index by using the AI.SEARCH function.
When a table has an AI index, only two arguments are required:
base_table or base_table_query, and query_value.
Search across all indexed columns
To search across all columns covered by the AI index, omit the
column_to_search argument and pass the base table and query string.
The following example searches all columns in mydataset.products that are
covered by the AI index:
SELECT base.id, base.name, base.description, distance FROM AI.SEARCH( TABLE mydataset.products, 'shoes that keep your feet dry in the rain', top_k => 5) ORDER BY distance;
Monitor the status of AI indexes
You can monitor the status, coverage, and indexed columns of your AI indexes
by querying the following INFORMATION_SCHEMA views:
INFORMATION_SCHEMA.AI_INDEXES: contains metadata about each AI index in a dataset.INFORMATION_SCHEMA.AI_INDEX_COLUMNS: lists the columns covered by each AI index in a dataset, allowing you to verify which columns were selected when usingAUTO COLUMNS.INFORMATION_SCHEMA.JOBS: contains information about background jobs, including the background jobs used to create and maintain AI indexes.
AI index example
The following query shows the coverage and storage size of all AI indexes in
my_project.my_dataset:
SELECT table_name, index_name, creation_time, coverage_percentage, unindexed_row_count, total_logical_bytes FROM my_project.my_dataset.INFORMATION_SCHEMA.AI_INDEXES;
The result is similar to the following:
+------------+-------------------+---------------------+---------------------+---------------------------+-----------------------+
| table_name | index_name | creation_time | coverage_percentage | unindexed_row_count | total_logical_bytes |
+------------+-------------------+---------------------+---------------------+---------------------------+-----------------------+
| products | products_ai_index | 2026-10-01 18:02:11 | 100 | 0 | 25344 |
+------------+-------------------+---------------------+---------------------+---------------------------+-----------------------+
AI index columns example
The following query lists the indexed columns for AI indexes in
my_project.my_dataset:
SELECT table_name, index_name, index_column_name FROM my_project.my_dataset.INFORMATION_SCHEMA.AI_INDEX_COLUMNS;
The result is similar to the following:
+------------+-------------------+-------------------+
| table_name | index_name | index_column_name |
+------------+-------------------+-------------------+
| products | products_ai_index | name |
| products | products_ai_index | description |
| products | products_ai_index | category |
| products | products_ai_index | review |
+------------+-------------------+-------------------+
Track background job costs example
To track the total_slot_ms and total_bytes_billed values for background AI
index data processing jobs on a table, query the INFORMATION_SCHEMA.JOBS view
for jobs with a job_id prefix of gc_:
SELECT job_id, creation_time, reservation_id, total_slot_ms, total_bytes_processed, total_bytes_billed FROM `region-REGION`.INFORMATION_SCHEMA.JOBS j WHERE EXISTS ( SELECT 1 FROM UNNEST(j.referenced_tables) t WHERE j.project_id = 'PROJECT_ID' AND t.dataset_id = 'DATASET_ID' AND t.table_id = 'TABLE' ) AND STARTS_WITH(job_id, 'gc_') ORDER BY j.creation_time DESC;
Replace the following:
REGION: the dataset region that contains your table, for example, us.PROJECT_ID: the ID of the project that contains your table.DATASET_ID: the name of the dataset that contains your table.TABLE: the name of the table that contains the AI index.
Delete an AI index
When you delete an indexed table, its AI index is also deleted. To delete an AI
index without deleting the table, use the DROP AI INDEX
DDL statement:
DROP AI INDEX IF EXISTS INDEX_NAME ON DATASET_NAME.TABLE_NAME;
Replace the following:
INDEX_NAME: the name of the AI index that you're deleting.DATASET_NAME: the name of the dataset that contains the table.TABLE_NAME: the name of the table that contains the AI index.
What's next
- Learn more about the
AI.SEARCHfunction. - Learn about embeddings in BigQuery.