3.9.1 Training a Forecasting Model

Run the ML_TRAIN routine to create a forecasting model, and use the following JSON options:

As of MySQL 8.1.0 ML_TRAIN does not require target_column_name for forecasting, and it can be set to NULL.

• task: forecasting: Specifies the machine learning task.

• datetime_index: 'column' 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.

• endogenous_variables: JSON_ARRAY('column'[,'column'] ...) The column or columns to be forecast. One of these columns must also be specified as the target_column_name.

• exogenous_variables: JSON_ARRAY('column'[,'column'] ...) The column or columns of independent, non-forecast, predictive variables. For example, for sales forecasting these variables might be advertising expenditure, occurrence of promotional events, weather, or holidays.

• include_column_list: JSON_ARRAY('column'[,'column'] ...) include_column_list can include exogenous_variables.

Before MySQL 8.0.32, use the following JSON options:

• task: forecasting: Specifies the machine learning task.

• datetime_index: 'column' 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.

• endogenous_variables: JSON_ARRAY('column') The column to be forecast. This column must also be specified as the target_column_name.

See Section 3.5, “Training a Model”, and for full details of all the options, see ML_TRAIN.

Syntax Examples

• An ML_TRAIN example that specifies the forecasting task type and the additional required parameters datetime_index and endogenous_variables:

  mysql> CALL sys.ML_TRAIN('ml_data.opsd_germany_daily_train', 'consumption', 
            JSON_OBJECT('task', 'forecasting', 'datetime_index', 'ddate', 
            'endogenous_variables', JSON_ARRAY('consumption')), 
            @forecast_model);