The AI.GENERATE_DOUBLE function

Preview

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

Note: For support during the preview, contact [e-mail].

This document describes the AI.GENERATE_DOUBLE function, which lets you analyze any combination of text and unstructured data from BigQuery standard tables. For each row in the table, the function generates a STRUCT that contains a FLOAT64 value.

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

You can use the AI.GENERATE_DOUBLE function to perform tasks such as classification and sentiment analysis.

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

Input

Using the AI.GENERATE_DOUBLE 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_DOUBLE only returns results based on the first two minutes.

To minimize Vertex AI charges when you use AI.GENERATE_DOUBLE in a query, use a table rather than a subquery in the FROM clause. For example, the first of the following examples is preferable to the second one:

SELECT
  city,
  AI.GENERATE_DOUBLE(
    ('What is the population of ', city, ' in millions?'),
    connection_id => 'us.test_connection',
    endpoint => 'gemini-2.0-flash').result
FROM mydataset.cities;

SELECT
  city,
  AI.GENERATE_DOUBLE(
    ('What is the population of ', city, ' in millions?'),
    connection_id => 'us.test_connection',
    endpoint => 'gemini-2.0-flash').result
FROM (SELECT city_name from mydataset.customers WHERE...);

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

Syntax

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

Arguments

AI.GENERATE_DOUBLE takes the following arguments:

