The AI.EVALUATE function

This document describes the AI.EVALUATE function, which lets you evaluate TimesFM forecasted data against a reference time series based on historical data.

Syntax

SELECT
  *
FROM
  AI.EVALUATE(
    { TABLE HISTORY_TIME_SERIES_TABLE | (HISTORY_TIME_SERIES_QUERY_STATEMENT) },
    { TABLE ACTUAL_TIME_SERIES_TABLE | (ACTUAL_TIME_SERIES_QUERY_STATEMENT) },
    data_col => 'DATA_COL',
    timestamp_col => 'TIMESTAMP_COL',
    [, model => 'MODEL']
    [, id_cols => ID_COLS]
    [, horizon => HORIZON]
  )

Arguments

AI.EVALUATE takes the following arguments:

  • HISTORY_TIME_SERIES_TABLE: the name of the table that contains historical time series data which is used to generate a forecast. For example, `mydataset.mytable`. The forecasted values are then evaluated against the data in the ACTUAL_TIME_SERIES_TABLE or ACTUAL_TIME_SERIES_QUERY_STATEMENT argument.

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

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

    For example, `myproject.mydataset.mytable`.

  • HISTORY_TIME_SERIES_QUERY_STATEMENT: the GoogleSQL query that generates historical time series data which is used to generate a forecast. The forecasted values are then evaluated against the data in the ACTUAL_TIME_SERIES_TABLE or ACTUAL_TIME_SERIES_QUERY_STATEMENT argument. See the GoogleSQL query syntax page for the supported SQL syntax of the HISTORY_TIME_SERIES_QUERY_STATEMENT clause.

  • ACTUAL_TIME_SERIES_TABLE: the name of the table that contains the actual time series data. For example, `mydataset.mytable`. The data in this table is evaluated against the forecasted values for the historical time series provided by the HISTORY_TIME_SERIES_TABLE or HISTORY_TIME_SERIES_QUERY_STATEMENT argument.

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

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

    For example, `myproject.mydataset.mytable`.

  • ACTUAL_TIME_SERIES_QUERY_STATEMENT: the GoogleSQL query that generates the actual time series data. The data from this query is evaluated against the forecasted values for the historical time series provided by the HISTORY_TIME_SERIES_TABLE or HISTORY_TIME_SERIES_QUERY_STATEMENT argument. See the GoogleSQL query syntax page for the supported SQL syntax of the ACTUAL_TIME_SERIES_QUERY_STATEMENT clause.

  • DATA_COL: a STRING value that specifies the name of the time series data column. The data column must use one of the following data types:

    • INT64
    • NUMERIC
    • BIGNUMERIC
    • FLOAT64

  • TIMESTAMP_COL: a STRING value that specifies the name of the timestamp column. The timestamp column must use one of the following data types:

    • TIMESTAMP
    • DATE
    • DATETIME

  • MODEL: a STRING value that specifies the name of the model to use. TimesFM 2.0 is the only supported value, and is the default value.

  • ID_COLS: an ARRAY<STRING> value that specifies the names of one or more ID columns. Each unique combination of IDs identifies a unique time series to evaluate. Specify one or more values for this argument in order to evaluate multiple time series using a single query. The columns that you specify must use one of the following data types:

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

  • HORIZON: an INT64 value that specifies the number of forecasted time points to evaluate. The default value is 1024. The valid input range is [1, 10,000].

Output

AI.EVALUATE returns the following columns:

  • id_col or id_cols: the identifiers of a time series. Only present when evaluating multiple time series at once. The column names and types are inherited from the ID_COLS argument.
  • mean_absolute_error: a FLOAT64 value that contains the mean absolute error for the time series.
  • mean_squared_error: a FLOAT64 value that contains the mean squared error for the time series.
  • root_mean_squared_error: a FLOAT64 value that contains the root mean squared error for the time series.
  • mean_absolute_percentage_error: a FLOAT64 value that contains the mean absolute percentage error for the time series.
  • symmetric_mean_absolute_percentage_error: a FLOAT64 value that contains the symmetric mean absolute percentage error for the time series.
  • ai_evaluate_status: a STRING value that contains the evaluation status. The value is empty if the operation was successful. If the operation wasn't successful, the value is the error string. A common error is The time series data is too short. This error indicates that there wasn't enough historical data in the time series to generate forecasted data to evaluate. A minimum of 3 data points is required.

Examples

The following examples show how to use the AI.EVALUATE function.

Evaluate a single times series

The following example evaluates historical bike trips against actual bike trips for a single time series:

WITH
  citibike_trips AS (
    SELECT EXTRACT(DATE FROM starttime) AS date, COUNT(*) AS num_trips
    FROM `bigquery-public-data.new_york.citibike_trips`
    GROUP BY date
  )
SELECT *
FROM
  AI.EVALUATE(
    (SELECT * FROM citibike_trips WHERE date < '2016-07-01'),
    (SELECT * FROM citibike_trips WHERE date >= '2016-07-01'),
    data_col => 'num_trips',
    timestamp_col => 'date');

Evaluate multiple time series

The following example evaluates historical bike trips against actual bike trips for multiple time series:

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.EVALUATE(
    (SELECT * FROM citibike_trips WHERE date < '2016-07-01'),
    (SELECT * FROM citibike_trips WHERE date >= '2016-07-01'),
    data_col => 'num_trips',
    timestamp_col => 'date',
    id_cols => ['usertype']);

Locations

AI.EVALUATE and the TimesFM model are available in all supported BigQuery ML locations.

Pricing

AI.EVALUATE usage is billed at the evaluation, inspection, and prediction rate documented in the BigQuery ML on-demand pricing section of the BigQuery ML pricing page.

What's next