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

-- 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="">
  ),

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'

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

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

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
    )
  ),

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

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

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