table_name: The name of the table that contains the labeled training dataset. The table name must be valid and fully qualified, so it must include the database name, database_name.table_name. The table cannot exceed 10 GB, 100 million rows, or 1017 columns.

target_column_name: The name of the target column containing ground truth values.

MySQL HeatWave AutoML does not support a text target column.

If training an unsupervised Anomaly detection model (unlabeled data), set target_column_name to NULL.

Forecasting does not require target_column_name, and it can be set to NULL.

model_handle: A user-defined session variable that stores the machine learning model handle for the duration of the connection. User variables are written as @var_name. Any valid name for a user-defined variable is permitted. For example, @my_model.

If you set a value to the model_handle variable before calling ML_TRAIN, that model handle is used for the model. A model handle must be unique in the model catalog. We recommend this method.

If you don't set a value to the model_handle variable, MySQL HeatWave AutoML generates one. When ML_TRAIN finishes executing, retrieve the generated model handle by querying the session variable. See Model Handles to learn more.

task: Specifies the machine learning task.

model_list: The type of model to be trained. If more than one model is specified, the best model type is selected from the list. See Model Types.

This option cannot be used together with the exclude_model_list option.

exclude_model_list: Model types that should not be trained. Specified model types are excluded from consideration during model selection. See Model Types.

This option cannot be specified together with the model_list option.

optimization_metric: The scoring metric to optimize for when training a machine learning model. The metric must be compatible with the task type and the target data. See Section 10.2.15, “Optimization and Scoring Metrics”.

This is not supported for anomaly_detection tasks. Instead, metrics for anomaly detection can only be used with the ML_SCORE routine.

include_column_list: ML_TRAIN must include this list of columns.

For classification, regression, anomaly_detection and recommendation tasks, include_column_list ensures that ML_TRAIN will not drop these columns.

For forecasting tasks, include_column_list can only include exogenous_variables. If include_column_list is included in the ML_TRAIN options for a forecasting task with at least one exogenous_variables, this forces ML_TRAIN to only consider those models that support exogenous_variables.

All columns in include_column_list must be included in the training table.

exclude_column_list: Feature columns of the training dataset to exclude from consideration when training a model. Columns that are excluded using exclude_column_list do not also need to be excluded from the dataset used for predictions.

The exclude_column_list cannot contain any columns provided in endogenous_variables, exogenous_variables, and include_column_list.

notes: Add notes to the model_metadata for your own reference.

The following example sets the model handle before training, which is good practice. See Define Model Handle. The task is set to classification.

mysql> SET @census_model = 'census_manual';
mysql> CALL sys.ML_TRAIN('census_data.census_train', 'revenue', JSON_OBJECT('task', 'classification'), @census_model);

The following example sets all options to NULL, so ML_TRAIN runs the classification task option by default.

mysql> CALL sys.ML_TRAIN('census_data.census_train', 'revenue', NULL, @census_model);

The following example specifies the regression task type.

mysql> CALL sys.ML_TRAIN('nyc_taxi.nyc_taxi_train', 'tip_amount', JSON_OBJECT('task', 'regression'), @nyc_taxi);

Forecasting Task Types

Prediction Intervals

Train a Forecasting Model

datetime_index: The column name for a datetime column that acts as an index for the forecast variable. The column can be one of the supported datetime column types, DATETIME, TIMESTAMP, DATE, TIME, and YEAR, or an auto-incrementing index.

The forecast models SARIMAXForecaster, VARMAXForecaster, and DynFactorForecaster cannot back test, that is forecast into training data, when using exogenous_variables. Therefore, the predict table must not overlap the datetime_index with the training table. The start date in the predict table must be a date immediately following the last date in the training table when exogenous_variables are used. For example, the predict table has to start with year 2024 if the training table with YEAR data type datetime_index ends with year 2023.

The datetime_index for the predict table must not have missing dates after the last date in the training table. For example, the predict table has to start with year 2024 if the training table with YEAR data type datetime_index ends with year 2023. The predict table cannot start with year, for example, 2025 or 2030, because that would miss out 1 and 6 years, respectively.

When options do not include exogenous_variables , the predict table can overlap the datetime_index with the training table. This supports back testing.

The valid range of years for datetime_index dates must be between 1678 and 2261. It will cause an error if any part of the training table or predict table has dates outside this range. The last date in the training table plus the predict table length must still be inside the valid year range. For example, if the datetime_index in the training table has YEAR data type, and the last date is year 2023, the predict table length must be less than 238 rows: 2261 minus 2023 equals 238 rows.

