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 )

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

When using a FROM clause to specify a dataset, the PREDICT function implicitly maps the feature columns of the specified dataset to those in the model. You can use a WITH clause to either:

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.
You can use braces to map multiple columns:
```
SELECT PREDICT(Trained_Model WITH {age = year, income = salary}) FROM dataset
```
The order of these columns in your WITH clause does not matter, and any missing column names are taken from the FROM clause.
Specify a list of arguments to make a prediction with. When using this form of WITH, you do not provide a FROM clause. For example:
```
SELECT PREDICT(Flower_Model WITH (5.1, 3.5, 1.4, 0.2, 'setosa'))
```
This query makes a prediction using Flower_Model on the expression (5.1, 3.5, 1.4, 0.2, 'setosa').
Arguments must be ordered exactly as specified in your CREATE MODEL statement. Missing arguments can be specified by empty commas. For example:
```
SELECT PREDICT(Flower_Model WITH (5.1, , 1.4, , 'setosa'))
```
You can use braces to provide multiple sets of arguments:
```
SELECT PREDICT(Flower_Model WITH ({5.1, 3.5, 1.4, 0.2, 'setosa'}, {6.4, 3.2, 4.3, 1.2, 'versicolor'}))
```

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.

Arguments

model-name

The name of the model.

USE trained-model-name

An optional argument that specifies the name of a non-default trained model. See details above.

WITH feature-columns-clause

An optional argument. The specific columns to provide as input for your trained model. See details above.

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