The AI.GENERATE_TABLE function

This document describes the AI.GENERATE_TABLE function, which lets you perform generative natural language tasks by using any combination of text and unstructured data from BigQuery standard tables, and also specify a schema to format the response from the model.

The function works by sending requests to a BigQuery ML remote model that represents a Vertex AI Gemini model, and then returning that model's response. The function supports remote models over any of the generally available or preview Gemini models.

Several of the AI.GENERATE_TABLE function's arguments provide the parameters that shape the Vertex AI model's response.

You can use the AI.GENERATE_TABLE function to perform tasks such as classification, sentiment analysis, image captioning, and transcription.

Prompt design can strongly affect the responses returned by the Vertex AI model. For more information, see Introduction to prompting.

Input

Using the AI.GENERATE_TABLE function, you can use the following types of input:

Text data from standard tables.
ObjectRefRuntime values that are generated by the OBJ.GET_ACCESS_URL function. You can use ObjectRef values from standard tables as input to the OBJ.GET_ACCESS_URL function. (Preview)

When you analyze unstructured data, that data must meet the following requirements:

Content must be in one of the supported formats that are described in the Gemini API model mimeType parameter.
If you are analyzing a video, the maximum supported length is two minutes. If the video is longer than two minutes, AI.GENERATE_TABLE only returns results for the first two minutes.

Syntax

AI.GENERATE_TABLE(
MODEL `PROJECT_ID.DATASET.MODEL`,
{ TABLE `PROJECT_ID.DATASET.TABLE` | (QUERY_STATEMENT) },
STRUCT(
  OUTPUT_SCHEMA AS output_schema
  [, MAX_OUTPUT_TOKENS AS max_output_tokens]
  [, TOP_P AS top_p]
  [, TEMPERATURE AS temperature]
  [, STOP_SEQUENCES AS stop_sequences]
  [, SAFETY_SETTINGS AS safety_settings]
  [, REQUEST_TYPE AS request_type])
)

Arguments

AI.GENERATE_TABLE takes the following arguments:

PROJECT_ID: the project that contains the resource.
DATASET: the dataset that contains the resource.
MODEL: the name of the remote model. For more information, see The CREATE MODEL statement for remote models over LLMs.

Note: Using a remote model based on a Gemini 2.5 model incurs charges for the thinking process.
TABLE: the name of the BigQuery table that contains the prompt data. The text in the column that's named prompt is sent to the model. If your table does not have a prompt column, use the QUERY_STATEMENT argument instead and provide a SELECT statement that includes an alias for an existing table column. An error occurs if no prompt column is available.

QUERY_STATEMENT: the GoogleSQL query that generates the prompt data. The query must produce a column named prompt. Within the query, you can provide the prompt value in the following ways:

Specify a STRING value. For example, ('Write a poem about birds').

Specify a STRUCT value that contains one or more fields. You can use the following types of fields within the struct value:

Field type	Description	Examples
`STRING`	A string literal, or the name of a `STRING` column.	String literal: `'Is Seattle a US city?'` String column name: `my_string_column`
`ARRAY<STRING>`	You can only use string literals in the array.	Array of string literals: `['Is ', 'Seattle', ' a US city']`
`ObjectRefRuntime`	An `ObjectRefRuntime` value returned by the `OBJ.GET_ACCESS_URL` function. The `OBJ.GET_ACCESS_URL` function takes an `ObjectRef` value as input, which you can provide by either specifying the name of a column that contains `ObjectRef` values, or by constructing an `ObjectRef` value. `ObjectRefRuntime` values must have the `access_url.read_url` and `details.gcs_metadata.content_type` elements of the JSON value populated.	Function call with `ObjectRef` column: `OBJ.GET_ACCESS_URL(my_objectref_column, 'r')` Function call with constructed `ObjectRef` value: `OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image.jpg', 'myconnection'), 'r')`
`ARRAY<ObjectRefRuntime>`	`ObjectRefRuntime` values returned from multiple calls to the `OBJ.GET_ACCESS_URL` function. The `OBJ.GET_ACCESS_URL` function takes an `ObjectRef` value as input, which you can provide by either specifying the name of a column that contains `ObjectRef` values, or by constructing an `ObjectRef` value. `ObjectRefRuntime` values must have the `access_url.read_url` and `details.gcs_metadata.content_type` elements of the JSON value populated.	Function calls with `ObjectRef` columns: `[OBJ.GET_ACCESS_URL(my_objectref_column1, 'r'), OBJ.GET_ACCESS_URL(my_objectref_column2, 'r')]` Function calls with constructed `ObjectRef` values: `[OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image1.jpg', 'myconnection'), 'r'), OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image2.jpg', 'myconnection'), 'r')]`

