> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for AI Functions

# AI Functions

AI Functions are built-in functions in ClickHouse that you can use to call AI or generate embeddings to work with your data, extract information, classify data, etc...

<Note>
  AI functions are experimental. Set [`allow_experimental_ai_functions`](/reference/settings/session-settings#allow_experimental_ai_functions) to enable them.
</Note>

<Note>
  AI functions can return unpredictable outputs. The result will highly depend on the quality of the prompt and the model used.
</Note>

All functions are sharing a common infrastructure that provides:

* **Quota enforcement**: Per-query limits on tokens ([`ai_function_max_input_tokens_per_query`](/reference/settings/session-settings#ai_function_max_input_tokens_per_query), [`ai_function_max_output_tokens_per_query`](/reference/settings/session-settings#ai_function_max_output_tokens_per_query)) and API calls ([`ai_function_max_api_calls_per_query`](/reference/settings/session-settings#ai_function_max_api_calls_per_query)).
* **Retry with backoff**: Transient failures are retried ([`ai_function_max_retries`](/reference/settings/session-settings#ai_function_max_retries)) with exponential backoff ([`ai_function_retry_initial_delay_ms`](/reference/settings/session-settings#ai_function_retry_initial_delay_ms)).

<h2 id="configuration">
  Configuration
</h2>

AI functions reference a **named collection** that stores provider credentials and configuration. Different named collections can be created and used for different functions or functions calls. For example you may want to define a different named collection to use with the text functions (`aiGenerate`, `aiClassify`, `aiExtract`, `aiTranslate`) vs the `aiEmbed` function, which require different endpoints and usually use different models.

Example statement to create a named collection with provider credentials, one with a chat endpoint and another with an embedding endpoint:

```sql theme={null}
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-...';
```

<h3 id="named-collection-parameters">
  Named collection parameters
</h3>

| Parameter     | Type   | Default | Description                                                                                                                                                                    |
| ------------- | ------ | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `provider`    | String | —       | Model provider. Supported: `'openai'`, `'anthropic'`. See note below.                                                                                                          |
| `endpoint`    | String | —       | API endpoint URL.                                                                                                                                                              |
| `model`       | String | —       | Model name (e.g. `'gpt-4o-mini'`). Used by the text functions; `aiEmbed` requires `model` as a positional argument and errors if `model` is specified in the named collection. |
| `api_key`     | String | —       | Authentication key for the provider. Optional: when omitted, the auth header is not sent, which allows targeting OpenAI-compatible servers that do not require authentication. |
| `max_tokens`  | UInt64 | `1024`  | Maximum number of output tokens per API call.                                                                                                                                  |
| `api_version` | String | —       | API version string. Used by Anthropic (`'2023-06-01'`).                                                                                                                        |

<Note>
  Any OpenAI-compatible API (e.g. vLLM, Ollama, LiteLLM) can be used by setting `provider = 'openai'` and pointing the `endpoint` to your service.
</Note>

<h3 id="selecting-credentials">
  Selecting credentials
</h3>

A function resolves the named collection to use from, in order:

1. the `credentials` key of its parameter map, when present;
2. otherwise the applicable default-credentials setting:
   * [`ai_function_text_default_credentials`](/reference/settings/session-settings#ai_function_text_default_credentials) for the text functions (`aiGenerate`, `aiClassify`, `aiExtract`, `aiTranslate`);
   * [`ai_function_embedding_default_credentials`](/reference/settings/session-settings#ai_function_embedding_default_credentials) for `aiEmbed`.

If neither is set, the call fails. The text and embedding functions use separate default settings because a chat-completions endpoint differs from an embeddings one.

```sql theme={null}
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'));
```

<h3 id="parameter-map">
  Parameter map
</h3>

Each function accepts an optional trailing `Map(String, String)` of parameters. All values are strings (quote numbers, e.g. `'0.2'`). Unknown keys are rejected. A key that is present overrides the corresponding named-collection value; a key that is absent falls back to the named collection (for `model`/`max_tokens`) or the built-in default. The exception is `aiEmbed`, which takes `model` as a required positional argument (`aiEmbed(text, model[, params])`) and errors if it is instead set in the parameter map or named collection.

The following parameters are common to all the AI functions:

| Key           | Description                                                                                                                         |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `credentials` | Named collection to use (see above).                                                                                                |
| `model`       | Overrides the collection's `model` (text functions only; `aiEmbed` takes `model` as a required positional argument, not a map key). |

Individual functions accept additional, function-specific parameters (such as `max_tokens`, `temperature`, `system_prompt`, `instructions`, and `dimensions`). See each function's reference below for the parameters it accepts and their defaults.

```sql theme={null}
SELECT aiGenerate(body, map('temperature', '0.2', 'system_prompt', 'You are terse.')) FROM articles;
```

<h3 id="query-level-settings">
  Query-level settings
</h3>

All AI-related settings are listed in [Settings](/reference/settings/session-settings) under the `ai_function_` prefix.

<h3 id="restricting-endpoint-hosts">
  Restricting endpoint hosts
</h3>

The `endpoint` URL in an AI named collection is an outbound destination the server connects to under its own identity, potentially carrying (if specified) the named collection's `api_key` in the request headers. By default, ClickHouse permits any host. To restrict functions to a specific set of providers, configure [`remote_url_allow_hosts`](/reference/settings/server-settings/settings#remote_url_allow_hosts) in the server config, e.g.:

```xml theme={null}
<remote_url_allow_hosts>
    <host>api.openai.com</host>
    <host>api.anthropic.com</host>
</remote_url_allow_hosts>
```

Note that this setting is server-wide and applies to all HTTP-using features.

<h3 id="transport-security">
  Transport security (HTTP vs HTTPS)
</h3>

The transport is determined solely by the scheme of the `endpoint` URL. There is no application-level encryption of the request payload; the protection of data in transit depends entirely on the scheme:

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

AI functions do not force HTTPS: an `http://` endpoint is accepted and sends data unencrypted. There is currently no server-side setting that rejects cleartext AI endpoints — [`remote_url_allow_hosts`](/reference/settings/server-settings/settings#remote_url_allow_hosts) restricts the destination host only and does not inspect the URL scheme, so an `http://` endpoint to an allowed host still passes. To ensure encrypted transport, configure named collections with `https://` endpoints.

Note that in either case the provider receives the input data in cleartext after TLS termination; TLS protects the data only on the network path between the server and the provider.

<h2 id="supported-providers">
  Supported providers
</h2>

| Provider  | `provider` value | Chat functions | Notes                         |
| --------- | ---------------- | -------------- | ----------------------------- |
| OpenAI    | `'openai'`       | Yes            | Default provider.             |
| Anthropic | `'anthropic'`    | Yes            | Uses `/v1/messages` endpoint. |

<h2 id="observability">
  Observability
</h2>

AI function activity is tracked through ClickHouse [ProfileEvents](/reference/system-tables/query_log):

| ProfileEvent      | Description                                                                              |
| ----------------- | ---------------------------------------------------------------------------------------- |
| `AIAPICalls`      | Number of HTTP requests made to the AI provider.                                         |
| `AIInputTokens`   | Total input tokens consumed.                                                             |
| `AIOutputTokens`  | Total output tokens consumed.                                                            |
| `AIRowsProcessed` | Number of rows that received a result.                                                   |
| `AIRowsSkipped`   | Number of rows skipped (quota exceeded, or error with `ai_function_throw_on_error = 0`). |

Query these events:

```sql theme={null}
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;
```

{/*AUTOGENERATED_START*/}

<h2 id="aiClassify">
  aiClassify
</h2>

Introduced in: v26.4.0

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

The function sends the text together with a fixed classification prompt and a JSON-schema response format
constraining the model to return exactly one of the supplied labels. When the response is returned as a JSON
object of the form `{"category": "..."}`, the label is unwrapped and the label string is returned.

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 theme={null}
aiClassify(text, categories[, params])
```

**Aliases**: `AIClassify`

**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 theme={null}
SELECT aiClassify('I love this product!', ['positive', 'negative', 'neutral'])
```

```response title=Response theme={null}
positive
```

**Classify a column with explicit credentials**

```sql title=Query theme={null}
SELECT body, aiClassify(body, ['bug', 'question', 'feature'], map('credentials', 'ai_text_credentials')) AS kind FROM issues LIMIT 5
```

<h2 id="aiEmbed">
  aiEmbed
</h2>

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`](/reference/settings/session-settings#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 rather than silently ignored.

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 theme={null}
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 theme={null}
SELECT aiEmbed('Hello world', 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials'))
```

**With explicit dimensions**

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

**Embed a column of texts**

```sql title=Query theme={null}
SELECT aiEmbed(title, 'text-embedding-3-small', map('credentials', 'ai_embedding_credentials', 'dimensions', '256')) FROM articles LIMIT 10
```

<h2 id="aiExtract">
  aiExtract
</h2>

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

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 theme={null}
aiExtract(text, instruction_or_schema[, params])
```

**Aliases**: `AIExtract`

**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 theme={null}
SELECT aiExtract('The package arrived late and was damaged.', 'the main complaint')
```

```response title=Response theme={null}
late and damaged package
```

**Schema extraction**

```sql title=Query theme={null}
SELECT aiExtract(review, '{"sentiment": "positive, negative or neutral", "topic": "main topic of the review"}') FROM reviews LIMIT 5
```

<h2 id="aiGenerate">
  aiGenerate
</h2>

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.

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 theme={null}
aiGenerate(prompt[, params])
```

**Aliases**: `AIGenerate`

**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 theme={null}
SELECT aiGenerate('What is 2 + 2? Reply with just the number.')
```

```response title=Response theme={null}
4
```

**With explicit credentials and system prompt**

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

**Summarize column values**

```sql title=Query theme={null}
SELECT article_title, aiGenerate(concat('Summarize in one sentence: ', article_body)) AS summary FROM articles LIMIT 5
```

<h2 id="aiTranslate">
  aiTranslate
</h2>

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

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 theme={null}
aiTranslate(text, target_language[, params])
```

**Aliases**: `AITranslate`

**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 theme={null}
SELECT aiTranslate('Hello, world!', 'French')
```

```response title=Response theme={null}
Bonjour le monde!
```

**Translate to Japanese with style instructions**

```sql title=Query theme={null}
SELECT aiTranslate(body, 'Japanese', map('instructions', 'Use polite form (desu/masu)')) FROM articles LIMIT 5
```