Forecast multiple time series with an ARIMA_PLUS univariate model

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to the BigQuery page

2. In the Explorer pane, click your project name.

3. Click more_vert View actions > Create dataset

4. On the Create dataset page, do the following:

   • For Dataset ID, enter bqml_tutorial.

   • For Location type, select Multi-region, and then select US (multiple regions in United States).

   • Leave the remaining default settings as they are, and click Create dataset.

bq

To create a new dataset, use the bq mk command with the --location flag. For a full list of possible parameters, see the bq mk --dataset command reference.

1. Create a dataset named bqml_tutorial with the data location set to US and a description of BigQuery ML tutorial dataset:

   bq --location=US mk -d \
    --description "BigQuery ML tutorial dataset." \
    bqml_tutorial

   Instead of using the --dataset flag, the command uses the -d shortcut. If you omit -d and --dataset, the command defaults to creating a dataset.

2. Confirm that the dataset was created:

   bqls

API

Call the datasets.insert method with a defined dataset resource.

{
"datasetReference":{
"datasetId":"bqml_tutorial"
}
}

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

importgoogle.cloud.bigquery
bqclient = google.cloud.bigquery .Client ()
bqclient.create_dataset ("bqml_tutorial", exists_ok=True)

SQL

The SELECT statement of the following query uses the EXTRACT function to extract the date information from the starttime column. The query uses the COUNT(*) clause to get the daily total number of Citi Bike trips.

Follow these steps to visualize the time series data:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   SELECT
   EXTRACT(DATEfromstarttime)ASdate,
   COUNT(*)ASnum_trips
   FROM
   `bigquery-public-data.new_york.citibike_trips`
   GROUPBYdate;

3. When the query completes, click Open in > Looker Studio. Looker Studio opens in a new tab. Complete the following steps in the new tab.

4. In the Looker Studio, click Insert > Time series chart.

5. In the Chart pane, choose the Setup tab.

6. In the Metric section, add the num_trips field, and remove the default Record Count metric. The resulting chart looks similar to the following:

   Chart showing bike trip data over time.

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

importbigframes.pandasasbpd
df = bpd.read_gbq("bigquery-public-data.new_york.citibike_trips")
features = bpd.DataFrame(
 {
 "num_trips": df.starttime,
 "date": df["starttime"].dt.date,
 }
)
date = df["starttime"].dt.date
df.groupby([date])
num_trips = features.groupby(["date"]).count()
# Results from running "print(num_trips)"
# num_trips
# date
# 2013年07月01日 16650
# 2013年07月02日 22745
# 2013年07月03日 21864
# 2013年07月04日 22326
# 2013年07月05日 21842
# 2013年07月06日 20467
# 2013年07月07日 20477
# 2013年07月08日 21615
# 2013年07月09日 26641
# 2013年07月10日 25732
# 2013年07月11日 24417
# 2013年07月12日 19006
# 2013年07月13日 26119
# 2013年07月14日 29287
# 2013年07月15日 28069
# 2013年07月16日 29842
# 2013年07月17日 30550
# 2013年07月18日 28869
# 2013年07月19日 26591
# 2013年07月20日 25278
# 2013年07月21日 30297
# 2013年07月22日 25979
# 2013年07月23日 32376
# 2013年07月24日 35271
# 2013年07月25日 31084
num_trips.plot.line(
 # Rotate the x labels so they are more visible.
 rot=45,
)

SQL

In the following query, the OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', ...) clause indicates that you are creating an ARIMA-based time series model. You use the time_series_id_col option of the CREATE MODEL statement to specify one or more columns in the input data that you want to get forecasts for, in this case the Citi Bike station, as represented by the start_station_name column. You use the WHERE clause to limit the start stations to those with Central Park in their names. The auto_arima_max_order option of the CREATE MODEL statement controls the search space for hyperparameter tuning in the auto.ARIMA algorithm. The decompose_time_series option of the CREATE MODEL statement defaults to TRUE, so that information about the time series data is returned when you evaluate the model in the next step.

