3.15.6 ML_EXPLAIN_ROW

The ML_EXPLAIN_ROW routine generates explanations for one or more rows of unlabeled data. ML_EXPLAIN_ROW is invoked using a SELECT statement.

ML_EXPLAIN_ROW limits explanations to the 100 most relevant features.

A loaded model, trained with a prediction explainer, is required to run ML_EXPLAIN_ROW. See Section 3.13.3, “Loading Models” and Section 3.15.2, “ML_EXPLAIN”.

MySQL 8.0.32 introduces anomaly detection. ML_EXPLAIN_ROW does not support anomaly detection, and a call with an anomaly detection model will produce an error.

MySQL 8.0.32 allows a call to ML_EXPLAIN_ROW to include columns that were not present during ML_TRAIN. A table can include extra columns, and still use the HeatWave AutoML model. This allows side by side comparisons of target column labels, ground truth, and explanations in the same table. ML_EXPLAIN_ROW ignores any extra columns, and appends them to the results.

MySQL 8.0.33 introduces recommendation models. ML_EXPLAIN_ROW does not support recommendation models, and a call with a recommendation model will produce an error.

ML_EXPLAIN_ROW Syntax

mysql> SELECT sys.ML_EXPLAIN_ROW(input_data, model_handle, [options]);
 
options: {
    JSON_OBJECT('key','value'[,'key','value'] ...)
        'key','value':
        |'prediction_explainer', {'permutation_importance'|'shap'}|NULL
}

ML_EXPLAIN_ROW parameters:

• input_data: Specifies the data to generate explanations for. Data must be specified in JSON key-value format, where the key is a column name. The column names must match the feature column names in the table used to train the model. A single row of data can be specified as follows:

  mysql> SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT("column_name", value, "column_name", value, ...)', 
            model_handle, options);

  You can run ML_EXPLAIN_ROW on multiple rows of data by specifying the columns in JSON key-value format and selecting from an input table:

  mysql> SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT("output_col_name", schema.`input_col_name`, 
            output_col_name", schema.`input_col_name`, ...), 
            model_handle, options) 
            FROM input_table_name 
            LIMIT N;

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

• options: A set of options in JSON format. The available options are:

  • prediction_explainer: The name of the prediction explainer that you have trained for this model using ML_EXPLAIN. Valid values are:

    • permutation_importance: The default prediction explainer.

    • shap: The SHAP prediction explainer, which produces global feature importance values based on Shapley values.

Syntax Examples

• Run ML_EXPLAIN_ROW on a single row of data with the default Permutation Importance prediction explainer. The results include the ml_results field, which uses JSON format:

  mysql> SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT("sepal length", 7.3, "sepal width", 2.9, 
            "petal length", 6.3, "petal width", 1.8), @iris_model, 
            JSON_OBJECT('prediction_explainer', 'permutation_importance'));
  +-------------------------------------------------------------------------------------------------------------------+
  | sys.ML_EXPLAIN_ROW(JSON_OBJECT("sepal length", 7.3, "sepal width", 2.9, "petal length", 6.3, "petal width", 1.8), |
  | @iris_model, JSON_OBJECT('prediction_explainer', 'permutation_importance'))                                       |
  +-------------------------------------------------------------------------------------------------------------------+
  | {"Notes": "petal width (1.8) had the largest impact towards predicting Iris-virginica",                           |
  | "Prediction": "Iris-virginica", "ml_results": "{'attributions': {'petal length': 0.57, 'petal width': 0.73},      |
  | 'predictions': {'class': 'Iris-virginica'}, 'notes': 'petal width (1.8) had the largest impact towards            |
  | predicting Iris-virginica'}", "petal width": 1.8, "sepal width": 2.9, "petal length": 6.3, "sepal length": 7.3,   |
  | "petal width_attribution": 0.73, "petal length_attribution": 0.57}                                                |
  +-------------------------------------------------------------------------------------------------------------------+
  1 row in set (5.92 sec)

  Before MySQL 8.0.32, the results do not include the ml_results field:

  +------------------------------------------------------------------------------+
  | sys.ML_EXPLAIN_ROW(JSON_OBJECT("sepal length", 7.3, "sepal width", 2.9,      |
  |         "petal length", 6.3, "petal width", 1.8), @iris_model,               |
  |         JSON_OBJECT('prediction_explainer', 'permutation_importance'))       |
  +------------------------------------------------------------------------------+
  | {"Prediction": "Iris-virginica", "petal width": 1.8, "sepal width": 2.9,     |
  | "petal length": 6.3, "sepal length": 7.3, "petal width_attribution": 0.73,   |
  | "petal length_attribution": 0.57}                                            |
  +------------------------------------------------------------------------------+

• Run ML_EXPLAIN_ROW with the SHAP prediction explainer:

  mysql> SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT('sepal length',`iris_test`.`sepal length`,
            'sepal width',`iris_test`.`sepal width`,'petal length',`iris_test`.`petal length`,
            'petal width',`iris_test`.`petal width` ), @iris_model, 
            JSON_OBJECT('prediction_explainer', 'shap')) 
            FROM `iris_test` 
            LIMIT 4;