Train, predict, and export SQL

This walkthrough fits a reference model, scores it, measures RMSE, and exports the trees. The exported query is then run in a second build/release/duckdb process whose script never calls duckboost_*.

The holdout is x1 >= 20 from a 30-row line. Predictions below are whatever this build’s reference trainer produces; the check is that the SQL matches duckboost_predict, not a hand-written target.

Train, predict, evaluate

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT
  i::DOUBLE AS x1,
  ((i * 3) % 5)::DOUBLE AS x2,
  (1.5 * i - 0.25 * ((i * 3) % 5))::DOUBLE AS y
FROM range(30) t(i);
CREATE TABLE score AS
SELECT x1, x2, y FROM train WHERE x1 >= 20 ORDER BY x1, x2;
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x1, x2],
  MAP {
    'backend': 'reference',
    'task': 'regression',
    'n_estimators': '6',
    'max_depth': '2',
    'learning_rate': '0.3',
    'feature_names': 'x1,x2'
  }
) AS model
FROM train;
SELECT s.x1, s.x2, duckboost_predict(m.model, [s.x1, s.x2]) AS pred
FROM score s, models m
ORDER BY s.x1, s.x2;
SELECT duckboost_evaluate_agg(
  m.model, s.y, [s.x1, s.x2], MAP {'metric': 'rmse'}
) AS rmse
FROM score s, models m;

┌──────┬─────┬────────────────────┐
│  x1  │ x2  │        pred        │
├──────┼─────┼────────────────────┤
│ 20.0 │ 0.0 │ 28.33474144771992  │
│ 21.0 │ 3.0 │ 28.33474144771992  │
│ 22.0 │ 1.0 │ 31.689652162005636 │
│ 23.0 │ 4.0 │ 31.689652162005636 │
│ 24.0 │ 2.0 │ 34.382620912005635 │
│ 25.0 │ 0.0 │ 35.28349314429873  │
│ 26.0 │ 3.0 │ 36.975148650251114 │
│ 27.0 │ 1.0 │ 38.08935498395647  │
│ 28.0 │ 4.0 │ 38.08935498395647  │
│ 29.0 │ 2.0 │ 38.08935498395647  │
└──────┴─────┴────────────────────┘
┌────────────────────┐
│        rmse        │
├────────────────────┤
│ 2.4093488156004845 │
└────────────────────┘

Export, then score without calling the extension

duckboost_to_sql returns a SELECT. The second process runs that text after creating score with ordinary SQL. separate_trees is true, so the query sums one expression per tree (the default).

Extension session: train, then duckboost_predict.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT
  i::DOUBLE AS x1,
  ((i * 3) % 5)::DOUBLE AS x2,
  (1.5 * i - 0.25 * ((i * 3) % 5))::DOUBLE AS y
FROM range(30) t(i);
CREATE TABLE score AS
SELECT x1, x2 FROM train WHERE x1 >= 20 ORDER BY x1, x2;
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x1, x2],
  MAP {
    'backend': 'reference',
    'task': 'regression',
    'n_estimators': '6',
    'max_depth': '2',
    'learning_rate': '0.3',
    'feature_names': 'x1,x2'
  }
) AS model
FROM train;
SELECT duckboost_predict(m.model, [s.x1, s.x2]) AS pred
FROM score s, models m
ORDER BY s.x1, s.x2;

┌────────────────────┐
│        pred        │
├────────────────────┤
│ 28.33474144771992  │
│ 28.33474144771992  │
│ 31.689652162005636 │
│ 31.689652162005636 │
│ 34.382620912005635 │
│ 35.28349314429873  │
│ 36.975148650251114 │
│ 38.08935498395647  │
│ 38.08935498395647  │
│ 38.08935498395647  │
└────────────────────┘

Same model, exported with duckboost_to_sql:

SELECT duckboost_to_sql(
  model,
  'score',
  ['x1', 'x2'],
  MAP {'separate_trees': 'true', 'prediction_alias': 'pred'}
) AS exported_sql
FROM models;

Generated query:

SELECT ("__duckboost_base" + 0.29999999999999999 * "__duckboost_tree_0" + 0.29999999999999999 * "__duckboost_tree_1" + 0.29999999999999999 * "__duckboost_tree_2" + 0.29999999999999999 * "__duckboost_tree_3" + 0.29999999999999999 * "__duckboost_tree_4" + 0.29999999999999999 * "__duckboost_tree_5") AS "pred" FROM (SELECT (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -17.214285714285715 WHEN "x1" < 6.5 THEN -17.214285714285715 ELSE -6.03125 END WHEN "x1" < 14.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -17.214285714285715 WHEN "x1" < 6.5 THEN -17.214285714285715 ELSE -6.03125 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 5.2857142857142856 WHEN "x1" < 21.5 THEN 5.2857142857142856 ELSE 16.46875 END END) AS "__duckboost_tree_0", (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -13.585714285714285 WHEN "x1" < 4.5 THEN -13.585714285714285 ELSE -5.6950892857142845 END WHEN "x1" < 13.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -13.585714285714285 WHEN "x1" < 4.5 THEN -13.585714285714285 ELSE -5.6950892857142845 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.0828125000000002 WHEN "x1" < 23.5 THEN 4.0828125000000002 ELSE 13.059375000000001 END END) AS "__duckboost_tree_1", (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -7.9500669642857131 WHEN "x1" < 9.5 THEN -7.9500669642857131 ELSE -0.92356823979592051 END WHEN "x1" < 16.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -7.9500669642857131 WHEN "x1" < 9.5 THEN -7.9500669642857131 ELSE -0.92356823979592051 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.8777108134920617 WHEN "x1" < 25.5 THEN 4.8777108134920617 ELSE 10.516562499999999 END END) AS "__duckboost_tree_2", (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -5.9225989583333352 WHEN "x1" < 8.5 THEN -5.9225989583333352 ELSE -0.66703050595238378 END WHEN "x1" < 18.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -5.9225989583333352 WHEN "x1" < 8.5 THEN -5.9225989583333352 ELSE -0.66703050595238378 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.4392393043154748 WHEN "x1" < 26.5 THEN 4.4392393043154748 ELSE 8.1532604166666633 END END) AS "__duckboost_tree_3", (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -6.6815335565476195 WHEN "x1" < 2.5 THEN -6.6815335565476195 ELSE -2.5749532135770985 END WHEN "x1" < 11.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -6.6815335565476195 WHEN "x1" < 2.5 THEN -6.6815335565476195 ELSE -2.5749532135770985 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 0.29774369270168766 WHEN "x1" < 19.5 THEN 0.29774369270168766 ELSE 4.0837230050223212 END END) AS "__duckboost_tree_4", (CASE WHEN "x1" IS NULL OR isnan("x1") THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -4.4017336819727895 WHEN "x1" < 3.5 THEN -4.4017336819727895 ELSE -1.5808113732993201 END WHEN "x1" < 11.5 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -4.4017336819727895 WHEN "x1" < 3.5 THEN -4.4017336819727895 ELSE -1.5808113732993201 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 0.8466049171889295 WHEN "x1" < 24.5 THEN 0.8466049171889295 ELSE 3.8495123581659243 END END) AS "__duckboost_tree_5", 21.25 AS "__duckboost_base" FROM "score") AS "_duckboost_trees"

The next result is that query in a new build/release/duckdb process. The script is the score-table DDL plus the generated SELECT, and it never calls duckboost_*. make links duckboost into that shell, so the extension is registered, but these statements do not use it.

CREATE TABLE score AS
SELECT
  i::DOUBLE AS x1,
  ((i * 3) % 5)::DOUBLE AS x2
FROM range(30) t(i)
WHERE i >= 20
ORDER BY x1, x2;
-- then the generated SELECT above

┌───────────────────────────────────────┐
│                 pred                  │
├───────────────────────────────────────┤
│ 28.3347414477199216638419517426692700 │
│ 28.3347414477199216638419517426692700 │
│ 31.6896521620056358720115945998121260 │
│ 31.6896521620056358720115945998121260 │
│ 34.3826209120056360222459695998121180 │
│ 35.2834931442987344322168951900421700 │
│ 36.9751486502511155658283783249627970 │
│ 38.0893549839564720786881672014509120 │
│ 38.0893549839564720786881672014509120 │
│ 38.0893549839564720786881672014509120 │
└───────────────────────────────────────┘

Maximum absolute difference versus duckboost_predict: 7.105427357601002e-15.