Search code examples
machine-learninggoogle-bigqueryarimaforecast

Is there a way to use BigQuery's ML.FORECAST on historical data to test your model?

The default call to BigQuery's ML.FORECAST for the ARIMA model is:

ML.FORECAST(MODEL model_name,
           [, STRUCT<horizon INT64, confidence_level FLOAT64> settings])

As an example,

SELECT
  *
FROM
  ML.FORECAST(MODEL `mydataset.mymodel`,
              STRUCT(30 AS horizon, 0.8 AS confidence_level))

But I want to test my model on historical data so that I can see how well my model overlays on my training set. The default ML.FORECAST argument list only allows me to look into the future (3 < X < 1000) number of days ahead (in the example it is 30).

Is there a way I can either look backwards at all with the BQ ML ARIMA model? I tried using a negative value to no luck. And the ARIMA model does not support ML.PREDICT.

Thanks for any help.

Referenced documentation:

Solution

  • This is not possible in BigQuery ML. The ARIMA model is useful to forecast the future data for a given number of points in your time series but you can't use it to forecast a value related to a point in the past.

    If you want to know some metrics about your model, I suggest that you use the ML.EVALUATE function. When using it, keep in mind that:

    Neither table_name nor query_statement is required when evaluating the ARIMA-based time series models.

    Below you can find the information about your model that you can access with the ML.EVALUATION function

    enter image description here


    If you still need to see some kind of forecast for values which you do have historical data to compare, I suggest that you:

    1. Create the model using your training data but not using the latest data. In other words, keep the latest data of your time series for tests.
    2. Use your model to forecast the values for the future
    3. Compare the forecast values with the actual historical values.