Follow these steps to create the model:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   CREATEORREPLACEMODEL`bqml_tutorial.nyc_citibike_arima_model_group`
   OPTIONS
   (model_type='ARIMA_PLUS',
   time_series_timestamp_col='date',
   time_series_data_col='num_trips',
   time_series_id_col='start_station_name',
   auto_arima_max_order=5
   )AS
   SELECT
   start_station_name,
   EXTRACT(DATEfromstarttime)ASdate,
   COUNT(*)ASnum_trips
   FROM
   `bigquery-public-data.new_york.citibike_trips`
   WHEREstart_station_nameLIKE'%Central Park%'
   GROUPBYstart_station_name,date;

   The query takes approximately 24 seconds to complete, after which you can access the nyc_citibike_arima_model_group model. Because the query uses a CREATE MODEL statement, you don't see query results.

This query creates twelve time series models, one for each of the twelve Citi Bike start stations in the input data. The time cost, approximately 24 seconds, is only 1.4 times more than that of creating a single time series model because of the parallelism. However, if you remove the WHERE ... LIKE ... clause, there would be 600+ time series to forecast, and they wouldn't be forecast completely in parallel because of slot capacity limitations. In that case, the query would take approximately 15 minutes to finish. To reduce the query runtime with the compromise of a potential slight drop in model quality, you could decrease the value of the auto_arima_max_order. This shrinks the search space of hyperparameter tuning in the auto.ARIMA algorithm. For more information, see Large-scale time series forecasting best practices.

BigQuery DataFrames

In the following snippet, you are creating an ARIMA-based time series model.

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

frombigframes.mlimport forecasting
importbigframes.pandasasbpd
model = forecasting.ARIMAPlus(
 # To reduce the query runtime with the compromise of a potential slight
 # drop in model quality, you could decrease the value of the
 # auto_arima_max_order. This shrinks the search space of hyperparameter
 # tuning in the auto.ARIMA algorithm.
 auto_arima_max_order=5,
)
df = bpd.read_gbq("bigquery-public-data.new_york.citibike_trips")
# This query creates twelve time series models, one for each of the twelve
# Citi Bike start stations in the input data. If you remove this row
# filter, there would be 600+ time series to forecast.
df = df[df["start_station_name"].str.contains("Central Park")]
features = bpd.DataFrame(
 {
 "start_station_name": df["start_station_name"],
 "num_trips": df["starttime"],
 "date": df["starttime"].dt.date,
 }
)
num_trips = features.groupby(
 ["start_station_name", "date"],
 as_index=False,
).count()
X = num_trips["date"].to_frame()
y = num_trips["num_trips"].to_frame()
model.fit(
 X,
 y,
 # The input data that you want to get forecasts for,
 # in this case the Citi Bike station, as represented by the
 # start_station_name column.
 id_col=num_trips["start_station_name"].to_frame(),
)
# The model.fit() call above created a temporary model.
# Use the to_gbq() method to write to a permanent location.
model.to_gbq(
 your_model_id, # For example: "bqml_tutorial.nyc_citibike_arima_model",
 replace=True,
)

This creates twelve time series models, one for each of the twelve Citi Bike start stations in the input data. The time cost, approximately 24 seconds, is only 1.4 times more than that of creating a single time series model because of the parallelism.

SQL

Evaluate the time series model by using the ML.ARIMA_EVALUATE function. The ML.ARIMA_EVALUATE function shows you the evaluation metrics that were generated for the model during the process of automatic hyperparameter tuning.

Follow these steps to evaluate the model:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   SELECT
   *
   FROM
   ML.ARIMA_EVALUATE(MODEL`bqml_tutorial.nyc_citibike_arima_model_group`);

   The results should look like the following:

   Evaluation metrics for the time series model.

   While auto.ARIMA evaluates dozens of candidate ARIMA models for each time series, ML.ARIMA_EVALUATE by default only outputs the information of the best model to make the output table compact. To view all the candidate models, you can set the ML.ARIMA_EVALUATE function's show_all_candidate_model argument to TRUE.

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

