Translate text with the ML.TRANSLATE function

This document describes how to use the ML.TRANSLATE function with a remote model to translate text from a BigQuery standard table.

For information about model inference in BigQuery ML, see Model inference overview.

For information about supported model types of each SQL statement and function, and all supported SQL statements and functions for each model type, read End-to-end user journey for each model.

Required permissions

• To create a connection, you need membership in the following role:

  • roles/bigquery.connectionAdmin

• To grant permissions to the connection's service account, you need the following permission:

  • resourcemanager.projects.setIamPolicy

• To create the model using BigQuery ML, you need the following permissions:

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

• To run inference, you need the following permissions:

  • bigquery.tables.getData on the table
  • bigquery.models.getData on the model
  • bigquery.jobs.create

Before you begin

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

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

   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

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the BigQuery, BigQuery Connection API, and Cloud Translation APIs.

   Enable the APIs

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

   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

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the BigQuery, BigQuery Connection API, and Cloud Translation APIs.

   Enable the APIs

Create a connection

Create a cloud resource connection and get the connection's service account.

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.

Grant access to the service account

Select one of the following options:

Console

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click Add.

   The Add principals dialog opens.

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

4. In the Select a role field, select Service Usage, and then select Service Usage Consumer.

5. Click Add another role.

6. In the Select a role field, select BigQuery, and then select BigQuery Connection User.

7. Click Add another role.

8. In the Select a role field, select Cloud Translation, and then select Cloud Translation API User.

9. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command:

gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/serviceusage.serviceUsageConsumer' --condition=None
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/bigquery.connectionUser' --condition=None
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/cloudtranslate.user' --condition=None

Replace the following:

• PROJECT_NUMBER: your project number.
• MEMBER: the service account ID that you copied earlier.

Failure to grant the permission results in an error.

Create a model

Create a remote model with a REMOTE_SERVICE_TYPE of CLOUD_AI_TRANSLATE_V3:

CREATE OR REPLACE MODEL
`PROJECT_ID.DATASET_ID.MODEL_NAME`
REMOTE WITH CONNECTION PROJECT_ID.REGION.CONNECTION_ID
OPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_TRANSLATE_V3');

Replace the following:

• PROJECT_ID: your project ID.
• DATASET_ID: the ID of the dataset to contain the model. This dataset must be in the same location as the connection that you are using.
• MODEL_NAME: the name of the model.
• REGION: the region used by the connection.
• CONNECTION_ID: the connection ID—for example, myconnection.

  When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example projects/myproject/locations/connection_location/connections/myconnection.

Translate text

Translate text with the ML.TRANSLATE function:

SELECT *
FROM ML.TRANSLATE(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  { TABLE PROJECT_ID.DATASET_ID.TABLE_NAME | (QUERY) },
  STRUCT('MODE' AS translate_mode, ['LANGUAGE' AS target_language_code])
);

Replace the following:

• PROJECT_ID: your project ID.
• DATASET_ID: the ID of the dataset that contains the model.
• MODEL_NAME: the name of the model.
• TABLE_NAME: the name of the table that contains the text to translate in a column named text_content. If the text is in a column with a different name, specify text_content as an alias for that column.
• QUERY: a query that contains the text to translate in a column named text_content. If the text is in a column with a different name, specify text_content as an alias for that column.
• MODE: the name of a supported translation mode.
• LANGUAGE: the name of a supported language code. This argument is only required when you use the TRANSLATE_TEXT translation mode.

Example 1

The following example translates the text from the table's text_content column into Hindi:

SELECT * FROM ML.TRANSLATE(
  MODEL `mydataset.mytranslatemodel`,
  TABLE mydataset.mytable,
  STRUCT('translate_text' AS translate_mode, 'hi' AS target_language_code))
);

Example 2

The following example detects the language of the text in the table's comment column:

SELECT * FROM ML.TRANSLATE(
  MODEL `mydataset.mytranslatemodel`,
  (SELECT comment AS text_content from mydataset.mytable),
  STRUCT('detect_language' AS translate_mode)
);

What's next

Try the Unstructured data analytics with BigQuery ML and Vertex AI pre-trained models notebook.