ai_forecast function

Applies to: Databricks SQL

important

Version 1 of this function is in Public Preview and HIPAA compliant. Version 2 (recommended) is in Beta.

ai_forecast() is a table-valued function that extrapolates time series data forward in time. See Arguments for available arguments to configure this function.

The function has two versions. A research-optimized time series foundation model powers Version 2 for improved out-of-the-box accuracy, and Version 2 adds support for holidays, external covariates, and non-negative forecasts. Use the version argument to select which version runs. See Arguments for details.

Requirements

• Pro or Serverless SQL warehouse
• Enroll your workspace in the Predictive AI Functions preview (mandatory for the recommended version 2). See Manage Databricks previews.

Syntax

astuce

Databricks recommends version 2 for ai_forecast. Version 2 provides the following improvements over Version 1:

• A research-optimized time series foundation model for improved out-of-the-box accuracy
• Built-in holiday support with holiday_region
• External covariates, including future and past-only covariates, with covariate_col
• Non-negative forecasts with positive_only

To use version 2, pass version => '2'. Ensure you've enabled the Predictive AI Functions preview. See Manage Databricks previews.

Version 2 (recommended)

ai_forecast(observed, horizon, time_col, value_col
  [, group_col] [, covariate_col] [, prediction_interval_width]
  [, frequency] [, holiday_region] [, positive_only] [, version])

Version 1

ai_forecast(observed, horizon, time_col, value_col
  [, group_col] [, prediction_interval_width] [, frequency]
  [, seed] [, parameters] [, version])

Arguments

ai_forecast() can forecast any number of groups (see group_col) and up to 100 metrics (see value_col) within each group. The forecast frequency is the same for all metrics in a group but can differ across groups (see frequency).

Version 2 (recommended)

• observed is the table-valued input that is used as training data for the forecasting procedure.
  • This input relation must contain one "time" column and one or more "value" columns. "Group" and "covariate" columns are optional. Any additional columns in the input relation are ignored.
• horizon is a timestamp-castable quantity representing the right-exclusive end time of the forecasting results. Within a group (see group_col) forecast results span the time between the last observation and horizon. If horizon is less than the last observation time, then no results are generated.
• time_col is a string referencing the "time column" in observed. The column referenced by time_col must be a DATE or a TIMESTAMP.
• value_col is a string or an array of strings referencing value columns in observed. The columns referenced by this argument must be castable to DOUBLE.
• group_col (optional) is a string or an array of strings representing the group columns in observed. If specified, group columns are used as partitioning criteria, and forecasts are generated for each group independently. If unspecified, the full input data is treated as a single group.
• covariate_col (optional) is a string or an array of strings referencing external covariate columns in observed. Covariates are additional variables that influence the forecast, such as price, marketing spend, or weather. Two kinds of covariates are supported:
  • Future covariates: values are known for the forecast horizon, such as planned pricing or scheduled campaigns. To use a covariate this way, include rows in observed that cover the forecast horizon (dates after the last observation, up to horizon) with the covariate populated and the value_col column(s) left NULL. ai_forecast forecasts these NULL-target rows and conditions the forecast on their covariate values.
  • Past-only covariates: values are known only for the historical period, such as observed weather or macroeconomic indicators. Populate the covariate on historical rows only. If you do not supply covariate values over the forecast horizon, ai_forecast uses the covariate as past-only; this is not an error.
• prediction_interval_width (optional) is a value between 0 and 1 representing the width of the prediction interval. Forecasted values have a prediction_interval_width % probability of falling between {v}_upper and {v}_lower.
• frequency (optional) is a pandas offset alias string (for example, 'D', 'W', 'ME', 'H') specifying the time granularity of the forecast results. If unspecified, the forecast granularity is automatically inferred for each group independently. If specified, it must match the inferred granularity of the input data within each group.
  • The inferred frequency within a group is the mode of the most recent observations. This inference is a convenience operation that is not tunable by the user.
  • For example, a time series with 99 "mondays" and 1 "tuesday" results in the "week" being the inferred frequency.
