3.7.2 Table Predictions

ML_PREDICT_TABLE generates predictions for an entire table of unlabeled data and saves the results to an output table. Predictions are performed in parallel. For parameter and option descriptions, see Section 3.15.5, “ML_PREDICT_TABLE”.

ML_PREDICT_TABLE is a compute intensive process. Limiting operations to batches of 10 to 100 rows by splitting large tables into smaller tables is recommended. MySQL 8.2.0 adds batch processing with the batch_size option. See: Section 3.14, “Progress tracking”.

Before running ML_PREDICT_TABLE, ensure that the model you want to use is loaded; for example:

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

For more information about loading models, see Section 3.13.3, “Loading Models”.

The following example creates a table with 10 rows of unlabeled test data and generates predictions for that table:

mysql> CREATE TABLE heatwaveml_bench.census_test_subset AS SELECT * 
          FROM heatwaveml_bench.census_test 
          LIMIT 10;

mysql> CALL sys.ML_PREDICT_TABLE('heatwaveml_bench.census_test_subset', 
          @census_model, 'heatwaveml_bench.census_predictions');

where:

• heatwaveml_bench.census_test_subset is the fully qualified name of the test dataset table (schema_name.table_name). The table must have the same feature column names as the training dataset but no target column.

• @census_model is the session variable that contains the model handle.

• heatwaveml_bench.census_predictions is the output table where predictions are stored. The table is created if it does not exist. A fully qualified table name must be specified (schema_name.table_name). If the table already exists, an error is returned.

To view ML_PREDICT_TABLE results, query the output table; for example:

mysql> SELECT * FROM heatwaveml_bench.census_predictions;

ML_PREDICT_TABLE populates the output table with predictions and the features used to make each prediction.