# AI functions in BigQuery (AI.GENERATE, AI.IF, AI.CLASSIFY) > BigQuery calls Gemini from SQL with AI.GENERATE, AI.IF, AI.CLASSIFY, AI.SCORE and others. Every row is a request to Vertex AI, billed there. The BigQuery dry run shows only the bytes of your table, so it can't warn you what a query will cost. Source: https://docs.querylab.io/bigquery/ai-functions/ Updated: 2026-10-06 BigQuery's AI functions send each row's prompt to a Gemini model on Vertex AI (which Google now calls Gemini Enterprise Agent Platform) and return the answer as a SQL value. `AI.GENERATE` returns free text or a typed struct, `AI.IF` returns a `BOOL` you can put in a `WHERE`, `AI.CLASSIFY` returns a category, `AI.SCORE` a number. The model call is billed by Vertex AI, per token, on top of the BigQuery query. The dry run doesn't include it: a query that asks Gemini for a summary of 2,225 articles dry-runs at 5 MB, the size of the text column, which is a fraction of a cent. We did not run any of the LLM calls on this page, so the examples are dry runs and the return types come from dry-run schemas. ## The functions, and what each returns Google groups them into general-purpose functions, where you pick the model, prompt and parameters, and managed functions, where BigQuery chooses the model and prompt wording. We read the return types from the schema of a dry run: | Function | Returns | Use | | -------------------------------------- | ---------------------------------------------------------------------- | ------------------------------------------------------------------------------- | | `AI.GENERATE(prompt)` | `STRUCT` | Any prompt; with `output_schema` the struct has your fields instead of `result` | | `AI.GENERATE_BOOL`, `_INT`, `_DOUBLE` | `STRUCT` | One typed value per row | | `AI.IF(prompt)` | `BOOL` | Filter or join on a natural-language condition | | `AI.SCORE(prompt)` | `FLOAT64` | Rate rows so you can `ORDER BY` the score | | `AI.CLASSIFY(input, categories)` | `STRING` | Label rows; `GROUP BY` the label | | `AI.AGG(input, instruction)` | `STRING` | Aggregate many rows into one answer | | `AI.EMBED(content, endpoint => ...)` | `STRUCT, status STRING>` | An embedding for [vector search](https://docs.querylab.io/bigquery/vector-search/) | | `AI.SIMILARITY(a, b, endpoint => ...)` | `FLOAT64` | Cosine similarity of two inputs' embeddings | | `AI.COUNT_TOKENS(prompt)` | `STRUCT` | Count input tokens before you run | The table-valued versions work on a table with a remote model: `AI.GENERATE_TEXT` (the older name `ML.GENERATE_TEXT` also exists), `AI.GENERATE_TABLE` for a custom output schema, and `AI.GENERATE_EMBEDDING`. Both names resolved in our test. Separate task-specific functions call Cloud AI APIs for translation, document processing, transcription, natural language and vision. The general-purpose functions return a struct on purpose. `status` holds the error for that row, and `full_response` has the model's complete response, including safety ratings. The managed functions return a bare scalar and no status. Row-level failures, such as exceeding quota, come back as `NULL`, and the query still succeeds. For `AI.IF`, `AI.SCORE` and `AI.CLASSIFY`, `max_error_ratio` sets the share of failed rows the query tolerates. The default is 1.0, which tolerates failures. Lower it (we saw `0.2` accepted) so that a quota problem fails the query instead of returning a partial answer. Passing `max_error_ratio` to `AI.GENERATE` is rejected: "No matching signature". `AI.IF` also takes an `optimization_mode` argument, `MINIMIZE_COST` or `MAXIMIZE_QUALITY`, for the optimized mode described below. `max_error_ratio` isn't supported together with `MINIMIZE_COST`. ## The prompt is built from columns A prompt is a string, or a struct of strings and columns that BigQuery concatenates: ```sql SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary FROM mydataset.articles; SELECT AI.GENERATE(('Extract the name and age: ', description), output_schema => 'name STRING, age INT64') AS person FROM mydataset.patients; SELECT title FROM mydataset.articles WHERE AI.IF(('Is this article about football? ', body)); SELECT AI.CLASSIFY(ticket, ['billing', 'shipping', 'other']) AS topic, COUNT(*) FROM mydataset.tickets GROUP BY topic; ``` With `output_schema`, the result is a struct with the field names and types you give, and the schema is checked: our dry run of the `name STRING, age INT64` schema returned `age INT64` and `name STRING` in the output schema. Types follow the schema, so you can use the result in `WHERE` and `GROUP BY` without a `CAST`. ## What the dry run shows The first example reads only the `body` and `title` columns of a public table of 2,225 BBC articles, and BigQuery reports the bytes of those two columns: **a summary for every article** ```sql SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary FROM `bigquery-public-data.bbc_news.fulltext` ``` BigQuery dry run: this query processes 4.8 MB. Adding `LIMIT 3` doesn't change the estimate: **the same query with LIMIT 3** ```sql SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result AS summary FROM `bigquery-public-data.bbc_news.fulltext` LIMIT 3 ``` BigQuery dry run: this query processes 4.8 MB. Both say 5,045,835 bytes. Google's reference says that to limit Vertex AI charges when you use `LIMIT`, you should write the selected rows to a table first and call the function on that table, not on a subquery with `LIMIT`: ```sql CREATE TABLE mydataset.sample AS SELECT title, body FROM mydataset.articles LIMIT 10; SELECT title, AI.GENERATE(CONCAT('Summarize in one sentence: ', body)).result FROM mydataset.sample; ``` `AI.IF` in a `WHERE` clause is the same. The function runs on each row of the table to decide whether it passes: **AI.IF as a filter, dry run** ```sql SELECT title FROM `bigquery-public-data.bbc_news.fulltext` WHERE AI.IF(('Is this article about football? ', body)) ``` BigQuery dry run: this query processes 4.8 MB. And a query with no table at all reports nothing: **a prompt with no table** ```sql SELECT AI.GENERATE('Write a haiku about nulls').result AS poem ``` BigQuery dry run: this query processes 0 B. 0 bytes, one request to Gemini. The estimate in the editor, in the console and from `bq --dry_run` is the BigQuery side only. To estimate the model side, call `AI.COUNT_TOKENS` on a sample of your prompts and multiply by the model's per-token price from the Vertex AI pricing page. After the fact, the job's details show input, output, thought and cache token counts. Querylab.io's status-bar estimate and its per-query byte limit are that same dry run, so neither of them sees the model charge ([Cost limits](https://docs.querylab.io/writing-sql/cost-limits/#what-gets-estimated)). ## Cost, connections and quotas BigQuery bills the query as usual. Vertex AI bills the model calls to the project that runs the query. Charges can take up to 24 hours to appear. To find them in Cloud Billing, filter the service Vertex AI and the label `bigquery_job_id_prefix` set to the job's ID (the first 63 characters, in lower case). You can also attach your own query label to find a group of jobs. With no `connection_id`, the function runs with your own credentials, which need `roles/bigquery.jobUser` and `roles/aiplatform.user` on the project. Google recommends a BigQuery connection, which runs as a service account, for jobs expected to run 48 hours or longer. A remote model for `AI.GENERATE_TEXT` and the other table functions always uses a connection: ```sql CREATE MODEL mydataset.gemini REMOTE WITH CONNECTION `myproject.us.myconnection` OPTIONS (endpoint = 'gemini-2.5-flash'); ``` An `endpoint` that BigQuery doesn't know is an error at compile time ("Unsupported remote model endpoint"), and a connection that doesn't exist fails the same way, in a dry run, before any request is made. Requests use dynamic shared quota by default. `request_type` can force Provisioned Throughput (`DEDICATED`) or shared quota (`SHARED`). The model and region combinations Google lists change often, so read the current list before you hard-code an endpoint. Google's overview adds an optimized mode for `AI.IF` and `AI.CLASSIFY` (Preview) that trains a small model on a sample of your rows and uses it for the rest. It avoids an LLM call for most rows, which cuts tokens and latency on large tables. ## Store the answers Running the query again calls the model again and bills again. Write the answers to a table once and query the table. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Generative AI overview](https://cloud.google.com/bigquery/docs/generative-ai-overview) - [The AI.GENERATE function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate) - [Set permissions for generative AI functions](https://cloud.google.com/bigquery/docs/permissions-for-ai-functions) - [Introduction to AI in BigQuery](https://cloud.google.com/bigquery/docs/ai-introduction)