The AI.GENERATE function

This document describes theAI.GENERATE function, which lets youanalyze any combination of text and unstructured data. You can choose togenerate text orstructured output according to acustom schema that you specify. The function generates aSTRUCT that containsyour generated data, the full model response, and a status.

The function works by sending requests to a Vertex AI Geminimodel, and then returning that model's response.

You can use theAI.GENERATE function to perform tasks such asclassification and sentiment analysis.

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

Input

Using theAI.GENERATE 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 only returnsresults based on the first two minutes.

Syntax

AI.GENERATE([prompt=>]'PROMPT',[,endpoint=>'ENDPOINT'][,model_params=>MODEL_PARAMS][,output_schema=>'OUTPUT_SCHEMA'][,connection_id=>'CONNECTION'][,request_type=>'REQUEST_TYPE'])

Arguments

AI.GENERATE takes the following arguments:

• PROMPT: aSTRING orSTRUCT value that specifiesthePROMPT value to send tothe model. The prompt must be the first argument that you specify.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 theSTRUCT value:

    Field type	Description	Examples
    STRING	A string literal, or the name of aSTRING column.	String literal: 'Describe the city of Seattle in 15 words' String column name: my_string_column
    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. Your input can contain at most one video object.	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')

    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, STRING, STRING>	('Describe the city of ', my_city_column, ' in 15 words')	'Describe the city ofmy_city_column_value in 15 words'
    STRUCT<STRING, ObjectRefRuntime>	('Describe the following city', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))	'Describe the following city'image

• ENDPOINT: aSTRING value that specifies the Vertex AIendpoint to use for the model. You can specify anygenerally availableorpreviewGemini model. If you specify the model name,BigQuery ML automatically identifies and uses the full endpointof the model. If you don't specify anENDPOINT value,BigQuery ML selects a recent stable version ofGemini to use. You can also specify theglobal endpoint.For example, to usegemini-3-pro-preview, specify the following endpoint:

  https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3-pro-preview

  Note: Don't use the global endpoint if you have requirements for the dataprocessing location, because when you use the global endpoint, you can'tcontrol or know the region where your processing requests are handled.Note: Using Gemini 2.5 models incurs charges for thethinking process.You can set a budget for the thinking process forGemini 2.5 Flash and Gemini 2.5 Flash-Lite models by usingthemodel_params argument to set thethinking_budget parameter.For an example, seeSet the thinking budget for a Gemini 2.5 Flash model.You can't set a budget for Gemini 2.5 Pro models.

• MODEL_PARAMS: aJSON literal that provides additional parameters tothe model. TheMODEL_PARAMS value must conform to thegenerateContent request body format.You can provide a value for any field in the request body except for thecontents field; thecontents field is populated with thePROMPTargument value.

• OUTPUT_SCHEMA: aSTRING value that specifies the schema of the output, inthe formfield_name1 data_type1, field_name2 data_type2, .... Supported datatypes includeSTRING,INT64,FLOAT64,BOOL,ARRAY, andSTRUCT.

• CONNECTION: aSTRING value specifying the connectionto use to communicate with the model, in the format[PROJECT_ID].LOCATION.CONNECTION_ID.For example,myproject.us.myconnection.

  If you don't specify a connection, then the query uses yourend-user credentials.

  For information about configuring permissions, seeSet permissions for BigQuery ML generative AI functions that call Vertex AI models.

• REQUEST_TYPE: aSTRING value that specifies the type of inferencerequest to send to the Gemini model. The request typedetermines what quota the request uses. Valid values are asfollows:

  • SHARED: The function only usesdynamic shared quota (DSQ).
  • DEDICATED: The function only usesProvisioned Throughput quota. The function returns an invalidquery error if Provisioned Throughput quota isn't available. For more information,seeUse Vertex AI Provisioned Throughput.

  • UNSPECIFIED: The function uses quota as follows:

    • If you haven't purchased Provisioned Throughput quota,the function uses DSQ quota.
    • If you have purchased Provisioned Throughput quota,the function uses the Provisioned Throughputquota first. If requests exceed the Provisioned Throughputquota, the overflow traffic uses DSQ quota.

  The default value isUNSPECIFIED.

Output

AI.GENERATE returns aSTRUCT value for each row in the table. The structcontains the following fields:

• result: aSTRING value containing the model's response to the prompt. Theresult isNULL if the request fails or is filtered byresponsible AI. If youspecify an output schema thenresult is replaced by your custom schema.
• full_response: a JSON value containing theresponsefrom theprojects.locations.endpoints.generateContentcall to the model. The generated text is in thetext element.
• status: aSTRING value that contains the API responsestatus for the corresponding row. This value is empty if the operation wassuccessful.

Examples

The following examples assume that you have granted theVertex AI User role to your personal account. SeeRun generative AI queries with end-user credentials for how-to.

Describe cities

To generate a short description of each city, you can call theAI.GENERATE function and select theresult field in the outputby running the following query:

SELECTcity,AI.GENERATE(("Give a short, one sentence description of ",city)).resultFROMUNNEST(["Seattle","Beijing","Paris","London"])city;

