Make predictions with remote models on Vertex AI

You can register a Vertex AI endpoint as a remote model and call it directly from BigQuery with ML.PREDICT.

This can be helpful when a model is too large to import into BigQuery or when you want to use a single point of inference for online, batch, and micro-batch use cases.

This tutorial uses a customized sentiment analysis model by fine-tuning a BERT model with plain-text IMDB movie reviews. The resulting model uses text input (movie reviews) and returns sentiment scores between (0, 1). The model is registered in Vertex AI Model Registry and served on a Vertex AI endpoint. From there the model is added to BigQuery as a remote model. You can use the remote model within BigQuery to get sentiment predictions for a text column (reviews of movies from the 100,000 row table bigquery-public-data.imdb.reviews.

See the BQML Remote Model Tutorial for a Python version GitHub tutorial.

Workflow overview

• Tutorial Setup
• Create ML Model
• Deploy Model on Vertex AI
• Create BigQuery ML Remote Model
• Clean Up

Tutorial Setup

This tutorial uses the following billable components of Google Cloud: Cloud Storage, Vertex AI and BigQuery. At then end of the tutorial, you will remove the billable components.

1. Click here to Enable APIs for Vertex AI, Cloud Storage, and BigQuery Cloud Resource Connections.
2. Cloud Storage: to create a Bucket in the default US multi-region following these instructions.

Create ML model

Create a model by using the BQML Remote Model Tutorial, which includes a sentiment analysis prediction model created by fine-tuning a BERT model while adding a classification layer.

We already trained a sample model and uploaded it to gs://cloud-samples-data/bigquery/ml/remote_model_tutorial/ for you to use directly.

Deploy model on Vertex AI

Follow the instructions to register the model in the Vertex AI Model Registry.

Follow the instructions to deploy the model from the Vertex AI Model Registry to a Vertex AI endpoint.

We recommend setting the maximum number of compute nodes. This turns on the autoscaling capability on Vertex AI side and helps the endpoint to process more requests when your BigQuery data table has a large amount of rows.

BigQuery ML remote model

Creating a BigQuery ML remote model has two components: a BigQuery cloud resource connection and a BigQuery remote model that uses the connection.

Create a BigQuery Cloud resource connection

You must have a Cloud resource connection to connect to Vertex AI.

Select one of the following options:

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. To create a connection, click Add, and then click Connections to external data sources.

3. In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).

4. In the Connection ID field, enter a name for your connection.

5. Click Create connection.

6. Click Go to connection.

7. In the Connection info pane, copy the service account ID for use in a later step.

bq

1. In a command-line environment, create a connection:

   bq mk --connection --location=REGION --project_id=PROJECT_ID \
       --connection_type=CLOUD_RESOURCE CONNECTION_ID
   

   The --project_id parameter overrides the default project.

   Replace the following:

   • REGION: your connection region
   • PROJECT_ID: your Google Cloud project ID
   • CONNECTION_ID: an ID for your connection

   When you create a connection resource, BigQuery creates a unique system service account and associates it with the connection.

   Troubleshooting: If you get the following connection error, update the Google Cloud SDK:

   Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
   

2. Retrieve and copy the service account ID for use in a later step:

   bq show --connection PROJECT_ID.REGION.CONNECTION_ID
   

   The output is similar to the following:

   name                          properties
   1234.REGION.CONNECTION_ID     {"serviceAccountId": "[e-mail]"}
   

Terraform

Use the google_bigquery_connection resource.

Note: To create BigQuery objects using Terraform, you must enable the Cloud Resource Manager API.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a Cloud resource connection named my_cloud_resource_connection in the US region:

# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Set up access

Grant the connection's service account the Vertex AI User role. You must grant this role in the same project that you plan to specify in the remote model endpoint URL (or your model project if you are using short-version endpoint) in the Create a BigQuery ML remote model section. Granting the role in a different project results in the error [e-mail] does not have the permission to access resource.

To grant the role, follow these steps:

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click Grant Access.

3. In the New principals field, enter the service account ID that you copied earlier.

4. In the Select a role field, choose Vertex AI, and then select Vertex AI User role.

5. Click Save.

Create your dataset

Create a BigQuery dataset to store your ML model:

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

     The public datasets are stored in the US multi-region. For simplicity, store your dataset in the same location.

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

     

Create a BigQuery ML remote model

To see the input and output schema for the TensorFlow model by sending a request to the endpoint:

An example of a request:

curl -X POST -H "Authorization: Bearer $(gcloud auth print-access-token)" -H "Content-Type: application/json" https://LOCATION-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/endpoints/ENDPOINT_ID:predict -d "{'instances': [{ 'text': 'This is an amazing movie'}, { 'text': 'The movie was terrible'}]}"

An example of a response:

{
  "predictions": [
    [
      [ 0.999410391 ]
    ],
    [
      [ 0.000628352049 ]
    ]
  ]
}

When creating a remote model, the input and output field names and types need to be exactly same as Vertex AI input and output.

For the model with single output, Vertex AI won't return field name. In CREATE MODEL, you can specify any field name.

The following example shows how to create a remote model using a connection:

CREATE OR REPLACE MODEL `PROJECT_ID.bqml_tutorial.bert_sentiment`
    INPUT (text STRING)
    OUTPUT(scores ARRAY<FLOAT64>)
    REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
    OPTIONS(endpoint = 'ENDPOINT_URL')

Get predictions with ML.PREDICT

Get predictions from the remote model within BigQuery using the ML.PREDICT function. Here 10,000 records are selected and sent for prediction. The remote model defaults to a batch size of 128 instances for its requests.

SELECT *
FROM ML.PREDICT (
    MODEL `PROJECT_ID.bqml_tutorial.bert_sentiment`,
    (
        SELECT review as text
        FROM `bigquery-public-data.imdb.reviews`
        LIMIT 10000
    )
)

Supported regions

There are two types of locations in BigQuery:

• A region is a specific geographic place, such as London.
• A multi-region is a large geographic area, such as the United States, that contains two or more geographic places.

Single region

In a BigQuery single region dataset, you can only create a remote model that uses a Vertex AI endpoint deployed in the same region. A remote model in BigQuery single region us-central1 can only use a Vertex AI in us-central1. So for single regions, remote models are only supported in regions that support both Vertex AI and BigQuery.

Multi-regions

In a BigQuery multi-region (US, EU) dataset, you can only create a remote model that uses a remote model deployed in a region within the same large geographic area (US, EU). For example: A remote model in BigQuery US multi-region can only use a Vertex AI endpoint deployed in any single region in the US geographic area, such as us-central1, us-east4, us-west2, etc.

A remote model in BigQuery EU multi-region can only use a Vertex AI endpoint deployed in any single region in member states of the European Union, such as europe-north1, europe-west3, etc.

For more information about BigQuery regions and multi-regions, see the Dataset Locations page. For more information about Vertex AI regions, see the Vertex AI locations.

Using VPC Service Controls

VPC Service Controls is a Google Cloud feature that allows you to set up a secure perimeter to guard against data exfiltration. To use VPC Service Controls with remote models for additional security, follow the VPC Service Controls guide to: Create a service perimeter.

Add the BigQuery project of the query using the remote model into the perimeter. Add the endpoint project into the perimeter and set Vertex AI API in the restricted services based on your endpoint type. For more details, see Vertex AI VPC Service Controls.

Clean Up

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 an appspot.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 the Manage resources page.

   Go to Manage resources

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