Machine Learning Functions

Note

The documentation below is automatically generated from system.functions

evalMLMethod

Introduced in: v20.1.0

Applies a trained machine learning model to input features to generate predictions.

Syntax

evalMLMethod(model, x1[, x2, ...])

Arguments

model — The trained machine learning model. AggregateFunctionState
x1, x2, ... — Feature values for prediction. Float* or (U)Int*

Returned value

Returns the predicted value based on the trained model. Float64

Examples

Example usage

SELECT
evalMLMethod(model, trip_distance),
total_amount
FROM trips
LEFT JOIN models ON year = toYear(pickup_datetime)
LIMIT 5

┌─evalMLMethod(model, trip_distance)─┬─total_amount─┐
│ 8.087692004204174                  │ 5.4          │
│ 7.861181608305352                  │ 4.6          │
│ 26.661544467907536                 │ 23.4         │
│ 8.767223191900637                  │ 5.8          │
│ 10.80581675499003                  │ 9            │
└────────────────────────────────────┴──────────────┘

naiveBayesClassifier

Introduced in: v25.11.0

Classifies input text using a Naive Bayes model with n-grams and Laplace smoothing. The model must be configured in ClickHouse before use.

Implementation details

Algorithm

Uses the Naive Bayes classification algorithm with Laplace smoothing to handle unseen n-grams, based on n-gram probabilities as described here.

Key features

Supports n-grams of any size.
Three tokenization modes:
- byte: Operates on raw bytes. Each byte is one token.
- codepoint: Operates on Unicode scalar values decoded from UTF-8. Each codepoint is one token.
- token: Splits on runs of Unicode whitespace (regex \s+). Tokens are substrings of non-whitespace; punctuation is part of the token if adjacent (e.g. you? is one token).

Model configuration

Sample source code for creating a Naive Bayes model for language detection is available here, along with sample models and their associated config files here.

An example configuration for a Naive Bayes model in ClickHouse:

<clickhouse>
    <nb_models>
        <model>
            <name>sentiment</name>
            <path>/etc/clickhouse-server/config.d/sentiment.bin</path>
            <n>2</n>
            <mode>token</mode>
            <alpha>1.0</alpha>
            <priors>
                <prior>
                    <class>0</class>
                    <value>0.6</value>
                </prior>
                <prior>
                    <class>1</class>
                    <value>0.4</value>
                </prior>
            </priors>
        </model>
    </nb_models>
</clickhouse>

Configuration parameters

Parameter	Description	Example	Default
`name`	Unique model identifier.	`language_detection`	Required
`path`	Full path to the model binary.	`/etc/clickhouse-server/config.d/language_detection.bin`	Required
`mode`	Tokenization method: `byte` (byte sequences), `codepoint` (Unicode characters) or `token` (word tokens).	`token`	Required
`n`	N-gram size: `1` (single word), `2` (word pairs) or `3` (word triplets).	`2`	Required
`alpha`	Laplace smoothing factor used during classification for n-grams that do not appear in the model.	`0.5`	`1.0`
`priors`	Class probabilities (percentage of documents belonging to a class).	60% class 0, 40% class 1	Equal distribution

Model training guide

File format

In human-readable format, for n=1 and token mode, the model might look like this:

<class_id> <n-gram> <count>
0 excellent 15
1 refund 28

For n=3 and codepoint mode, it might look like:

<class_id> <n-gram> <count>
0 exc 15
1 ref 28

The human-readable format is not used by ClickHouse directly; it must be converted to the binary format described below.

Binary format details

Each n-gram is stored as:

4-byte class_id (UInt, little-endian).
4-byte n-gram bytes length (UInt, little-endian).
Raw n-gram bytes.
4-byte count (UInt, little-endian).

Preprocessing requirements

Before the model is created from the document corpus, the documents must be preprocessed to extract n-grams according to the specified mode and n:

Add boundary markers at the start and end of each document based on the tokenization mode:
- byte: 0x01 (start), 0xFF (end)
- codepoint: U+10FFFE (start), U+10FFFF (end)
- token: <s> (start), </s> (end)
Note: (n - 1) tokens are added at both the beginning and the end of the document.
Example for n=3 in token mode:
- Document: ClickHouse is fast
- Processed as: <s> <s> ClickHouse is fast </s> </s>
- Generated trigrams:
  - <s> <s> ClickHouse
  - <s> ClickHouse is
  - ClickHouse is fast
  - is fast </s>
  - fast </s> </s>

To simplify model creation for byte and codepoint modes, it may be convenient to first tokenize the document (a list of bytes for byte mode, a list of codepoints for codepoint mode), append n - 1 start tokens at the beginning and n - 1 end tokens at the end, then generate the n-grams and write them to the serialized file.

Syntax

naiveBayesClassifier(model_name, input_text)

Arguments

model_name — Name of the pre-configured model. The model must be defined in ClickHouse's configuration files. String
input_text — Text to classify. Input is processed exactly as provided (case/punctuation preserved). String

Returned value

Predicted class ID as an unsigned integer. Class IDs correspond to categories defined during model construction. UInt32

Examples

Classify the language of a text

SELECT naiveBayesClassifier('language', 'How are you?');

┌─naiveBayesClassifier('language', 'How are you?')─┐
          │ 0                                                │
          └──────────────────────────────────────────────────┘

          Result 0 might represent English, while 1 could indicate French - class meanings depend on your training data.

evalMLMethod​

naiveBayesClassifier​

evalMLMethod

naiveBayesClassifier