PREDICT (SQL)
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