endogenous_variables: The column or columns to be forecast.

Univariate forecasting models support a single numeric column, specified as a JSON_ARRAY. This column must also be specified as the target_column_name, because that field is required, but it is not used in that location.

Multivariate forecasting models support multiple numeric columns, specified as a JSON_ARRAY. One of these columns must also be specified as the target_column_name.

endogenous_variables cannot be text.

exogenous_variables: For forecasting tasks, the column or columns of independent, non-forecast, predictive variables, specified as a JSON_ARRAY. These optional variables are not forecast, but help to predict the future values of the forecast variables. These variables affect a model without being affected by it. For example, for sales forecasting these variables might be advertising expenditure, occurrence of promotional events, weather, or holidays.

ML_TRAIN will consider all supported models during the algorithm selection stage if options includes exogenous_variables, including models that do not support exogenous_variables.

For example, if options includes univariate endogenous_variables with exogenous_variables, then ML_TRAIN will consider NaiveForecaster, ThetaForecaster, ExpSmoothForecaster, ETSForecaster, STLwESForecaster, STLwARIMAForecaster, and SARIMAXForecaster. ML_TRAIN will ignore exogenous_variables if the model does not support them.

Similarly, if options includes multivariate endogenous_variables with exogenous_variables, then ML_TRAIN will consider VARMAXForecaster and DynFactorForecaster.

If options also includes include_column_list, this will force ML_TRAIN to only consider those models that support exogenous_variables.

include_column_list: Can only include exogenous_variables. If include_column_list contains at least one exogenous_variables, this will force ML_TRAIN to only consider those models that support exogenous_variables.

The following example specifies the forecasting task type, and the additional required parameters, datetime_index and endogenous_variables.

mysql> CALL sys.ML_TRAIN('air_data.air_train', 'passengers', JSON_OBJECT('task', 'forecasting', 'datetime_index', 'Period', 'endogenous_variables', JSON_ARRAY('passengers')), @model);

The following example specifies the OrbitForecaster forecasting model with exogenous variables.

mysql> CALL sys.ML_TRAIN('mlcorpus.opsd_germany_daily_train', NULL, JSON_OBJECT('task', 'forecasting', 'datetime_index', 'ddate', 'endogenous_variables', 
       JSON_ARRAY('consumption'), 'exogenous_variables', JSON_ARRAY('wind', 'solar', 'wind_solar'), 'model_list', JSON_ARRAY('OrbitForecaster')), @model);

The following example specifies the OrbitForecaster forecasting model without exogenous variables.

mysql> CALL sys.ML_TRAIN('air_data.air_train', 'passengers', JSON_OBJECT('task', 'forecasting', 'datetime_index', 'Period', 'endogenous_variables', 
       JSON_ARRAY('passengers'), 'model_list', JSON_ARRAY('OrbitForecaster')), @model);

Anomaly Detection Model Types

Anomaly Detection Learning Types

Anomaly Detection for Logs

Set the task parameter to anomaly_detection for running anomaly detection on table data, or log_anomaly_detection for running anomaly detection on log data (MySQL 9.2.2 and later).

If running an unsupervised model, the target_column_name parameter must be set to NULL.

If running a semi-supervised model:

If running anomaly detection on log data (MySQL 9.2.2 and later), the input table can only have the following columns:

contamination: Represents an estimate of the percentage of outliers in the training table.

model_list: You can select the Principal Component Analysis (PCA), Generalized Local Outlier Factor (GLOF), or Generalized kth Nearest Neighbors (GkNN) model. If no option is specified, the default model is GkNN. Selecting more than one model or an unsupported model produces an error.

supervised_submodel_options: Allows you to set optional override parameters for the supervised model component. The only model supported is DistanceWeightedKNNClassifier. The following parameters are supported:

ensemble_score: This option specifies the metric to use to score the ensemble of unsupervised and supervised components. It identifies the optimal weight between the two components based on the metric. The supported metrics are accuracy, precision, recall, and f1. The default metric is f1.

logad_options: A JSON_OBJECT that allows you to configure the following options.

exclude_model_list

optimization_metric

The following example specifies the anomaly_detection task type and excludes the target column for training. After training, query the model type to confirm the default GKNN model is selected.