The result is similar to the following:

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+|  city   |                                                                           result                                                                            |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+| Seattle | Seattle is a vibrant city nestled between mountains and water, renowned for its coffee culture, tech industry, and rainy weather.                           || Beijing | Beijing is a vibrant metropolis where ancient history meets modern innovation, offering a captivating blend of cultural treasures and bustling urban life.  || Paris   | Paris is a romantic city renowned for its iconic landmarks, elegant architecture, and vibrant culture.                                                      || London  | London, a vibrant global metropolis brimming with history, culture, and innovation.                                                                         |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

Use structured output for entity extraction

The following query extracts information about a person from an unstructureddescription. The query uses theoutput_schema argument to set custom fields inthe output:

SELECTAI.GENERATE(input,output_schema=>'''name STRING,                        age INT64,                        address STRUCT,                        is_married BOOL,                        phone_number ARRAY,                        weight_in_pounds FLOAT64''')ASinfoFROM(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.'''ASinput);

The result is similar to the following:

+------------+----------+-----------------------------+-------------------+-----+| info.name  | info.age | info.address.street_address | info.address.city | ... |+------------+----------+-----------------------------+-------------------+-----+| John Smith | 20       | 1234 NW 45th St             | Kirkland          | ... |+------------+----------+-----------------------------+-------------------+-----+

Process images in a Cloud Storage bucket

The following query creates an external table from images of pet productsstored in a publicly available Cloud Storage bucket:

CREATESCHEMAIFNOTEXISTSbqml_tutorial;CREATEORREPLACEEXTERNALTABLEbqml_tutorial.product_imagesWITHCONNECTIONDEFAULTOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);

You can useAI.GENERATE to describe images and what's in them.To do that, construct your prompt from a natural language instructionand anObjectRefRuntime of the image. The following query asksGemini what each image is. It specifies anoutput_schema to structure the results with one column toname the items in the image and another column to providea description of the image.

SELECTuri,STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url)ASsigned_url,AI.GENERATE(("What is this: ",OBJ.GET_ACCESS_URL(ref,'r')),output_schema=>"image_description STRING, entities_in_the_image ARRAY<STRING>").*FROMbqml_tutorial.product_imagesWHEREuriLIKE"%aquarium%";

This result is similar to the following:

Use grounding with Google Search

The following query shows how to set themodel_params argument to useGoogle Search grounding for the request. You can only useGoogle Search grounding with Gemini 2.0 or later models.

SELECTname,AI.GENERATE(('Please check the weather of ',name,' for today.'),model_params=>JSON'{"tools": [{"googleSearch": {}}]}')FROMUNNEST(['Seattle','NYC','Austin'])ASname;

Set the thinking budget for a Gemini 2.5 Flash model

The following query shows how to set themodel_params argument to set themodel's thinking budget to0 for the request:

SELECTAI.GENERATE(('What is the capital of Monaco?'),endpoint=>'gemini-2.5-flash',model_params=>JSON'{"generation_config":{"thinking_config": {"thinking_budget": 0}}}');

Best Practices

This function passes your input to a Gemini model andincurs charges in Vertex AI each time it's called.For information about how to view these charges, seeTrack costs.To minimize Vertex AI charges when you useAI.GENERATE ona subset of data using theLIMIT clause, materialize the selected data to atable first. For example, the first of the following examples is preferable tothe second one:

CREATETABLEmydataset.citiesAS(SELECTcity_namefrommydataset.customersLIMIT10);SELECTcity,AI.GENERATE(('Give a short, one sentence description of ',city)).resultFROMmydataset.cities;

SELECTcity,AI.GENERATE(('Give a short, one sentence description of ',city)).resultFROM(SELECTcity_namefrommydataset.customersLIMIT10);

Writing the query results to a table beforehand helps you to ensure that youare sending as few rows as possible to the model.

Use Vertex AI Provisioned Throughput

You can useVertex AI Provisioned Throughputwith theAI.GENERATE function to provide consistent high throughput forrequests. The remote model that you reference in theAI.GENERATE functionmust use asupported Gemini modelin order for you to use Provisioned Throughput.

To use Provisioned Throughput,calculate your Provisioned Throughput requirementsand thenpurchase Provisioned Throughputquota before running theAI.GENERATE function. When you purchaseProvisioned Throughput, do the following:

• ForModel, select the same Gemini model as the one usedby the remote model that you reference in theAI.GENERATE function.

• ForRegion, select the same region as the dataset that containsthe remote model that you reference in theAI.GENERATE function, withthe following exceptions:

  • If the dataset is in theUS multi-region, select theus-central1region.
  • If the dataset is in theEU multi-region, select theeurope-west4region.

After you submit the order, wait for the order to be approved and appear on theOrders page.

After you have purchased Provisioned Throughput quota, use theREQUEST_TYPE argument to determine how theAI.GENERATE function usesthe quota.

Locations

You can runAI.GENERATE in all of theregionsthat support Gemini models, and also in theUS andEUmulti-regions.

Quotas

SeeVertex AI and Cloud AI service functions quotas and limits.

What's next

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