Forecast hierarchical time series with an ARIMA_PLUS univariate model

This tutorial teaches you how to use anARIMA_PLUS univariate time series modelto forecast hierarchical time series. It forecasts the future valuefor a given column, based on the historical values for that column,and also calculates roll-up values for that column for one or more dimensions ofinterest.

Forecasted values are calculated for each time point, for each value in one ormore columns that specify the dimensions of interest. For example, if you wantedto forecast daily traffic incidents and specified a dimension column containingstate data, the forecasted data would contain values for each day for StateA, then values for each day for State B, and so forth. If you wantedto forecast daily traffic incidents and specified dimension columns containingstate and city data, the forecasted data would contain values for each dayfor State A and City A, then values for each day for State A and CityB, and so forth. In hierarchical time series models,hierarchical reconciliationis used to roll up and reconcile each child time series with its parent. Forexample, the sum of the forecasted values for all of the cities in State A mustbe equal to the forecasted value for State A.

In this tutorial, you create two time series models over the same data, one thatuses hierarchical forecasting and one that doesn't. This lets youcompare the results returned by the models.

This tutorial uses data from the publicbigquery-public-data.iowa_liquor.sales.salestable. This table contains information for over 1 million liquor products indifferent stores using public Iowa liquor sales data.

Before reading this tutorial, we highly recommend that you readForecast multiple time series with a univariate model.

Required Permissions

• To create the dataset, you need thebigquery.datasets.createIAM permission.

• To create the model, you need the following permissions:

  • bigquery.jobs.create
  • bigquery.models.create
  • bigquery.models.getData
  • bigquery.models.updateData

• To run inference, you need the following permissions:

  • bigquery.models.getData
  • bigquery.jobs.create

For more information about IAM roles and permissions inBigQuery, seeIntroduction to IAM.

Objectives

In this tutorial, you use the following:

• Creating a multiple time series model and a multiple hierarchical timeseries model to forecast bottle sales values by using theCREATE MODEL statement.
• Retrieving the forecasted bottle sales values from the models by using theML.FORECAST function.

Costs

This tutorial uses billable components of Google Cloud, including the following:

• BigQuery
• BigQuery ML

For more information about BigQuery costs, see theBigQuery pricing page.

For more information about BigQuery ML costs, seeBigQuery ML pricing.

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to

   Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

Create a dataset

Create a BigQuery dataset to store your ML model.

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

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

Create a time series model

Create a time series model, using the Iowa liquor sales data.

The following GoogleSQL query creates a model that forecasts the dailytotal number of bottles sold in 2015 in Polk, Linn and Scott counties.

In the following query, theOPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', ...)clause indicates that you are creating anARIMA-basedtime series model. You use theTIME_SERIES_ID optionof theCREATE MODEL statement to specify one or more columns in the input datafor which you want to get forecasts. Theauto_arima_max_order optionof theCREATE MODEL statement controls thesearch space for hyperparameter tuning in theauto.ARIMA algorithm. Thedecompose_time_series optionof theCREATE MODEL statement defaults toTRUE, so that information aboutthe time series data is returned when you evaluate the model in the next step.

TheOPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', ...)clause indicates that you are creating anARIMA-basedtime series model. By default,auto_arima=TRUE,so theauto.ARIMA algorithm automatically tunes the hyperparameters inARIMA_PLUS models. The algorithm fits dozens of candidate models and choosesthe best model, which is the model with the lowestAkaike information criterion (AIC).Setting theholiday_region optiontoUS allows a more accurate modeling on those United States holidays timepoints if there are United States holiday patterns in the time series.

Follow these steps to create the model:

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

   Go to BigQuery

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

   CREATEORREPLACEMODEL`bqml_tutorial.liquor_forecast`OPTIONS(MODEL_TYPE='ARIMA_PLUS',TIME_SERIES_TIMESTAMP_COL='date',TIME_SERIES_DATA_COL='total_bottles_sold',TIME_SERIES_ID_COL=['store_number','zip_code','city','county'],HOLIDAY_REGION='US')ASSELECTstore_number,zip_code,city,county,date,SUM(bottles_sold)AStotal_bottles_soldFROM`bigquery-public-data.iowa_liquor_sales.sales`WHEREdateBETWEENDATE('2015-01-01')ANDDATE('2015-12-31')ANDcountyIN('POLK','LINN','SCOTT')GROUPBYstore_number,date,city,zip_code,county;

   The query takes approximately 37 seconds to complete, after which you canaccess theliquor_forecast model. Because the query uses aCREATE MODELstatement to create a model, there are no query results.

Use the model to forecast data

Forecast future time series values by using theML.FORECASTfunction.

In the following query, theSTRUCT(20 AS horizon, 0.8 AS confidence_level) clause indicates that thequery forecasts 20 future time points, and generates a prediction intervalwith a 80% confidence level.

