Random Forest Classifier
UDTF: cuml_random_forest_classifier
Official cuML reference: Python API
Fit a query-local cuML random forest classifier and score independent rows.
Quickstart
Register the relations referenced by the parenthesized SELECT clauses below. For metadata validation, the descriptor names training_vectors and predict_vectors.
SELECT id, prediction
FROM cuml_random_forest_classifier(
training => (SELECT id, d0, label FROM train),
predict => (SELECT id, d0 FROM test)
)
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, Float32 feature, null, finite-value, and runtime-dimension contract.
Vector element types
Arguments and options
Scalar SQL arguments
SQL value argument schemas
Vector binding shapes
Each relation subquery projects a non-null id and either non-null Float32 feature columns in dimension order or one non-null vector list column of non-null Float32 values. For classification, training also projects non-null Int32 label; regression requires non-null finite Float32 label. The predict relation omits it.
The list-column shape excludes other feature columns. See ML Inputs for the allowed list containers and runtime checks.
Output
Concrete schemas are call-specific. Run gpu_validate_call against registered relations to inspect the output schema after the actual ID types and literal options are validated.
Examples
This example uses the EMBER2024 demo dataset.
How much analyst review does each classifier leave for a new quarter?
The held-out EMBER2024 test period contains 3,618 sampled Win32 files: 1,787 malicious and 1,831 benign. Both query-local classifiers train on the same 15,588 sampled Win32 training files and use the same 512 normalized byte and byte-entropy histogram features. The sample rule is independent of labels and retains hashes whose first eight hexadecimal characters, read as an integer, give remainder zero modulo 100. Test labels join only after prediction.
In this capture the random forest leaves 129 fewer false positive files for
review and misses 124 fewer malicious files. Both UDTFs return the binary
class IDs 0 and 1 in prediction. The comparison applies to this feature
set and the model options in the SQL below.
The official EMBER2024_all.model reference has 17 false positives and 53
false negatives on the same Win32 test rows at a 0.5 score threshold. That
model uses all 2,568 features and a larger upstream training corpus, so its
metrics are a reference and do not support a fair algorithm ranking against
the 512-feature query-local fits.
Download the JSON capture, including the exact SQL, both result rows, execution conditions, supervised report metrics by week, and source query IDs.
WITH logistic AS (
SELECT p.id, p.prediction, m.label, m.week_id, m.sha256, m.family
FROM cuml_logistic_regression(
training => (
SELECT v.id, v.histogram AS vector, v.label
FROM ember_research_vectors_train v
JOIN ember_metadata_train m ON m.id = v.id
WHERE m.file_type = 'Win32'
ORDER BY v.id
),
predict => (
SELECT v.id, v.histogram AS vector
FROM ember_research_vectors_test v
JOIN ember_metadata_test m ON m.id = v.id
WHERE m.file_type = 'Win32'
ORDER BY v.id
),
max_iter => 1000,
penalty_l2 => 1.0
) p
JOIN ember_metadata_test m ON m.id = p.id
),
random_forest AS (
SELECT p.id, p.prediction, m.label, m.week_id, m.sha256, m.family
FROM cuml_random_forest_classifier(
training => (
SELECT v.id, v.histogram AS vector, v.label
FROM ember_research_vectors_train v
JOIN ember_metadata_train m ON m.id = v.id
WHERE m.file_type = 'Win32'
ORDER BY v.id
),
predict => (
SELECT v.id, v.histogram AS vector
FROM ember_research_vectors_test v
JOIN ember_metadata_test m ON m.id = v.id
WHERE m.file_type = 'Win32'
ORDER BY v.id
),
n_trees => 100,
max_depth => 12,
seed => 42
) p
JOIN ember_metadata_test m ON m.id = p.id
),
classified AS (
SELECT 'logistic' AS model, id, prediction, label FROM logistic
UNION ALL
SELECT 'random_forest' AS model, id, prediction, label FROM random_forest
)
SELECT model, COUNT(*) AS files,
SUM(CASE WHEN prediction = 1 AND label = 1 THEN 1 ELSE 0 END) AS true_positive,
SUM(CASE WHEN prediction = 1 AND label = 0 THEN 1 ELSE 0 END) AS false_positive,
SUM(CASE WHEN prediction = 0 AND label = 1 THEN 1 ELSE 0 END) AS false_negative,
SUM(CASE WHEN prediction = 0 AND label = 0 THEN 1 ELSE 0 END) AS true_negative
FROM classified
GROUP BY model
ORDER BY model;
Both classifiers are fitted within the statement. Repeating the SQL trains them again on the selected training rows.
Limits
- Validation resolves named tables or views and reads schemas only; it does not execute relation scans or GPU work.
- Execution relation arguments require parenthesized subqueries; dry-run validation accepts registered named relations only.
- Classifier labels must be non-null Int32 contiguous classes 0..C-1; regressor labels must be non-null finite Float32. Predict must omit label and match the training feature dimension.
- Prediction imports the fitted forest into nvForest and scores the predict relation on the GPU; a classifier tie resolves to the lowest class index. Training and prediction inputs are limited to i32::MAX rows and the packed feature matrix to i32::MAX elements; the random forest path does not batch rows.
- Training uses one RAFT pool stream whose allocations share the query grant. The forest is query-local; an empty predict relation returns an empty result.
To dry-run validate relation metadata, column types, and options without execution, see gpu_validate_call.