Category: General-Purpose AI Function - Table-Valued Function (TVF)

Description

Table-valued function that generates text using remote models deployed to Vertex AI. Supports Gemini models, partner models (Anthropic Claude, Mistral, Llama), and open models. Unlike scalar functions, this returns a table with multiple columns including the generated text and metadata.

Use Cases

Content Generation: Create product descriptions, summaries, or marketing copy

Translation: Translate text between languages

Summarization: Generate summaries of long documents

Question Answering: Answer questions about your data

Code Generation: Generate SQL queries or code snippets

Syntax

AI.GENERATE_TEXT(
  MODEL remote_model_name,
  prompt_column_or_literal,
  [, STRUCT(param => value, ...) ]
)

AI.GENERATE_TEXT(
  MODEL remote_model_name,
  prompt_column_or_literal,
  [, STRUCT(param => value, ...) ]
)

AI.GENERATE_TEXT(
  MODEL remote_model_name,
  prompt_column_or_literal,
  [, STRUCT(param => value, ...) ]
)

Parameters

MODEL: Remote model created with CREATE REMOTE MODEL

prompt: Column name or string literal containing the prompt

STRUCT (optional): Model parameters like temperature, max_output_tokens, top_p, etc.

Code Examples

Example 1: Create Remote Model and Generate Text

-- Step 1: Create remote model
CREATE OR REPLACE MODEL my_dataset.gemini_model
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-2.0-flash-exp'
);

-- Step 2: Generate text
SELECT 
  product_id,
  product_name,
  ml_generate_text_llm_result AS generated_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Write a compelling product description for: ', product_name, '. Features: ', features),
    STRUCT(
      0.7 AS temperature,
      256 AS max_output_tokens
    )
  ),
  products
WHERE description IS NULL

-- Step 1: Create remote model
CREATE OR REPLACE MODEL my_dataset.gemini_model
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-2.0-flash-exp'
);

-- Step 2: Generate text
SELECT 
  product_id,
  product_name,
  ml_generate_text_llm_result AS generated_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Write a compelling product description for: ', product_name, '. Features: ', features),
    STRUCT(
      0.7 AS temperature,
      256 AS max_output_tokens
    )
  ),
  products
WHERE description IS NULL

-- Step 1: Create remote model
CREATE OR REPLACE MODEL my_dataset.gemini_model
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-2.0-flash-exp'
);

-- Step 2: Generate text
SELECT 
  product_id,
  product_name,
  ml_generate_text_llm_result AS generated_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Write a compelling product description for: ', product_name, '. Features: ', features),
    STRUCT(
      0.7 AS temperature,
      256 AS max_output_tokens
    )
  ),
  products
WHERE description IS NULL

Example 2: Analyze Images from Object Table

-- Create object table
CREATE OR REPLACE EXTERNAL TABLE product_images.photos
WITH CONNECTION us.my_vertex_connection
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://my-bucket/products/*.jpg']
);

-- Analyze images
SELECT 
  signed_url,
  ml_generate_text_llm_result AS image_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    'Describe this product image in detail, focusing on key features and colors.',
    STRUCT(0.5 AS temperature)
  ),
  EXTERNAL_OBJECT_TRANSFORM(
    TABLE product_images.photos,
    ['SIGNED_URL']
  )

-- Create object table
CREATE OR REPLACE EXTERNAL TABLE product_images.photos
WITH CONNECTION us.my_vertex_connection
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://my-bucket/products/*.jpg']
);

-- Analyze images
SELECT 
  signed_url,
  ml_generate_text_llm_result AS image_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    'Describe this product image in detail, focusing on key features and colors.',
    STRUCT(0.5 AS temperature)
  ),
  EXTERNAL_OBJECT_TRANSFORM(
    TABLE product_images.photos,
    ['SIGNED_URL']
  )

-- Create object table
CREATE OR REPLACE EXTERNAL TABLE product_images.photos
WITH CONNECTION us.my_vertex_connection
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://my-bucket/products/*.jpg']
);

-- Analyze images
SELECT 
  signed_url,
  ml_generate_text_llm_result AS image_description
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    'Describe this product image in detail, focusing on key features and colors.',
    STRUCT(0.5 AS temperature)
  ),
  EXTERNAL_OBJECT_TRANSFORM(
    TABLE product_images.photos,
    ['SIGNED_URL']
  )

Example 3: Batch Question Answering

SELECT 
  question_id,
  question_text,
  ml_generate_text_llm_result AS answer
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Answer this question based on the context: ', question_text, '. Context: ', context_text)
  ),
  customer_questions
WHERE answered = FALSE

