AI Functions in Trino — AKKO vs upstream

Two independent families of AI functions are usable inside Trino. They cohabit without collision and target different operational needs:

Family	Prefix	Invocation	Shipped by
AKKO plugin (this platform)	akko_ai_*	global, no catalog prefix	AKKO trino-ai-functions plugin
Trino upstream	ai_*	llm.ai.<function>(...) via the llm catalog	Trino 466+ llm catalog connector

TL;DR

Use akko_ai_* for every production workload in AKKO — they go through the governed AKKO stack (Caffeine cache, OPA per-role enforcement, Tempo audit spans, multimodal support, embeddings). Use the upstream llm.ai.* family when you need a stock Trino behaviour or for side-by-side demos. Both can be enabled at once.

At a glance — 23 AKKO functions vs 7 upstream

Aspect	AKKO akko_ai_* (global plugin)	Trino upstream ai_* (llm.ai)
Invocation	SELECT akko_ai_translate(...)	SELECT llm.ai.ai_translate(...) (or ai_translate(...) if llm.ai is on search_path)
Functions available	23 — text, multimodal, embeddings, admin	7 — text only
Provider	akko-ai-service → LiteLLM → Ollama/OpenAI/Anthropic/Mistral	Direct HTTP to a single provider (OpenAI, Anthropic, or Ollama)
Per-query-text cache	Caffeine LRU, per-worker, 10k entries, 1h TTL	none
Circuit breaker	yes — fail-closed after N errors, auto half-open	none
OPA RBAC per function	yes — role-scoped allow-list enforced at the service	not integrated
Audit trail	Tempo spans + X-Akko-Ai-Function header + Loki audit_type=AI_RBAC	request logged by the provider only
Multimodal	text + image + audio + PDF	text only
Embeddings + semantic search	akko_ai_embed, akko_ai_similarity, akko_ai_search (embeds once per distinct query text per worker — HTTP is hit once regardless of row cardinality)	not available
Error handling	@SqlNullable — any failure returns SQL NULL, Trino never destabilised	plugin-default (may surface as query error)
Cost model	Caching collapses N rows with the same query text into 1 HTTP call	1 HTTP call per row
License	Apache 2.0, ships with AKKO	Apache 2.0, shipped by Trino upstream

The AKKO akko_ai_* family — 23 functions

All 23 functions are global Trino plugin functions registered via Plugin.getFunctions(). Call them directly:

SELECT akko_ai_sentiment('I love this product');

Do not prefix with ai.default. — that path routes through the ai PostgreSQL catalog (PL/Python fallback), not the native JVM plugin. If SELECT akko_ai_sentiment(...) returns "Function not registered", the akko-trino custom image is not deployed — check kubectl describe pod -l app=trino for the correct image tag.

Categories

Category	Functions	Cost
Text scalars (HTTP per distinct input)	akko_ai_ask, akko_ai_sentiment, akko_ai_classify, akko_ai_summarize, akko_ai_translate, akko_ai_entities, akko_ai_anomaly, akko_ai_sql, akko_ai_risk, akko_ai_pii, akko_ai_sensitivity, akko_ai_language, akko_ai_keywords	1 LLM call per distinct value (cache-collapsed)
Multimodal	akko_ai_ocr, akko_ai_describe_image, akko_ai_parse_document, akko_ai_transcribe	1 multimodal call per input
Embeddings	akko_ai_embed	1 embed call per distinct text
Vector math (pure local)	akko_ai_similarity, akko_ai_search	0 HTTP calls per row (after first akko_ai_search)
Admin helpers	akko_ai_stats, akko_ai_health, akko_ai_version, akko_ai_cache_clear, akko_ai_cb_reset	none

Text scalars

