The AI.GENERATE_TABLE function

This document describes theAI.GENERATE_TABLE function, which lets you performgenerative natural language tasks by using any combination of text andunstructured data from BigQuerystandard tables, and alsospecify a schema to format the response from the model.

The function works by sending requests to a BigQuery ML remote modelthat represents a Vertex AI Gemini model, and then returningthat model's response. The function supports remote models over any of thegenerally availableorpreviewGemini models.

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

You can use theAI.GENERATE_TABLE function to perform tasks such asclassification, sentiment analysis, image captioning, and transcription.

Prompt design can strongly affect the responses returned by theVertex AI model. For more information, seeIntroduction to prompting.

Input

Using theAI.GENERATE_TABLE function, you can use the following typesof input:

• Text data from standard tables.
• ObjectRefRuntimevalues that are generated by theOBJ.GET_ACCESS_URL function.You can useObjectRefvalues from standard tables as input to theOBJ.GET_ACCESS_URL function.(Preview)

When you analyze unstructured data, that data must meet the followingrequirements:

• Content must be in one of the supported formats that aredescribed in the Gemini API modelmimeType 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 returnsresults 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 theresource.

• DATASET: the dataset that contains theresource.

• MODEL: the name of the remote model. For moreinformation, seeTheCREATE MODEL statement for remote models over LLMs.

  Note: Using a remote model based on a Gemini 2.5 model incurscharges for thethinking process.

• TABLE: the name of the BigQuery tablethat contains the prompt data. The text in the column that's namedprompt issent to the model. If your table does not have aprompt column, use theQUERY_STATEMENT argument instead and provide aSELECT statement thatincludes an alias for an existing table column. An error occurs if nopromptcolumn is available.

• QUERY_STATEMENT: the GoogleSQL query thatgenerates the prompt data. The query must produce a column namedprompt.Within the query, you can provide the prompt value in the following ways:

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

  • Specify aSTRUCT value that contains one or more fields. You can usethe following types of fields within the struct value:

    Field type	Description	Examples
    STRING	A string literal, or the name of aSTRING 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	AnObjectRefRuntime value returned by theOBJ.GET_ACCESS_URL function. TheOBJ.GET_ACCESS_URL function takes anObjectRef value as input, which you can provide by either specifying the name of a column that containsObjectRef values, or by constructing anObjectRef value. ObjectRefRuntime values must have theaccess_url.read_url anddetails.gcs_metadata.content_type elements of the JSON value populated.	Function call withObjectRef column: OBJ.GET_ACCESS_URL(my_objectref_column, 'r') Function call with constructedObjectRef value: OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image.jpg', 'myconnection'), 'r')
    ARRAY<ObjectRefRuntime>	ObjectRefRuntime values returned from multiple calls to theOBJ.GET_ACCESS_URL function. TheOBJ.GET_ACCESS_URL function takes anObjectRef value as input, which you can provide by either specifying the name of a column that containsObjectRef values, or by constructing anObjectRef value. ObjectRefRuntime values must have theaccess_url.read_url anddetails.gcs_metadata.content_type elements of the JSON value populated.	Function calls withObjectRef columns: [OBJ.GET_ACCESS_URL(my_objectref_column1, 'r'), OBJ.GET_ACCESS_URL(my_objectref_column2, 'r')] Function calls with constructedObjectRef 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 combinesSTRUCT fields similarly to aCONCAToperation and concatenates the fields in their specified order. Thesame is true for the elements of any arrays used within the struct.The following table shows some examples ofSTRUCT prompt values and howthey 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 citymy_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_imagecountry_image

  Note: To minimize Vertex AI charges, write query resultsto a table and then reference that table in theAI.GENERATE_TABLEfunction. This can help you ensure that you are sending as few rows aspossible to the model.

• OUTPUT_SCHEMA: a schema to use to format the model'sresponse. TheOUTPUT_SCHEMA value must a SQL schema definition,similar to that used in theCREATE TABLE statement.The following data types are supported:

  • INT64
  • FLOAT64
  • BOOL
  • STRING
  • ARRAY
  • STRUCT

  When using theOUTPUT_SCHEMA argument to generatestructured data based on prompts from a table, it is important to understandthe prompt data in order to specify an appropriate schema.

  For example, say you are analyzing movie review content from a table thathas the following fields:

  • movie_id
  • review
  • prompt

  Then you might create prompt text by running a query similar to thefollowing:

  UPDATE`mydataset.movie_review`SETprompt=CONCAT('Extract the key words and key sentiment from the text below: ',review)WHEREreviewISNOTNULL;

  And you might specify aOUTPUT_SCHEMA value similar to"keywords ARRAY<STRING>, sentiment STRING" AS output_schema.

Examples

The following examples demonstrate how to useAI.GENERATE_TABLE.

Format text input

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

SELECTaddress,age,is_married,name,phone_number,weight_in_poundsFROMAI.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.'ASprompt),STRUCT("address STRING, age INT64, is_married BOOL, name STRING, phone_number ARRAY<STRING>, weight_in_pounds FLOAT64"ASoutput_schema,8192ASmax_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 animage_description column by analyzing a product image that is stored asanObjectRef value in a standard table:

CREATEORREPLACETABLE`mydataset.products`ASSELECTproduct_id,product_name,image,image_descriptionFROMAI.GENERATE_TABLE(MODEL`mydataset.gemini`,(SELECT('Can you describe the following image?',OBJ.GET_ACCESS_URL(image,'r'))ASprompt,*FROM`mydataset.products`),STRUCT("image_description STRING"ASoutput_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 theoutput_response argument.

• full_response: this is theJSON responsefrom theprojects.locations.endpoints.generateContentcall to the model. The generated data is in thetext element.

• status: aSTRING value that contains the API responsestatus for the corresponding row. This value is empty if the operation wassuccessful.

Locations

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

You can create remote models over Gemini models in thesupported regionsfor the given Gemini model, and also in theUS andEU multi-regions.

Quotas

SeeVertex AI and Cloud AI service functions quotas and limits.

Known issues

Resource exhausted errors

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

Aretryableerroroccurred:RESOURCEEXHAUSTEDerrorfrom<remoteendpoint>

This issue occurs because BigQuery query jobs finish successfullyeven if the function fails for some of the rows. The function fails when thevolume of API calls to the remote endpoint exceeds the quota limits for thatservice. This issue occurs most often when you are running multiple parallelbatch queries. BigQuery retries these calls, but if the retriesfail, theresource exhausted error message is returned.

To iterate through inference calls until all rows are successfully processed,you can use theBigQuery remote inference SQL scriptsor theBigQuery remote inference pipeline Dataform package.

What's next

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