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

Append the following section into your main.tf file.

 ## This creates a cloud resource connection.
 ## Note: The cloud resource nested object has only one output only field - serviceAccountId.
 resource "google_bigquery_connection" "connection" {
    connection_id = "CONNECTION_ID"
    project = "PROJECT_ID"
    location = "REGION"
    cloud_resource {}
}        

Replace the following:

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

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.