Create an ML model in BigQuery ML by using the Google Cloud console

This document shows you how to use the Google Cloud console to create a BigQuery ML model.

Required roles

• To create a model and run inference, you must be granted the following roles:

  • BigQuery Data Editor (roles/bigquery.dataEditor)
  • BigQuery User (roles/bigquery.user)

Before you begin

1. 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 the resourcemanager.projects.create permission. Learn how to grant roles.

   Go to project selector

2. Verify that billing is enabled for your Google Cloud project.

3. Enable the BigQuery and BigQuery Connection APIs.

   Roles required to enable APIs

   To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

   Enable the APIs

Model-specific prerequisites

Before you create a model, make sure that you have addressed any prerequisites for the type of model that you are creating:

• If you want to use a query to select training data for a model, you must have that query available as a saved query.

• Matrix factorization models require reservations. For more information, see Pricing.

• The following remote models require a Cloud resource connection:

  • Remote models over Gemini Enterprise Agent Platform and partner models
  • Remote models over open models
  • Remote models over Cloud AI services
  • Remote models over custom models in Gemini Enterprise Agent Platform

  The connection's service account must also be granted certain roles, depending on the type of remote model.

• To import a model, you must have that model uploaded to a Cloud Storage bucket.

Create a dataset

Create a BigQuery dataset to contain your resources:

Create an internally or externally trained model

Use this procedure to create the following types of models:

• Time series models:

  • ARIMA_PLUS
  • ARIMA_PLUS_XREG

• Contribution analysis: Contribution analysis

• Classification:

  • Logistic regression
  • Boosted tree classification
  • Random forest classification
  • Deep Neural network (DNN) classification
  • Wide-and-deep classification
  • AutoML classification

• Regression:

  • Linear regression
  • Boosted tree regression
  • Random forest regression
  • Deep Neural network (DNN) regression
  • Wide-and-deep regression
  • AutoML regression

• Clustering: K-means

• Recommendation: Matrix factorization

• Dimensionality reduction:

  • Principal component analysis (PCA)
  • Autoencoder

These models have different sets of options according to their type. While BigQuery ML automatic tuning works well in most cases, you can choose to manually tune your model as part of the procedure. If you want to do so, refer to the documentation for the given type of model to learn more about the model options.

To create a model:

1. Go to the BigQuery page.

   Go to BigQuery

2. In the left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left pane to open the pane.

3. In the Explorer pane, click Datasets, and then click the dataset that you created.

4. Click more_vert View actions next to the dataset, and then click Create BQML Model.

   The Create new model pane opens.

5. For Model name, type a name for the model.

6. If you want to create a saved query that contains the CREATE MODEL statement for the model, select Save Query .

   1. For Query name, type a name for the saved query.
   2. For Region, choose a region for the saved query.

7. Click Continue.

8. In the Creation method section, select Train a Model in BigQuery.

9. In the Modeling objective section, select a modeling objective for the model.

10. Click Continue.

11. On the Model options page, select a model type. The type of model you can select varies based on the modeling objective you chose.

12. In the Training data section, do one of the following:

    • Select Table/View to get training data from a table or view, and then select the project, dataset, and view or table name.
    • Select Query to get training data from a saved query, and then select the saved query.

13. In Selected input label columns, choose the columns from the table, view, or query that you want to use as input to the model.

14. If there is a Required options section, specify the requested column information:

    • For classification and regression models, for INPUT_LABEL_COLS, select the column that contains the label data.

    • For matrix factorization models, select the following:

      • For RATING_COL, select the column that contains the rating data.
      • For USER_COL, select the column that contains the user data.
      • For ITEM_COL, select the column that contains the item data.

    • For time series forecasting models, select the following:

      • For TIME_SERIES_TIMESTAMP_COL, select the column that contains the time points to use when training the model.
      • For TIME_SERIES_DATA_COL, select the column that contains the data to forecast.

15. Optional: In the Optional section, specify values for additional model tuning arguments. The arguments that are available vary based on the type of model that you are creating.

16. Optional: If there's a Hyperparameter tuning section, you can specify the NUM_TRIALS option to enable [hyperparameter tuning](/bigquery/docs/hyperparameter-tuning-tutorial for your model. The arguments that are available for hyperparameter tuning vary based on the type of model that you're creating.

17. Click Create model.

18. When model creation is complete, click Go to model to view model details.

Create a remote model over a pre-trained model

Use this procedure to create the following types of remote models:

• Models over Agent Platform or partner models
• Models over open models