MySQL HeatWave User Guide

6.7.1.3 Generate Predictions for a Classification Model

After training the model, you can generate predictions.

To generate predictions, use the sample data from the testing_data dataset. Even though the table has labels for the Approved target column, the column is not considered when generating predictions. This allows you to compare the predictions to the actual values in the dataset and determine if the predictions are reliable. Once you determine the trained model is reliable for generating predictions, you can start using unlabeled datasets for generating predictions.

Before You Begin

Complete the following tasks:

Generate Predictions for a Table

  1. If not already done, load the model. You can use the session variable for the model that is valid for the duration of the connection. Alternatively, you can use the model handle previously set. For the option to set the user name, you can set it to NULL.

    The following example uses the session variable. 

    mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

    The following example uses the model handle. 

    mysql> CALL sys.ML_MODEL_LOAD('classification_use_case', NULL);

  2. Make predictions for the test dataset by using the ML_PREDICT_TABLE routine. 

    mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

    Replace table_name, model_handle, and output_table_name with your own values. Add options as needed.

    The following example runs ML_PREDICT_TABLE on the testing dataset previously created. 

    mysql> CALL sys.ML_PREDICT_TABLE('classification_data.Loan_Testing', @model, 'classification_data.Loan_Testing_predictions',NULL);

    Where:

    • classification_data.Loan_Testing is the fully qualified name of the input table that contains the data to generate predictions for (database_name.table_name).

    • @model is the session variable for the model handle.

    • classification_data.Loan_Testing_predictions is the fully qualified name of the output table with predictions (database_name.table_name).

    • NULL sets no options for the routine.

  3. Query the Approved, Prediction, and ml_results columns from the output table. This allows you to compare the real value with the generated prediction. You can also review the probabilities for each prediction. If needed, you can also query all the columns from the table (SELECT * FROM classification_predictions) to review all the data at once. 

    mysql> SELECT Approved, Prediction, ml_results FROM Loan_Testing_predictions;
+----------+------------+------------------------------------------------------------------------------------------------------+
| Approved | Prediction | ml_results                                                                                           |
+----------+------------+------------------------------------------------------------------------------------------------------+
| Approved | Approved   | {"predictions": {"Approved": "Approved"}, "probabilities": {"Approved": 0.9838, "Rejected": 0.0162}} |
| Rejected | Rejected   | {"predictions": {"Approved": "Rejected"}, "probabilities": {"Approved": 0.1135, "Rejected": 0.8865}} |
| Approved | Approved   | {"predictions": {"Approved": "Approved"}, "probabilities": {"Approved": 0.986, "Rejected": 0.014}}   |
| Rejected | Rejected   | {"predictions": {"Approved": "Rejected"}, "probabilities": {"Approved": 0.0962, "Rejected": 0.9038}} |
| Approved | Rejected   | {"predictions": {"Approved": "Rejected"}, "probabilities": {"Approved": 0.0409, "Rejected": 0.9591}} |
| Rejected | Rejected   | {"predictions": {"Approved": "Rejected"}, "probabilities": {"Approved": 0.1082, "Rejected": 0.8918}} |
| Approved | Approved   | {"predictions": {"Approved": "Approved"}, "probabilities": {"Approved": 0.5535, "Rejected": 0.4465}} |
| Rejected | Rejected   | {"predictions": {"Approved": "Rejected"}, "probabilities": {"Approved": 0.1695, "Rejected": 0.8305}} |
| Approved | Approved   | {"predictions": {"Approved": "Approved"}, "probabilities": {"Approved": 0.9838, "Rejected": 0.0162}} |
| Rejected | Approved   | {"predictions": {"Approved": "Approved"}, "probabilities": {"Approved": 0.5542, "Rejected": 0.4458}} |
+----------+------------+------------------------------------------------------------------------------------------------------+
10 rows in set (0.0430 sec)

    The results show that two predictions do not match up with the real values.

To learn more about generating predictions for one or more rows of data, see Generate Predictions for a Row of Data.

What's Next
MySQL Documentation Library