XGBoost dictionaries

The xgboost (XGBOOST) dictionary trains an XGBoost gradient-boosted model, at load time, from a source table of training rows, then predicts a numeric target for any feature vector you pass in. The feature columns are the dictionary key and the single attribute is the target the model learns. It is suited to tabular regression and binary classification where the features are numeric — for example forecasting a value from several measurements, or scoring rows against a learned target. Multiclass objectives are not supported (see Layout parameters).

The XGBoost integration is experimental. Enable it with the enable_xgboost setting before creating an XGBOOST dictionary or calling predictXGBoost:

SET enable_xgboost = 1;

Only CREATE DICTIONARY is supported: an XGBOOST dictionary defined in a server configuration file fails to load.

predictXGBoost is the only way to query the dictionary: it takes the features as individual arguments, returns the prediction, and accepts additional prediction parameters. The dictionary holds a trained model rather than rows, so the generic dictionary interface — dictGet, dictHas and SELECT * FROM dict — is not supported and reports an error.

Quickstart

Here we train a regressor on the linear target y = 2*x1 + 3*x2. 1. Create a source table of training rows — the feature columns followed by the target:

CREATE TABLE training_data (x1 Float64, x2 Float64, y Float64)
ENGINE = MergeTree ORDER BY tuple();

2. Insert training data:

INSERT INTO training_data
SELECT number AS x1, number * 2 AS x2, 2 * x1 + 3 * x2 AS y
FROM numbers(100);

3. Create the dictionary with the XGBOOST layout — the feature columns are the key and y is the target attribute:

CREATE DICTIONARY model (x1 Float64, x2 Float64, y Float64)
PRIMARY KEY (x1, x2)
SOURCE(CLICKHOUSE(TABLE 'training_data'))
LAYOUT(XGBOOST(
    objective 'reg:squarederror'
    num_iterations 100
    max_depth 6
))
LIFETIME(0);

PRIMARY KEY (x1, x2) makes x1 and x2 the features. The target the model learns is y, inferred as the single column that is not part of the key; the parameters in LAYOUT are XGBoost hyperparameters (see Layout parameters). 4. Predict — predictXGBoost takes the features positionally and returns the prediction:

SELECT predictXGBoost('model', 1.0, 2.0) AS prediction;

The ground truth is 2*1 + 3*2 = 8, so the model’s prediction is close to 8.

How it works

Training (at load time). Each source row is a (features..., target) observation. When the dictionary loads, the source is read block by block and the model is then trained once over the whole set. Feature and target values are read as floats, so the key columns must be numeric and the target attribute floating-point (see Dictionary structure).

Training holds the entire training set in memory. Reading the source in blocks is not out-of-core training: each block is accumulated rather than consumed, so the whole training set is memory resident before the first boosting round and stays memory resident until the last one.

Predicting (at query time). To predict, the model takes the feature vector — in the same order as the key columns were declared — and runs it through the trained booster, returning a Float64. A NULL feature is passed to the model as a missing value, which XGBoost handles the way it learned to during training, so the prediction is never NULL. When every feature is a constant, the model is evaluated once per block instead of once per row. The model is not persisted. It lives only in memory, for as long as the dictionary is loaded, and is trained again from the source on every load — including after a server restart. Retraining the model. Because every load trains from scratch, SYSTEM RELOAD DICTIONARY retrains the model against the current contents of the source table:

INSERT INTO training_data VALUES (5, 10, 40);
SYSTEM RELOAD DICTIONARY model;

A non-zero LIFETIME also retrains, since a lifetime-triggered reload is an ordinary load. Use it to refresh the model periodically as the training data grows.

Dictionary structure

An XGBOOST dictionary has a fixed shape:

The PRIMARY KEY is one or more columns of a native numeric type (integers and floats) — the features. At query time this “key” is the feature vector you pass in to predict, not a stored lookup key. The feature order is the key-column declaration order, and predictXGBoost binds its positional arguments to that order.
Alongside them, declare exactly one attribute of type Float32 or Float64: the target the model learns. It is always inferred as the single column that is not part of the feature key — there is no parameter to name it, and it is an error to declare more than one attribute.

A column that does not match these requirements is rejected when the dictionary loads, not when you create it.

Layout parameters

Only the parameters listed below are accepted; any other name fails the load, so typos are caught when the model trains rather than being silently ignored. num_iterations is handled by ClickHouse (see its description); every other parameter is forwarded to the XGBoost booster unchanged, as a string, and takes XGBoost’s own default and value range — see the XGBoost parameter reference.

