BigQuery AI.Forecast
AI.FORECAST leverages the pre-trained TimesFM foundation model to generate
forecasts without the need to train and manage custom models.
Syntax Reference
SELECT
*
FROM
AI.FORECAST(
{ TABLE `project.dataset.table` | (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]
)Input Arguments
| Argument | Requirement | Type | Description |
|---|---|---|---|
input_data |
Required | The source table or subquery containing historical data. | |
data_col |
Required | String | The numeric column to predict. |
timestamp_col |
Required | String | The column containing dates/timestamps. |
id_cols |
Optional | Array<String> | Grouping columns for multiple series (e.g., ['store_id']). |
horizon |
Optional | Int64 | Number of future points to predict. Defaults to 10. The valid input range is [1, 10,000]. |
confidence_level |
Optional | Float64 | Confidence interval (0 to 1). Defaults to 0.95. |
model |
Optional | String | Model version. Defaults to TimesFM 2.0. |
context_window |
Optional | Int64 | The number of historical data points the model uses to forecast. The min value is 64 and the max value is 2048 for TimesFM 2.0. If not set, the model determines this automatically. |
Output Schema
The schema adjusts based on the output_historical_time_series flag.
| Column | Type | Included if output_historical_time_series=FALSE | Included if output_historical_time_series=TRUE | Description |
|---|---|---|---|---|
id_cols |
(As Input) | Yes | Yes | Original identifiers for the series. |
forecast_timestamp |
TIMESTAMP | Yes | No | Timestamp for predicted points. |
forecast_value |
FLOAT64 | Yes | No | The 50% quantile (median) prediction. |
time_series_timestamp |
TIMESTAMP | No | Yes | Uniform timestamp column for both history and forecast. |
time_series_data |
FLOAT64 | No | Yes | Merged column: actual values for history, median for forecast. |
time_series_type |
STRING | No | Yes | Label: 'history' or 'forecast'. |
prediction_interval_lower_bound |
FLOAT64 | Yes | Yes | Lower bound (NULL for historical rows). |
prediction_interval_upper_bound |
FLOAT64 | Yes | Yes | Upper bound (NULL for historical rows). |
confidence_level |
FLOAT64 | Yes | Yes | The constant confidence level used. |
ai_forecast_status |
STRING | Yes | Yes | Error messages or empty string on success. A minimum of 3 data points is required. |
Examples
Forecasting with History
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,
output_historical_time_series => true);