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, or TabFM predicted data against ground truth data.

For example, suppose you have tables that contain data about car rentals each day. The following query returns statistics about how well the model predicts car rentals in the current year based on the previous year:

SELECT *
FROM
  AI.EVALUATE(
    TABLE rentals.last_year,
    TABLE rentals.this_year,
    data_col => 'num_rentals',
    timestamp_col => 'date');

Syntax

Choose one of the following syntaxes based on whether you're evaluating TimesFM or TabFM predictions:

TimesFM

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]
    [, context_window => CONTEXT_WINDOW]
  )

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. Supported models include TimesFM 2.0 and TimesFM 2.5. The default value is TimesFM 2.5, which we recommend for all new evaluation tasks.
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
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].

CONTEXT_WINDOW: an INT64 value that specifies the context window length used by BigQuery ML's built-in TimesFM model. The context window length determines how many of the most recent data points from the input time series are used by the model. For example, if your time series date range is March 1 to April 15, data points are selected starting at 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 a CONTEXT_WINDOW value, the AI.EVALUATE function automatically chooses the smallest possible context window length to use that is still large enough to cover the number of time series data points in your input data. The following table shows the relationships between the number of time series data points in the input data, the selected context window length, and 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 the TimesFM 2.0 model, 2,048 is the maximum number of time series data points that are passed to the model. For the TimesFM 2.5 model, 15,360 is the 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.

TabFM

SELECT
  *
FROM
  AI.EVALUATE(
  { TABLE TRAINING_TABLE | (TRAINING_QUERY)  },
  { TABLE PREDICTION_TABLE | (PREDICTION_QUERY)  },
  label_col => 'LABEL_COL'
  )

Arguments

AI.EVALUATE takes the following arguments:

TRAINING_TABLE | TRAINING_QUERY: the table or query that contains the training data. The table or query result must contain a column named label or the column that you specify in the LABEL_COL argument. Every other column is considered a feature column. The feature and label columns must be one of the following types: STRING, BOOL, INT64, FLOAT64, NUMERIC or BIGNUMERIC.
PREDICTION_TABLE | PREDICTION_QUERY: the table or query that contains the data to evaluate. The table or query result must contain all of the feature columns in the training data and can optionally contain additional columns.
LABEL_COL: a STRING value that specifies the name of the label column in the training data. You must provide this argument for TabFM evaluation. If the column is of type STRING or BOOL, then classification is evaluated. If the column is of type INT64, FLOAT64, NUMERIC or BIGNUMERIC, then regression is evaluated.

Output

AI.EVALUATE returns the following columns based on whether you're evaluating TimesFM or TabFM predictions:

TimesFM

The following columns are returned for TimesFM forecasted data:

id_cols: one or more values that contain the identifiers of a time series. id_cols can be an INT64, STRING, ARRAY<INT64> or ARRAY<STRING> value. The column names and types are inherited from the ID_COLS argument value specified in the function input.
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.
mean_absolute_scaled_error: a FLOAT64 value that contains the mean absolute scaled 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.

TabFM

The following columns are returned for TabFM regression predictions:

mean_absolute_error: a FLOAT64 value that contains the mean absolute error for the data.
mean_squared_error: a FLOAT64 value that contains the mean squared error for the data.
mean_squared_log_error: a FLOAT64 value that contains the mean squared logarithmic error for the data.
median_absolute_error: a FLOAT64 value that contains the median absolute error for the data.
r2_score: a FLOAT64 value that contains the coefficient of determination for the data.
explained_variance: a FLOAT64 value that contains the explained variance for the data.

The following columns are returned for TabFM classification predictions:

precision: a FLOAT64 value that contains the macro-average precision across all classes.
recall: a FLOAT64 value that contains the macro-average recall across all classes.
accuracy: a FLOAT64 value that contains the accuracy of the prediction.
f1_score: a FLOAT64 value that contains the macro-average F₁score across all classes.

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');

The result is similar to the following:

+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+-----------------------------+--------------------+
| mean_absolute_error | mean_squared_error | root_mean_squared_error | mean_absolute_percentage_error | symmetric_mean_absolute_percentage_error | mean_absolute_scaled_error  | ai_evaluate_status |
+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+-----------------------------+--------------------+
| 7512.2744140624982  | 88702684.834815472 | 9418.210277691589       | 16.068001108491149             | 15.740030591250889                       | 0.981234567                 | null               |
+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+-----------------------------+--------------------+

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']);

Evaluate regression prediction

The following query evaluates the performance of TabFM regression to predict a penguin's mass:

WITH prepared_data AS (
  SELECT *, RAND() <= 0.8 AS training
  FROM `bigquery-public-data.ml_datasets.penguins`
  WHERE body_mass_g > 0
)
SELECT
 *
FROM
 AI.EVALUATE(
  # Training data
  (SELECT * EXCEPT(training) FROM prepared_data WHERE training),
  # Prediction data
  (SELECT * EXCEPT(training) FROM prepared_data WHERE NOT training),
  label_col => 'body_mass_g');

The result is similar to the following:

+---------------------+--------------------+------------------------+-----------------------+---------------------+---------------------+
| mean_absolute_error | mean_squared_error | mean_squared_log_error | median_absolute_error | r2_score            | explained_variance  |
+---------------------+--------------------+------------------------+-----------------------+---------------------+---------------------+
| 187.47826086956516  | 57233.014492753638 | 0.0036635839277726163  | 160.0                 | 0.91107011835200613 | 0.91265547176600315 |
+---------------------+--------------------+------------------------+-----------------------+---------------------+---------------------+

Evaluate classification prediction

The following query evaluates the performance of TabFM classification to predict a penguin's sex:

WITH prepared_data AS (
  SELECT *, RAND() <= 0.8 AS training
  FROM `bigquery-public-data.ml_datasets.penguins`
  WHERE sex IS NOT NULL and sex != "."

)
SELECT
 *
FROM
 AI.EVALUATE(
  # Training data
  (SELECT * EXCEPT(training) FROM prepared_data WHERE training),
  # Prediction data
  (SELECT * EXCEPT(training) FROM prepared_data WHERE NOT training),
  label_col => 'sex');

The result is similar to the following:

+---------------------+---------------------+----------+-------------------+
| precision           | recall              | accuracy | f1_score          |
+---------------------+---------------------+----------+-------------------+
| 0.96511627906976738 | 0.95714285714285707 | 0.96     | 0.959539651141881 |
+---------------------+---------------------+----------+-------------------+

Locations

AI.EVALUATE and the TimesFM and TabFM models 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

Try using a TimesFM model with the AI.FORECAST function.
Try performing regression or classification with the AI.PREDICT function.
For information about forecasting in BigQuery ML, see Forecasting overview.
For more information about supported SQL statements and functions for time series forecasting models, see End-to-end user journeys for time series forecasting models.