• PROMPT: a STRING or STRUCT value that specifies the prompt to send to the model. The prompt must be the first argument that you specify. You can provide the prompt value in the following ways:

  • Specify a STRING value. For example, ('What is the square root of 10?').

  • 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: 'What is the square root of 10?' String column name: my_string_column
    ARRAY<STRING>	You can only use string literals in the array.	Array of string literals: ['What is ', 'the square root', ' of 10?']
    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>	('What is the population of Seattle in millions?')	'What is the population of Seattle in millions?'
    STRUCT<STRING, STRING, STRING>	('What is the population of ', my_city_column, ' in millions?')	'What is the population of my_city_column_value in millions?'
    STRUCT<STRING, ARRAY<STRING>>	('What is the population of ', ['Seattle', ' in millions?'])	'What is the population of Seattle in millions?'
    STRUCT<STRING, ObjectRefRuntime>	('What is the population in millions of the city shown in the following image?', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))	'What is the population in millions of the city shown in the following image?' image
    STRUCT<STRING, ObjectRefRuntime, ObjectRefRuntime>	('If the city in the first image is within the country of the second image, provide the city's population in millions', 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 the city's population in millions' city_image country_image

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

  Replace the following:

  • PROJECT_ID: the project ID of the project that contains the connection.
  • LOCATION: the location used by the connection. The connection must be in the same location as the dataset that contains the model.
  • CONNECTION_ID: the connection ID—for example, myconnection.

    You can get this value by viewing the connection details in the Google Cloud console and copying the value in the last section of the fully qualified connection ID that is shown in Connection ID. For example, projects/myproject/locations/connection_location/connections/myconnection.

  Important

  You need to grant the Vertex AI User role to the connection's service account.

  If you specify the ENDPOINT argument value as a URL, for example ENDPOINT = 'https://us-central1-aiplatform.googleapis.com/v1/projects/myproject/locations/us-central1/publishers/google/models/gemini-2.0-flash', grant this role in the same project that you specify in the URL.

  If you specify the ENDPOINT argument value by using the model name, for example ENDPOINT = 'gemini-2.0-flash', grant this role in the same project where you run the AI.GENERATE_DOUBLE function.

  For more information, see Grant or revoke a single IAM role.

• ENDPOINT: a STRING value that specifies the Vertex AI endpoint to use for the model. You can specify any generally available or preview Gemini model. If you specify the model name, BigQuery ML automatically identifies and uses the full endpoint of the model. If you don't specify an ENDPOINT value, BigQuery ML selects a recent stable version of Gemini to use.

  Note: Using Gemini 2.5 models incurs charges for the thinking process. You can set a budget for the thinking process for Gemini 2.5 Flash and Gemini 2.5 Flash-Lite models by using the model_params argument to set the thinking_budget parameter. For an example, see Set the thinking budget for a Gemini 2.5 Flash model. You can't set a budget for Gemini 2.5 Pro models.

• MODEL_PARAMS: a JSON literal that provides additional parameters to the model. The MODEL_PARAMS value must conform to the generateContent request body format. You can provide a value for any field in the request body except for the contents field; the contents field is populated with the PROMPT argument value.

Output

AI.GENERATE_DOUBLE returns a STRUCT value for each row in the table. The struct contains the following fields:

• result: a DOUBLE value containing the model's response to the prompt. The result is NULL if the request fails or is filtered by responsible AI.
• full_response: a JSON value containing the response from the projects.locations.endpoints.generateContent call to the model. The generated text 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.

Examples

The following examples demonstrate how to use the AI.GENERATE_DOUBLE function.

Use string input

Suppose you have the following table called mydataset.cities with a single city column:

+---------+
| city    |
+---------+
| Seattle |
| Beijing |
| Paris   |
| London  |
+---------+

To determine the population of each city in millions, you can call the AI.GENERATE_DOUBLE function and select the result field in the output by running the following query:

SELECT
  city,
  AI.GENERATE_DOUBLE(('What is the population of ', city, ' in millions?'),
  connection_id => 'us.test_connection',
  endpoint => 'gemini-2.0-flash').result
FROM mydataset.cities;

The result is similar to the following:

+---------+--------+
| city    | result |
+---------+--------+
| Seattle | 0.753  |
| Beijing | 21.89  |
| Paris   | 2.14   |
| London  | 9.5    |
+---------+--------+

Use ObjectRefRuntime input

Suppose you have the following table called mydataset.elements with a single STRUCT column that uses the ObjectRef format and contains PDF files that describe different elements:

+--------------------------------+-----------------+--------------------+---------------------------------------------------------------+
| element.uri                    | element.version | element.authorizer | element.details                                               |
+--------------------------------+-----------------+--------------------+---------------------------------------------------------------+
| gs://mybucket/cadmium.pdf      | 12345678        | us.conn            | {"gcs_metadata":{"content_type":"application/pdf","md5_hash"… |
+--------------------------------+-----------------+--------------------+---------------------------------------------------------------+
| gs://mybucket/lead.pdf         | 23456789        | us.conn            | {"gcs_metadata":{"content_type":"application/pdf","md5_hash"… |
+--------------------------------+-----------------+--------------------+---------------------------------------------------------------+
| gs://mybucket/krypton.pdf      | 234567890       | us.conn            | {"gcs_metadata":{"content_type":"application/pdf","md5_hash"… |
+--------------------------------+-----------------+--------------------+---------------------------------------------------------------+

To get the atomic weight for each element, call the AI.GENERATE_DOUBLE function and select the result field in the output by running the following query:

SELECT
  AI.GENERATE_DOUBLE(('What is the atomic weight of the element described in ', OBJ.GET_ACCESS_URL(element, 'r') , '?'),
  connection_id => 'us.test_connection',
  endpoint => 'gemini-2.0-flash').result
FROM mydataset.elements;

The result is similar to the following:

+---------+
| result  |
+---------+
| 112.41  |
| 207.20  |
| 83.798  |
+---------+

Set the thinking budget for a Gemini 2.5 Flash model

The following query shows how to set the model_params argument to set the model's thinking budget to 0 for the request:

SELECT
  city,
  AI.GENERATE_DOUBLE(('What is the population of ', city, ' in millions?'),
    connection_id => 'us.test_connection',
    endpoint => 'gemini-2.5-flash',
    model_params => JSON '{"generation_config":{"thinking_config": {"thinking_budget": 0}}}')
FROM mydataset.cities;

Locations

You can run AI.GENERATE_DOUBLE in all of the regions that support Gemini models, and also in the US and EU multi-regions.

Quotas

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

What's next

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