Forest Predict

UDTF: cuml_forest_predict

Official cuML reference: nvForest

Score Float32 rows with a pretrained XGBoost, LightGBM, or Treelite forest through nvForest.

Quickstart

Register the relations referenced by the parenthesized SELECT clauses below. For metadata validation, the descriptor names input_vectors.

SELECT id, prediction
FROM cuml_forest_predict(
  input => (SELECT id, d0, d1, d2, d3 FROM t),
  model => 'scorer.json',
  model_format => 'xgboost_json'
)
ORDER BY row_ordinal;

Inputs

Each relation argument is a parenthesized SELECT subquery. Metadata validation resolves a registered table or view for the same role without scanning its rows. See ML Inputs for the ID, feature element, Float64 narrowing, null, finite-value, and runtime-dimension contract.

Role	Required	Validation reference	Description
input	yes	table	Dense feature rows evaluated by the call.

Vector element types

Feature values must be Float32.

Arguments and options

Scalar SQL arguments

Argument	Type	Required	Default	Constraints	Description
model	string	yes	No default	minimum string length 1; maximum string length 1024	Model file path relative to the server-configured models root.
model_format	string	yes	No default	one of "xgboost_json", "xgboost_ubjson", "xgboost_legacy", "lightgbm", "treelite"	Serialized format of the model file.
output	string	no	"scores"	one of "scores", "class_index"	Return the model's postprocessed scores, or the index of the largest class score.

Vector binding shapes

Each relation subquery projects an id and either feature columns in dimension order or one list column, with the element types listed under Vector element types. Nullable field types are accepted. A null ID, feature value, list row, or list element fails the query.

The list-column shape excludes other feature columns. See ML Inputs for the allowed list containers and runtime checks.

Output

Column	Type	Nullable	Description
row_ordinal	UInt64	no	Evaluated input row position, including when IDs repeat. Follows the input's top-level ORDER BY on output columns; unspecified without one.
id	same_as_input.id	no	Logical input ID.
prediction	Float32 or Int32	no	Float32 score of a single-output model with output => 'scores'; Int32 class index with output => 'class_index'.
output_<index>	Float32	no	Score of one model output, returned instead of prediction for a multi-output model with output => 'scores'. The output contains model output count such columns, output_0 through output_&lt;model output count - 1&gt;.

The output schema depends on the call. Run gpu_validate_call against registered relations to inspect the schema that the validated ID types and literal options produce.

Model files

The model argument names a file beneath the root of the server's [models] table. The server opens that directory once at startup and resolves every model beneath the opened directory. Renaming or replacing root later, or a symlink among its ancestors, does not redirect lookups. Pointing the server at a new directory takes a restart. Lookup uses openat2(2), which requires Linux 5.6 or newer.

A model value is a path relative to root. Relative symlinks that stay beneath root are followed, and only regular files are accepted.

Condition	Rejection code
model is an absolute path or contains a .. component.	model_path_rejected
An absolute symlink, or a symlink whose target leaves root, appears in the path.	model_path_rejected, rejection outside_root
The path names a FIFO, a directory or another special file.	model_path_rejected, rejection not_regular_file
The file is larger than max_model_bytes.	model_too_large
nvForest cannot parse the file, or the model is multi-target.	model_rejected
The lookup keeps racing concurrent renames or mounts under root.	model_path_rejected, rejection resolution_retry_exhausted
Over Flight SQL, the file changes between GetFlightInfo and DoGet, or between preparing and executing a prepared statement.	model_changed

• An outside_root symlink is rejected without looking up its target.
• A statement that fails with model_changed must be planned again.

Planning reads and binds the model, and EXPLAIN shows its SHA-256 digest as model=sha256:<first 12 hex>. The server reads and parses the file synchronously on each planning pass (GetFlightInfo, DoGet, and prepared statement creation). A large model therefore adds its read and parse time to the planning latency of every query that names it.

Every file reachable beneath root, including hard links and bind mounts placed there, is trusted as a model source. root and its subdirectories must not be writable by untrusted users.

Examples

MerRec

Can an existing model improve a fixed-size buyer shortlist?

The recent-purchase rule selects the 55,404 shoppers who bought in the previous 30 days. The model list keeps the same number of highest-scoring shoppers, and the query counts September buyers in each list.

The model was trained before the query on features preceding August 1, 2023, with purchases in the next 30 days as labels. Training and model review are separate preparation steps; SQL loads propensity_xgb.json for inference.

The earlier test CTE computes ten activity and price features from June to August, retains shoppers with at least 20 views, and attaches the September outcome. The visible query segment below scores those features, ranks shoppers, and evaluates both budget allocations.