Function	Signature	Example	Returns
akko_ai_ask	(question VARCHAR[, context VARCHAR])	akko_ai_ask('What is a lakehouse?')	free-form answer
akko_ai_sentiment	(text VARCHAR)	akko_ai_sentiment('I love this')	POSITIVE / NEUTRAL / NEGATIVE
akko_ai_classify	(text VARCHAR, categories VARCHAR)	akko_ai_classify('server down', 'bug,feature')	one category
akko_ai_summarize	(text VARCHAR[, n INT])	akko_ai_summarize(description, 2)	summary in N sentences
akko_ai_translate	(text VARCHAR[, target_lang VARCHAR])	akko_ai_translate('Hello', 'French')	translated string
akko_ai_entities	(text VARCHAR)	akko_ai_entities('Tim Cook at Apple')	JSON array [{type, value}]
akko_ai_anomaly	(value VARCHAR[, context VARCHAR])	akko_ai_anomaly('150000', 'avg 45000')	JSON {is_anomaly, score, reason}
akko_ai_sql	(question VARCHAR[, schema VARCHAR])	akko_ai_sql('top customers', 'orders(id,total)')	SQL string
akko_ai_risk	(profile VARCHAR)	akko_ai_risk('balance=-5000,inactive')	0–100 score
akko_ai_pii	(text VARCHAR)	akko_ai_pii('Jean, jean@mail.com')	redacted string
akko_ai_sensitivity	(text VARCHAR)	akko_ai_sensitivity('SSN 123-45-6789')	GDPR class (personal, sensitive, public)
akko_ai_language	(text VARCHAR)	akko_ai_language('Bonjour')	ISO code (fr, en, …)
akko_ai_keywords	(text VARCHAR[, n INT])	akko_ai_keywords('ML fraud detection', 3)	comma-separated keywords

Multimodal

Function	Signature	Purpose
akko_ai_ocr	(image BYTES)	Image → extracted text
akko_ai_describe_image	(image BYTES[, prompt VARCHAR])	Image → caption / description
akko_ai_parse_document	(pdf BYTES)	PDF → structured JSON
akko_ai_transcribe	(audio BYTES)	Audio → transcript

Embeddings and vector math

Function	Signature	Purpose
akko_ai_embed	(text VARCHAR) → ARRAY<DOUBLE>	768-dim embedding via nomic-embed-text
akko_ai_similarity	(a ARRAY<DOUBLE>, b ARRAY<DOUBLE>) → DOUBLE	cosine similarity, pure local math — no HTTP call
akko_ai_search	(embedding ARRAY<DOUBLE>, query VARCHAR) → DOUBLE	embeds query once per worker (LRU 1024, 1h TTL), then cosine locally

Admin and observability

Function	Purpose
akko_ai_stats()	JSON with circuit breaker state, latency p50/p95/p99, cache hit rate, per-function counters
akko_ai_health()	UP / DOWN — AI Service reachability
akko_ai_version()	plugin version (e.g. 2026.04)
akko_ai_cache_clear()	flush the result cache (admin only, checked via X-Trino-User)
akko_ai_cb_reset()	force-close the circuit breaker (admin only)

Architecture

flowchart LR
    SQL[Trino SQL<br/>SELECT akko_ai_sentiment...] --> COORD[Trino coordinator<br/>JVM]
    COORD --> PLUGIN[trino-ai-functions<br/>plugin]
    PLUGIN --> HC[Caffeine LRU cache<br/>10 000 entries - 1 h TTL]
    HC -->|miss| HTTP[HTTP/2 client<br/>java.net.http]
    HTTP --> AIS[akko-ai-service<br/>FastAPI + OPA check]
    AIS --> LLM[LiteLLM gateway]
    LLM --> OLL[Ollama<br/>qwen2.5 - nomic-embed-text]
    LLM -.optional.-> CLOUD[OpenAI / Anthropic / Mistral]
    PLUGIN --> JMX[JMX MBean<br/>HdrHistogram]
    JMX --> PROM[Prometheus<br/>jmx_exporter]
    AIS --> TEMPO[Tempo spans<br/>audit trail]

Every akko_ai_*() invocation:

1. Looks up the result in a user-scoped Caffeine LRU cache (user:function:sha256(args)).
2. On miss, opens an HTTP/2 connection (pool of 16) to AI Service with X-Trino-User + X-Akko-Ai-Function: akko_ai_<name> forwarded.
3. Authenticates with a bearer token loaded from the akko-trino-ai Kubernetes Secret.
4. AI Service checks the user's role and OPA allow-list for the function, records a Tempo span, then routes the call through LiteLLM.
5. Returns the parsed string — or SQL NULL (via @SqlNullable) on any error. Trino is never destabilised.