The function combines STRUCT fields similarly to a CONCAT operation and concatenates the fields in their specified order. The same is true for the elements of any arrays used within the struct. The following table shows some examples of STRUCT prompt values and how they are interpreted:

Struct field types	Struct value	Semantic equivalent
`STRUCT<STRING>`	`('Describe the city of Seattle')`	'Describe the city of Seattle'
`STRUCT<STRING, STRING, STRING>`	`('Describe the city ', my_city_column, ' in 15 words')`	'Describe the city `my_city_column_value` in 15 words'
`STRUCT<STRING, ARRAY<STRING>>`	`('Describe ', ['the city of', 'Seattle'])`	'Describe the city of Seattle'
`STRUCT<STRING, ObjectRefRuntime>`	`('Describe this city', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))`	'Describe this city' `image`
`STRUCT<STRING, ObjectRefRuntime, ObjectRefRuntime>`	`('If the city in the first image is within the country of the second image, provide a ten word description of the city', OBJ.GET_ACCESS_URL(city_image_objectref_column, 'r'), OBJ.GET_ACCESS_URL(country_image_objectref_column, 'r'))`	'If the city in the first image is within the country of the second image, provide a ten word description of the city' `city_image` `country_image`

OUTPUT_SCHEMA: a schema to use to format the model's response. The OUTPUT_SCHEMA value must a SQL schema definition, similar to that used in the CREATE TABLE statement. The following data types are supported:
- INT64
- FLOAT64
- BOOL
- STRING
- ARRAY
- STRUCT
When using the OUTPUT_SCHEMA argument to generate structured data based on prompts from a table, it is important to understand the prompt data in order to specify an appropriate schema.

For example, say you are analyzing movie review content from a table that has the following fields:
- movie_id
- review
- prompt
Then you might create prompt text by running a query similar to the following:
```
UPDATE `mydataset.movie_review`
SET prompt = CONCAT('Extract the key words and key sentiment from the text below: ', review)
WHERE review IS NOT NULL;
```
And you might specify a OUTPUT_SCHEMA value similar to "keywords ARRAY<STRING>, sentiment STRING" AS output_schema.
MAX_OUTPUT_TOKENS: an INT64 value that sets the maximum number of tokens that can be generated in the response. A token might be smaller than a word and is approximately four characters. One hundred tokens correspond to approximately 60-80 words.This value must be in the range [1,8192]. Specify a lower value for shorter responses and a higher value for longer responses. If you don't specify a value, the model determines an appropriate value.
TOP_P: a FLOAT64 value in the range [0.0,1.0] that changes how the model selects tokens for output. Specify a lower value for less random responses and a higher value for more random responses. If you don't specify a value, the model determines an appropriate value.

Tokens are selected from the most to least probable until the sum of their probabilities equals the TOP_P value. For example, if tokens A, B, and C have a probability of 0.3, 0.2, and 0.1, and the TOP_P value is 0.5, then the model selects either A or B as the next token by using the TEMPERATURE value and doesn't consider C.
TEMPERATURE: a FLOAT64 value in the range [0.0,2.0] that controls the degree of randomness in token selection. Lower TEMPERATURE values are good for prompts that require a more deterministic and less open-ended or creative response, while higher TEMPERATURE values can lead to more diverse or creative results. A TEMPERATURE value of 0 is deterministic, meaning that the highest probability response is always selected. If you don't specify a value, the model determines an appropriate value.
STOP_SEQUENCES: an ARRAY<STRING> value that removes the specified strings if they are included in responses from the model. Strings are matched exactly, including capitalization. The default is an empty array.
SAFETY_SETTINGS: an ARRAY<STRUCT<STRING AS category, STRING AS threshold>> value that configures content safety thresholds to filter responses. The first element in the struct specifies a harm category, and the second element in the struct specifies a corresponding blocking threshold. The model filters out content that violate these settings. You can only specify each category once. For example, you can't specify both STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_MEDIUM_AND_ABOVE' AS threshold) and STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_ONLY_HIGH' AS threshold). If there is no safety setting for a given category, the BLOCK_MEDIUM_AND_ABOVE safety setting is used.

