Category: General-Purpose AI Function (Preview)

Description

Generates a floating-point numeric value for each row based on a natural language prompt and data analysis. Provides full control over prompts and models while returning a FLOAT64 output. Ideal for scores, ratings, percentages, or any decimal numeric extraction.

Use Cases

Sentiment Scoring: Rate sentiment on continuous scale (0.0-1.0)

Percentage Extraction: Extract percentages from text

Quality Metrics: Calculate quality scores with decimals

Confidence Scores: Generate confidence ratings

Price Extraction: Extract monetary values from text

Syntax

AI.GENERATE_DOUBLE(
  prompt => 'PROMPT',
  [, data => INPUT_DATA ]
  [, model => 'MODEL_ENDPOINT' ]
  [, parameters => STRUCT(...) ]
  [, connection_id => 'CONNECTION' ]
)

AI.GENERATE_DOUBLE(
  prompt => 'PROMPT',
  [, data => INPUT_DATA ]
  [, model => 'MODEL_ENDPOINT' ]
  [, parameters => STRUCT(...) ]
  [, connection_id => 'CONNECTION' ]
)

AI.GENERATE_DOUBLE(
  prompt => 'PROMPT',
  [, data => INPUT_DATA ]
  [, model => 'MODEL_ENDPOINT' ]
  [, parameters => STRUCT(...) ]
  [, connection_id => 'CONNECTION' ]
)

Parameters

prompt: Natural language instruction/question (required)

data: Text or ObjectRefRuntime values to analyze

model: Gemini model endpoint (e.g., 'gemini-2.0-flash-exp')

parameters: Generation config (temperature, max_tokens, etc.)

connection_id: Vertex AI connection

Code Examples

Example 1: Sentiment Score (0-1 Scale)

SELECT 
  review_id,
  review_text,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the sentiment of this review from 0.0 (very negative) to 1.0 (very positive).',
    data => review_text,
    connection_id => 'us.my_vertex_connection'
  ) AS sentiment_score
FROM customer_reviews
ORDER BY

SELECT 
  review_id,
  review_text,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the sentiment of this review from 0.0 (very negative) to 1.0 (very positive).',
    data => review_text,
    connection_id => 'us.my_vertex_connection'
  ) AS sentiment_score
FROM customer_reviews
ORDER BY

SELECT 
  review_id,
  review_text,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the sentiment of this review from 0.0 (very negative) to 1.0 (very positive).',
    data => review_text,
    connection_id => 'us.my_vertex_connection'
  ) AS sentiment_score
FROM customer_reviews
ORDER BY

Example 2: Extract Percentage Values

SELECT 
  report_id,
  AI.GENERATE_DOUBLE(
    prompt => 'What is the year-over-year growth percentage mentioned in this text? Return as decimal (e.g., 15.5 for 15.5%).',
    data => report_text,
    connection_id => 'us.my_vertex_connection'
  ) AS yoy_growth_pct
FROM

SELECT 
  report_id,
  AI.GENERATE_DOUBLE(
    prompt => 'What is the year-over-year growth percentage mentioned in this text? Return as decimal (e.g., 15.5 for 15.5%).',
    data => report_text,
    connection_id => 'us.my_vertex_connection'
  ) AS yoy_growth_pct
FROM

SELECT 
  report_id,
  AI.GENERATE_DOUBLE(
    prompt => 'What is the year-over-year growth percentage mentioned in this text? Return as decimal (e.g., 15.5 for 15.5%).',
    data => report_text,
    connection_id => 'us.my_vertex_connection'
  ) AS yoy_growth_pct
FROM

Example 3: Quality Assessment

