HeatWave Release Notes
ML_EXPLAIN_TABLE explains
predictions for an entire table of unlabeled data. It limits explanations to the 100
most relevant features.
Note
ML_EXPLAIN_TABLE is a very
memory-intensive process. Depending on your MySQL version,
we recommend the following:
Before MySQL 9.4.1, use the
batch_sizeoption to limit operations to batches of 10 to 100 rows by splitting large tables into smaller tables. For tables with over ten columns, we suggest abatch_sizevalue of10.As of MySQL 9.4.1, the
batch_sizeoption is deprecated. Limit the input table to a maximum of 100 rows. If the input table has more than ten columns, limit it to ten rows.
A call to ML_EXPLAIN_TABLE can
include columns that were not present during
ML_TRAIN. A table can include
extra columns, and still use the MySQL HeatWave AutoML model. This allows
side by side comparisons of target column labels, ground
truth, and explanations in the same table.
ML_EXPLAIN_TABLE ignores any
extra columns, and appends them to the results.
A loaded model and trained with the appropriate prediction
explainer is required to run
ML_EXPLAIN_TABLE. See
Generate
Prediction Explanations for a Table.
The output table includes a primary key:
If the input table has a primary key, the output table will have the same primary key.
-
If the input table does not have a primary key, the output table will have a new primary key column that auto increments.
As of MySQL 8.4.1, the name of the new primary key column is
_4aad19ca6e_pk_id. The input table must not have a column with the name_4aad19ca6e_pk_idthat is not a primary key.Before MySQL 8.4.1, the name of the new primary key column is
_id. The input table must not have a column with the name_idthat is not a primary key.
As of MySQL 9.4.1, you have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.
ML_EXPLAIN_TABLE does not
support recommendation, anomaly detection, and topic modeling
models. A call with one of these models produces an error.
ML_EXPLAIN_TABLE does not
support the following model types:
Forecasting
Recommendation
Anomaly detection
Anomaly detection for logs
Topic modeling
mysql> CALL sys.ML_EXPLAIN_TABLE(table_name, model_handle, output_table_name, [options]);
options: {
JSON_OBJECT("key","value"[,"key","value"] ...)
"key","value": {
['prediction_explainer', {'permutation_importance'|'shap'}|NULL]
['batch_size', 'N']
}
}Set the following required parameters.
table_name: Specifies the fully qualified name of the input table (database_name.table_name). The input table should contain the same feature columns as the table used to train the model. If the target column is included in the input table, it is not considered when generating prediction explanations.model_handle: Specifies the model handle or a session variable containing the model handle. See Work with Model Handles.output_table_name: Specifies the table where explanation data is stored. A fully qualified table name must be specified (database_name.table_name). As of MySQL 9.4.1, you have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.
Set the following options as needed.
-
prediction_explainer: The name of the prediction explainer that you have trained for this model usingML_EXPLAIN.permutation_importance: The default prediction explainer.shap: The SHAP prediction explainer, which produces feature importance values based on Shapley values.
batch_size: Deprecated as of MySQL 9.4.1. The size of each batch. You can set a value between 1 and 100. For tables with over ten columns, we recommend a value of10. If on MySQL 9.4.1 and later, manually limit the size of the input table to 100 rows. If the input table has over ten columns, limit it to ten rows.
-
The following example generates explanations for a table of data with the default Permutation Importance prediction explainer. The
ML_EXPLAIN_TABLEcall specifies the fully qualified name of the table to generate explanations for, the session variable containing the model handle, and the fully qualified output table name.mysql> CALL sys.ML_EXPLAIN_TABLE('census_data.census_train', @census_model, 'census_data.census_train_permutation', JSON_OBJECT('prediction_explainer', 'permutation_importance'));To view
ML_EXPLAIN_TABLEresults, query the output table. TheSELECTstatement retrieves explanation data from the output table. The table includes the primary key,_4aad19ca6e_pk_id, and theml_resultscolumn, which usesJSONformat:mysql> SELECT * FROM census_train_permutation LIMIT 3; +-------------------+-----+-----------+--------+------------+---------------+--------------------+-----------------+--------------+-------+--------+--------------+--------------+----------------+----------------+---------+------------+-----------------+---------------------------+----------------------------+-----------------------+----------------------------+--------------------------+------------------+-----------------+-----------------------+--------------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | _4aad19ca6e_pk_id | age | workclass | fnlwgt | education | education-num | marital-status | occupation | relationship | race | sex | capital-gain | capital-loss | hours-per-week | native-country | revenue | Prediction | age_attribution | education-num_attribution | marital-status_attribution | education_attribution | hours-per-week_attribution | relationship_attribution | race_attribution | sex_attribution | workclass_attribution | fnlwgt_attribution | capital-gain_attribution | Notes | ml_results | +-------------------+-----+-----------+--------+------------+---------------+--------------------+-----------------+--------------+-------+--------+--------------+--------------+----------------+----------------+---------+------------+-----------------+---------------------------+----------------------------+-----------------------+----------------------------+--------------------------+------------------+-----------------+-----------------------+--------------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 37 | Private | 99146 | Bachelors | 13 | Married-civ-spouse | Exec-managerial | Husband | White | Male | 0 | 1977 | 50 | United-States | >50K | <=50K | -0.1 | -0.08 | -0.05 | -0.05 | -0.03 | -0.03 | 0.02 | -0.02 | 0.01 | 0 | 0 | race (White) had the largest impact towards predicting =50K, whereas age (37) contributed the most against predicting <=50K | {"attributions": {"age": -0.1, "education-num": -0.08, "marital-status": -0.05, "education": -0.05, "hours-per-week": -0.03, "relationship": -0.03, "race": 0.02, "sex": -0.02, "workclass": 0.01, "fnlwgt": 0.0, "capital-gain": 0.0}, "predictions": {"revenue": "<=50K"}, "notes": "race (White) had the largest impact towards predicting <=50K, whereas age (37) contributed the most against predicting <=50K"} | | 2 | 34 | Private | 27409 | 9th | 5 | Married-civ-spouse | Craft-repair | Husband | White | Male | 0 | 0 | 50 | United-States | <=50K | <=50K | 0 | 0 | -0.04 | 0.06 | -0.03 | 0.02 | 0.02 | -0.02 | 0.01 | 0 | 0 | education (9th) had the largest impact towards predicting <=50K, whereas marital-status (Married-civ-spouse) contributed the most against predicting <=50K | {"attributions": {"age": 0.0, "education-num": 0.0, "marital-status": -0.04, "education": 0.06, "hours-per-week": -0.03, "relationship": 0.02, "race": 0.02, "sex": -0.02, "workclass": 0.01, "fnlwgt": 0.0, "capital-gain": 0.0}, "predictions": {"revenue": "<=50K"}, "notes": "education (9th) had the largest impact towards predicting <=50K, whereas marital-status (Married-civ-spouse) contributed the most against predicting <=50K"} | | 3 | 30 | Private | 299507 | Assoc-acdm | 12 | Separated | Other-service | Unmarried | White | Female | 0 | 0 | 40 | United-States | <=50K | <=50K | 0 | 0 | 0 | 0 | 0 | 0.03 | 0.01 | 0.02 | 0 | 0 | 0 | relationship (Unmarried) had the largest impact towards predicting <=50K | {"attributions": {"age": 0.0, "education-num": 0.0, "marital-status": 0.0, "education": 0.0, "hours-per-week": 0.0, "relationship": 0.03, "race": 0.01, "sex": 0.02, "workclass": 0.0, "fnlwgt": -0.0, "capital-gain": 0.0}, "predictions": {"revenue": "<=50K"}, "notes": "relationship (Unmarried) had the largest impact towards predicting <=50K"} | +-------------------+-----+-----------+--------+------------+---------------+--------------------+-----------------+--------------+-------+--------+--------------+--------------+----------------+----------------+---------+------------+-----------------+---------------------------+----------------------------+-----------------------+----------------------------+--------------------------+------------------+-----------------+-----------------------+--------------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+