mysql> CALL sys.ML_TRAIN('volcano_data.volcano_data_train', NULL, JSON_OBJECT('task', 'anomaly_detection', 'exclude_column_list', JSON_ARRAY('target')), @anomaly_model);
Query OK, 0 rows affected (10.1872 sec)
mysql> SELECT model_handle, model_type FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle=@model;
+---------------+------------+
| model_handle  | model_type |
+---------------+------------+
| anomaly_model | GKNN       |
+---------------+------------+
1 row in set (0.0428 sec)

The following example specifies the anomaly_detection task with a contamination option. After training, query the model catalog metadata to check the value of the contamination option.

mysql> CALL sys.ML_TRAIN('volcano_data.volcano_data_train', NULL, JSON_OBJECT('task', 'anomaly_detection', 'contamination', 0.013, 'exclude_column_list', JSON_ARRAY('target')), @model2);
Query OK, 0 rows affected (11.6307 sec)

mysql> SELECT JSON_EXTRACT(model_metadata, '$.contamination') 
          FROM ML_SCHEMA_user1.MODEL_CATALOG 
          WHERE model_handle = @model2;
+-------------------------------------------------+
| JSON_EXTRACT(model_metadata, '$.contamination') |
+-------------------------------------------------+
| 0.0130000002682209                              |
+-------------------------------------------------+
1 row in set (0.0717 sec)

The following example enables semi-supervised learning using all defaults. The target_column_name is set to target. The experimental option is set to semisupervised.

mysql> CALL sys.ML_TRAIN('mlcorpus.anomaly_train_with_partial_target', "target", CAST('{"task": "anomaly_detection", "experimental": {"semisupervised": {}}}' as JSON), @semisupervised_model);

The following example enables semi-supervised learning with additional options.

mysql> CALL sys.ML_TRAIN('mlcorpus.anomaly_train_with_partial_target', "target", CAST('{"task": "anomaly_detection", "experimental": {"semisupervised": 
       {"supervised_submodel_options": {"min_labels": 10, "n_neighbors": 3}, "ensemble_score": "recall"}}}' as JSON), @semisupervised_model_options);

Where:

The following example selects the PCA (Principal Component Analysis) anomaly detection model. After training, query the model handle and model type to confirm the selected model.

mysql> CALL sys.ML_TRAIN('volcano_data.volcano_data_train', NULL, JSON_OBJECT('task', 'anomaly_detection', 'exclude_column_list', JSON_ARRAY('target'), 'model_list', JSON_ARRAY('PCA')), @model);
Query OK, 0 rows affected (4.8730 sec)
mysql> SELECT model_handle, model_type FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle=@model;
+--------------+------------+
| model_handle | model_type |
+--------------+------------+
| anomaly_pca  | PCA        |
+--------------+------------+
1 row in set (0.0416 sec)

The following example runs the log_anomaly_detection task with available default values.

mysql> CALL sys.ML_TRAIN('mlcorpus.`log_anomaly_just_patterns`', NULL, JSON_OBJECT('task', 'log_anomaly_detection'), @logad_model);

The following example runs the log_anomaly_detection task with the PCA anomaly detection model.

mysql> CALL sys.ML_TRAIN('mlcorpus.`log_anomaly_just_patterns`', NULL, JSON_OBJECT('task', 'log_anomaly_detection', 'model_list', JSON_ARRAY('PCA')), @logad_model);

An ML_TRAIN example that excludes two primary key columns: primary_key_column1 and primary_key_column2. These columns must be excluded because they do not have one of the required items of data for training: the log data, the source of the log, or the label.

mysql>CALL sys.ML_TRAIN( 'mlcorpus.log_anomaly_two_primary', NULL, 
       JSON_OBJECT( 'task', 'log_anomaly_detection', 'logad_options', 
       JSON_OBJECT('window_size', 2, 'window_stride', 1), 'exclude_column_list', 
       JSON_ARRAY('primary_key_column1', 'primary_key_column2') ), @log_anomaly_us );

The following example runs the log_anomaly_detection task and masks log data with the additional_masking_regex option. In addition to the default parameters that are automatically masked, email addresses from Yahoo, Hotmail, and Gmail are also masked. The log_source_column option is also included, which specifies the column that identifies the respective source of the log line.

