AI Functions

• Quota enforcement: Per-query limits on tokens (ai_function_max_input_tokens_per_query, ai_function_max_output_tokens_per_query) and API calls (ai_function_max_api_calls_per_query).
• Retry with backoff: Transient failures are retried (ai_function_max_retries) with exponential backoff (ai_function_retry_initial_delay_ms).

​

Configuration

CREATE NAMED COLLECTION ai_text_credentials AS
    provider = 'openai',
    endpoint = 'https://api.openai.com/v1/chat/completions',
    model = 'gpt-4o-mini',
    api_key = 'sk-...';

-- `aiEmbed` does not read `model` from the named collection; pass it as a positional argument instead.
-- Defining `model` in an `aiEmbed` collection is an error, not silently ignored.
CREATE NAMED COLLECTION ai_embedding_credentials AS
    provider = 'openai',
    endpoint = 'https://api.openai.com/v1/embeddings',
    api_key = 'sk-...';

​

Named collection parameters

​

Selecting credentials

1. the credentials key of its parameter map, when present;
2. otherwise the applicable default-credentials setting:
   • ai_function_text_default_credentials for the text functions (aiGenerate, aiClassify, aiExtract, aiTranslate);
   • ai_function_embedding_default_credentials for aiEmbed.

SET ai_function_text_default_credentials = 'ai_text_credentials';

-- Uses ai_text_credentials from the setting:
SELECT aiGenerate('What is 2 + 2? Reply with just the number.');

-- Overrides the default for this call:
SELECT aiGenerate('Bonjour', map('credentials', 'other_credentials'));

​

Parameter map

SELECT aiGenerate(body, map('temperature', '0.2', 'system_prompt', 'You are terse.')) FROM articles;

​

Query-level settings

​

Restricting endpoint hosts

<remote_url_allow_hosts>
    <host>api.openai.com</host>
    <host>api.anthropic.com</host>
</remote_url_allow_hosts>

​

Transport security (HTTP vs HTTPS)

• https:// — the connection uses TLS. The request body (input text, prompts) and the api_key in the request headers are encrypted in transit, and the provider’s certificate is validated. Use this for any remote provider.
• http:// — the connection is not encrypted. The request body and the api_key are sent in cleartext. Only use this for a trusted provider on a private network (e.g. a local vLLM or Ollama instance).

​

Supported providers

​

Observability

SELECT
    ProfileEvents['AIAPICalls'] AS api_calls,
    ProfileEvents['AIInputTokens'] AS input_tokens,
    ProfileEvents['AIOutputTokens'] AS output_tokens
FROM system.query_log
WHERE query_id = 'query_id'
AND type = 'QueryFinish'
ORDER BY event_time DESC;

​

aiClassify

aiClassify(text, categories[, params])

• text — Text to classify. String
• categories — Constant list of candidate category labels. Array(String)
• params — Optional constant Map(String, String) of parameters. Function-specific keys: temperature (sampling temperature controlling randomness; default 0.0), max_tokens (maximum output tokens per call; default 1024). The common parameters credentials and model also apply (see AI Functions). Map(String, String)

SELECT aiClassify('I love this product!', ['positive', 'negative', 'neutral'])

positive

SELECT body, aiClassify(body, ['bug', 'question', 'feature'], map('credentials', 'ai_text_credentials')) AS kind FROM issues LIMIT 5

​

aiEmbed

aiEmbed(text, model[, params])

• text — Text to embed. String
• model — Embedding model name. const String
• params — Optional constant Map(String, String) of parameters. Function-specific key: dimensions (target dimensionality of the output vector; 0 or omitted means the model’s native size). The common parameter credentials also applies (see AI Functions). Map(String, String)

SELECT aiEmbed('Hello world', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials'))

SELECT aiEmbed('Hello world', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials', 'dimensions', '256'))

SELECT aiEmbed(title, 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials', 'dimensions', '256')) FROM articles LIMIT 10

​

aiExtract

aiExtract(text, instruction_or_schema[, params])

• text — Text to extract information from. String
• instruction_or_schema — Free-form extraction instruction, or a constant JSON object describing the fields to extract. const String
• params — Optional constant Map(String, String) of parameters. Function-specific keys: temperature (sampling temperature controlling randomness; default 0.0), max_tokens (maximum output tokens per call; default 1024). The common parameters credentials and model also apply (see AI Functions). Map(String, String)

SELECT aiExtract('The package arrived late and was damaged.', 'the main complaint')

late and damaged package

SELECT aiExtract(review, '{"sentiment": "positive, negative or neutral", "topic": "main topic of the review"}') FROM reviews LIMIT 5

​

aiGenerate

aiGenerate(prompt[, params])

• prompt — The user prompt or question to send to the model. String
• params — Optional constant Map(String, String) of parameters. Function-specific keys: temperature (sampling temperature controlling randomness; default 0.7), max_tokens (maximum output tokens per call; default 1024), system_prompt (constant system-level instruction guiding the model’s behavior; default a generic assistant prompt). The common parameters credentials and model also apply (see AI Functions). Map(String, String)

SELECT aiGenerate('What is 2 + 2? Reply with just the number.')

4

SELECT aiGenerate('Explain ClickHouse', map('credentials', 'ai_text_credentials', 'system_prompt', 'You are a database expert. Be concise.'))

SELECT article_title, aiGenerate(concat('Summarize in one sentence: ', article_body)) AS summary FROM articles LIMIT 5

​

aiTranslate

aiTranslate(text, target_language[, params])

• text — Text to translate. String
• target_language — Target language name or BCP-47 code (e.g. 'French', 'es-MX'). String
• params — Optional constant Map(String, String) of parameters. Function-specific keys: temperature (sampling temperature controlling randomness; default 0.3), max_tokens (maximum output tokens per call; default 1024), instructions (additional style or dialect instructions for the translator). The common parameters credentials and model also apply (see AI Functions). Map(String, String)

SELECT aiTranslate('Hello, world!', 'French')

Bonjour le monde!

SELECT aiTranslate(body, 'Japanese', map('instructions', 'Use polite form (desu/masu)')) FROM articles LIMIT 5