# AI (/docs/reference/sql/functions/ai)



This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).

aiClassify [#aiclassify]

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**

```sql
aiClassify(text, categories[, params])
```

**Arguments**

* `text` — Text to classify. [`String`](/reference/data-types/string)
* `categories` — Constant list of candidate category labels. [`Array(String)`](/reference/data-types/array)
* `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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/string)

**Examples**

**Classify sentiment**

```sql title=Query
SELECT aiClassify('I love this product!', ['positive', 'negative', 'neutral'])
```

```text title=Response
positive
```

**Classify a column with explicit credentials**

```sql title=Query
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
```

*Introduced in version 26.4.0.*

aiEmbed [#aiembed]

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`](/reference/settings/session-settings/ai-function#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**

```sql
aiEmbed(text, model[, params])
```

**Arguments**

* `text` — Text to embed. [`String`](/reference/data-types/string)
* `model` — Embedding model name. [`const String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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)`](/reference/data-types/array)

**Examples**

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

```sql title=Query
SELECT aiEmbed('Hello world', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials'))
```

**With explicit dimensions**

```sql title=Query
SELECT aiEmbed('Hello world', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials', 'dimensions', '256'))
```

**Embed a column of texts**

```sql title=Query
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
```

*Introduced in version 26.6.0.*

aiExtract [#aiextract]

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`.

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**

```sql
aiExtract(text, instruction_or_schema[, params])
```

**Arguments**

* `text` — Text to extract information from. [`String`](/reference/data-types/string)
* `instruction_or_schema` — Free-form extraction instruction, or a constant JSON object describing the fields to extract. [`const String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/string)

**Examples**

**Free-form instruction**

```sql title=Query
SELECT aiExtract('The package arrived late and was damaged.', 'the main complaint')
```

```text title=Response
late and damaged package
```

**Schema extraction**

```sql title=Query
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
```

*Introduced in version 26.4.0.*

aiFilter [#aifilter]

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.

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.

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

**Syntax**

```sql
aiFilter(text, condition[, params])
```

**Arguments**

* `text` — Text to evaluate. [`String`](/reference/data-types/string)
* `condition` — Constant natural-language condition the text must satisfy. [`String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/int-uint)

**Examples**

**Filter angry reviews**

```sql title=Query
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**

```sql title=Query
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
```

*Introduced in version 26.8.0.*

aiGenerate [#aigenerate]

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.

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.

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**

```sql
aiGenerate(prompt[, params])
```

**Arguments**

* `prompt` — The user prompt or question to send to the model. [`String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/string)

**Examples**

**Simple question**

```sql title=Query
SELECT aiGenerate('What is 2 + 2? Reply with just the number.')
```

```text title=Response
4
```

**With explicit credentials and system prompt**

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

**Summarize column values**

```sql title=Query
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
```

*Introduced in version 26.4.0.*

aiRedact [#airedact]

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

:::warning
`aiRedact` performs PII detection and redaction on a best-effort basis using an LLM, and its output is not
reliable. Whether PII is detected and removed depends on the chosen model, the prompt, and the input: the
model can miss identifiers, redact them only partially, or alter the surrounding text. It works best with
well-formed English text; results may be worse for other languages or for text with many spelling,
punctuation, or grammatical errors. `aiRedact` does not guarantee that its output is free of PII and must not
be treated as a safe or sufficient anonymization mechanism on its own. Always review the output to ensure it
meets your organization's data privacy and compliance policies before exposing data to untrusted parties.
:::

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**

```sql
aiRedact(text, categories[, params])
```

**Arguments**

* `text` — Text to redact. [`String`](/reference/data-types/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)`](/reference/data-types/array)
* `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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/string)

**Examples**

**Redact specific categories**

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

```text title=Response
Purchase was done by customer [REDACTED] with email [REDACTED]
```

**Redact the default PII categories with a custom token**

```sql title=Query
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
```

*Introduced in version 26.8.0.*

aiSimilarity [#aisimilarity]

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

Calculates the vector embeddings of both texts and returns their
[cosine similarity](https://en.wikipedia.org/wiki/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**

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

**Arguments**

* `text1` — First text. [`String`](/reference/data-types/string)
* `text2` — Second text. [`String`](/reference/data-types/string)
* `model` — Embedding model name. [`const String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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)`](/reference/data-types/nullable)

**Examples**

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

```sql title=Query
SELECT aiSimilarity('cat', 'kitten', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials'))
```

**Rank reviews by similarity to a query**

```sql title=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**

```sql title=Query
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
```

*Introduced in version 26.8.0.*

aiTranslate [#aitranslate]

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'`).

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**

```sql
aiTranslate(text, target_language[, params])
```

**Arguments**

* `text` — Text to translate. [`String`](/reference/data-types/string)
* `target_language` — Target language name or BCP-47 code (e.g. `'French'`, `'es-MX'`). [`String`](/reference/data-types/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](/reference/functions/regular-functions/ai-functions)). [`Map(String, String)`](/reference/data-types/map)

**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`](/reference/data-types/string)

**Examples**

**Translate to French**

```sql title=Query
SELECT aiTranslate('Hello, world!', 'French')
```

```text title=Response
Bonjour le monde!
```

**Translate to Japanese with style instructions**

```sql title=Query
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
```

*Introduced in version 26.4.0.*
