AI.GENERATE_TABLE

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

Description

Generates structured data as a table with your custom schema by calling a remote model. This table-valued function is ideal for extracting multiple structured records from unstructured text, such as extracting multiple products from a catalog description or multiple entities from a document.

Use Cases

Data Extraction: Extract multiple structured records from text

Entity Recognition: Identify and structure multiple entities from documents

List Generation: Create structured lists from narrative text

Parsing Documents: Extract tabular data from PDFs or text

Catalog Processing: Structure product information from descriptions

Syntax

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

Parameters

MODEL: Remote model created with CREATE REMOTE MODEL

prompt: Column name or string literal containing the prompt

output_schema: TABLE defining the structure

STRUCT (optional): Model parameters like temperature, max_output_tokens

Code Examples

Example 1: Extract Product List from Description

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

-- Extract structured product data
SELECT 
  source_doc_id,
  product_name,
  price,
  category
FROM 
  AI.GENERATE_TABLE(
    MODEL my_dataset.gemini_model,
    CONCAT('Extract all products mentioned in this text: ', product_catalog_text),
    TABLE<product_name string,="" price="" float64,="" category="" string="">
  ),
  catalog_documents;</product_name>

Example 2: Extract Contacts from Email

SELECT 
  email_id,
  contact_name,
  email_address,
  phone_number,
  role
FROM 
  AI.GENERATE_TABLE(
    MODEL my_dataset.gemini_model,
    CONCAT('Extract all contacts mentioned in this email: ', email_body),
    TABLE<
      contact_name STRING,
      email_address STRING,
      phone_number STRING,
      role STRING
    >
  ),
  emails
WHERE category = 'business';

Example 3: Parse Meeting Notes into Action Items

SELECT 
  meeting_id,
  action_item,
  assigned_to,
  due_date,
  priority
FROM 
  AI.GENERATE_TABLE(
    MODEL my_dataset.gemini_model,
    CONCAT('Extract all action items from these meeting notes: ', notes),
    TABLE<
      action_item STRING,
      assigned_to STRING,
      due_date STRING,
      priority STRING
    >,
    STRUCT(0.3 AS temperature)
  ),
  meeting_notes
WHERE meeting_date >= CURRENT_DATE() - 7;

Example 4: Extract Product Features

SELECT 
  product_id,
  feature_name,
  feature_value,
  feature_category
FROM 
  AI.GENERATE_TABLE(
    MODEL my_dataset.gemini_model,
    CONCAT('List all features of this product: ', product_description),
    TABLE<
      feature_name STRING,
      feature_value STRING,
      feature_category STRING
    >
  ),
  products
WHERE features_extracted = FALSE;

Example 5: Extract Entities from Legal Documents

SELECT 
  document_id,
  party_name,
  party_type,
  role_in_contract,
  contact_info
FROM 
  AI.GENERATE_TABLE(
    MODEL my_dataset.gemini_model,
    CONCAT('Extract all parties mentioned in this contract: ', contract_text),
    TABLE<
      party_name STRING,
      party_type STRING,
      role_in_contract STRING,
      contact_info STRING
    >,
    STRUCT(
      0.2 AS temperature,
      2000 AS max_output_tokens
    )
  ),
  legal_contracts;

Data Output Examples

Product Extraction

Contact Extraction

Action Items

Best Practices

Define clear schemas: Specify exact column names and types needed

Use descriptive prompts: Be explicit about what to extract

Control generation: Use lower temperature (0.2-0.4) for structured extraction

Handle empty results: Some prompts may return no rows

Validate output: Check for completeness and accuracy

Limit token usage: Set max_output_tokens appropriately

When to Use

✅ Use when you need to extract multiple structured records

✅ Use for parsing unstructured text into tabular format

✅ Use when output needs to be a table with multiple rows

✅ Use for entity extraction tasks

Alternatives

AI.GENERATE (with output_schema): For single-row structured output

REGEXP_EXTRACT_ALL: For simple pattern-based extraction (cheaper)

Cloud Document AI: For specialized document parsing

Manual parsing: For highly predictable formats

Platform Support

Regions: Model-dependent, see Vertex AI documentation

Models: Gemini models, partner models (Claude, Mistral, Llama)

Preview Status: Check current status in BigQuery documentation

Cost: Charged per Vertex AI API call

Output Schema

Returns a table with:

• Your custom columns as defined in output_schema
• Each row represents an extracted entity/record
• NULL values for missing fields

Common Patterns

One-to-Many Extraction

-- Extract multiple items from single source document
SELECT doc_id, item_name, item_value
FROM AI.GENERATE_TABLE(...), source_docs

Flattening Nested Data

-- Extract nested entities as flat table
SELECT entity_type, entity_name, entity_value
FROM AI.GENERATE_TABLE(...)

Joining with Source

-- Join extracted data back to source
SELECT s.*, e.extracted_field
FROM source_table s
CROSS JOIN AI.GENERATE_TABLE(...) e