Choose a text generation function

This document provides a comparison of the BigQuery MLAI.GENERATE_TEXTandAI.GENERATEtext generation functions. You can use the information in this document tohelp you decide which function to use in cases where the functions haveoverlapping capabilities.

Function similarities

TheAI.GENERATE_TEXT andAI.GENERATE functions are similar in thefollowing ways:

  • Purpose: Generate text by passing a prompt to a large language model(LLM).
  • Billing: Incur BigQuery ML charges for data processed.For more information, seeBigQuery ML pricing.Incur Vertex AI charges for calls to the LLM. If you are usinga Gemini 2.0 or greater model, the call is billed at the batchAPI rate. For more information, seeCost of building and deploying AI models in Vertex AI.
  • Scalability: Process between 1 million and 10 million rows for each a6-hour query job. Actual throughput depends on factors like the average tokenlength in the input rows. For more information, seeGenerative AI functions.
  • Input data: Support both text and unstructured data fromBigQuery standard tables and object tables.

Function differences

Use the following table to evaluate the differences between theAI.GENERATE_TEXT andAI.GENERATE functions:

AI.GENERATE_TEXTAI.GENERATE
Function signatureA table-valued function that takes a table as input and returns a table as output.A scalar function that takes a single value as input and returns a single value as output.
Supported LLMs
  • Gemini models
  • Partner models such as Anthropic Claude, Llama, and Mistral AI
  • open models
Gemini models
Function output content

Function output content for Gemini models:

  • Generated text
  • Responsible AI (RAI) results
  • Google Search grounding results, if enabled
  • LLM call status

Function output content for other types of models:

  • Generated text
  • LLM call status
  • Generated text
  • Full model response in JSON format
  • LLM call status
Function output formatGenerated values are returned in a single JSON column or in separate table columns, depending on theflatten_json_output argument value.Generated values are returned as fields in aSTRUCT object.
User journeyYou must create aremote model before using the function.You can use the function directly, without the need to create a remote model.
Permission setupYou must manually create a BigQuery connection, and grant the Vertex AI User role permission to the service account of the connection. You can skip this step if you are using the BigQuerydefault connection.You can call this function using yourend-user credentials.
AdvantagesAllows for more flexible input and output formats.Easier to integrate into SQL queries.
Extended functionsYou can use theAI.GENERATE_TABLE function to generate output that is structured according to a SQL output schema that you specify.You can use theAI.GENERATE_BOOL,AI.GENERATE_INT, andAI.GENERATE_DOUBLE functions to generate different types of scalar values.

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

Last updated 2025-12-15 UTC.