Supported categories are as follows:
- HARM_CATEGORY_HATE_SPEECH
- HARM_CATEGORY_DANGEROUS_CONTENT
- HARM_CATEGORY_HARASSMENT
- HARM_CATEGORY_SEXUALLY_EXPLICIT
Supported thresholds are as follows:
- BLOCK_NONE (Restricted)
- BLOCK_LOW_AND_ABOVE
- BLOCK_MEDIUM_AND_ABOVE (Default)
- BLOCK_ONLY_HIGH
- HARM_BLOCK_THRESHOLD_UNSPECIFIED
For more information, see Harm categories and How to configure content filters.
REQUEST_TYPE: a STRING value that specifies the type of inference request to send to the Gemini model. The request type determines what quota the request uses. Valid values are as follows:
- DEDICATED: The AI.GENERATE_TABLE function only uses Provisioned Throughput quota. The AI.GENERATE_TABLE function returns the error Provisioned throughput is not purchased or is not active if Provisioned Throughput quota isn't available.
- SHARED: The AI.GENERATE_TABLE function only uses dynamic shared quota (DSQ), even if you have purchased Provisioned Throughput quota.
- UNSPECIFIED: The AI.GENERATE_TABLE function uses quota as follows:
  - If you haven't purchased Provisioned Throughput quota, the AI.GENERATE_TABLE function uses DSQ quota.
  - If you have purchased Provisioned Throughput quota, the AI.GENERATE_TABLE function uses the Provisioned Throughput quota first. If requests exceed the Provisioned Throughput quota, the overflow traffic uses DSQ quota.
The default value is UNSPECIFIED.

Examples

The following examples demonstrate how to use AI.GENERATE_TABLE.

Format text input

The following example shows a request that provides a SQL schema to format the model's response:

SELECT
  address,
  age,
  is_married,
  name,
  phone_number,
  weight_in_pounds
FROM
AI.GENERATE_TABLE( MODEL `mydataset.gemini_model`,
  (
      SELECT
        'John Smith is a 20-year old single man living at 1234 NW 45th St, Kirkland WA, 98033. He has two phone numbers 123-123-1234, and 234-234-2345. He is 200.5 pounds.'
          AS prompt
  ),
  STRUCT("address STRING, age INT64, is_married BOOL, name STRING, phone_number ARRAY<STRING>, weight_in_pounds FLOAT64"
          AS output_schema, 8192 AS max_output_tokens));

The results look similar to the following:

+-------------------------------------+-----+------------+------------+---------------+------------------+
| address                             | age | is_married | name       | phone_number  | weight_in_pounds |
+-------------------------------------+-----+------------+------------+---------------+------------------+
| 1234 NW 45th St, Kirkland WA, 98033 | 20  | No         | John Smith | 123-123-1234  | 200.5            |
|                                     |     |            |            | 234-234-2345  |                  |
|                                     |     |            |            |               |                  |
+-------------------------------------+-----+------------+------------+---------------+------------------+

Create a column based on image data

The following example shows how to to create and populate an image_description column by analyzing a product image that is stored as an ObjectRef value in a standard table:

CREATE OR REPLACE TABLE
  `mydataset.products`
AS
  SELECT
  product_id,
  product_name,
  image,
  image_description
FROM
AI.GENERATE_TABLE( MODEL `mydataset.gemini`,
  (
  SELECT
    ('Can you describe the following image?',
      OBJ.GET_ACCESS_URL(image, 'r')) AS prompt,
    *
  FROM
   `mydataset.products` ),
  STRUCT ( "image_description STRING" AS output_schema ));

Details

The model and input table must be in the same region.

Output

AI.GENERATE_TABLE returns the following columns:

All columns in the input table.
All columns specified in the output_response argument.
full_response: this is the JSON response from the projects.locations.endpoints.generateContent call to the model. The generated data is in the text element.
status: a STRING value that contains the API response status for the corresponding row. This value is empty if the operation was successful.

Locations

AI.GENERATE_TABLE must run in the same region or multi-region as the remote model that the function references.

You can create remote models over Gemini models in the supported regions for the given Gemini model, and also in the US and EU multi-regions.

Quotas

See Vertex AI and Cloud AI service functions quotas and limits.

Known issues

This section contains information about known issues.

Resource exhausted errors

Sometimes after a query job that uses this function finishes successfully, some returned rows contain the following error message:

A retryable error occurred: RESOURCE EXHAUSTED error from <remote endpoint>

This issue occurs because BigQuery query jobs finish successfully even if the function fails for some of the rows. The function fails when the volume of API calls to the remote endpoint exceeds the quota limits for that service. This issue occurs most often when you are running multiple parallel batch queries. BigQuery retries these calls, but if the retries fail, the resource exhausted error message is returned.

To iterate through inference calls until all rows are successfully processed, you can use the BigQuery remote inference SQL scripts or the BigQuery remote inference pipeline Dataform package.

What's next

Get step-by-step instructions on how to generate structured data using your own data.
For more information about using Vertex AI models to generate text and embeddings, see Generative AI overview.
For more information about using Cloud AI APIs to perform AI tasks, see AI application overview.
For more information about supported SQL statements and functions for generative AI models, see End-to-end user journeys for generative AI models.