A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text below:

The ML.GENERATE_TEXT function | BigQuery

Skip to main content

Stay organized with collections Save and categorize content based on your preferences.

The ML.GENERATE_TEXT function

This document describes the ML.GENERATE_TEXT function, which lets you perform generative natural language tasks by using any combination of text and unstructured data from BigQuery standard tables, or unstructured data from BigQuery object tables.

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 types of remote models are supported:

Several of the ML.GENERATE_TEXT function's arguments provide the parameters that shape the Vertex AI model's response.

You can use the ML.GENERATE_TEXT 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 or Design multimodal prompts.

Input

The input you can provide to ML.GENERATE_TEXT varies depending on the Vertex AI model that you reference from your remote model.

Input for Gemini models

When you use the Gemini models, you can use the following types of input:

When you analyze unstructured data, that data must meet the following requirements:

Input for other models

For all other types of models, you can analyze text data from a standard table.

Syntax for standard tables

ML.GENERATE_TEXT syntax differs depending on the Vertex AI model that your remote model references. Choose the option appropriate for your use case.

Gemini 
ML.GENERATE_TEXT(
MODEL project_id.dataset.model,
{ TABLE project_id.dataset.table | (query_statement) },
STRUCT(
  [max_output_tokens AS max_output_tokens]
  [, top_p AS top_p]
  [, temperature AS temperature]
  [, flatten_json_output AS flatten_json_output]
  [, stop_sequences AS stop_sequences]
  [, ground_with_google_search AS ground_with_google_search]
  [, safety_settings AS safety_settings]
  [, request_type AS request_type])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

The default value is UNSPECIFIED.

Details

The model and input table must be in the same region.

Claude

You must enable Claude models in Vertex AI before you can use them. For more information, see Enable a partner model.

ML.GENERATE_TEXT(
MODEL project_id.dataset.model,
{ TABLE project_id.dataset.table | (query_statement) },
STRUCT(
  [max_output_tokens AS max_output_tokens]
  [, top_k AS top_k]
  [, top_p AS top_p]
  [, flatten_json_output AS flatten_json_output])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

Details

The model and input table must be in the same region.

Llama

You must enable Llama models in Vertex AI before you can use them. For more information, see Enable a partner model.

ML.GENERATE_TEXT(
MODEL project_id.dataset.model,
{ TABLE project_id.dataset.table | (query_statement) },
STRUCT(
  [max_output_tokens AS max_output_tokens]
  [, top_p AS top_p]
  [, temperature AS temperature]
  [, flatten_json_output AS flatten_json_output]
  [, stop_sequences AS stop_sequences])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

Details

The model and input table must be in the same region.

Mistral AI

You must enable Mistral AI models in Vertex AI before you can use them. For more information, see Enable a partner model.

ML.GENERATE_TEXT(
MODEL project_id.dataset.model,
{ TABLE project_id.dataset.table | (query_statement) },
STRUCT(
  [max_output_tokens AS max_output_tokens]
  [, top_p AS top_p]
  [, temperature AS temperature]
  [, flatten_json_output AS flatten_json_output]
  [, stop_sequences AS stop_sequences])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

Details

The model and input table must be in the same region.

Open models Note: You must deploy open models in Vertex AI before you can use them. For more information, see Deploy an open model. 
ML.GENERATE_TEXT(
MODEL project_id.dataset.model,
{ TABLE project_id.dataset.table | (query_statement) },
STRUCT(
  [max_output_tokens AS max_output_tokens]
  [, top_k AS top_k]
  [, top_p AS top_p]
  [, temperature AS temperature]
  [, flatten_json_output AS flatten_json_output])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

Details

The model and input table must be in the same region.

Syntax for object tables

Use the following syntax to use ML.GENERATE_TEXT with Gemini models and object table data.

ML.GENERATE_TEXT(
MODEL `project_id.dataset.model`,
TABLE `project_id.dataset.table`,
STRUCT(
  prompt AS prompt
  [, max_output_tokens AS max_output_tokens]
  [, top_p AS top_p]
  [, temperature AS temperature]
  [, flatten_json_output AS flatten_json_output]
  [, stop_sequences AS stop_sequences]
  [, safety_settings AS safety_settings])
)
Arguments

ML.GENERATE_TEXT takes the following arguments:

Details

The model and input table must be in the same region.

Output

ML.GENERATE_TEXT returns the input table plus the following columns:

Gemini API models Claude models LLama models Mistral AI models Open models Examples Text analysis

Example 1

This example shows a request to a Claude model that provides a single prompt.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.claude_model`,
    (SELECT 'What is the purpose of dreams?' AS prompt));

Example 2

This example shows a request to a Gemini model that provides prompt data from a table column named question that is aliased as prompt.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.gemini_model`,
    (SELECT question AS prompt FROM `mydataset.prompt_table`));

Example 3

This example shows a request to a Gemini model that concatenates strings and a table column to provide the prompt data.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.gemini_model`,
    (
      SELECT
        CONCAT(
          'Classify the sentiment of the following text as positive or negative.Text:',
          input_column,
          'Sentiment:') AS prompt
      FROM `mydataset.input_table`));

Example 4

This example shows a request a Gemini model that excludes model responses that contain the strings Golf or football.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.gemini_model`,
    TABLE `mydataset.prompt_table`,
    STRUCT(
      .15 AS TEMPERATURE,
      TRUE AS flatten_json_output,
      ['Golf', 'football'] AS stop_sequences));

Example 5

This example shows a request to a Gemini model with the following characteristics:

Note: ground_with_google_search only work with Gemini 1.5 flash and Gemini 1.5 pro models. 
SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.gemini_model`,
    TABLE `mydataset.prompt_table`,
    STRUCT(
      TRUE AS flatten_json_output,
      TRUE AS ground_with_google_search));

Example 6

This example shows a request to a Gemini model with the following characteristics:

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.gemini_model`,
    TABLE `mydataset.prompt_table`,
    STRUCT(
      75 AS max_output_tokens,
      [STRUCT('HARM_CATEGORY_HATE_SPEECH' AS category,
        'BLOCK_LOW_AND_ABOVE' AS threshold),
      STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category,
        'BLOCK_MEDIUM_AND_ABOVE' AS threshold)] AS safety_settings));
Visual content analysis

Example 1

This example adds product description information to a table by analyzing the object data in an ObjectRef column named image:

UPDATE mydataset.products
SET
  image_description = (
    SELECT
      ml_generate_text_llm_result
    FROM
      ML.GENERATE_TEXT(
        MODEL `mydataset.gemini_model`,
        (
          SELECT
            ('Can you describe the following image?', OBJ.GET_ACCESS_URL(image, 'r')) AS prompt
        ),
        STRUCT(
          TRUE AS FLATTEN_JSON_OUTPUT))
  )
WHERE image IS NOT NULL;

Example 2

This example analyzes visual content from an object table that's named dogs and identifies the breed of dog contained in the content. The content returned is filtered by the specified safety settings:

SELECT
  uri,
  ml_generate_text_llm_result
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.dog_identifier_model`,
    TABLE `mydataset.dogs`
      STRUCT(
        'What is the breed of the dog?' AS PROMPT,
        .01 AS TEMPERATURE,
        TRUE AS FLATTEN_JSON_OUTPUT,
        [STRUCT('HARM_CATEGORY_HATE_SPEECH' AS category,
          'BLOCK_LOW_AND_ABOVE' AS threshold),
        STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category,
          'BLOCK_MEDIUM_AND_ABOVE' AS threshold)] AS safety_settings));
Audio content analysis

This example translates and transcribes audio content from an object table that's named feedback:

SELECT
  uri,
  ml_generate_text_llm_result
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.audio_model`,
        TABLE `mydataset.feedback`,
          STRUCT(
          'What is the content of this audio clip, translated into Spanish?' AS PROMPT,
          .01 AS TEMPERATURE,
          TRUE AS FLATTEN_JSON_OUTPUT));
