MySQL :: MySQL HeatWave User Guide

MySQL HeatWave User Guide / ... / ML_SCORE

3.13.8 ML_SCORE

ML_SCORE scores a model by generating predictions using the feature columns in a labeled dataset as input and comparing the predictions to ground truth values in the target column of the labeled dataset. The dataset used with ML_SCORE should have the same feature columns as the dataset used to train the model but the data should be different; for example, you might reserve 20 to 30 percent of the labeled training data for scoring.

ML_SCORE returns a computed metric indicating the quality of the model.

ML_SCORE Syntax

As of MySQL 8.1.0, forecasting does not require target_column_name, and it can be set to NULL.

MySQL 8.0.32 added an options parameter in JSON format that supports the anomaly_detection task. For all other tasks, set this parameter to NULL.

mysql> CALL sys.ML_SCORE(table_name, target_column_name, model_handle, metric, score, [options]);

options: {
    JSON_OBJECT('key','value'[,'key','value'] ...)
        'key','value':
        |'threshold', 'N'
        |'topk', 'N'
}

ML_SCORE parameters:

table_name: Specifies the fully qualified name of the table used to compute model quality (schema_name.table_name). The table must contain the same columns as the training dataset.
target_column_name: Specifies the name of the target column containing ground truth values.

As of MySQL 8.1.0 forecasting does not require target_column_name, and it can be set to NULL.
model_handle: Specifies the model handle or a session variable containing the model handle.
metric: Specifies the name of the metric. See Section 3.13.13, “Optimization and Scoring Metrics”.
score: Specifies the user-defined variable name for the computed score. The ML_SCORE routine populates the variable. User variables are written as @var_name. The examples in this guide use @score as the variable name. Any valid name for a user-defined variable is permitted, for example @my_score.
options: A set of options in JSON format. As of MySQL 8.0.32, this parameter only supports the anomaly detection task. For all other tasks, set this parameter to NULL. Before MySQL 8.0.32, ignore this parameter.
- threshold: The optional threshold for use with the anomaly_detection task to convert anomaly scores to 1: an anomaly or 0: normal. 0 < threshold < 1. The default value is (1 - contamination)-th percentile of all the anomaly scores.
- topk: The results include the topk rows with the highest anomaly scores. It is an integer between 1 and the table length. If topk is not set, ML_PREDICT_TABLE uses threshold.
  
  Do not set both threshold and topk. Use threshold or topk, or set options to NULL.

Syntax Example

The following example runs ML_SCORE on the ml_data.iris_train table to determine model quality:

mysql> CALL sys.ML_SCORE('ml_data.iris_validate', 'class', @iris_model, 
          'balanced_accuracy', @score);

mysql> SELECT @score;
+--------------------+
| @score             |
+--------------------+
| 0.9583333134651184 |
+--------------------+