• holiday_region (optional) is a region code that enables automatic modeling of holiday effects for that region, such as 'US'. When unspecified, no holiday effects are modeled.
• positive_only (optional) when set to TRUE, constrains the forecasted values to be non-negative. Use this argument for metrics that cannot be negative, such as sales, counts, or inventory. Default is FALSE.
• version (optional): Version switch to support migration ('1' for version 1 behavior, '2' for version 2 behavior). If unspecified, defaults to version 1. The version 2 arguments (covariate_col, holiday_region, positive_only) require version => '2'.

Version 1

• observed is the table-valued input that is used as training data for the forecasting procedure.
  • This input relation must contain one "time" column and one or more "value" columns. "Group" and "parameters" columns are optional. Any additional columns in the input relation are ignored.
• horizon is a timestamp-castable quantity representing the right-exclusive end time of the forecasting results. Within a group (see group_col) forecast results span the time between the last observation and horizon. If horizon is less than the last observation time, then no results are generated.
• time_col is a string referencing the "time column" in observed. The column referenced by time_col must be a DATE or a TIMESTAMP.
• value_col is a string or an array of strings referencing value columns in observed. The columns referenced by this argument must be castable to DOUBLE.
• group_col (optional) is a string or an array of strings representing the group columns in observed. If specified, group columns are used as partitioning criteria, and forecasts are generated for each group independently. If unspecified, the full input data is treated as a single group.
• prediction_interval_width (optional) is a value between 0 and 1 representing the width of the prediction interval. Forecasted values have a prediction_interval_width % probability of falling between {v}_upper and {v}_lower.
• frequency (optional) is a time unit or pandas offset alias string specifying the time granularity of the forecast results. If unspecified, the forecast granularity is automatically inferred for each group independently. If a frequency value is specified, it is applied equally to all groups.
  • The inferred frequency within a group is the mode of the most recent observations. This inference is a convenience operation that is not tunable by the user.
  • For example, a time series with 99 "mondays" and 1 "tuesday" results in the "week" being the inferred frequency.
• seed (optional) is a number used to start any pseudorandom number generators used in the forecasting procedure.
• parameters (optional) is a string-encoded JSON or the name of a column identifier that represents the parameterization of the forecasting procedure. Any combination of parameters can be specified in any order, for example, {"weekly_order": 10, "global_cap": 1000}. Any unspecified parameters are automatically determined based on the attributes of the training data. The following parameters are supported:
  • global_cap and global_floor can be used together or independently to define the possible domain of the metric values. {"global_floor": 0}, for example, can be used to constrain a metric like cost to always be positive. These constraints apply globally to the training data and the forecasted data, and can not be used to provide tight constraints on the forecasted values only.
  • daily_order and weekly_order set the fourier order of the daily and weekly seasonality components.
• version (optional): Version switch to support migration ('1' for version 1 behavior, '2' for version 2 behavior). If unspecified, defaults to version 1. The version 2 arguments (covariate_col, holiday_region, positive_only) require version => '2'.

Returns

Version 2 (recommended)

A new set of rows containing the forecasted data. The output schema contains the time and group columns with their types unchanged. For example, if the input time column has type DATE, then the output time column type is also DATE. For each value column there are three output columns with the pattern {v}_forecast, {v}_upper, and {v}_lower. Regardless of the input value types, the forecasted value columns are always type DOUBLE. The output table contains forecasted values only, spanning the range of time between the end of the observed data until horizon.

The following table shows some examples of the schema inference performed by AI_FORECAST:

Input table	Arguments	Output table
ts: TIMESTAMP val: DOUBLE	time_col => 'ts' value_col => 'val'	ts: TIMESTAMP val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ds: DATE val BIGINT	time_col => 'ds' value_col => 'val'	ds: DATE val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dollars: DECIMAL(10, 2)	time_col => 'ts' value_col => 'dollars' group_col => 'dim1'	ts: TIMESTAMP dim1: STRING dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars: DECIMAL(10, 2) users: BIGINT	time_col => 'ts' value_col => ARRAY('dollars', 'users') group_col => ARRAY('dim1', 'dim2')	ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE users_forecast: DOUBLE users_upper: DOUBLE users_lower: DOUBLE

