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 ML_PREDICT_TABLE parameter descriptions, see Section 3.13.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.

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.12.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.