scores AS (
  SELECT id, prediction AS score FROM cuml_forest_predict(
    input => (SELECT id,
      LN(1 + views) AS views,
      LN(1 + likes) AS likes,
      LN(1 + intents) AS intents,
      LN(1 + buys) AS buys,
      LN(1 + gmv) AS gmv,
      LN(1 + views_30d) AS views_30d,
      LN(1 + likes_30d) AS likes_30d,
      LN(1 + intents_30d) AS intents_30d,
      LN(1 + buys_30d) AS buys_30d,
      LN(1 + mean_price) AS mean_price FROM test ORDER BY id),
    model => 'propensity_xgb.json', model_format => 'xgboost_json')
), ranked AS (
  SELECT t.label, t.buys_30d,
         ROW_NUMBER() OVER (ORDER BY s.score DESC, t.id) AS rk,
         COUNT(*) OVER () AS shoppers,
         SUM(CASE WHEN t.buys_30d > 0 THEN 1 ELSE 0 END) OVER () AS rule_size
  FROM scores s JOIN test t ON t.id = s.id
)
SELECT MAX(shoppers) AS shoppers, MAX(shoppers) / 100 AS top_1pct, MAX(rule_size) AS rule_size,
       SUM(label) AS buyers,
       SUM(CASE WHEN rk <= shoppers / 100 THEN label ELSE 0 END) AS model_top_1pct_buyers,
       SUM(CASE WHEN rk <= rule_size THEN label ELSE 0 END) AS model_rule_size_buyers,
       SUM(CASE WHEN buys_30d > 0 THEN label ELSE 0 END) AS rule_buyers
FROM ranked;

The evaluation contains 2,017,739 eligible shoppers and 38,925 September buyers.

Selection	Shoppers	Later buyers	Purchase rate
Highest model scores, 1% budget	20,177	9,704	48.1%
Highest model scores, rule-sized budget	55,404	16,087	29.0%
Bought in previous 30 days	55,404	14,107	25.5%

At equal list size, the model includes 1,980 more observed buyers than the recent-purchase rule. The smaller model list has higher precision but reaches fewer buyers.

Prices use the latest retained item attributes, which also applies to training.

The capture contains the full result and execution record.

EMBER2024

Which high-scoring files should an analyst inspect first?

The query scores all 6,315 challenge files with a pretrained LightGBM model using 2,568 features, then joins the three largest positive feature-block contributions for each file. A review queue built from this result carries both the score and the inputs behind it.

The model is loaded by cuml_forest_predict. CPU LightGBM pred_contrib was run before SQL, with its 2,568 contributions summed into 12 named blocks. cuvs_top_k ranks those prepared values. The query does not compute SHAP.

SELECT p.id, m.sha256, m.file_type, m.week_id, p.prediction,
       t.rank, g.group_name, t.value AS positive_log_odds_contribution
FROM cuml_forest_predict(
  input => (
    SELECT id, vector
    FROM ember_research_vectors_challenge
    ORDER BY id
  ),
  model => 'EMBER2024_all.model',
  model_format => 'lightgbm'
) p
JOIN cuvs_top_k(
  input => (SELECT id, vector FROM ember_attributions ORDER BY id),
  k => 3,
  select => 'max'
) t ON t.id = p.id
JOIN ember_feature_groups g ON g.group_index = t.position
JOIN ember_metadata_challenge m ON m.id = p.id
WHERE p.prediction >= 0.99 AND t.value > 0
ORDER BY p.prediction DESC, p.id, t.rank
LIMIT 30;

The 0.99 threshold includes 1,616 files before the output limit. The returned 30 rows represent ten files in this capture. The highest-ranked file is 20001348, with a score of 0.999851. Its three returned contributions are:

Feature block	Positive log-odds contribution
histogram	+2.504
strings	+2.249
header	+1.235

The query took 0.24 s.

The returned blocks identify model inputs for further inspection. Negative contributions and model bias are absent from the table, and these values do not explain why a file is malicious.

GPU predictions matched CPU LightGBM within 3.0e-8 maximum absolute error across all 6,315 files. Every challenge label is malicious, so this cohort cannot measure false-positive rate or establish score calibration.

The capture retains the full SHA-256 records, all 30 rows, attribution preparation and CPU score comparison.

Limits

• Scores pretrained XGBoost (JSON, UBJSON, legacy binary), LightGBM text, and Treelite checkpoint forests with nvForest. Multi-target models are rejected at planning.
• Features must be Float32 without null values and match the model feature count. Forests with double-precision thresholds widen the features to Float64 on the device.
• output => 'class_index' requires a model with at least two outputs and resolves ties to the lowest class index.
• model resolves under the server-configured models root, and files above max_model_bytes are rejected. The model is read and imported each time a query is planned; there is no model cache.