Input table	Arguments	Output table
ts: TIMESTAMP val: DOUBLE	time_col => 'ts' value_col => 'val'	ts: TIMESTAMP val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ds: DATE val BIGINT	time_col => 'ds' value_col => 'val'	ds: DATE val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dollars: DECIMAL(10, 2)	time_col => 'ts' value_col => 'dollars' group_col => 'dim1'	ts: TIMESTAMP dim1: STRING dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars: DECIMAL(10, 2) users: BIGINT	time_col => 'ts' value_col => ARRAY('dollars', 'users') group_col => ARRAY('dim1', 'dim2')	ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE users_forecast: DOUBLE users_upper: DOUBLE users_lower: DOUBLE

Version 1

A new set of rows containing the forecasted data. The output schema contains the time and group columns with their types unchanged. For example, if the input time column has type DATE, then the output time column type is also DATE. For each value column there are three output columns with the pattern {v}_forecast, {v}_upper, and {v}_lower. Regardless of the input value types, the forecasted value columns are always type DOUBLE. The output table contains forecasted values only, spanning the range of time between the end of the observed data until horizon.

The following table shows some examples of the schema inference performed by AI_FORECAST:

Input table	Arguments	Output table
ts: TIMESTAMP val: DOUBLE	time_col => 'ts' value_col => 'val'	ts: TIMESTAMP val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ds: DATE val BIGINT	time_col => 'ds' value_col => 'val'	ds: DATE val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dollars: DECIMAL(10, 2)	time_col => 'ts' value_col => 'dollars' group_col => 'dim1'	ts: TIMESTAMP dim1: STRING dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars: DECIMAL(10, 2) users: BIGINT	time_col => 'ts' value_col => ARRAY('dollars', 'users') group_col => ARRAY('dim1', 'dim2')	ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE users_forecast: DOUBLE users_upper: DOUBLE users_lower: DOUBLE

Input table	Arguments	Output table
ts: TIMESTAMP val: DOUBLE	time_col => 'ts' value_col => 'val'	ts: TIMESTAMP val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ds: DATE val BIGINT	time_col => 'ds' value_col => 'val'	ds: DATE val_forecast: DOUBLE val_upper: DOUBLE val_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dollars: DECIMAL(10, 2)	time_col => 'ts' value_col => 'dollars' group_col => 'dim1'	ts: TIMESTAMP dim1: STRING dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE
ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars: DECIMAL(10, 2) users: BIGINT	time_col => 'ts' value_col => ARRAY('dollars', 'users') group_col => ARRAY('dim1', 'dim2')	ts: TIMESTAMP dim1: STRING dim2: BIGINT dollars_forecast: DOUBLE dollars_upper: DOUBLE dollars_lower: DOUBLE users_forecast: DOUBLE users_upper: DOUBLE users_lower: DOUBLE

Examples

Version 2 (recommended)

The following example forecasts until a specified date using Version 2:

SQL

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1
)
SELECT * FROM AI_FORECAST(
  TABLE(aggregated),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue',
  version => '2'
)

The following example models holiday effects for the United States and constrains the forecast to non-negative values:

SQL

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1
)
SELECT * FROM AI_FORECAST(
  TABLE(aggregated),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue',
  holiday_region => 'US',
  positive_only => true,
  version => '2'
)

The following example uses an external covariate. The observed table (daily_sales) includes a promotion column populated for both the historical period and the forecast horizon, with revenue left NULL on the forecast-horizon rows, so promotion is used as a future covariate:

SQL

SELECT * FROM AI_FORECAST(
  TABLE(daily_sales),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue',
  covariate_col => 'promotion',
  version => '2'
)

Version 1

The following example forecasts until a specified date:

SQL

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1
)
SELECT * FROM AI_FORECAST(
  TABLE(aggregated),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue'
)

The following is a more complex example:

