The ML.FORECAST function

This document describes theML.FORECAST function, which you can use toforecast a time series based on a trainedARIMA_PLUS orARIMA_PLUS_XREGmodel.

If you don't want to manage your own times series forecasting model, you canuse theAI.FORECAST functionwith BigQuery ML's built-inTimesFM time series model to perform forecasting.

Syntax

#ARIMA_PLUS models:ML.FORECAST(  MODEL `PROJECT_ID.DATASET.MODEL_NAME`,    STRUCT(      [,HORIZON AS horizon]      [,CONFIDENCE_LEVEL AS confidence_level]))#ARIMA_PLUS_XREG model:ML.FORECAST(  MODEL `PROJECT_ID.DATASET.MODEL_NAME`,    [{ TABLE `PROJECT_ID.DATASET.TABLE` | (QUERY_STATEMENT) } ,]    STRUCT(HORIZON AS horizon,CONFIDENCE_LEVEL AS confidence_level))

Arguments

ML.FORECAST takes the following arguments:

• PROJECT_ID: the project that contains theresource.
• DATASET: the dataset that contains theresource.
• MODEL: The name of the model.

• HORIZON: anINT64 value that specifies the number oftime points to forecast. The default value is3, and the maximum value isthe valueof theHORIZON option specified in theCREATE MODELstatement for time-series models, or1000 if that option isn't specified.When forecasting multiple time series at the same time, this parameterapplies to each time series.

  Note: Forecasting takes place when theCREATE MODEL statementruns.ML.FORECAST just retrieves the forecasting values and computes theprediction intervals. Therefore, this argument exists mainly for filteringresults when forecasting multiple time series. To save query time, youshould specify a value for theHORIZON option in theCREATE MODELstatement.

• CONFIDENCE_LEVEL: aFLOAT64 value that specifiespercentage of the future values that fall in the prediction interval. Thedefault value is0.95. The valid input range is[0, 1).

• TABLE: The name of the input table that contains thefeatures.

  IfTABLE is specified, the input column names in the table must match thecolumn names in the model, and their types should be compatible according toBigQueryimplicit coercion rules.

  If there are unused columns from the table, they are ignored.

• QUERY_STATEMENT: The GoogleSQL query that isused to generate the features. See theGoogleSQL query syntaxpage for the supported SQL syntax of theQUERY_STATEMENT clause.

  IfQUERY_STATEMENT is specified, the input column names from the querymust match the column names in the model, and their types should becompatible according to BigQueryimplicit coercion rules.

  If there are unused columns from the query, they are ignored.

Output

ML.FORECAST returns the following columns:

• time_series_id_col ortime_series_id_cols: a value that containsthe identifiers of a time series.time_series_id_col can be anINT64 orSTRING value.time_series_id_cols can be anARRAY<INT64> orARRAY<STRING> value. Only present when forecasting multiple time series atonce. The column names and types are inherited from theTIME_SERIES_ID_COL option as specified in theCREATE MODEL statement.
• forecast_timestamp: aTIMESTAMP value that contains the timestamps of atime series.
• forecast_value: aFLOAT64 value that contains the average of theprediction_interval_lower_bound andprediction_interval_upper_bound values.
• standard_error: aFLOAT64 value that contains the amount of variabilityin the estimated results.
• confidence_level: aFLOAT64 value that contains theconfidence_levelvalue you specified in the function input, or0.95 if you didn'tspecify aconfidence_level value. It is the same across all rows.
• prediction_interval_lower_bound: aFLOAT64 value that contains the lowerbound of the prediction interval for each forecasted point.
• prediction_interval_upper_bound: aFLOAT64 value that contains the upperbound of the prediction interval for each forecasted point.
• confidence_interval_lower_bound: aFLOAT64 value that contains thelower bound of the confidence interval for each forecasted point.
• confidence_interval_upper_bound: aFLOAT64 value that contains the upperbound of the confidence interval for each forecasted point.

The output ofML.FORECAST has the following properties:

• For each time series, the output rows are sorted in the chronological order offorecast_timestamp.
• forecast_timestamp always has a type ofTIMESTAMP, regardless of the typeof the column specified in theTIME_SERIES_TIMESTAMP_COL option of theCREATE MODELstatement.

ARIMA_PLUS example

The following example forecasts 30 time points with aconfidence level of0.8:

SELECT*FROMML.FORECAST(MODEL`mydataset.mymodel`,STRUCT(30AShorizon,0.8ASconfidence_level))

ARIMA_PLUS_XREG example

The following example forecasts 30 time points with aconfidence level of0.8 with future features:

SELECT*FROMML.FORECAST(MODEL`mydataset.mymodel`,STRUCT(30AShorizon,0.8ASconfidence_level),(SELECT*FROM`mydataset.mytable`))

Limitation

Applying any additional computation on top ofML.FORECAST's resultcolumns might lead to an out of memory error if the model size is too large. If this happens, you might see errors likeResources exceeded during query execution: The query could not be executed in the allotted memory.Examples of operations that might cause this issue are calculating minimum or maximum values, or adding to or subtractingfrom a particular column. If you are trying to filter on the forecasted value,we recommend that you use theforecast with limit option instead, because the algorithm it uses is less likely to cause an issue. If you keep getting out of memory errors, you can try working around this issue bycreating a new table for theML.FORECAST result, and then applying other computations in a different query that uses data from the new table.

What's next

• For information about model inference, seeModel inference overview.
• For more information about supported SQL statements and functions for timeseries forecasting models, seeEnd-to-end user journeys for time series forecasting models.