MySQL HeatWave User Guide
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.
Complete the following tasks:
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);
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('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.
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.