6.7.2.3 Generate Predictions for a Regression Model

After training the model, you can generate predictions.

To generate predictions, use the sample data from the house_price_testing dataset. Even though the table has labels for the price 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:

• Prepare Data for a Regression Model

• Train a Regression Model

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('regression_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.

   As of MySQL 9.4.1, you have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.

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

   mysql> CALL sys.ML_PREDICT_TABLE('regression_data.house_price_testing', @model, 'regression_data.house_price_predictions', NULL);

   Where:

   • regression_data.regression_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.

   • regression_data.regression_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 price, Prediction, and ml_results columns from the output table. This allows you to compare the real value with the generated prediction. If needed, you can also query all the columns from the table (SELECT * FROM regression_predictions) to review all the data at once.

   mysql> SELECT price, Prediction, ml_results FROM house_price_predictions;
   +--------+------------+--------------------------------------+
   | price  | Prediction | ml_results                           |
   +--------+------------+--------------------------------------+
   | 470000 |     490000 | {"predictions": {"price": 490000.0}} |
   | 630000 |     660000 | {"predictions": {"price": 660000.0}} |
   | 530000 |     552000 | {"predictions": {"price": 552000.0}} |
   | 780000 |     810000 | {"predictions": {"price": 810000.0}} |
   | 460000 |     478000 | {"predictions": {"price": 478000.0}} |
   | 510000 |     594000 | {"predictions": {"price": 594000.0}} |
   | 500000 |     518000 | {"predictions": {"price": 518000.0}} |
   | 600000 |     700000 | {"predictions": {"price": 700000.0}} |
   | 430000 |     426000 | {"predictions": {"price": 426000.0}} |
   | 760000 |     790000 | {"predictions": {"price": 790000.0}} |
   +--------+------------+--------------------------------------+
   10 rows in set (0.0403 sec)

   Review the predictions and compare with the real prices.

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

What's Next

• Learn now to Query Model Explanation and Generate Prediction Explanations for a Regression Model.