Invoke a registered chat model per row: prompt in, generated text out.
AI_GENERATE(model_name, prompt [, parameters_json])
AI_GENERATE(prompt USE MODEL model_name [, parameters_json])## Overview AI_GENERATE sends each row's prompt to the chat model registered under model_name and returns the generated text. The model resolves through the control plane (configuration + connection base URL + vault credential) and is cached per session for five minutes; the HTTP call itself runs directly from the executing node through the shared REST transport (auth, retry with Retry-After, timeout), the same stack as INVOKE API ENDPOINT. ## System prompt precedence 1. A system_prompt key inside parameters_json (per call) wins. 2. Otherwise the model's SYSTEM_PROMPT applies. ## Batching, dedup, and cost control - Identical prompts within a record batch are sent once. - A bounded per-session response memo short-circuits repeats of the same (model, prompt, parameters) across batches, so re-running a query costs zero provider calls while the session lives. - At most 8 requests are in flight per batch. - Even so, a per-row LLM call over a large table is slow and metered: prefer materializing results with CTAS/INSERT ... SELECT over recomputing them per query. ## Error semantics A provider failure (auth, rate-limit exhaustion after retries, malformed response) fails the query with the provider's status and a bounded body preview. Failed inferences are never silently NULL.
| Name | Type | Description |
|---|---|---|
model_name | The registry name of a MODEL_TYPE = CHAT model (see CREATE MODEL). Must be constant within a query: per-row model switching is rejected. | |
prompt | The user prompt, typically a column or an expression composing column values into an instruction. NULL prompts produce NULL output without calling the provider. | |
parameters_json | A JSON object of per-call request parameters merged over the model's default PARAMETERS (per-call wins per key), e.g. '{"temperature":0.9,"max_tokens":64}'. The special key system_prompt overrides the model-level SYSTEM_PROMPT for this call. |
-- Per-row generation over a table column
SELECT id, AI_GENERATE('gpt5', 'Summarize in one sentence: ' || review_text) AS summary
FROM shop.silver.reviews;-- SQL Server style USE MODEL sugar (rewritten to the form above)
SELECT AI_GENERATE(review_text USE MODEL gpt5) FROM shop.silver.reviews;-- Per-call parameter override
SELECT AI_GENERATE('gpt5', question, '{"temperature":0.0,"max_tokens":32}') FROM faq.bronze.questions;-- Persist responses instead of regenerating them
CREATE TABLE shop.gold.review_summaries AS
SELECT id, AI_GENERATE('gpt5', review_text) AS summary FROM shop.silver.reviews;