AI Scalar Functions

Gnok exposes a comprehensive family of LLM-backed scalar UDFs that operate on text columns inline in any SELECT, WHERE, or JOIN expression. They share a single dispatch path with input deduplication, bounded parallel concurrency, per-call timeout, and exponential-backoff retry — reducing duplicate requests within an evaluation batch. Bounded caching can also reuse eligible results; it is not a promise of one provider call per unique value across an entire query.

Function	Signature	Description
AI_COMPLETE(prompt)	(VARCHAR) -> VARCHAR	Free-form completion for the given prompt.
AI_COMPLETE(prompt, max_tokens)	(VARCHAR, BIGINT) -> VARCHAR	Same as above, with a per-call token override.
AI_GENERATE(prompt)	(VARCHAR) -> VARCHAR	Vendor-neutral alias for AI_COMPLETE(prompt); many generative-SQL tools reach for this name first. Both forms run the same backing implementation.
AI_SUMMARIZE(text)	(VARCHAR) -> VARCHAR	Terse 1–3 sentence summary of text.
AI_CLASSIFY_TEXT(text, labels)	(VARCHAR, ARRAY<VARCHAR>) -> VARCHAR	Pick exactly one label from the supplied list.
AI_TRANSLATE(text, target_language)	(VARCHAR, VARCHAR) -> VARCHAR	Translate text into the named target language.
AI_EXTRACT_ANSWER(text, question)	(VARCHAR, VARCHAR) -> VARCHAR	Extract the answer to question from text. Returns SQL NULL when the source does not contain enough information.
AI_SENTIMENT(text)	(VARCHAR) -> VARCHAR	Classify sentiment; returns positive, negative, or neutral.
AI_FIX_GRAMMAR(text)	(VARCHAR) -> VARCHAR	Correct grammar, spelling, and punctuation while preserving meaning.
AI_MASK(text, pii_types)	(VARCHAR, ARRAY<VARCHAR>) -> VARCHAR	Redact the listed PII entity types with [TYPE] placeholders (e.g. [EMAIL]). An empty array redacts all common PII.
AI_EXTRACT(text, json_schema)	(VARCHAR, VARCHAR) -> VARCHAR	Extract structured data as a JSON object conforming to json_schema. Distinct from AI_EXTRACT_ANSWER, which returns a single free-text answer.
AI_SQL(request)	(VARCHAR) -> VARCHAR	Translate a natural-language request into a single SQL statement. Not schema-aware — for schema-grounded text-to-SQL use ASK.
AI_VALIDATE(text, criteria)	(VARCHAR, VARCHAR) -> VARCHAR	LLM data-quality judgment against criteria; returns a JSON object {"valid": <bool>, "reason": <text>}.
AI_RERANK(query, candidates [, k])	(VARCHAR, ARRAY<VARCHAR> [, BIGINT]) -> ARRAY<VARCHAR>	Listwise LLM rerank: reorder candidates by relevance to query, most-relevant first, and return the top k as an array. Omit k to return all candidates ranked. The ideal final step of a retrieval pipeline — rerank the rows a vector search returned before passing them to AI_COMPLETE.
AI_DEDUPE(items, threshold)	(ARRAY<VARCHAR>, DOUBLE) -> ARRAY<VARCHAR>	Fuzzy entity resolution: the LLM groups near-duplicate / co-referent entries (alternate spellings, abbreviations, formatting) and returns one canonical value per group. threshold (0.0–1.0, higher = stricter) tunes how aggressively entries merge. Call over ARRAY_AGG(col).
AI_FORECAST(series, horizon [, season])	(ARRAY<DOUBLE>, BIGINT [, BIGINT]) -> ARRAY<DOUBLE>	Time-series forecasting via native ARIMA/SARIMA — returns horizon future points. Pre-order the series with ARRAY_AGG(value ORDER BY ts); pass a season period for seasonal data. No AI provider call, so AI Governance is not required (classical model). A series too short to fit falls back to a naive last-value forecast.
AI_RAG(question, table, embed_col, k)	(VARCHAR, table, column, INT) -> VARCHAR	One-shot retrieval-augmented generation: embeds question, vector-searches the top k rows of table by embed_col, and answers from them with AI_COMPLETE. embed_col may be a vector column (searched directly; context comes from a sibling text column like content) or a text column (embedded on the fly). Composes EMBED + COSINE_DISTANCE + AI_COMPLETE — see below.

Composed & alias forms

These compose or rename the scalars above — they add no new dispatch path and inherit the same dedup / concurrency / retry behavior.

Function	Signature	Description
AI_SIMILARITY(a, b)	(VARCHAR, VARCHAR) -> DOUBLE	Cosine similarity of the two inputs' embeddings, in [-1.0, 1.0] (1.0 = identical). Rewrites to 1 - COSINE_DISTANCE(EMBED(a), EMBED(b)), so it uses your organization's monthly embedding allowance.
AI_EMBED(text [, model])	(VARCHAR [, VARCHAR]) -> FixedSizeList<Float32, N>	Alias of EMBED.
AI_REDACT(text, pii_types)	(VARCHAR, ARRAY<VARCHAR>) -> VARCHAR	An alias for AI_MASK.