SELECT 
  product_id,
  product_name,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the overall quality of this product based on the description, from 0.0 to 10.0.',
    data => CONCAT('Name: ', product_name, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score
FROM products
WHERE category = 'electronics'

SELECT 
  product_id,
  product_name,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the overall quality of this product based on the description, from 0.0 to 10.0.',
    data => CONCAT('Name: ', product_name, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score
FROM products
WHERE category = 'electronics'

SELECT 
  product_id,
  product_name,
  AI.GENERATE_DOUBLE(
    prompt => 'Rate the overall quality of this product based on the description, from 0.0 to 10.0.',
    data => CONCAT('Name: ', product_name, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS quality_score
FROM products
WHERE category = 'electronics'

Example 4: Confidence Scoring

SELECT 
  prediction_id,
  AI.GENERATE_DOUBLE(
    prompt => 'Based on the evidence provided, rate your confidence in this conclusion from 0.0 (no confidence) to 1.0 (complete confidence).',
    data => STRUCT(
      hypothesis,
      supporting_evidence,
      contradicting_evidence
    ),
    connection_id => 'us.my_vertex_connection'
  ) AS confidence_score
FROM

SELECT 
  prediction_id,
  AI.GENERATE_DOUBLE(
    prompt => 'Based on the evidence provided, rate your confidence in this conclusion from 0.0 (no confidence) to 1.0 (complete confidence).',
    data => STRUCT(
      hypothesis,
      supporting_evidence,
      contradicting_evidence
    ),
    connection_id => 'us.my_vertex_connection'
  ) AS confidence_score
FROM

SELECT 
  prediction_id,
  AI.GENERATE_DOUBLE(
    prompt => 'Based on the evidence provided, rate your confidence in this conclusion from 0.0 (no confidence) to 1.0 (complete confidence).',
    data => STRUCT(
      hypothesis,
      supporting_evidence,
      contradicting_evidence
    ),
    connection_id => 'us.my_vertex_connection'
  ) AS confidence_score
FROM

Example 5: Price Extraction from Text

SELECT 
  listing_id,
  description,
  AI.GENERATE_DOUBLE(
    prompt => 'Extract the price mentioned in this listing. Return only the numeric value (e.g., 99.99).',
    data => description,
    connection_id => 'us.my_vertex_connection'
  ) AS extracted_price
FROM marketplace_listings
WHERE price_field IS NULL

SELECT 
  listing_id,
  description,
  AI.GENERATE_DOUBLE(
    prompt => 'Extract the price mentioned in this listing. Return only the numeric value (e.g., 99.99).',
    data => description,
    connection_id => 'us.my_vertex_connection'
  ) AS extracted_price
FROM marketplace_listings
WHERE price_field IS NULL

SELECT 
  listing_id,
  description,
  AI.GENERATE_DOUBLE(
    prompt => 'Extract the price mentioned in this listing. Return only the numeric value (e.g., 99.99).',
    data => description,
    connection_id => 'us.my_vertex_connection'
  ) AS extracted_price
FROM marketplace_listings
WHERE price_field IS NULL

Data Output Examples

Sentiment Scores

YoY Growth Extraction

Quality Assessment

Best Practices

Define the numeric range: Be explicit about min/max values

Specify decimal precision: If needed, mention expected precision

Provide scale context: "Rate from 0.0 to 1.0, where..."

Consider AI.SCORE: Use AI.SCORE for ranking tasks (better optimization)

Validate outputs: Check for values outside expected range

When to Use

✅ Use when you need custom decimal numeric values

✅ Use for percentage or ratio extraction

✅ Use when you need full prompt control

✅ Use for continuous scoring (not just integers)

Alternatives

AI.SCORE: For ranking with managed optimization and auto-generated scoring rubrics

AI.GENERATE_INT: For integer values only

AI.GENERATE: For structured output with multiple fields

CAST/REGEXP_EXTRACT: For simple numeric extraction (cheaper)

Platform Support

Regions: All Gemini-supported regions + US/EU multi-regions

Models: Gemini 2.0 Flash, Gemini 1.5 Pro, Gemini 1.5 Flash

Preview Status: Currently in Preview (Pre-GA)

Cost: Charged per Vertex AI API call

Returns

FLOAT64 value. Returns NULL if the Vertex AI call fails or if the model cannot generate a valid number.

Comparison with AI.SCORE

AI.GENERATE_DOUBLE:

• Full control over prompt

• Manual scoring criteria definition

• More flexible for extraction tasks

AI.SCORE:

• Automatic prompt optimization

• Consistent scoring rubrics

• Better for ranking and ordering

• Optimized for cost and quality