mysql> CALL sys.ML_TRAIN('mlcorpus.`log_anomaly_sourced`', NULL, 
       JSON_OBJECT('task', 'log_anomaly_detection', 'logad_options', 
       JSON_OBJECT('additional_masking_regex', 
       JSON_ARRAY('(\W|^)[\w.\-]{0,25}@(yahoo|hotmail|gmail)\.com(\W|$)'), 'log_source_column', 'source')), @log_anomaly_us);

The following example sets semi-supervised learning for training the log data for anomaly detection. The window size is also set to a value of 4, and the window stride is set to 1.

mysql> CALL sys.ML_TRAIN('mlcorpus.`log_anomaly_semi`', "label", 
       JSON_OBJECT('task', 'log_anomaly_detection', 'logad_options', 
       JSON_OBJECT('window_size', 4, 'window_stride', 1), "experimental", 
       JSON_OBJECT("semisupervised", JSON_OBJECT("supervised_submodel_options", 
       JSON_OBJECT("min_labels", 10)))), @log_anomaly_us);

users: Specifies the column name corresponding to the user ids. Values in this column must be in a STRING data type, otherwise an error will be generated during training.

This must be a valid column name, and it must be different from the items column name.

items: Specifies the column name corresponding to the item ids. Values in this column must be in a STRING data type, otherwise an error will be generated during training.

This must be a valid column name, and it must be different from the users column name.

feedback_threshold: The feedback threshold for a recommendation model that uses implicit feedback. It represents the threshold required to be considered positive feedback. For example, if numerical data records the number of times users interact with an item, you might set a threshold with a value of 3. This means users would need to interact with an item more than three times to be considered positive feedback.

item_metadata: Defines the table that has item descrption. It is a JSON object that can have the table_name option as a key, which specifies the table that has item descriptions. This table must only have two columns: one corresponding to the item_id, and the other with a TEXT data type (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) that has the description of the item.

The following example defines a model handle, trains a recommendation model with no specified model type, and then queries the automatically selected model_type for the trained model from the model catalog.

