PREDICT (SQL)

A function that applies a specified trained model to predict the result for each input row provided.

Synopsis

PREDICT( model-name)

PREDICT( model-name USE  trained-model-name )

PREDICT( model-name WITH feature-columns-clause )

PREDICT( model-name USE  trained-model-name WITH feature-columns-clause )

Arguments

model-name	The name of the model.
USE trained-model-name	Optional — The name of a non-default trained model. See details below.
WITH feature-columns-clause	Optional — The specific columns to provide as input for your trained model. See details below.

Description

PREDICT returns the result of applying a trained machine learning model onto a specified query. This is performed on a row-by-row basis.

USE

If a trained model is not explicitly named by USE, PREDICT uses the default trained model for the specified model definition.

For example, if multiple models are trained:


CREATE MODEL MyModel PREDICTING( label ) FROM data
TRAIN MODEL MyModel AS FirstModel
TRAIN MODEL MyModel AS SecondModel NOT DEFAULT

FirstModel is the default model for MyModel. This means that PREDICT queries would use FirstModel for predictions. To specify use of SecondModel:


PREDICT( MyModel USE SecondModel)

WITH

PREDICT is a smart function, mapping the feature columns of the specified dataset to those in the model implicitly when there is no WITH clause. You can use a WITH clause to specify the mapping of columns between the dataset and your model. For example:


SELECT PREDICT(Trained_Model WITH age = year) FROM dataset

This query matches the age column from Trained_Model to the year column from dataset.

Required Security Privileges

Calling PREDICT requires %USE_MODEL privileges; otherwise, there is a SQLCODE –99 error (Privilege Violation). To assign %USE_MODEL privileges, use the GRANT command.

Examples


CREATE MODEL HousePriceModel PREDICTING( HousePrice ) FROM housing_data_2019
TRAIN MODEL HousePriceModel
SELECT * FROM housing_data_2020 WHERE PREDICT( HousePriceModel ) > 500000


CREATE MODEL PatientReadmission PREDICTING ( IsReadmitted ) FROM patient_data
TRAIN MODEL PatientReadmission
SELECT *, PREDICT( PatientReadmission ) FROM new_patient_data