PDF content analysis

This example classifies PDF content from an object table that's named documents:

SELECT
  uri,
  ml_generate_text_llm_result
FROM
  ML.GENERATE_TEXT(
    MODEL
      `mydataset.classify_model`
        TABLE `mydataset.documents`
          STRUCT(
          'Classify this document using the following categories: legal, tax-related, real estate' AS PROMPT,
          .2 AS TEMPERATURE,
          TRUE AS FLATTEN_JSON_OUTPUT));
Use Vertex AI Provisioned Throughput

You can use Vertex AI Provisioned Throughput with the ML.GENERATE_TEXT function to provide consistent high throughput for requests. The remote model that you reference in the ML.GENERATE_TEXT function must use a supported Gemini model in order for you to use Provisioned Throughput.

To use Provisioned Throughput, calculate your Provisioned Throughput requirements and then purchase Provisioned Throughput quota before running the ML.GENERATE_TEXT function. When you purchase Provisioned Throughput, do the following:

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

After you have purchased Provisioned Throughput quota, use the request_type argument to determine how the ML.GENERATE_TEXT function uses the quota.

Locations

ML.GENERATE_TEXT must run in the same region or multi-region as the remote model that the function references. You can create models in the following locations:

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. To try the BigQuery ML remote inference SQL script, see Handle quota errors by calling ML.GENERATE_TEXT iteratively.

What's next

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-09 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-09 UTC."],[[["The `ML.GENERATE_TEXT` function enables generative natural language tasks using text from BigQuery standard or object tables by sending requests to a BigQuery ML remote model linked to a Vertex AI model."],["This function supports various remote models, including pre-trained Vertex AI models, Anthropic Claude models, and supported open models, allowing for versatile text generation capabilities."],["Input to `ML.GENERATE_TEXT` varies based on the referenced Vertex AI model, with Gemini models supporting content analysis from object tables and other models supporting text generation from prompts in queries or standard table columns."],["The function's syntax differs depending on the Vertex AI model used, offering different arguments like `max_output_tokens`, `top_p`, `temperature`, and `safety_settings` to control response generation and filter content."],["`ML.GENERATE_TEXT` can be used for diverse applications, including text classification, sentiment analysis, image captioning, transcription, and also includes capabilities to ground responses with public web data for certain models."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4