The AI.FORECAST function

This document describes theAI.FORECAST function, which lets youforecast a time series by using BigQuery ML's built-inTimesFM model.

Using theAI.FORECAST function with the built-in TimesFM model lets youperform forecasting without having to create and train your own model, so youcan avoid the need for model management.

Syntax

SELECT  *FROM  AI.FORECAST(    { TABLETABLE | (QUERY_STATEMENT) },    data_col => 'DATA_COL',    timestamp_col => 'TIMESTAMP_COL'    [, model => 'MODEL']    [, id_cols =>ID_COLS]    [, horizon =>HORIZON]    [, confidence_level =>CONFIDENCE_LEVEL]    [, output_historical_time_series =>OUTPUT_HISTORICAL_TIME_SERIES]    [, context_window =>CONTEXT_WINDOW]  )

Arguments

AI.FORECAST takes the following arguments:

• TABLE: the name of the table that contains thedata that you want to forecast. For example,`mydataset.mytable`.

  If the table is in a different project, then you must prepend theproject ID to the table name in the following format, including backticks:

  `[PROJECT_ID].[DATASET].[TABLE]`

  For example,`myproject.mydataset.mytable`.

  To prevent query errors, we recommend providing the fully qualified tablename, including backticks. This is especially important if the project namecontains characters other than letters, numbers, and underscores.

• QUERY_STATEMENT: the GoogleSQL query thatgenerates the data that you want to forecast. See theGoogleSQL query syntaxpage for the supported SQL syntax of theQUERY_STATEMENT clause.

• DATA_COL: aSTRING value that specifies the name ofthe data column. The data column contains the data to forecast. The datacolumn must use one of the following data types:

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • FLOAT64

• TIMESTAMP_COL: aSTRING value that specified thename of the timestamp column. The timestampcolumn must use one of the following data types:

  • TIMESTAMP
  • DATE
  • DATETIME

• MODEL: aSTRING value that specifies the name of themodel to use. Supported models includeTimesFM 2.0 andTimesFM 2.5. Thedefault value isTimesFM 2.0.

• ID_COLS: anARRAY<STRING> value that specifies thenames of one or more ID columns. Each unique combination of IDs identifies aunique time series to forecast. Specify one or more values for this argumentin order to forecast multiple time series using a single query. The columnsthat you specify must use one of the following data types:

  • STRING
  • INT64
  • ARRAY<STRING>
  • ARRAY<INT64>

• HORIZON: anINT64 value that specifies the number oftime series data points to forecast. The default value is10. The validinput range is[1, 10,000].

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

• OUTPUT_HISTORICAL_TIME_SERIES: aBOOL value thatdetermines whether the input data is returned along with the forecasteddata. Set this argument toTRUE to return input data. The default valueisFALSE.

  Returning the input data along with the forecasted data lets you compare thehistorical value of the data column with the forecasted value of the datacolumn, or chart the change in the data column values over time.

• CONTEXT_WINDOW: anINT64 value that specifies thecontext window length used by BigQuery ML's built-in TimesFM model.The context window length determines how many of the most recent data pointsfrom the input time series are used by the model. For example, if your timeseries date range is March 1 to April 15, data points are selected startingat April 15 and working backwards. Valid values for models are as follows:

  Model Name	Supported Context Window Length
  TimesFM 2.0	64, 128, 256, 512, 1024, 2048
  TimesFM 2.5	64, 128, 256, 512, 1024, 2048, 4096, 8192, 15360

  If you don't specify aCONTEXT_WINDOW value, theAI.FORECAST functionautomatically chooses the smallest possible context window length to use thatis still large enough to cover the number of time series data points in yourinput data. The following table shows the relationships between the number oftime series data points in the input data, the selected context window lengthand the corresponding supported TimesFM model name:

  Number of time series data points	Context window length	Supported Model Names
  (1, 64]	64	TimesFM 2.0, TimesFM 2.5
  (65, 128]	128	TimesFM 2.0, TimesFM 2.5
  (129, 256]	256	TimesFM 2.0, TimesFM 2.5
  (257, 512]	512	TimesFM 2.0, TimesFM 2.5
  (513, 1024]	1,024	TimesFM 2.0, TimesFM 2.5
  (1025, 2048]	2,048	TimesFM 2.0, TimesFM 2.5
  >(2049, 4096]	4,096	TimesFM 2.5
  >(4097, 8192]	8,192	TimesFM 2.5
  >(8193, 15360]	15,360	TimesFM 2.5
  >15360	15,360	TimesFM 2.5

  For theTimesFM 2.0 model, 2,048 is the maximum number of time series datapoints that are passed to the model. For theTimesFM 2.5 model, 15,360 isthe maximum number of time series data points that are passed to the model. Any additional time series data points in the input data are ignored.