SELECT 
  question_id,
  question_text,
  ml_generate_text_llm_result AS answer
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Answer this question based on the context: ', question_text, '. Context: ', context_text)
  ),
  customer_questions
WHERE answered = FALSE

SELECT 
  question_id,
  question_text,
  ml_generate_text_llm_result AS answer
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_model,
    CONCAT('Answer this question based on the context: ', question_text, '. Context: ', context_text)
  ),
  customer_questions
WHERE answered = FALSE

Example 4: Use Grounding for Factual Accuracy

-- Create model with grounding
CREATE OR REPLACE MODEL my_dataset.gemini_grounded
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-pro',
  grounding = STRUCT(
    'google_search_retrieval' AS sources
  )
);

-- Generate grounded responses
SELECT 
  query,
  ml_generate_text_llm_result AS grounded_response
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_grounded,
    query
  ),

-- Create model with grounding
CREATE OR REPLACE MODEL my_dataset.gemini_grounded
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-pro',
  grounding = STRUCT(
    'google_search_retrieval' AS sources
  )
);

-- Generate grounded responses
SELECT 
  query,
  ml_generate_text_llm_result AS grounded_response
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_grounded,
    query
  ),

-- Create model with grounding
CREATE OR REPLACE MODEL my_dataset.gemini_grounded
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-pro',
  grounding = STRUCT(
    'google_search_retrieval' AS sources
  )
);

-- Generate grounded responses
SELECT 
  query,
  ml_generate_text_llm_result AS grounded_response
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.gemini_grounded,
    query
  ),

Example 5: Supervised Tuning

-- Create tuned model
CREATE OR REPLACE MODEL my_dataset.tuned_gemini
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-flash',
  supervised_tuning = STRUCT(
    'bq://my_project.my_dataset.training_data' AS training_dataset_uri,
    5 AS epochs
  )
);

-- Use tuned model
SELECT 
  input_text,
  ml_generate_text_llm_result AS custom_output
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.tuned_gemini,
    input_text
  ),

-- Create tuned model
CREATE OR REPLACE MODEL my_dataset.tuned_gemini
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-flash',
  supervised_tuning = STRUCT(
    'bq://my_project.my_dataset.training_data' AS training_dataset_uri,
    5 AS epochs
  )
);

-- Use tuned model
SELECT 
  input_text,
  ml_generate_text_llm_result AS custom_output
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.tuned_gemini,
    input_text
  ),

-- Create tuned model
CREATE OR REPLACE MODEL my_dataset.tuned_gemini
REMOTE WITH CONNECTION `us.my_vertex_connection`
OPTIONS (
  endpoint = 'gemini-1.5-flash',
  supervised_tuning = STRUCT(
    'bq://my_project.my_dataset.training_data' AS training_dataset_uri,
    5 AS epochs
  )
);

-- Use tuned model
SELECT 
  input_text,
  ml_generate_text_llm_result AS custom_output
FROM 
  AI.GENERATE_TEXT(
    MODEL my_dataset.tuned_gemini,
    input_text
  ),

Data Output Examples

Product Descriptions

Image Descriptions

Best Practices

Choose the right model: Gemini for multimodal, Claude for reasoning, Llama for open source

Use grounding for facts: Enable grounding when factual accuracy is critical

Tune for specific tasks: Use supervised tuning for domain-specific applications

Control generation: Adjust temperature (lower for factual, higher for creative)

Batch processing: Process multiple rows efficiently in a single query

Monitor costs: Track Vertex AI API usage

Supported Models

Gemini Models:

• gemini-2.0-flash-exp

• gemini-1.5-pro

• gemini-1.5-flash

Partner Models:

• Anthropic Claude (claude-3-sonnet, claude-3-opus)

• Mistral AI (mistral-large, mistral-small)

• Llama (llama-3-70b, llama-3-8b)

Open Models:

• Gemma

• And other supported open models

When to Use

✅ Use for text generation from remote models

✅ Use when you need Gemini model features (grounding, safety, multimodal)

✅ Use for batch text processing

✅ Use when you want to leverage partner or open models

Alternatives

AI.GENERATE: For inline inference with structured output

AI.GENERATE_TEXT (Vertex AI endpoint): Direct endpoint calls

Partner APIs: Direct API calls outside BigQuery

Platform Support

Regions: Model-dependent, see Vertex AI documentation

Preview Status: Generally Available (GA) for Gemini models

Cost: Charged per Vertex AI API call based on model pricing

Provisioned Throughput: Available for supported Gemini models

Output Schema

The function returns a table with these columns:

ml_generate_text_llm_result: The generated text

ml_generate_text_status: Success/error status

ml_generate_text_rai_result: Safety ratings (for Gemini)

ml_generate_text_llm_citations: Citation information (if applicable)