Create a machine learning 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.

   Go to project selector

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

3. Enable the BigQuery and BigQuery Connection APIs.

   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 Vertex AI and partner models
  • Remote models over open models
  • Remote models over Cloud AI services
  • Remote models over custom models in Vertex AI

  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 BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click the dataset that you created.

3. Click more_vert View actionsnext to the dataset, and then click Create BQML Model.

   The Create new modelpane opens.

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

5. 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.

6. Click Continue.

7. In the Creation methodsection, select Train a Model in BigQuery.

8. In the Modeling objectivesection, select a modeling objective for the model.

9. Click Continue.

10. On the Model optionspage, select a model type. The type of model you can select varies based on the modeling objective you chose.

11. In the Training datasection, do one of the following:

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

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

13. If there is a Required optionssection, 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 times 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.

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

15. Click Create model.

16. When model creation is complete, click Go to modelto 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 Vertex AI or partner models
• Models over open models

To create a model:

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click the dataset that you created.

3. Click more_vert View actionsnext to the dataset, and then click Create BQML Model.

   The Create new modelpane opens.

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

5. 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.

6. Click Continue.

7. In the Creation methodsection, select Connect to Vertex AI LLM service and Cloud AI services.

8. On the Model optionspage, select Google and Partner Modelsor Open Modelsfor the model type, as appropriate for your use case.

9. In the Remote connectionsection, do the one of the following:

   • If you have a default connection configured, or if you have both the BigQuery Admin and the Project IAM Admin roles, select Default connection.

   • If you don't have have a default connection configured, or if you lack the appropriate roles, select Cloud resource connection.

     1. For Project, select the project that contains the connection that you want to use.
     2. For Location, select the location used by the connection.

     3. For Connection, select the connection to use for the remote model, or select Create new connectionto create a new connection.

10. In the Required optionssection, do one of the following:

    • For remote models over Google models and partner models, specify the endpoint to use. This is the name of the model, for example gemini-2.0-flash . For more information about supported models, see ENDPOINT .
    • For remote models over open models, copy and paste in the endpoint to use. This is the shared public endpoint of a model deployed to Vertex AI, in the format https:// location -aiplatform.googleapis.com/v1/projects/ project /locations/ location /endpoints/ endpoint_id . For more information, see ENDPOINT .

11. Click Create model.

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

Create a remote model over a custom model

Use this procedure to create remote models over custom models deployed to Vertex AI .

To create a model:

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click the dataset that you created.

3. Click more_vert View actionsnext to the dataset, and then click Create BQML Model.

   The Create new modelpane opens.

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

5. 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.

6. Click Continue.

7. In the Creation methodsection, select Connect to user managed Vertex AI endpoints.

8. In the Remote connectionsection of the Model optionspage, do one of the following:

   • If you have a default connection configured, or if you have both the BigQuery Admin and the Project IAM Admin roles, select Default connection.

   • If you don't have have a default connection configured, or if you lack the appropriate roles, select Cloud resource connection.

     1. For Project, select the project that contains the connection that you want to use.
     2. For Location, select the location used by the connection.

     3. For Connection, select the connection to use for the remote model, or select Create new connectionto create a new connection.

9. In the Required optionssection, specify the endpoint to use. This is the shared public endpoint of a model deployed to Vertex AI, in the format https:// location -aiplatform.googleapis.com/v1/projects/ project /locations/ location /endpoints/ endpoint_id . For more information, see ENDPOINT .

10. Click Create model.

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

Create a remote model over a Cloud AI service

Use this procedure to create remote models over Cloud AI services .

To create a model:

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click the dataset that you created.

3. Click more_vert View actionsnext to the dataset, and then click Create BQML Model.

   The Create new modelpane opens.

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

5. 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.

6. Click Continue.

7. In the Creation methodsection, select Connect to Vertex AI LLM service and Cloud AI services.

8. On the Model optionspage, select Cloud AI Services.

9. In the Remote connectionsection, do the one of the following:

   • If you have a default connection configured, or if you have both the BigQuery Admin and the Project IAM Admin roles, select Default connection.

   • If you don't have have a default connection configured, or if you lack the appropriate roles, select Cloud resource connection.

     1. For Project, select the project that contains the connection that you want to use.
     2. For Location, select the location used by the connection.

     3. For Connection, select the connection to use for the remote model, or select Create new connectionto create a new connection.

10. In the Required optionssection, select the Cloud AI service type to use.

11. In the Optionalsection, specify document processor information if you are using the CLOUD_AI_DOCUMENT_V1 service. Optionally, you can specify speech recognizer information if you are using the CLOUD_AI_SPEECH_TO_TEXT_V2 service.

12. Click Create model.

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

Create an imported model

Use this procedure to create BigQuery ML models by importing the following types of models:

• ONNX
• TensorFlow
• TensorFlow Lite
• XGBoost

To create a model:

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click the dataset that you created.

3. Click more_vert View actionsnext to the dataset, and then click Create BQML Model.

   The Create new modelpane opens.

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

5. 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.

6. Click Continue.

7. In the Creation methodsection, select Import model.

8. On the Model optionspage, select the type of model that you want to import.

9. For GCS path, browse for or paste in the URI for the Cloud Storage bucket that contains the model.

10. Click Create model.

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