Configuration

All settings are Trino coordinator environment variables. Managed in helm/akko/charts/akko-trino/values.yaml:

trino:
  env:
    AKKO_AI_SERVICE_URL: http://akko-akko-ai-service:8000
    AKKO_AI_SERVICE_TOKEN:
      valueFrom:
        secretKeyRef:
          name: akko-trino-ai
          key: token
    AKKO_AI_TIMEOUT_MS: "30000"
    AKKO_AI_CB_THRESHOLD: "5"
    AKKO_AI_CB_RECOVERY_MS: "60000"
    AKKO_AI_RETRY_MAX: "3"
    AKKO_AI_POOL_SIZE: "16"
    AKKO_AI_CACHE_SIZE: "10000"
    AKKO_AI_CACHE_TTL_S: "3600"
    AKKO_AI_VERIFY_TLS: "true"

Usage patterns

Inline enrichment

SELECT id,
       akko_ai_sentiment(comment)                           AS sentiment,
       akko_ai_classify(comment, 'fraud,retention,support') AS topic,
       akko_ai_pii(comment)                                 AS redacted,
       akko_ai_embed(comment)                               AS vector
FROM   iceberg.banking.transactions
WHERE  ts > current_date - INTERVAL '7' DAY;

Materialise embeddings once

CREATE TABLE iceberg.kb.documents_vec AS
SELECT id, text, akko_ai_embed(text) AS embedding
FROM   iceberg.kb.documents;

Semantic search (efficient)

akko_ai_search embeds the query once per worker (Caffeine LRU 1024) and computes cosine locally:

SELECT id, text,
       akko_ai_search(embedding, 'how do I refund a charge?') AS score
FROM   iceberg.kb.documents
ORDER  BY score DESC
LIMIT  10;

Equivalent but 10 – 1000× slower (re-embeds the query per row) — avoid in production:

SELECT id, text,
       akko_ai_similarity(embedding, akko_ai_embed('how do I refund a charge?')) AS score
FROM   iceberg.kb.documents
ORDER  BY score DESC
LIMIT  10;

Observability from SQL

SELECT akko_ai_stats();

-- JMX (requires Trino jmx catalog)
SELECT *
FROM   jmx.current."dev.akko.trino.ai:type=aihttpclient";

RBAC

The 23 akko_ai_* UDFs bypass Trino's native OPA ExecuteFunction check (Trino does not route plugin UDFs through the access-control SPI). RBAC is therefore enforced at the AI Service layer: the plugin forwards X-Trino-User + X-Akko-Ai-Function: akko_ai_<name> on every HTTP call and the AI Service resolves the user's AKKO realm role via the Keycloak Admin API (cached 5 min) then consults the per-function matrix.

Fail-closed: any lookup error, missing role, or function not in the role's allow-list returns HTTP 403 from the AI Service. The plugin surfaces 403 / 401 / 429 as Trino PERMISSION_DENIED (via io.trino.spi.security.AccessDeniedException) so the query fails fast with the same error class a user would see from an OPA-denied SELECT — no silent NULL. Infra failures (5xx, timeout) still map to SQL NULL so transient LLM outages don't crash dashboards. Every decision is written to a structured Loki log line tagged audit_type=AI_RBAC and to the Prometheus counters akko_ai_rbac_allowed_total / akko_ai_rbac_denied_total.

Source of truth for the matrix: helm/akko/charts/akko-ai-service/values.yaml — kept in sync with helm/akko/charts/akko-opa/templates/configmap.yaml. See Admin / LLM RBAC for the full per-function table.

akko_ai_similarity(a, b) is pure local math (no HTTP), so it runs in-JVM for every role — gate the parent query through catalog-level RBAC instead.

Daily per-user quotas: admin=∞, engineer=1000, analyst=500, steward=100, viewer=50. Quota exhausts return HTTP 429 → SQL NULL.

The upstream ai_* family (Trino llm catalog)

Since Trino 466, Trino ships a native AI functions catalog named llm. As of Trino 480, the ai schema exposes seven functions:

Function	Signature	Returns
ai_gen	(prompt VARCHAR)	free-form answer
ai_translate	(text VARCHAR, target VARCHAR)	translated string
ai_classify	(text VARCHAR, categories ARRAY<VARCHAR>)	one category
ai_analyze_sentiment	(text VARCHAR)	sentiment label
ai_extract	(text VARCHAR, schema VARCHAR)	JSON extract
ai_fix_grammar	(text VARCHAR)	corrected text
ai_mask	(text VARCHAR)	PII-masked text

These functions live in a connector catalog, so the canonical form is fully qualified:

SELECT llm.ai.ai_translate('Hello', 'French');

Functional overlap with AKKO

Four names overlap: upstream ai_translate / ai_classify / ai_analyze_sentiment / ai_extract map roughly to AKKO akko_ai_translate / akko_ai_classify / akko_ai_sentiment / akko_ai_entities. AKKO deliberately uses the akko_ai_* prefix to avoid any ambiguity — see ADR-026.

Enabling the llm catalog against AKKO's sovereign LiteLLM gateway

The upstream connector lets you pick any of three providers. AKKO ships a reference llm.properties template that points it at AKKO's LiteLLM gateway so the upstream functions benefit from sovereign model routing (even though they still bypass the AKKO cache, OPA, and audit layers).

# etc/catalog/llm.properties — route Trino upstream AI at AKKO LiteLLM
connector.name=llm
llm.provider=openai
llm.openai.endpoint=http://akko-akko-litellm:4000
llm.openai.api-key=${ENV:AKKO_LITELLM_KEY}
llm.openai.model=qwen2.5:3b

Mount it via Helm:

trino:
  catalogs:
    llm: |
      connector.name=llm
      llm.provider=openai
      llm.openai.endpoint=http://akko-akko-litellm:4000
      llm.openai.api-key=${ENV:AKKO_LITELLM_KEY}
      llm.openai.model=qwen2.5:3b
  env:
    AKKO_LITELLM_KEY:
      valueFrom:
        secretKeyRef:
          name: akko-litellm-api
          key: key

After a Trino rollout, both families are reachable:

-- AKKO family (cached + OPA + audit)
SELECT akko_ai_translate('Hello', 'French');

-- Trino upstream family (stock, no cache, no OPA)
SELECT llm.ai.ai_translate('Hello', 'French');

When to use which

Situation	Use
Production query on governed data, dashboards, scheduled jobs	akko_ai_*
Semantic search, embeddings, multimodal	akko_ai_* (upstream doesn't offer them)
Regulated workload requiring per-user OPA audit	akko_ai_*
Stock Trino demo or upstream comparison	llm.ai.ai_*
Ad-hoc notebook with no need for cache/RBAC	either works; akko_ai_* is cheaper under row-heavy queries thanks to cache

Troubleshooting

Symptom	Likely cause	Fix
akko_ai_sentiment(...) → "Function not registered"	akko-trino custom image not deployed	Verify Trino pod runs akko-trino:2026.04, not stock trinodb/trino. Check kubectl describe pod -l app=trino
All akko_ai_* return NULL	Circuit breaker OPEN after 5+ failures	SELECT akko_ai_stats(); wait 60 s or call akko_ai_cb_reset()
401 / 403 in AI Service logs	Bearer token mismatch	Re-sync akko-trino-ai Secret; helm upgrade to restart Trino
First query very slow	Ollama pulling the model	Wait for ollama-init job; check kubectl logs -l app=akko-ollama
High latency on akko_ai_search	Query text never cached	Ensure you call akko_ai_search(col, 'literal-query') (same literal across rows)
jmx.current empty	jmx catalog disabled	Add jmx.properties to Trino catalog configmap
llm.ai.ai_translate missing	llm catalog not enabled	Add llm.properties (see template above)

Related

• Services / Trino AI Plugin — image, helm values, tests
• AI / ADEN — uses many of these functions internally
• AI / MCP servers — exposes akko_ai_* to LLM agents
• Architecture / Unified Data + AI
• Admin / LLM RBAC
• ADR-026 — akko_ai_* prefix