SQL

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    dropoff_zip,
    SUM(fare_amount) AS revenue,
    COUNT(*) AS n_trips
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1, 2
),
spine AS (
  SELECT all_dates.ds, all_zipcodes.dropoff_zip
  FROM (SELECT DISTINCT ds FROM aggregated) all_dates
  CROSS JOIN (SELECT DISTINCT dropoff_zip FROM aggregated) all_zipcodes
)
SELECT * FROM AI_FORECAST(
  TABLE(
    SELECT
      spine.*,
      COALESCE(aggregated.revenue, 0) AS revenue,
      COALESCE(aggregated.n_trips, 0) AS n_trips
    FROM spine LEFT JOIN aggregated USING (ds, dropoff_zip)
  ),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => ARRAY('revenue', 'n_trips'),
  group_col => 'dropoff_zip',
  prediction_interval_width => 0.9,
  parameters => '{"global_floor": 0}'
)

remarque

ai_forecast does not materialize 0s for missing or NULL entries in the table. If the proper values of the missing entries can be inferred, then they must be coalesced prior to calling the ai_forecast function. If the values are truly missing or unknown, then you can leave the values as NULL or remove them.

For sparse data, it is best practice to coalesce missing values or provide a frequency value explicitly to avoid unexpected output from the "auto" frequency inference. For example, "auto" frequency inference on two entries 14 days apart infers a frequency of "14D" even if the "real" frequency might be weekly with 1 missing value. Coalescing the missing entries removes this ambiguity.

The following example shows how different forecast parameters are applied to different groups in the input table. The example uses the parameters argument as a column identifier. This approach enables users to store previously-determined parameter JSONs in a table and reuse them on new data.

SQL

WITH past AS (
  SELECT
    CASE
      WHEN fare_amount < 30 THEN 'Under $30'
      ELSE '$30 or more'
    END AS revenue_bucket,
    CASE
      WHEN fare_amount < 30 THEN '{"daily_order": 0}'
      ELSE '{"daily_order": "auto"}'
    END AS parameters,
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM samples.nyctaxi.trips
  GROUP BY ALL
)
SELECT * FROM AI_FORECAST(
  TABLE(past),
  horizon => (SELECT MAX(ds) + INTERVAL 30 DAYS FROM past),
  time_col => 'ds',
  value_col => 'revenue',
  group_col => ARRAY('revenue_bucket'),
  parameters => 'parameters'
)

Limitations

Version 2 (recommended)

The following limitations apply during the Beta:

• Version 2 is in Beta and is not the default. To use version 2, opt in by setting version => '2'. Version 1, which is in Public Preview, remains the default.
• The default forecasting procedure is a time series foundation model. This model is the only supported forecasting procedure available.
• Error messages are delivered through the Python UDTF engine, and contain Python trace back information. The end of the trace back contains the actual error message.
• Each invocation of ai_forecast performs an independent inference. If you call ai_forecast multiple times with different prediction_interval_width values to produce nested prediction intervals, the resulting intervals are not guaranteed to be properly nested. To compare prediction intervals, use a single ai_forecast call with one prediction_interval_width value.

Version 1

The following limitations apply during the Public Preview:

• Version 1 is in Public Preview and is the default version. Version 2, which is in Beta, is available by setting version => '2'.
• Version 1 is on a deprecation path. In an upcoming release, the default version changes to version 2, and version 1 is deprecated. To keep using version 1 behavior after the default changes, pin it by setting version => '1'.
• The default forecasting procedure is a prophet-like piecewise linear and seasonality model. This model is the only supported forecasting procedure available.
• Error messages are delivered through the Python UDTF engine, and contain Python trace back information. The end of the trace back contains the actual error message.
• Each invocation of ai_forecast performs an independent quantile regression. If you call ai_forecast multiple times with different prediction_interval_width values to produce nested prediction intervals, the resulting intervals are not guaranteed to be properly nested because the quantiles are computed independently across calls, with no constraint to verify proper ordering. To compare prediction intervals, use a single ai_forecast call with one prediction_interval_width value.