Machine Learning Functions - ClickHouse Documentation

evalMLMethod

Prediction using fitted regression models uses evalMLMethod function. See link in linearRegression.

stochasticLinearRegression

The stochasticLinearRegression aggregate function implements stochastic gradient descent method using linear model and MSE loss function. Uses evalMLMethod to predict on new data.

stochasticLogisticRegression

The stochasticLogisticRegression aggregate function implements stochastic gradient descent method for binary classification problem. Uses evalMLMethod to predict on new data.

naiveBayesClassifier

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

naiveBayesClassifier(model_name, input_text);

Arguments

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

Returned Value

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

Example Classify text with a language detection model:

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.

Implementation Details

Algorithm Uses Naive Bayes classification algorithm with Laplace smoothing to handle unseen n-grams based on n-gram probabilities based on this. 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

You can find sample source code for creating a Naive Bayes model for language detection here. Additionally, sample models and their associated config files are available here. Here is 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 model binary	`/etc/clickhouse-server/config.d/language_detection.bin`	Required
mode	Tokenization method: - `byte`: Byte sequences - `codepoint`: Unicode characters - `token`: Word tokens	`token`	Required
n	N-gram size (`token` mode): - `1`=single word - `2`=word pairs - `3`=word triplets	`2`	Required
alpha	Laplace smoothing factor used during classification to address n-grams that do not appear in the model	`0.5`	`1.0`
priors	Class probabilities (% of the 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

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 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 being created from the document corpus, the documents must be preprocessed to extract n-grams according to the specified mode and n. The following steps outline the preprocessing:

Add boundary markers at the start and end of each document based on 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 into tokens (a list of bytes for byte mode and a list of codepoints for codepoint mode). Then, append n - 1 start tokens at the beginning and n - 1 end tokens at the end of the document. Finally, generate the n-grams and write them to the serialized file.

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

Query

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

Response

┌─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 dictionary. Returns the same predicted class value as dictGet(dictionary_name, class_attribute, input_text), where class_attribute is the name of the class label attribute configured in the dictionary’s layout. Unlike dictGet, the result type is always UInt32 rather than the declared type of the class attribute, and input_text must be a String (no key type conversion is applied).

This function is non-deterministic: it can return different results for the same arguments.

Syntax

naiveBayesClassifier(dictionary_name, input_text)

Arguments

dictionary_name — Name of a dictionary with the NAIVE_BAYES layout. String
input_text — Text to classify. String

Returned value Predicted class ID. UInt32 Examples Classify text

Query

SELECT naiveBayesClassifier('model', 'some text');

Response

naiveBayesClassifierWithAllProbs

Introduced in: v26.7.0 Classifies input text using a NAIVE_BAYES dictionary and returns all classes with their probabilities, ordered from most to least probable.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

naiveBayesClassifierWithAllProbs(dictionary_name, input_text)

Arguments

dictionary_name — Name of a dictionary with the NAIVE_BAYES layout. String
input_text — Text to classify. String

Returned value Array of (class_id, probability) tuples ordered from most to least probable. Array(Tuple(UInt32, Float64)) Examples All class probabilities

Query

SELECT naiveBayesClassifierWithAllProbs('model', 'some text');

Response

[(0,0.85),(1,0.15)]

naiveBayesClassifierWithProb

Introduced in: v26.7.0 Classifies input text using a NAIVE_BAYES dictionary and returns the predicted class with its probability.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

naiveBayesClassifierWithProb(dictionary_name, input_text)

Arguments

dictionary_name — Name of a dictionary with the NAIVE_BAYES layout. String
input_text — Text to classify. String

Returned value Tuple of (class_id, probability). Tuple(UInt32, Float64) Examples Classify with probability

Query

SELECT naiveBayesClassifierWithProb('model', 'some text');

Response

(0,0.85)

​evalMLMethod

​stochasticLinearRegression

​stochasticLogisticRegression

​naiveBayesClassifier

​Implementation Details

​Model Configuration

​evalMLMethod

​naiveBayesClassifier

​naiveBayesClassifierWithAllProbs

​naiveBayesClassifierWithProb

evalMLMethod

stochasticLinearRegression

stochasticLogisticRegression

naiveBayesClassifier

Implementation Details

Model Configuration

evalMLMethod

naiveBayesClassifier

naiveBayesClassifierWithAllProbs

naiveBayesClassifierWithProb