PREDICT_PMML

Applies an imported PMML model on an input relation. The function returns the result that would be expected for the model type encoded in the PMML model.

PREDICT_PMML returns NULL in the following cases:

  • the predictor is an invalid or NULL value
  • the categorical predictor is of an unknown class

PREDICT_PMML returns values of complex type ROW for models that use the Output tag. Vertica does not currently support directly inserting this data into a table.

You can work around this limitation by changing the output to JSON with TO_JSON before inserting it into a table:

=> CREATE TABLE predicted_output AS SELECT TO_JSON(PREDICT_PMML(X1,X2,X3 
USING PARAMETERS model_name='pmml_imported_model')) 
AS predicted_value 
FROM input_table;

Syntax

PREDICT_PMML ( input‑columns
        USING PARAMETERS model_name = 'model-name' [, match_by_pos = match-by-position] )

Arguments

input‑columns

Comma-separated list of columns to use from the input relation, or asterisk (*) to select all columns.

Parameters

Parameter Description
model_name

Name of the model (case-insensitive). The function supports PMML models that encode the following model types:

  • K-means
  • Linear regression
  • Logistic regression
match_by_pos

Boolean value that specifies how input columns are matched to model features:

  • false (default): Match by name.
  • true: Match by the position of columns in the input columns list.

Examples

In this example, the function call uses all the columns from the table as predictors and predicts the value using the 'my_kmeans' model in PMML format:

SELECT PREDICT_PMML(* USING PARAMETERS model_name='my_kmeans') AS predicted_label FROM table;

In this example, the function call takes only columns col1, col2 as predictors, and predicts the value for each row using the 'my_kmeans' model from schema 'my_schema':

SELECT PREDICT_PMML(col1, col2 USING PARAMETERS model_name='my_schema.my_kmeans') AS predicted_label FROM table;

In this example, the function call returns an error as neither schema nor model-name can accept * as a value:

SELECT PREDICT_PMML(* USING PARAMETERS model_name='*.*') AS predicted_label FROM table;
SELECT PREDICT_PMML(* USING PARAMETERS model_name='*') AS predicted_label FROM table;
SELECT PREDICT_PMML(* USING PARAMETERS model_name='models.*') AS predicted_label FROM table;

See Also