Output

AI.FORECAST returns the following columns:

• id_cols: one or more values that contain the identifiers of a timeseries.id_cols can be anINT64,STRING,ARRAY<INT64> orARRAY<STRING> value. The column names and types are inherited from theID_COLS argument value specified in the function input.
• confidence_level: aFLOAT64 value that contains theconfidence_level value that you specified in the function input, or0.95 if you didn't specify aconfidence_level value. This value is thesame across all rows.
• prediction_interval_lower_bound: aFLOAT64 value that contains thelower bound of the prediction interval for each forecasted point.
• prediction_interval_upper_bound: aFLOAT64 value that contains theupper bound of the prediction interval for each forecasted point.
• ai_forecast_status: aSTRING value that contains the forecast status.This value is empty if the operation was successful. If the operation wasn'tsuccessful, the value is the error string. A common error isThe time series data is too short. This error indicates that there wasn'tenough historical data in the time series to generate a forecast.A minimum of 3 data points is required.

Only forecasted values are returned when theOUTPUT_HISTORICAL_TIME_SERIESargument value is set toFALSE. The following columns are returned:

• forecast_timestamp: aTIMESTAMP value that contains the timestamps of the time series.
• forecast_value: aFLOAT64 value that contains the 50% quantile value for the forecasting output from the model. The 50% quantile value represents the median value of the forecasted data.

Both historical and forecasted values are returned when theOUTPUT_HISTORICAL_TIME_SERIES argument value is set toTRUE. The following columns are returned:

• time_series_type: aSTRING value that contains a value of eitherhistory orforecast. Rows that have a value ofhistory contain datafrom the input table or query. Rows that have a value offorecast containforecasted data.
• time_series_timestamp: aTIMESTAMP value that contains thetimestamps of the time series.
• time_series_data: aFLOAT64 value that represents the historical inputif thetime_series_type ishistory. Otherwise, if thetime_series_type isforecast this number represents the 50% quantile of the forecast.

Example

The following example forecasts the daily number of bike trips for eachdifferent user type for the next 30 days.

WITH  citibike_trips AS (    SELECT EXTRACT(DATE FROM starttime) AS date, usertype, COUNT(*) AS num_trips    FROM `bigquery-public-data.new_york.citibike_trips`    GROUP BY date, usertype  )SELECT *FROM  AI.FORECAST(    TABLE citibike_trips,    data_col => 'num_trips',    timestamp_col => 'date',    id_cols => ['usertype'],    horizon => 30);

Locations

AI.FORECAST and the TimesFM model are available in allsupported BigQuery ML locations.

Pricing

AI.FORECAST usage is billed at the evaluation, inspection, and predictionrate documented in theBigQuery ML on-demand pricing sectionof theBigQuery ML pricing page.

What's next

• Tryusing a TimesFM model with theAI.FORECAST function.
• Evaluate forecasting results from the TimesFM model using theAI.EVALUATE function.
• For information about forecasting in BigQuery ML, seeForecasting overview.
• For more information about supported SQL statements and functions fortime series forecasting models, seeEnd-to-end user journeys for time series forecasting models.