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). Leaf values and the learning rate are spelled as DOUBLE literals (0.3e0), so DuckDB 2.0 does not read them as DECIMAL.

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.29999999999999999e0 * "__duckboost_tree_0" + 0.29999999999999999e0 * "__duckboost_tree_1" + 0.29999999999999999e0 * "__duckboost_tree_2" + 0.29999999999999999e0 * "__duckboost_tree_3" + 0.29999999999999999e0 * "__duckboost_tree_4" + 0.29999999999999999e0 * "__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.214285714285715e0 WHEN "x1" < 6.5e0 THEN -17.214285714285715e0 ELSE -6.03125e0 END WHEN "x1" < 14.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -17.214285714285715e0 WHEN "x1" < 6.5e0 THEN -17.214285714285715e0 ELSE -6.03125e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 5.2857142857142856e0 WHEN "x1" < 21.5e0 THEN 5.2857142857142856e0 ELSE 16.46875e0 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.585714285714285e0 WHEN "x1" < 4.5e0 THEN -13.585714285714285e0 ELSE -5.6950892857142845e0 END WHEN "x1" < 13.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -13.585714285714285e0 WHEN "x1" < 4.5e0 THEN -13.585714285714285e0 ELSE -5.6950892857142845e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.0828125000000002e0 WHEN "x1" < 23.5e0 THEN 4.0828125000000002e0 ELSE 13.059375000000001e0 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.9500669642857131e0 WHEN "x1" < 9.5e0 THEN -7.9500669642857131e0 ELSE -0.92356823979592051e0 END WHEN "x1" < 16.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -7.9500669642857131e0 WHEN "x1" < 9.5e0 THEN -7.9500669642857131e0 ELSE -0.92356823979592051e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.8777108134920617e0 WHEN "x1" < 25.5e0 THEN 4.8777108134920617e0 ELSE 10.516562499999999e0 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.9225989583333352e0 WHEN "x1" < 8.5e0 THEN -5.9225989583333352e0 ELSE -0.66703050595238378e0 END WHEN "x1" < 18.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -5.9225989583333352e0 WHEN "x1" < 8.5e0 THEN -5.9225989583333352e0 ELSE -0.66703050595238378e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 4.4392393043154748e0 WHEN "x1" < 26.5e0 THEN 4.4392393043154748e0 ELSE 8.1532604166666633e0 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.6815335565476195e0 WHEN "x1" < 2.5e0 THEN -6.6815335565476195e0 ELSE -2.5749532135770985e0 END WHEN "x1" < 11.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -6.6815335565476195e0 WHEN "x1" < 2.5e0 THEN -6.6815335565476195e0 ELSE -2.5749532135770985e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 0.29774369270168766e0 WHEN "x1" < 19.5e0 THEN 0.29774369270168766e0 ELSE 4.0837230050223212e0 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.4017336819727895e0 WHEN "x1" < 3.5e0 THEN -4.4017336819727895e0 ELSE -1.5808113732993201e0 END WHEN "x1" < 11.5e0 THEN CASE WHEN "x1" IS NULL OR isnan("x1") THEN -4.4017336819727895e0 WHEN "x1" < 3.5e0 THEN -4.4017336819727895e0 ELSE -1.5808113732993201e0 END ELSE CASE WHEN "x1" IS NULL OR isnan("x1") THEN 0.8466049171889295e0 WHEN "x1" < 24.5e0 THEN 0.8466049171889295e0 ELSE 3.8495123581659243e0 END END) AS "__duckboost_tree_5", 21.25e0 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.33474144771992  │
│ 28.33474144771992  │
│ 31.689652162005636 │
│ 31.689652162005636 │
│ 34.382620912005635 │
│ 35.28349314429873  │
│ 36.975148650251114 │
│ 38.08935498395647  │
│ 38.08935498395647  │
│ 38.08935498395647  │
└────────────────────┘

Maximum absolute difference versus duckboost_predict: 0.0.