Category: General-Purpose AI Function (Preview)

Description

Generates an integer value for each row based on a natural language prompt and data analysis. Provides full control over prompts and models while returning a simple INT64 output. Useful for counting, categorizing numerically, or extracting integer values from text.

Use Cases

Data Extraction: Extract numeric values from text (dates, quantities, counts)

Categorization: Assign numeric codes to categories

Priority Assignment: Determine priority levels (1-5)

Counting: Count specific elements in text or images

Severity Rating: Rate issues on integer scales

Syntax

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

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

AI.GENERATE_INT(
  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: Extract Year from Text

SELECT 
  document_id,
  text_content,
  AI.GENERATE_INT(
    prompt => 'What year is mentioned in this text? Return only the 4-digit year.',
    data => text_content,
    connection_id => 'us.my_vertex_connection'
  ) AS mentioned_year
FROM

SELECT 
  document_id,
  text_content,
  AI.GENERATE_INT(
    prompt => 'What year is mentioned in this text? Return only the 4-digit year.',
    data => text_content,
    connection_id => 'us.my_vertex_connection'
  ) AS mentioned_year
FROM

SELECT 
  document_id,
  text_content,
  AI.GENERATE_INT(
    prompt => 'What year is mentioned in this text? Return only the 4-digit year.',
    data => text_content,
    connection_id => 'us.my_vertex_connection'
  ) AS mentioned_year
FROM

Example 2: Count Mentions

SELECT 
  article_id,
  title,
  AI.GENERATE_INT(
    prompt => 'How many times is the company name "Acme Corp" mentioned in this article?',
    data => article_text,
    connection_id => 'us.my_vertex_connection'
  ) AS mention_count
FROM news_articles
WHERE published_date >= CURRENT_DATE() - 30

SELECT 
  article_id,
  title,
  AI.GENERATE_INT(
    prompt => 'How many times is the company name "Acme Corp" mentioned in this article?',
    data => article_text,
    connection_id => 'us.my_vertex_connection'
  ) AS mention_count
FROM news_articles
WHERE published_date >= CURRENT_DATE() - 30

SELECT 
  article_id,
  title,
  AI.GENERATE_INT(
    prompt => 'How many times is the company name "Acme Corp" mentioned in this article?',
    data => article_text,
    connection_id => 'us.my_vertex_connection'
  ) AS mention_count
FROM news_articles
WHERE published_date >= CURRENT_DATE() - 30

Example 3: Assign Priority Levels

SELECT 
  ticket_id,
  subject,
  description,
  AI.GENERATE_INT(
    prompt => 'Based on urgency and business impact, assign a priority from 1 (lowest) to 5 (highest).',
    data => CONCAT('Subject: ', subject, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS priority_level
FROM support_tickets
WHERE status = 'new'

SELECT 
  ticket_id,
  subject,
  description,
  AI.GENERATE_INT(
    prompt => 'Based on urgency and business impact, assign a priority from 1 (lowest) to 5 (highest).',
    data => CONCAT('Subject: ', subject, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS priority_level
FROM support_tickets
WHERE status = 'new'

SELECT 
  ticket_id,
  subject,
  description,
  AI.GENERATE_INT(
    prompt => 'Based on urgency and business impact, assign a priority from 1 (lowest) to 5 (highest).',
    data => CONCAT('Subject: ', subject, '. Description: ', description),
    connection_id => 'us.my_vertex_connection'
  ) AS priority_level
FROM support_tickets
WHERE status = 'new'

Example 4: Count Objects in Images

SELECT 
  image_id,
  AI.GENERATE_INT(
    prompt => 'Count the number of people visible in this image.',
    data => OBJ.GET_ACCESS_URL(image_ref, 'r'),
    connection_id => 'us.my_vertex_connection'
  ) AS person_count
FROM event_photos
WHERE event_name = 'Annual Conference 2025'

SELECT 
  image_id,
  AI.GENERATE_INT(
    prompt => 'Count the number of people visible in this image.',
    data => OBJ.GET_ACCESS_URL(image_ref, 'r'),
    connection_id => 'us.my_vertex_connection'
  ) AS person_count
FROM event_photos
WHERE event_name = 'Annual Conference 2025'

SELECT 
  image_id,
  AI.GENERATE_INT(
    prompt => 'Count the number of people visible in this image.',
    data => OBJ.GET_ACCESS_URL(image_ref, 'r'),
    connection_id => 'us.my_vertex_connection'
  ) AS person_count
FROM event_photos
WHERE event_name = 'Annual Conference 2025'

Example 5: Severity Rating

SELECT 
  incident_id,
  AI.GENERATE_INT(
    prompt => 'Rate the severity of this security incident from 1 (minor) to 10 (critical).',
    data => incident_description,
    connection_id => 'us.my_vertex_connection'
  ) AS severity_rating
FROM security_incidents
ORDER BY severity_rating DESC

SELECT 
  incident_id,
  AI.GENERATE_INT(
    prompt => 'Rate the severity of this security incident from 1 (minor) to 10 (critical).',
    data => incident_description,
    connection_id => 'us.my_vertex_connection'
  ) AS severity_rating
FROM security_incidents
ORDER BY severity_rating DESC

SELECT 
  incident_id,
  AI.GENERATE_INT(
    prompt => 'Rate the severity of this security incident from 1 (minor) to 10 (critical).',
    data => incident_description,
    connection_id => 'us.my_vertex_connection'
  ) AS severity_rating
FROM security_incidents
ORDER BY severity_rating DESC

Data Output Examples

Year Extraction

Priority Assignment

Best Practices

Specify the expected range: Be clear about min/max values

Provide examples in prompt: "Assign 1-5, where 1 is..."

Handle edge cases: Consider what happens with invalid data

Validate results: Check for unexpected values

Consider AI.SCORE for rankings: Better optimization for scoring tasks

When to Use

✅ Use when you need to extract or generate integer values

✅ Use for custom numeric categorization

✅ Use when you need full control over the prompt

✅ Use for counting or enumeration tasks

Alternatives

AI.SCORE: For ranking with managed optimization (returns FLOAT64)

AI.GENERATE_DOUBLE: For decimal values

AI.GENERATE: For structured output with multiple fields

REGEXP_EXTRACT: For simple pattern-based number 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

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