AI Functions | ClickHouse Docs

aiClassify

Introduced in: v26.4.0

Classifies the given text into one of the provided categories using an LLM provider.

Credentials (a named collection specifying the provider, model, endpoint, and optionally an API key) are taken from the credentials key of the optional parameter map, or from the ai_function_text_default_credentials setting when the map omits it.

Syntax

aiClassify(text, categories[, params])

Arguments

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)

Returned value

One of the provided category labels, or the default value for the column type (empty string) if the request failed and ai_function_throw_on_error is disabled. String

Examples

Classify sentiment

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

positive

Classify a column with explicit credentials

CREATE TABLE issues (body String) ENGINE = Memory;
INSERT INTO issues VALUES ('The application exits unexpectedly after login.');
SELECT body, aiClassify(body, ['bug', 'question', 'feature'], map('credentials', 'ai_text_credentials')) AS kind FROM issues LIMIT 5

aiEmbed

Introduced in: v26.6.0

Generates an embedding vector for the given text using the configured AI provider.

The function sends the text to the configured embedding endpoint and returns the resulting vector as Array(Float32). Within a single block of rows, inputs are grouped into batches of up to ai_function_embedding_max_batch_size entries per HTTP request to reduce per-call overhead.

Credentials (a named collection specifying the provider, endpoint, and optionally an API key) are taken from the credentials key of the parameter map, or from the ai_function_embedding_default_credentials setting when the map omits it. Note that aiEmbed uses a separate default-credentials setting from the text functions, since an embeddings endpoint differs from a chat one.

The model is a required positional argument (a constant String). Unlike the text functions, aiEmbed does not read model from the named collection or the parameter map. A named collection that defines model is rejected.

The optional dimensions parameter, when supported by the model (e.g. OpenAI’s text-embedding-3-*), requests a vector of the given size; otherwise the model’s native size is returned.

Syntax

aiEmbed(text, model[, params])

Arguments

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)

Returned value

The embedding vector, or an empty array if the input is NULL or empty, the request failed and ai_function_throw_on_error is disabled, or a quota was exceeded with ai_function_throw_on_quota_exceeded disabled. Array(Float32)

Examples

Embed a single string (credentials can be omitted if the ai_function_embedding_default_credentials setting is set)

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

With explicit dimensions

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

Embed a column of texts

CREATE TABLE articles (title String) ENGINE = Memory;
INSERT INTO articles VALUES ('ClickHouse is a fast analytical database.');
SELECT aiEmbed(title, 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials', 'dimensions', '256')) FROM articles LIMIT 10

aiExtract

Introduced in: v26.4.0

Extracts structured information from unstructured text using an LLM provider.

The third argument may be either a free-form natural-language instruction (e.g. 'the main complaint') or a JSON-encoded schema of the form '{"field_a": "description of field a", "field_b": "description of field b"}'.

In instruction mode, the function returns the extracted value as a plain string, or an empty string if nothing was found. In schema mode, the function returns a JSON object string whose keys match the requested schema; missing fields are null.

Syntax

aiExtract(text, instruction_or_schema[, params])

Arguments

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)

Returned value

A single extracted value (instruction mode) or a JSON object string (schema mode). Returns the default value for the column type (empty string) if the request failed and ai_function_throw_on_error is disabled. String

Examples

Free-form instruction

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

late and damaged package

Schema extraction

CREATE TABLE reviews (review String) ENGINE = Memory;
INSERT INTO reviews VALUES ('The screen is bright, but the battery lasts only two hours.');
SELECT aiExtract(review, '{"sentiment": "positive, negative or neutral", "topic": "main topic of the review"}') FROM reviews LIMIT 5

aiFilter

Introduced in: v26.8.0

Evaluates a natural-language condition against the given text using an LLM provider and returns a boolean (UInt8) suitable for WHERE, PREWHERE, and JOIN ... ON.

The function asks the model to respond with only lowercase true or false. Any complete response other than true (including false and unrecognised text) maps to 0, so the row is filtered out. A provider-signalled incomplete reply — truncated, content-filtered, or requiring further action — is instead treated as an error: with ai_function_throw_on_error enabled (the default) the query is aborted; with it disabled the row maps to 0 and is filtered out.

Warning: Do not trust aiFilter results without scrutiny. LLM-based predicates can be incorrect or inconsistent; use them only where false positives and false negatives are acceptable.

Note: using aiFilter in JOIN ... ON evaluates the LLM once per candidate pair and can be expensive.

Syntax

aiFilter(text, condition[, params])

Arguments

text — Text to evaluate. String
condition — Constant natural-language condition the text must satisfy. 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)

Returned value

1 if the text matches the condition, 0 otherwise. Returns the default value (0) if the request failed and ai_function_throw_on_error is disabled. UInt8

Examples

Filter angry reviews

CREATE TABLE reviews (body String) ENGINE = Memory;
INSERT INTO reviews VALUES ('The package arrived three days late.');
SELECT * FROM reviews WHERE aiFilter(body, 'the customer is angry about shipping')

Filter a column with explicit credentials

CREATE TABLE issues (body String) ENGINE = Memory;
INSERT INTO issues VALUES ('The application exits unexpectedly after login.');
SELECT body, aiFilter(body, 'describes a bug', map('credentials', 'ai_text_credentials')) AS is_bug FROM issues LIMIT 5

aiGenerate

Introduced in: v26.4.0

Generates free-form text content from a prompt using an LLM provider.

The function sends the prompt to the configured AI provider and returns the generated text.

The optional parameter map may also set system_prompt (an instruction that guides the model’s behavior, e.g. tone, format, role), temperature, max_tokens, and model. If system_prompt is not set, the default is: You are a helpful assistant. Provide a clear and concise response.

