The AI.GENERATE_INT function

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document describes theAI.GENERATE_INT function, which lets youanalyze any combination of text and unstructured data. The function generates aSTRUCT that contains anINT64 value.

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

You can use theAI.GENERATE_INT 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_INT 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_INT only returnsresults based on the first two minutes.

Syntax

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

Arguments

AI.GENERATE_INT 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,'What is the square root of 9?'.

  • Specify aSTRUCT value that contains one or more fields. You can usethe following types of fields, or arrays containing these types,within theSTRUCT value:

    Field type	Description	Examples
    STRING	A string literal, or the name of aSTRING column.	String literal: 'What is the square root of 9?' 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>	('What is the atomic number of ', my_element_column, '?')	'What is the atomic number ofmy_element_column_value?'
    STRUCT<STRING, ObjectRefRuntime>	('What is the atomic number of the element described in the following document?', OBJ.GET_ACCESS_URL(pdf_objectref_column, 'r'))	'What is the atomic number of the element described in the following document?'pdf

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

  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.

• 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_INT returns aSTRUCT value for each row in the table. The structcontains the following fields:

• result: anINT64 value containing the model's response to the prompt. Theresult isNULL if the request fails or is filtered byresponsible AI.
• 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.For more information, seeRun generative AI queries with end-user credentials.

Use string input

To determine the population of each city, you can call theAI.GENERATE_INT function and select theresult field in the outputby running the following query:

SELECTcity,AI.GENERATE_INT(('What is the population of ',city)).resultFROMUNNEST(["Seattle","Beijing","Paris","London"])city;

The result is similar to the following:

+---------+----------+| city    | result   |+---------+----------+| Seattle | 771455   || Beijing | 21893000 || Paris   | 2165423  || London  | 9787426  |+---------+----------+

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:

SELECTcity,AI.GENERATE_INT(('What is the population of ',city),endpoint=>'gemini-2.5-flash',model_params=>JSON'{"generation_config":{"thinking_config": {"thinking_budget": 0}}}')FROMmydataset.cities;

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_INTon a 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_INT(('What is the population of ',city)).resultFROMmydataset.cities;

SELECTcity,AI.GENERATE_INT(('What is the population 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_INT function to provide consistent high throughput forrequests. The remote model that you reference in theAI.GENERATE_INT 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_INT 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_INT function.

• ForRegion, select the same region as the dataset that containsthe remote model that you reference in theAI.GENERATE_INT 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_INT function usesthe quota.

Locations

You can runAI.GENERATE_INT 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.