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 model, and then returning that model's response. The following Gemini models are supported:

  • gemini-2.0-flash-001
  • gemini-1.5-flash-001
  • gemini-1.5-flash-002
  • gemini-1.5-pro-001
  • gemini-1.5-pro-002

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:

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]) )

Arguments

AI.GENERATE_TABLE takes the following arguments:

  • project_id: your project ID.

  • dataset: the BigQuery dataset that contains the model.

  • model: the name of the remote model. For more information, see The CREATE MODEL statement for remote models over LLMs.

  • 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

    For Gemini 1.5 models, only specify a FLOAT64 data type if you are certain that the return value won't be a round number. These models can sometimes return INT values rather than FLOAT values for round numbers, for example 2 instead of 2.0, and this can cause a parsing error in the query.

    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. The default is 128.

  • 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. The default is 0.95.

    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. The default is 1.0.

  • 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.

Examples

The following examples demonstrate how to use ML.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