# Evaluate the time series models by using the summary() function. The summary()
# function shows you the evaluation metrics of all the candidate models evaluated
# during the process of automatic hyperparameter tuning.
summary = model.summary()
print(summary.peek())
# Expected output:
# start_station_name non_seasonal_p non_seasonal_d non_seasonal_q has_drift log_likelihood AIC variance ...
# 1 Central Park West & W 72 St 0 1 5 False -1966.449243 3944.898487 1215.689281 ...
# 8 Central Park W & W 96 St 0 0 5 False -274.459923 562.919847 655.776577 ...
# 9 Central Park West & W 102 St 0 0 0 False -226.639918 457.279835 258.83582 ...
# 11 Central Park West & W 76 St 1 1 2 False -1700.456924 3408.913848 383.254161 ...
# 4 Grand Army Plaza & Central Park S 0 1 5 False -5507.553498 11027.106996 624.138741 ...

SQL

Inspect the time series model's coefficients by using the ML.ARIMA_COEFFICIENTS function.

Follow these steps to retrieve the model's coefficients:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   SELECT
   *
   FROM
   ML.ARIMA_COEFFICIENTS(MODEL`bqml_tutorial.nyc_citibike_arima_model_group`);

   The query takes less than a second to complete. The results should look similar to the following:

   Coefficients for the time series model.

   For more information about the output columns, see ML.ARIMA_COEFFICIENTS function.

BigQuery DataFrames

Inspect the time series model's coefficients by using the coef_ function.

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

coef = model.coef_
print(coef.peek())
# Expected output:
# start_station_name ar_coefficients ma_coefficients intercept_or_drift
# 5 Central Park West & W 68 St [] [-0.41014089 0.21979212 -0.59854213 -0.251438... 0.0
# 6 Central Park S & 6 Ave [] [-0.71488957 -0.36835772 0.61008532 0.183290... 0.0
# 0 Central Park West & W 85 St [] [-0.39270166 -0.74494638 0.76432596 0.489146... 0.0
# 3 W 82 St & Central Park West [-0.50219511 -0.64820817] [-0.20665325 0.67683137 -0.68108631] 0.0
# 11 W 106 St & Central Park West [-0.70442887 -0.66885553 -0.25030325 -0.34160669] [] 0.0

SQL

Forecast future time series values by using the ML.FORECAST function.

In the following GoogleSQL query, the STRUCT(3 AS horizon, 0.9 AS confidence_level) clause indicates that the query forecasts 3 future time points, and generates a prediction interval with a 90% confidence level.

Follow these steps to forecast data with the model:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   SELECT
   *
   FROM
   ML.FORECAST(MODEL`bqml_tutorial.nyc_citibike_arima_model_group`,
   STRUCT(3AShorizon,0.9ASconfidence_level))

3. Click Run.

   The query takes less than a second to complete. The results should look like the following:

   ML.FORECAST output.

For more information about the output columns, see ML.FORECAST function.

BigQuery DataFrames

Forecast future time series values by using the predict function.

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

prediction = model.predict(horizon=3, confidence_level=0.9)
print(prediction.peek())
# Expected output:
# forecast_timestamp start_station_name forecast_value standard_error confidence_level ...
# 4 2016年10月01日 00:00:00+00:00 Central Park S & 6 Ave 302.377201 32.572948 0.9 ...
# 14 2016年10月02日 00:00:00+00:00 Central Park North & Adam Clayton Powell Blvd 263.917567 45.284082 0.9 ...
# 1 2016年09月25日 00:00:00+00:00 Central Park West & W 85 St 189.574706 39.874856 0.9 ...
# 20 2016年10月02日 00:00:00+00:00 Central Park West & W 72 St 175.474862 40.940794 0.9 ...
# 12 2016年10月01日 00:00:00+00:00 W 106 St & Central Park West 63.88163 18.088868 0.9 ...

SQL

You can get explainability metrics in addition to forecast data by using the ML.EXPLAIN_FORECAST function. The ML.EXPLAIN_FORECAST function forecasts future time series values and also returns all the separate components of the time series. If you just want to return forecast data, use the ML.FORECAST function instead, as shown in Use the model to forecast data.

The STRUCT(3 AS horizon, 0.9 AS confidence_level) clause used in the ML.EXPLAIN_FORECAST function indicates that the query forecasts 3 future time points and generates a prediction interval with 90% confidence.