The text scalars return VARCHAR; AI_SIMILARITY returns DOUBLE, AI_EMBED returns a fixed-size float vector, AI_RERANK / AI_DEDUPE return ARRAY<VARCHAR>, and AI_FORECAST returns ARRAY<DOUBLE>. NULL inputs are passed through without invoking the LLM (the result is SQL NULL). AI_RERANK / AI_DEDUPE likewise return SQL NULL for a row whose primary input is NULL or whose array is empty — no LLM call is made. AI_FORECAST runs a classical statistical model, not an LLM, so it needs no AI Governance setup. AI_EXTRACT_ANSWER additionally returns SQL NULL when the LLM determines the source text doesn't answer the question. AI_EXTRACT and AI_VALIDATE return JSON text — query into it with the JSON functions.

TRY_ variants

The base LLM-backed scalars have TRY_-prefixed twins (TRY_AI_COMPLETE, TRY_AI_SENTIMENT, TRY_AI_EXTRACT, …) that returns SQL NULL for a cell whose provider call fails instead of failing the whole query — a common convention for keeping batch pipelines running through provider outages. See Error handling. Policy and identity denials are still errors; TRY_ does not bypass authorization.

Quick start

These fragments assume the named tables and output columns exist. Start with the AISQL tutorial for complete setup and data. An organization administrator must first enable AI Governance: an Available monthly token budget and an enabled rollout policy that allows the provider, model, and operations, with egress and retention approved. Generated text and PII masking need application-level checks; an LLM response is not proof that every fact, schema constraint, or sensitive value was handled correctly.

-- Free-form completion
SELECT AI_COMPLETE('Write a friendly subject line for a 10% off email')
  AS subject;

-- Summarise a column of long-form text
SELECT id, AI_SUMMARIZE(article_body) AS tldr
FROM articles
WHERE created_at > now() - INTERVAL '7 days';

-- One-shot classification
SELECT
  ticket_id,
  AI_CLASSIFY_TEXT(message, ARRAY['billing', 'technical', 'feature_request', 'spam'])
    AS category
FROM support_tickets;

-- Translation
SELECT id, AI_TRANSLATE(body, 'Spanish') AS body_es FROM emails;

-- Extractive QA — NULL when the text doesn't contain the answer
SELECT
  doc_id,
  AI_EXTRACT_ANSWER(body, 'What is the contract end date?') AS end_date
FROM contracts
WHERE AI_EXTRACT_ANSWER(body, 'What is the contract end date?') IS NOT NULL;

-- Sentiment label (positive / negative / neutral)
SELECT review_id, AI_SENTIMENT(body) AS sentiment FROM reviews;

-- Clean up user-generated text
SELECT AI_FIX_GRAMMAR(comment) AS corrected FROM feedback;

-- Redact PII before sharing — empty ARRAY[] masks all common PII
SELECT AI_MASK(body, ARRAY['EMAIL', 'PHONE', 'NAME']) AS redacted FROM messages;

-- Structured extraction into JSON, then query the fields
SELECT
  AI_EXTRACT(body, '{"company": "string", "amount": "number"}') AS j
FROM invoices;

-- Natural-language → SQL as a value (schema-agnostic; use ASK for grounded NL2SQL)
SELECT AI_SQL('top 10 customers by revenue last quarter') AS generated_sql;

-- Data-quality validation → {"valid": bool, "reason": "..."}
SELECT email, AI_VALIDATE(email, 'must be a valid email address') AS check
FROM contacts;

-- Rerank candidate passages by relevance, keep the top 3 as an array
SELECT AI_RERANK(
         'how do I rotate my API key?',
         ARRAY['Billing FAQ', 'Rotating API keys', 'Rate limits', 'Key rotation CLI'],
         3
       ) AS top_docs;

-- Fuzzy-dedupe company names per region into canonical forms
SELECT region, AI_DEDUPE(ARRAY_AGG(company), 0.85) AS canonical_companies
FROM accounts GROUP BY region;

-- Forecast the next 12 months per series (classical ARIMA, no provider call)
SELECT series_id,
       AI_FORECAST(ARRAY_AGG(value ORDER BY ts), 12) AS next_12
FROM metrics GROUP BY series_id;

-- Semantic similarity (cosine, 1.0 = identical) — uses the embedding allowance
SELECT AI_SIMILARITY(title_a, title_b) AS score FROM candidate_pairs;

-- Keep a batch pipeline alive through provider outages: failed cells → NULL
SELECT id, TRY_AI_SUMMARIZE(body) AS tldr FROM articles;

-- One-shot RAG: retrieve the 5 most relevant docs and answer from them
SELECT AI_RAG('how do I rotate my API key?', docs, embedding, 5) AS answer;

AI_RAG — retrieval-augmented generation in one expression

AI_RAG(question, table, embed_col, k) bundles the whole embed → vector-search → generate pipeline into a single call. It expands (at bind time) to:

AI_COMPLETE(
  '<preamble>\n\n## Question\n' || question || '\n\n## Context\n' ||
  (SELECT STRING_AGG(ctx, '\n\n---\n\n') FROM (
     SELECT <context_col> AS ctx
     FROM <table>
     ORDER BY COSINE_DISTANCE(<search>, EMBED(question)) ASC
     LIMIT k))
)

embed_col is auto-detected against the table schema:

• Vector column (VECTOR(n) / FixedSizeList<Float32>): searched directly (and the planner can push the ORDER BY COSINE_DISTANCE … LIMIT k into a vector index). The context text is taken from a sibling text column — a content / text / chunk / body / document / passage column if present, otherwise the first text column.
• Text column: embedded on the fly with EMBED(col), and the column itself is the context.

question may be a string literal or any expression (e.g. a column, for a per-row RAG join). EMBED uses your organization's monthly embedding allowance, and the AI_COMPLETE answer needs AI Governance that allows ai_complete.

Configuration

Gnok manages the AI provider, concurrency, deadlines, and retry settings; you don't supply a provider API key. AI functions are off for each organization until an organization administrator, in Studio under Operations → AI Governance, saves a monthly token budget as Available and enables a rollout policy. The policy names the allowed provider, models, and operations, and records the egress and retention approvals. See AI Governance.

Dispatch semantics

Each call into an AI_* UDF processes a column chunk. Within that chunk the dispatcher:

1. Deduplicates identical (primary, extra_args) tuples. The LLM is called once per distinct cell, and the result is fanned out to every row that produced that tuple. For columns with heavy repetition (e.g. classifying ticket categories from a small surface area of messages) this is the difference between an N-row LLM bill and an O(distinct N) bill.
2. Dispatches calls in parallel within the service concurrency limit, which is shared across concurrent queries that use AI_* and backed by tenant admission controls. A concurrency bound is not a provider rate-limit guarantee.
3. Times out any individual call at the service deadline. A timeout is treated as a retryable error.
4. Retries with exponential backoff within the configured retry limit.
5. Records an audit row for each batch.
6. Passes through NULLs without invoking the LLM.

Cost & rate-limit awareness

AI calls can become expensive on large datasets. A SELECT AI_SUMMARIZE(body) FROM articles over a 1M-row table issues up to 1M LLM calls (less the dedup factor). For production hot paths, prefer one of:

• Materialise the result — INSERT INTO articles_summarised SELECT id, AI_SUMMARIZE(body) FROM articles WHERE … then read the cached column.
• Pre-filter aggressively — push WHERE clauses upstream of the AI_* call so only the rows that need it pay the LLM cost.
• Size the AI Governance budget and policy — the token budget and policy apply before dispatch. See AI Governance. Track provider usage separately from warehouse resource consumption.

Error handling

Identity, policy, and budget denials remain errors, including for TRY_AI_*. The nullable provider-failure behavior described below does not authorize a blocked request.

Also distinguish these outcomes:

• NULL input — a SQL NULL argument passes straight through to a NULL result; the LLM is never invoked.
• Hard failure (rate limit exhausted, malformed response, deadline) that survives the service retry limit retries — the base functions surface the error and fail the query. The TRY_ variants (TRY_AI_COMPLETE, TRY_AI_SENTIMENT, …) instead return SQL NULL for that cell and let the query continue; an audit row is emitted with error_message set. This follows the common TRY_-prefix convention. Reach for TRY_AI_* in batch/ETL pipelines that must survive transient provider issues, and the bare functions in interactive queries where you want a failure to be loud.
• AI not enabled — until an organization administrator saves an Available token budget and enables a rollout policy under Operations → AI Governance, AI_* calls are refused rather than return data. The same applies when the budget is exhausted or the policy doesn't allow the provider, model, or operation. Non-AI queries are unaffected.

AI_EXTRACT_ANSWER is a special case on top of the above: it returns SQL NULL when the LLM determines the source text doesn't contain the answer — a semantic "no answer" signal, not a failure.

Example — moderation pipeline

-- 1. Classify support tickets
INSERT INTO support_tickets_classified
SELECT
  ticket_id,
  message,
  language,
  AI_CLASSIFY_TEXT(message, ARRAY['billing', 'technical', 'feature_request', 'spam'])
    AS category,
  AI_SUMMARIZE(message) AS tldr
FROM support_tickets
WHERE created_at > now() - INTERVAL '1 hour';

-- 2. Translate non-English entries for the support team's dashboards
SELECT ticket_id, category, tldr,
  CASE WHEN language != 'en'
    THEN AI_TRANSLATE(tldr, 'English')
    ELSE tldr
  END AS tldr_en
FROM support_tickets_classified;

-- 3. Extract structured fields for the ones that look like billing
SELECT ticket_id,
  AI_EXTRACT_ANSWER(message, 'What invoice number is the customer asking about?')
    AS invoice_number,
  AI_EXTRACT_ANSWER(message, 'What dollar amount is in dispute?')
    AS disputed_amount
FROM support_tickets_classified
WHERE category = 'billing';

Related

• ASK — natural-language → SQL, distinct surface tuned for SQL generation.
• ML_PREDICT — invoke registered ONNX / PyTorch / sklearn / native models.
• EMBED — generate embeddings via the embedding provider.