Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb


3.8.6 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 your labeled training data for scoring.

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

ML_SCORE Syntax

CALL sys.ML_SCORE(table_name, target_column_name, model_handle, metric, score);

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.

  • model_handle: Specifies the model handle or a session variable containing the model handle.

  • metric: Specifies the name of the classification or regression type metric. Commonly used classification metrics are accuracy, balanced_accuracy, precision, and recall. Commonly used regression metrics are neg_mean_squared_error, neg_absolute_squared_error, and r2. For more information about scoring metrics, refer to scikit-learn.org.

    • classification metrics:

      • accuracy: Computes the fraction of labels a model predicts correctly

      • balanced_accuracy: Computes the balanced accuracy for imbalanced datasets

      • precision: Computes precision

      • recall: Computes the recall

      • f1: Computes the F1 score

      • f1_micro: Computes the F1 score (micro-averaged)

      • f1_macro: Computes the F1 score (macro-averaged)

      • f1_weighted: Computes the F1 score (weighted)

      • f1_samples: Computes the F1 score (by multilabel sample)

      • recall_micro: Computes the recall (micro-averaged)

      • recall_macro: Computes the recall (macro-averaged)

      • recall_weighted: Computes the recall (weighted)

      • recall_samples: Computes the recall (by multilabel sample)

      • precision_micro: Computes precision (micro-averaged)

      • precision_macro: Computes precision (macro-averaged)

      • precision_weighted: Computes precision (weighted)

      • precision_samples: Computes precision (by multilabel sample)

    • regression metrics:

      • neg_mean_squared_error: Computes mean squared error regression loss.

      • neg_mean_absolute_error: Computes mean absolute error regression loss.

      • r2: Computes the R2 (coefficient of determination) regression score function.

      • neg_mean_squared_log_error: Computes mean squared logarithmic error regression loss.

      • neg_median_absolute_error: Computes median absolute error regression loss.

  • 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 (e.g., @my_score).

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 |
+--------------------+