Machine Learning Functions

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

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:

1. 4-byte class_id (UInt, little-endian)
2. 4-byte n-gram bytes length (UInt, little-endian)
3. Raw n-gram bytes
4. 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:

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

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

CREATE TABLE trips (pickup_datetime DateTime('UTC'), trip_distance Float64, total_amount Float64) ENGINE = Memory;

-- A fare of 3, plus 2.5 for every unit of distance.
INSERT INTO trips
SELECT toDateTime('2020-01-01 00:00:00', 'UTC') + number * 60, number % 10 + 1, 2.5 * (number % 10 + 1) + 3
FROM numbers(1000);

-- One model per year of the data.
CREATE TABLE models ENGINE = Memory AS
SELECT
    toYear(pickup_datetime) AS year,
    stochasticLinearRegressionState(0.01, 0.0, 10, 'SGD')(total_amount, trip_distance) AS model
FROM trips
GROUP BY year;

SELECT
    trip_distance,
    round(evalMLMethod(model, trip_distance), 2) AS predicted,
    total_amount
FROM trips
LEFT JOIN models ON year = toYear(pickup_datetime)
ORDER BY pickup_datetime
LIMIT 5

┌─trip_distance─┬─predicted─┬─total_amount─┐
│             1 │      4.05 │          5.5 │
│             2 │      6.79 │            8 │
│             3 │      9.53 │         10.5 │
│             4 │     12.28 │           13 │
│             5 │     15.02 │         15.5 │
└───────────────┴───────────┴──────────────┘

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

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

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

SELECT naiveBayesClassifier('sentiment', 'a good and great film') AS class_id;

┌─class_id─┐
│        0 │
└──────────┘

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.

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

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

SELECT arrayMap(p -> (p.1, round(p.2, 4)), naiveBayesClassifierWithAllProbs('sentiment', 'a good and great film')) AS predictions;

┌─predictions─────────────┐
│ [(0,0.9677),(1,0.0323)] │
└─────────────────────────┘

naiveBayesClassifierWithProb

Introduced in: v26.7.0

Classifies input text using a NAIVE_BAYES dictionary and returns the predicted class with its probability.

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

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

WITH naiveBayesClassifierWithProb('sentiment', 'a good and great film') AS p
SELECT (p.1, round(p.2, 4)) AS prediction;

┌─prediction─┐
│ (0,0.9677) │
└────────────┘