Syntax

aiGenerate(prompt[, params])

Arguments

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)

Returned value

The generated text response, or the default value for the column type (empty string) if the request failed and ai_function_throw_on_error is disabled. String

Examples

Simple question

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

With explicit credentials and system prompt

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

Summarize column values

CREATE TABLE articles (article_title String, article_body String) ENGINE = Memory;
INSERT INTO articles VALUES ('ClickHouse', 'ClickHouse is an open-source column-oriented database for online analytical processing.');
SELECT article_title, aiGenerate(concat('Summarize in one sentence: ', article_body)) AS summary FROM articles LIMIT 5

aiRedact

Introduced in: v26.8.0

Detects and redacts personally identifiable information (PII) in the given text using an LLM provider.

Each detected PII span is replaced with a redaction token ([REDACTED] by default, configurable via the replacement parameter). The categories array restricts which PII types are redacted; an empty array falls back to a default set of common categories (name, email, phone number, address, credit card, IP address).

aiRedact instructs the model to change only the detected PII spans, but preserving the surrounding text is best-effort, the model may still alter it (see the warning above). Control characters other than tab, newline, and carriage return are also normalized to spaces before the request, so the output is not byte-identical to inputs that contain them.

Because aiRedact returns the whole input text with PII replaced, the output is about as long as the input. Set max_tokens (default 1024) above the input length in tokens; a reply truncated by a too-low limit is rejected with AI_PROVIDER_RESPONSE_TRUNCATED (or yields the column default when ai_function_throw_on_error is disabled) rather than returning partially redacted text.

Syntax

aiRedact(text, categories[, params])

Arguments

text — Text to redact. String
categories — Constant list of PII categories to redact (e.g. ['name', 'ssn', 'credit_card']). An empty array falls back to a default set of common categories (name, email, phone number, address, credit card, IP address). 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 — because aiRedact returns the full text, set it above the input length in tokens; a reply truncated by a too-low limit is rejected rather than returning partially redacted text), replacement (token that replaces each detected PII span; default [REDACTED]). The common parameters credentials and model also apply (see AI Functions). Map(String, String)

Returned value

The text with detected PII replaced by the redaction token, or the default value for the column type (empty string) if the request failed and ai_function_throw_on_error is disabled. String

Examples

Redact specific categories

SELECT aiRedact('Purchase was done by customer John Doe with email test@test.org', ['email', 'credit_card', 'name'])

Purchase was done by customer [REDACTED] with email [REDACTED]

Redact the default PII categories with a custom token

CREATE TABLE tickets (body String) ENGINE = Memory;
INSERT INTO tickets VALUES ('Contact Jane Doe at jane@example.com.');
SELECT aiRedact(body, [], map('replacement', '***')) FROM tickets LIMIT 5

aiSimilarity

Introduced in: v26.8.0

Computes the semantic similarity of two texts using the configured embedding provider.

Calculates the vector embeddings of both texts and returns their cosine similarity. A score of -1 is given to opposite embedding vectors, semantically this means texts with scores approaching -1 are opposite in meaning. A score of 0 means the vectors are orthogonal: semantically unrelated. Finally, a score of 1 means the embedding vectors are pointing in the same direction, texts with scores approaching 1 are similar in meaning. This is the complement of cosineDistance over the same embeddings (aiSimilarity = 1 - cosineDistance(embedding1, embedding2)).

Batching, credentials, and the dimensions parameter match aiEmbed, including the ai_function_embedding_default_credentials default-credentials setting.

Like aiEmbed, model is a required positional argument (a constant String), not read from the named collection or the parameter map.

Syntax

aiSimilarity(text1, text2, model[, params])

Arguments

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

Returned value

The cosine similarity in [-1, 1], or NULL if either text is NULL or empty, an embedding request failed and ai_function_throw_on_error is disabled, or a quota was exceeded with ai_function_throw_on_quota_exceeded disabled. Nullable(Float32)

Examples

Compare two strings (credentials can be omitted if the ai_function_embedding_default_credentials setting is set)

SELECT aiSimilarity('cat', 'kitten', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials'))

Rank reviews by similarity to a query

CREATE TABLE product_reviews (review String) ENGINE = Memory;
INSERT INTO product_reviews VALUES ('It works well under rain.');
SELECT review FROM product_reviews ORDER BY aiSimilarity(review, 'It works well under rain', 'text-embedding-3-small') DESC LIMIT 100

Semantic dedup over a self-join

CREATE TABLE docs (id UInt64, title String) ENGINE = Memory;
INSERT INTO docs VALUES (1, 'ClickHouse documentation'), (2, 'ClickHouse database guide');
SELECT a.id, b.id FROM docs a, docs b WHERE a.id < b.id AND aiSimilarity(a.title, b.title, 'text-embedding-3-small') > 0.9

aiTranslate

Introduced in: v26.4.0

Translates the given text into the specified target language using an LLM provider.

Additional style or dialect instructions may be passed via the instructions key of the parameter map (e.g. 'keep technical terms untranslated').

Syntax

aiTranslate(text, target_language[, params])

Arguments

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)

Returned value

The translated text, or the default value for the column type (empty string) if the request failed and ai_function_throw_on_error is disabled. String

Examples

Translate to French

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

Bonjour le monde!

Translate to Japanese with style instructions

CREATE TABLE articles (body String) ENGINE = Memory;
INSERT INTO articles VALUES ('ClickHouse processes analytical queries quickly.');
SELECT aiTranslate(body, 'Japanese', map('instructions', 'Use polite form (desu/masu)')) FROM articles LIMIT 5