mysql> SET @rec_model = 'rec_model';
mysql> CALL sys.ML_TRAIN('movielens_data.movielens_train', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id'), @rec_model);
Query OK, 0 rows affected (14.4091 sec)

mysql> SELECT model_handle, model_type FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle='rec_model';
+--------------+------------+
| model_handle | model_type |
+--------------+------------+
| rec_model    | SVDpp      |
+--------------+------------+
1 row in set (0.0395 sec)

The following example specifies the SVD recommendation model type, and then queries the model_type for the trained model from the model catalog to confirm the selected model.

mysql> CALL sys.ML_TRAIN('movielens_data.movielens_train', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id', 'model_list', JSON_ARRAY('SVD')), @rec_model);
Query OK, 0 rows affected (9.4139 sec)

mysql> SELECT model_type FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle=@rec_model;
+------------+
| model_type |
+------------+
| SVD        |
+------------+
1 row in set (0.0485 sec)

The following example specifies three models for the model_list option. From those three recommendation models, the SVDpp model is automatically selected for training.

mysql> CALL sys.ML_TRAIN('movielens_data.movielens_train', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id', 'model_list', JSON_ARRAY('SVD', 'SVDpp', 'NMF')), @rec_model);

Query OK, 0 rows affected (13.8714 sec)

mysql> SELECT model_type FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle=@rec_model;
+------------+
| model_type |
+------------+
| SVDpp      |
+------------+
1 row in set (0.0403 sec)

The following example specifies the recommendation task with implicit feedback.

mysql> CALL sys.ML_TRAIN('mlcorpus.training_table', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id', 'feedback', 'implicit'), @model);
Query OK, 0 rows affected (2 min 13.6415 sec)

The following example trains a content-based recommendation model by specifying a table with item descriptions (mlcorpus_recsys.`citeulike_items_sample). The optimization metric hit_ratio_at_k is used. The model must use implicit feedback.

mysql> CALL sys.ML_TRAIN('amazon_data.`amazon_train`', 'rating', JSON_OBJECT('task', 'recommendation', 'model_list', 
       JSON_ARRAY('CTR'), 'users', 'user_id', 'items', 'item_id','feedback', 'implicit', 'optimization_metric', 'hit_ratio_at_k', 'item_metadata', JSON_OBJECT('table_name', 'amazon_data.`amazon_item_descriptions`')), @rec_model);
              

document_column: Specify the column name that contains the text to train.

model_list

optimization_metric

exclude_model_list

exclude_column_list

include_column_list

The model_list option permits specifying the type of model to be trained. If more than one type of model specified, the best model type is selected from the list. For a list of supported model types, see Model Types. This option cannot be used together with the exclude_model_list option.

The following example trains either an XGBClassifier or LGBMClassifier model.

mysql> CALL sys.ML_TRAIN('ml_data.iris_train', 'class', JSON_OBJECT('task','classification', 'model_list', JSON_ARRAY('XGBClassifier', 'LGBMClassifier')), @iris_model);

The exclude_model_list option specifies types of models that should not be trained. Specified model types are excluded from consideration. For a list of model types you can specify, see Model Types. This option cannot be used together with the model_list option.

The following example excludes the LogisticRegression and GaussianNB models.

mysql> CALL sys.ML_TRAIN('ml_data.iris_train', 'class', JSON_OBJECT('task','classification', 'exclude_model_list', JSON_ARRAY('LogisticRegression', 'GaussianNB')), @iris_model);

The optimization_metric option specifies a scoring metric to optimize for. See: Optimization and Scoring Metrics.

The following example optimizes for the neg_log_loss metric.

mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task','classification', 'optimization_metric', 'neg_log_loss'), @census_model);

The exclude_column_list option specifies feature columns to exclude from consideration when training a model.

The following example excludes the 'age' column from consideration when training a model for the census dataset.

mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task','classification', 'exclude_column_list', JSON_ARRAY('age')), @census_model); 

The include_column_list option specifies feature columns that must be considered for training and should not be dropped.

The following example specifies to consider the 'job' column when training a model for the census dataset.

mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task','classification', 'include_column_list', JSON_ARRAY('job')), @census_model); 

The following example adds notes to the model_metadata. After training, query the model metadata to confirm the notes were successfully added. Optionally, use JSON_PRETTY to view the output in an easily readable format.

mysql> CALL sys.ML_TRAIN('bank_test.bank_train', 'y', JSON_OBJECT('task', 'classification', 'notes', 'bank marketing model'), @model);
Query OK, 0 rows affected (1 min 34.7958 sec)

mysql> SELECT JSON_PRETTY(model_metadata) FROM ML_SCHEMA_admin.MODEL_CATALOG WHERE model_handle=@model;
+----------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(model_metadata)                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------+
| {
  "task": "classification",
  "notes": "bank marketing model",
  "chunks": 1,
  "format": "HWMLv2.0",
  "n_rows": 300,
  "status": "Ready",
  "options": {
    "task": "classification",
    "notes": "bank marketing model",
    "model_explainer": "permutation_importance",
    "prediction_explainer": "permutation_importance"
  },
  "n_columns": 16,
  "column_names": [
    "age",
    "job",
    "marital",
    "education",
    "default",
    "balance",
    "housing",
    "loan",
    "contact",
    "day",
    "month",
    "duration",
    "campaign",
    "pdays",
    "previous",
    "poutcome"
  ],
  "contamination": null,
  "model_quality": "high",
  "training_time": 70.57345581054688,
  "algorithm_name": "XGBClassifier",
  "training_score": -0.2614343762397766,
  "build_timestamp": 1746109286,
  "n_selected_rows": 240,
  "training_params": {
    "recommend": "ratings",
    "force_use_X": false,
    "recommend_k": 3,
    "remove_seen": true,
    "ranking_topk": 10,
    "lsa_components": 100,
    "ranking_threshold": 1,
    "feedback_threshold": 1
  },
  "train_table_name": "bank_test.bank_train",
  "model_explanation": {
    "permutation_importance": {
      "age": 0.0,
      "day": 0.0,
      "job": 0.0,
      "loan": 0.0,
      "month": 0.0,
      "pdays": 0.0,
      "balance": 0.0,
      "contact": 0.0175,
      "default": 0.0,
      "housing": 0.0,
      "marital": 0.0,
      "campaign": 0.0,
      "duration": 0.1524,
      "poutcome": 0.0278,
      "previous": 0.0,
      "education": 0.0
    }
  },
  "n_selected_columns": 3,
  "target_column_name": "y",
  "optimization_metric": "neg_log_loss",
  "selected_column_names": [
    "contact",
    "duration",
    "poutcome"
  ],
  "training_drift_metric": {
    "mean": 0.2961,
    "variance": 0.3273
  }
} |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0403 sec)

Train a Model

The Model Catalog

Track Progress for MySQL HeatWave AutoML Routines