Parameter	Description
`num_iterations`	Number of boosting rounds (how many trees to train). A positive integer, used as the training loop count rather than forwarded to the booster. Default `100`.
`booster`	Booster type: `gbtree`, `gblinear`, or `dart`.
`objective`	Learning objective, e.g. `reg:squarederror` or `binary:logistic`. Must be an objective that predicts a single value per row; multiclass objectives (`multi:softmax`, `multi:softprob`) are rejected.
`seed`	Random number seed. Training is otherwise deterministic, so this only changes the model when a stochastic parameter (`subsample`, `sampling_method`, any `colsample_*`) is also set.
`verbosity`	Logging verbosity of XGBoost: `0` (silent) to `3` (debug), default `1` (warnings). Affects only which messages XGBoost logs, never the trained model. See Notes for where the messages go.
`nthread`	Number of parallel threads used for training. Accepted and forwarded, but currently has no effect: the bundled XGBoost is built without OpenMP, so training and prediction always run on a single thread.
`eta`	Step-size shrinkage applied after each boosting round. Only this spelling is accepted; XGBoost’s `learning_rate` alias is not.
`gamma`	Minimum loss reduction required to make a further split on a leaf.
`max_depth`	Maximum depth of a tree.
`min_child_weight`	Minimum sum of instance weight (hessian) needed in a child.
`max_delta_step`	Maximum delta step allowed for each leaf’s output.
`subsample`	Fraction of the training rows sampled for each boosting round.
`sampling_method`	Row sampling method: `uniform` or `gradient_based`. Has no effect unless `subsample` is set to less than `1`.
`colsample_bytree` / `colsample_bylevel` / `colsample_bynode`	Fraction of columns (features) sampled per tree / per level / per split.
`lambda`	L2 regularization term on weights. Only this spelling is accepted; XGBoost’s `reg_lambda` alias is not.
`alpha`	L1 regularization term on weights. Only this spelling is accepted; XGBoost’s `reg_alpha` alias is not.
`tree_method`	Tree construction algorithm: `auto`, `exact`, `approx`, or `hist`.
`scale_pos_weight`	Balances positive and negative weights, useful for imbalanced classes.
`grow_policy`	How new nodes are added to the tree: `depthwise` or `lossguide`.
`max_leaves`	Maximum number of leaf nodes (used with `grow_policy` `lossguide`).
`max_bin`	Maximum number of discrete bins used to bucket continuous features (used with `tree_method` `hist`).
`num_parallel_tree`	Number of trees grown per boosting round (a value `> 1` trains a boosted random forest).

Parameter names are case-insensitive, and each may be given only once. Values must be a positive integer, a float, or a quoted string: a negative literal is rejected by the dictionary DDL itself, before the layout sees it, so a negative seed cannot be expressed. For example, a dictionary that also sets the step size eta:

CREATE DICTIONARY model_tuned (x1 Float64, x2 Float64, y Float64)
PRIMARY KEY (x1, x2)
SOURCE(CLICKHOUSE(TABLE 'training_data'))
LAYOUT(XGBOOST(
    objective 'reg:squarederror'
    num_iterations 100
    max_depth 6
    eta 0.3
))
LIFETIME(0);

Prediction parameters

predictXGBoost accepts an optional trailing constant Map of XGBoost prediction parameters, after the features, built with map:

SELECT predictXGBoost('model', 1.0, 2.0, map('type', 0, 'iteration_end', 0));

The parameter names map to the prediction parameters of XGBoost’s XGBoosterPredictFromDMatrix. Only the keys below are accepted; any other key fails the query. Every parameter is an integer or a boolean, so the Map values must be an integer type.

Parameter	Description	Default
`type`	Prediction type. Only `0` (value) and `1` (margin) are accepted, because `predictXGBoost` returns a single `Float64` per row. Other XGBoost types (`2`/`3` SHAP contributions, `4`/`5` feature interactions, `6` leaf index) emit several values per row and are rejected.	`0`
`iteration_begin`	First boosting round to include in the prediction, counted from `0` (inclusive). Must not exceed the number of boosting iterations the model was trained with (`num_iterations`).	`0`
`iteration_end`	One past the last boosting round to include (exclusive), so `iteration_end 1` uses only the first round; `0` uses all rounds. Bounded like `iteration_begin`.	`0`

Notes

Computational dictionary semantics. This is a computational dictionary: it holds a trained model, not rows, and predictXGBoost is the only way to query it. The generic dictionary interface is not supported and reports an error: dictGet (there is no stored attribute to look up — the “key” is a feature vector to predict from), dictHas (no keys are stored), SELECT * FROM dict and joining the dictionary as a table. Because predictXGBoost is the only entry point, the enable_xgboost setting must be enabled for every prediction.
Numeric columns only. Every feature (key) column must be a native numeric type and the target attribute must be Float32 or Float64. Values are read as floats during training and prediction. The feature arguments of predictXGBoost may also be Nullable, see How it works.
system.dictionaries reports no stored items. The dictionary trains a model instead of storing rows, so element_count is 0, as it is for a direct dictionary, and bytes_allocated is 0 too: the trained model belongs to XGBoost, which does not report how much memory it holds. query_count and found_rate count the rows passed to the model by predictXGBoost; a call whose features are all constant is evaluated once per block, so it counts one row per block rather than one per row.
A failed reload keeps the previous model. If retraining fails — the source table is gone, its schema changed, a hyperparameter is no longer accepted — the dictionary does not start failing predictions. It keeps serving the last model that trained successfully, and records the error instead. Compare last_successful_update_time with last_exception in system.dictionaries to tell whether the model still reflects the current source data:
SELECT name, status, last_successful_update_time, last_exception FROM system.dictionaries WHERE name = 'model';
XGBoost’s messages go to the server log. They are written under the XGBoost logger, at the level XGBoost gives them, and are attached to the query that trains or predicts, so they also appear in system.text_log. Raise verbosity to see more of them.
Feature order matters. predictXGBoost binds its positional feature arguments to the key columns in declaration order, and the number of feature arguments must match the number of key columns.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Quickstart

How it works

Dictionary structure

Layout parameters

Prediction parameters

Notes

​Quickstart

​How it works

​Dictionary structure

​Layout parameters

​Prediction parameters

​Notes

Quickstart

How it works

Dictionary structure

Layout parameters

Prediction parameters

Notes