MySQL HeatWave User Guide  /  ...  /  ML_EXPLAIN_ROW

3.10.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 is required to run ML_EXPLAIN_ROW. See Section 3.9.3, “Loading Models”.

ML_EXPLAIN_ROW Syntax

SELECT sys.ML_EXPLAIN_ROW(input_data, model_handle, options);

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:

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

    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:

    SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT("output_col_name", schema.`input_col_name`, 
      output_col_name", schema.`input_col_name`, ...), model_handle) FROM input_table_name LIMIT N;
  • model_handle: Specifies the model handle or a session variable containing the model handle.

  • options: Optional parameters specified as key-value pairs in JSON_OBJECT() format. If an option is not specified, the default setting is used. 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:

    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, NULL);
  • Run ML_EXPLAIN_ROW on five rows of data selected from an input table with the Partial Dependence prediction explainer:

    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', 'partial_dependence')) 
    FROM ml_data.iris_test LIMIT 5;
  • Run ML_EXPLAIN_ROW with the SHAP prediction explainer:

    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;