MySQL HeatWave User Guide

10.2.2 ML_EXPLAIN

Running the ML_EXPLAIN routine on a model and dataset trains a prediction explainer and model explainer, and adds a model explanation to the model catalog. See Generate Model Explanations and Generate Prediction Explanations to learn more.

MySQL 9.0.0 introduces support for large models that changes how MySQL HeatWave AutoML stores models. See The Model Object Catalog Table. ML_EXPLAIN upgrades older models.

ML_EXPLAIN does not support the following model types:

ML_EXPLAIN Syntax

mysql> CALL sys.ML_EXPLAIN ('table_name', 'target_column_name',
      model_handle, [options]);

options: {
 JSON_OBJECT("key","value"[,"key","value"] ...)
      "key","value": {
      ['model_explainer', {'permutation_importance'|'partial_dependence'|'shap'|'fast_shap'}| NULL]
      ['prediction_explainer', {'permutation_importance'|'shap'}]
      ['columns_to_explain', JSON_ARRAY('column'[,'column'] ...)]
      ['target_value', 'target_class']
      }
}

When the ML_TRAIN routine runs, ML_EXPLAIN also runs with the Permutation Importance model explainer and prediction explainer. To run ML_EXPLAIN_ROW and ML_EXPLAIN_TABLE with a different explainer, you must first run ML_EXPLAIN with the same explainer. See Generate Model Explanations and Generate Prediction Explanations to learn more.

Required ML_EXPLAIN Parameters

Set the following required parameters:

  • table_name: You must define the table that you previously trained. The table name must be valid and fully qualified, so it must include the database name (database_name.table_name).

  • target_column_name: Define the name of the target column in the training dataset that contains ground truth values.

  • model_handle: Enter the model handle for the trained model. The model explanation is stored in this model metadata. The model must be loaded first. For example: 

    mysql> CALL sys.ML_MODEL_LOAD('ml_data.iris_train_user1_1636729526', NULL);

    See Load a Model and Work with Model Handles to learn more.

    If you run ML_EXPLAIN again with the same model handle and model explainer, the model explanation field is overwritten with the new result.

ML_EXPLAIN Options

Optional parameters are specified as key-value pairs in JSON format. If an option is not specified, the default setting is used. If you specify NULL in place of the JSON argument, the default Permutation Importance model explainer is trained, and no prediction explainer is trained.

Set the following options as needed:

  • model_explainer: Specifies one of the following model explainers:

    • permutation_importance: The default model explainer.

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

    • fast_shap: The Fast SHAP model explainer, which is a subsampling version of the SHAP model explainer. It usually has a faster runtime.

    • partial_dependence: Explains how changing the values in one or more columns will change the value predicted by the model. The following additional arguments are required:

      • columns_to_explain: A JSON array of one or more column names in the table specified by table_name. The model explainer explains how changing the value in this column or columns affects the model.

      • target_value: A valid value that the target column containing ground truth values, as specified by target_column_name, can take.

  • prediction_explainer: Specifies one of the following prediction explainers:

    • permutation_importance: The default prediction explainer.

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

Syntax Examples

Before running these examples, you must train and load the model first. See Train a Model and Load a Model.

  • The following example sets NULL for the options, which trains the default Permutation Importance model explainer and no prediction explainer. 

    mysql> CALL sys.ML_EXPLAIN('bank_marketing_test.bank_train', 'y', @bank_test, NULL);

  • The following example trains the Fast SHAP model explainer and SHAP prediction explainer. 

    mysql> CALL sys.ML_EXPLAIN('bank_marketing_test.bank_train', 'y', @bank_test, JSON_OBJECT('model_explainer', 'fast_shap', 'prediction_explainer', 'shap'));

  • The following example trains the Partial Dependence model explainer (which requires extra options) and the SHAP prediction explainer. In this example, sepal width is the column to explain and the target value to include in Iris_setosa. 

    mysql> CALL sys.ML_EXPLAIN('ml_data.iris_train', 'class', @iris_model, JSON_OBJECT('columns_to_explain', 
       JSON_ARRAY('sepal width'), 'target_value', 'Iris-setosa', 'model_explainer', 'partial_dependence', 'prediction_explainer', 'shap'));

  • You can query the model explanation from the model catalog. The JSON_PRETTY parameter displays the output in an easily readable format. See View Model Explanations. 

    mysql> SELECT JSON_PRETTY(model_explanation) FROM ML_SCHEMA_user1.MODEL_CATALOG WHERE model_handle=@census_model;
+---------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(model_explanation)                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------+
| {
  "permutation_importance": {
    "age": 0.0292,
    "sex": 0.0023,
    "race": 0.0019,
    "fnlwgt": 0.0038,
    "education": 0.0008,
    "workclass": 0.0068,
    "occupation": 0.0223,
    "capital-gain": 0.0479,
    "capital-loss": 0.0117,
    "relationship": 0.0234,
    "education-num": 0.0352,
    "hours-per-week": 0.0148,
    "marital-status": 0.024,
    "native-country": 0.0
  }
} |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0427 sec)

See Also

MySQL Documentation Library