Follow these steps to forecast data with the model:

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

   Go to BigQuery

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

   SELECT*FROMML.FORECAST(MODEL`bqml_tutorial.liquor_forecast`,STRUCT(20AShorizon,0.8ASconfidence_level))ORDERBYstore_number,county,city,zip_code,forecast_timestamp;

   The results should look similar to the following:

   

   The output starts with the forecasted data for the first time series;store_number=2190,zip_code=50314,city=DES MOINES,county=POLK. Asyou scroll through the data, you see the forecasts for each subsequentunique time series. In order to generate forecasts that aggregate totals fordifferent dimensions, such as forecasts for a specific county, you mustgenerate a hierarchical forecast.

Create a hierarchical time series model

Create a hierarchical time series forecast, using the Iowa liquor sales data.

The following GoogleSQL query creates a model that generateshierarchical forecasts for the daily total number of bottles sold in 2015 inPolk, Linn and Scott counties.

In the following query, theHIERARCHICAL_TIME_SERIES_COLS option in theCREATE MODEL statement indicates that you are creating a hierarchical forecastbased on a set of columns that you specify. Each of these columns is rolled upand aggregated. For example, from the earlier query, this means that thestore_number column value is rolled up to show forecasts for eachcounty,city andzip_code value. Separately, bothzip_code andstore_numbervalues are also rolled up to show forecasts for eachcounty andcity value.The column order is important because it defines the structure of the hierarchy.

Follow these steps to create the model:

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

   Go to BigQuery

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

   CREATEORREPLACEMODEL`bqml_tutorial.liquor_forecast_hierarchical`OPTIONS(MODEL_TYPE='ARIMA_PLUS',TIME_SERIES_TIMESTAMP_COL='date',TIME_SERIES_DATA_COL='total_bottles_sold',TIME_SERIES_ID_COL=['store_number','zip_code','city','county'],HIERARCHICAL_TIME_SERIES_COLS=['zip_code','store_number'],HOLIDAY_REGION='US')ASSELECTstore_number,zip_code,city,county,date,SUM(bottles_sold)AStotal_bottles_soldFROM`bigquery-public-data.iowa_liquor_sales.sales`WHEREdateBETWEENDATE('2015-01-01')ANDDATE('2015-12-31')ANDcountyIN('POLK','LINN','SCOTT')GROUPBYstore_number,date,city,zip_code,county;

   The query takes approximately 45 seconds to complete, after which thebqml_tutorial.liquor_forecast_hierarchical model can be accessed in theExplorer pane. Because the query uses aCREATE MODEL statement tocreate a model, there are no query results.

Use the hierarchical model to forecast data

Retrieve hierarchical forecast data from the model by using theML.FORECASTfunction.

Follow these steps to forecast data with the model:

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

   Go to BigQuery

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

   SELECT*FROMML.FORECAST(MODEL`bqml_tutorial.liquor_forecast_hierarchical`,STRUCT(30AShorizon,0.8ASconfidence_level))WHEREcity='LECLAIRE'ORDERBYcounty,city,zip_code,store_number,forecast_timestamp;

   The results should look similar to the following:

   

   Notice how the aggregated forecast is displayed for the city of LeClaire,store_number=NULL,zip_code=NULL,city=LECLAIRE,county=SCOTT. Asyou look at the rest of the rows, notice the forecasts for the othersubgroups. For example, the following image shows the forecasts aggregatedfor the zip code52753,store_number=NULL,zip_code=52753,city=LECLAIRE,county=SCOTT:

   

Clean up

To avoid incurring charges to your Google Cloud account for the resources usedin this tutorial, either delete the project that contains the resources, orkeep the project and delete the individual resources.

• You can delete the project you created.
• Or you can keep the project and delete the dataset.

Delete your dataset

Deleting your project removes all datasets and all tables in the project. If youprefer to reuse the project, you can delete the dataset you created in thistutorial:

1. If necessary, open the BigQuery page in theGoogle Cloud console.

   Go to the BigQuery page

2. In the navigation, click thebqml_tutorial dataset you created.

3. ClickDelete dataset on the right side of the window.This action deletes the dataset, the table, and all the data.

4. In theDelete dataset dialog, confirm the delete command by typingthe name of your dataset (bqml_tutorial) and then clickDelete.

Delete your project

To delete the project:

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to theManage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then clickDelete.
3. In the dialog, type the project ID, and then clickShut down to delete the project.

What's next

• Learn how toforecast a single time series with a univariate model
• Learn how toforecast multiple time series with a univariate model
• Learn how toscale a univariate model when forecasting multiple time series over many rows.
• Learn how toforecast a single time series with a multivariate model
• For an overview of BigQuery ML, seeIntroduction to AI and ML in BigQuery.