Follow these steps to explain the model's results:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, paste in the following query and click Run:

   SELECT
   *
   FROM
   ML.EXPLAIN_FORECAST(MODEL`bqml_tutorial.nyc_citibike_arima_model_group`,
   STRUCT(3AShorizon,0.9ASconfidence_level));

   The query takes less than a second to complete. The results should look like the following:

   The first nine output columns of forecasted data and forecast explanations. The tenth through seventeenth output columns of forecasted data and forecast explanations. The last six output columns of forecasted data and forecast explanations.

   The first thousands rows returned are all history data. You must scroll through the results to see the forecast data.

   The output rows are ordered first by start_station_name, then chronologically by the time_series_timestamp column value. In time series forecasting, the prediction interval, as represented by the prediction_interval_lower_bound and prediction_interval_upper_bound column values, is as important as the forecast_value column value. The forecast_value value is the middle point of the prediction interval. The prediction interval depends on the standard_error and confidence_level column values.

   For more information about the output columns, see ML.EXPLAIN_FORECAST.

BigQuery DataFrames

You can get explainability metrics in addition to forecast data by using the predict_explain function. The predict_explain function forecasts future time series values and also returns all the separate components of the time series. If you just want to return forecast data, use the predict function instead, as shown in Use the model to forecast data.

The horizon=3, confidence_level=0.9 clause used in the predict_explain function indicates that the query forecasts 3 future time points and generates a prediction interval with 90% confidence.

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

explain = model.predict_explain(horizon=3, confidence_level=0.9)
print(explain.peek(5))
# Expected output:
# time_series_timestamp	 start_station_name	 time_series_type	 time_series_data	 time_series_adjusted_data	 standard_error	 confidence_level	 prediction_interval_lower_bound	 prediction_interval_upper_bound	 trend	 seasonal_period_yearly	 seasonal_period_quarterly	 seasonal_period_monthly	 seasonal_period_weekly	 seasonal_period_daily	 holiday_effect	 spikes_and_dips	 step_changes	 residual
# 0	2013年07月01日 00:00:00+00:00	Central Park S & 6 Ave	 history	 69.0	 154.168527	 32.572948	 <NA>	 <NA>	 <NA>	 0.0	 35.477484	 <NA>	 <NA>	 -28.402102	 <NA>	 <NA>	 0.0	 -85.168527	 147.093145
# 1	2013年07月01日 00:00:00+00:00	Grand Army Plaza & Central Park S	 history	 79.0	 79.0	 24.982769	 <NA>	 <NA>	 <NA>	 0.0	 43.46428	 <NA>	 <NA>	 -30.01599	 <NA>	 <NA>	 0.0	 0.0	 65.55171
# 2	2013年07月02日 00:00:00+00:00	Central Park S & 6 Ave	 history	 180.0	 204.045651	 32.572948	 <NA>	 <NA>	 <NA>	 147.093045	 72.498327	 <NA>	 <NA>	 -15.545721	 <NA>	 <NA>	 0.0	 -85.168527	 61.122876
# 3	2013年07月02日 00:00:00+00:00	Grand Army Plaza & Central Park S	 history	 129.0	 99.556269	 24.982769	 <NA>	 <NA>	 <NA>	 65.551665	 45.836432	 <NA>	 <NA>	 -11.831828	 <NA>	 <NA>	 0.0	 0.0	 29.443731
# 4	2013年07月03日 00:00:00+00:00	Central Park S & 6 Ave	 history	 115.0	 205.968236	 32.572948	 <NA>	 <NA>	 <NA>	 191.32754	 59.220766	 <NA>	 <NA>	 -44.580071	 <NA>	 <NA>	 0.0	 -85.168527	 -5.799709

The output rows are ordered first by time_series_timestamp, then chronologically by the start_station_name column value. In time series forecasting, the prediction interval, as represented by the prediction_interval_lower_bound and prediction_interval_upper_bound column values, is as important as the forecast_value column value. The forecast_value value is the middle point of the prediction interval. The prediction interval depends on the standard_error and confidence_level column values.