3.8.2 Table Explanations

ML_EXPLAIN_TABLE explains predictions for an entire table of unlabeled data and saves results to an output table. Explanations are performed in parallel. For parameter and option descriptions, see Section 3.15.7, “ML_EXPLAIN_TABLE”.

ML_EXPLAIN_TABLE is a compute intensive process. Limiting operations to batches of 10 to 100 rows by splitting large tables into smaller tables is recommended. MySQL 8.2.0 adds batch processing with the batch_size option. See: Section 3.14, “Progress tracking”.

The following example creates a table with 10 rows of data selected from the census_test dataset and generates explanations for that table.

Before running ML_EXPLAIN_TABLE, ensure that the model you want to use is loaded; for example:

mysql> CALL sys.ML_MODEL_LOAD(@census_model, NULL);

For more information about loading models, see Section 3.13.3, “Loading Models”.

The following example creates a table with 10 rows of unlabeled test data and generates explanations for that table:

mysql> CREATE TABLE heatwaveml_bench.census_test_subset AS SELECT * 
          FROM heatwaveml_bench.census_test 
          LIMIT 10;

mysql> CALL sys.ML_EXPLAIN_TABLE('heatwaveml_bench.census_test_subset', 
          @census_model, 'heatwaveml_bench.census_explanations', 
          JSON_OBJECT('prediction_explainer', 'shap'));

where:

• heatwaveml_bench.census_test_subset is the fully qualified name of the input table (schema_name.table_name). The table must have the same feature column names as the training dataset but no target column.

• @census_model is the session variable that contains the model handle.

• heatwaveml_bench.census_explanations is the table where explanation data is stored. The table is created if it does not exist. A fully qualified table name must be specified (schema_name.table_name). If the table already exists, an error is returned.

• prediction_explainer provides the name of the prediction explainer that you have trained for this model, either the Permutation Importance prediction explainer or the SHAP prediction explainer. You train this using the ML_EXPLAIN routine (see Section 3.6, “Training Explainers”).

To view ML_EXPLAIN_TABLE results, query the output table; for example:

mysql> SELECT * FROM heatwaveml_bench.census_explanations;

The ML_EXPLAIN_TABLE output table includes the features used to make the explanations, the explanations, and feature_attribution columns that provide a weighted numerical value that indicates feature importance.

As of MySQL 8.0.30, ML_EXPLAIN_TABLE output also includes a Notes field that identifies features with the greatest impact on predictions. ML_EXPLAIN_TABLE reports a warning if the model is low quality.