Remote functions and Translation API tutorial

This tutorial describes how to create aBigQuery remote function,invoke theCloud Translation API, andperform content translation from any language to Spanish using SQL and Python.

Use cases for this function include the following:

• Translate user comments on a website into a local language
• Translate support requests from many languages into one common language forsupport case workers

Objectives

• Assign necessary roles to your account.
• Create a Cloud Run functions function.
• Create a BigQuery dataset.
• Create a BigQuery connection and service account.
• Grant permissions to the BigQuery service account.
• Create a BigQuery remote function.
• Call the BigQuery remote function.

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery
• Cloud Run functions
• Cloud Translation

To generate a cost estimate based on your projected usage, use thepricing calculator.

Before you begin

We recommend that you create a Google Cloud project for this tutorial.Also, make sure that you have the required roles to complete this tutorial.

Set up a Google Cloud project

To set up a Google Cloud project for this tutorial, complete these steps:

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.

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

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

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery, BigQuery Connection,Cloud Translation, Cloud Run functions, Cloud Build, Cloud Logging,Cloud Pub/Sub, Artifact Registry, and Cloud Run Admin APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the APIs

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

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

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery, BigQuery Connection,Cloud Translation, Cloud Run functions, Cloud Build, Cloud Logging,Cloud Pub/Sub, Artifact Registry, and Cloud Run Admin APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the APIs

Required roles for your account

To get the permissions that you need to perform the tasks in this tutorial, ask your administrator to grant you the following IAM roles on your project:

• BigQuery Data Owner (roles/bigquery.dataOwner)
• BigQuery Connection Admin (roles/bigquery.connectionAdmin)
• Cloud Functions Developer (roles/cloudfunctions.developer)

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to perform the tasks in this tutorial. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Required roles for the Compute Engine default service account

When you enabled the API for Cloud Run functions, aCompute Engine default service accountwas created. To complete this tutorial, you must give thisdefault service account the Cloud Translation API User role.

1. Get the ID assigned to the project.

2. Copy your Compute Engine default service account. Your defaultservice account looks like this:

   PROJECT_NUMBER-compute@developer.gserviceaccount.com

   ReplacePROJECT_NUMBER with your project ID.

3. In the Google Cloud console, go to theIAM page.

   Go to IAM

4. Select your project.

5. Clickperson_addGrant access, and then in theNew principals field, paste theCompute Engine default service account that you copied earlier.

6. In theAssign roles list, search for and selectCloud Translation API User.

7. ClickSave.

Create a Cloud Run functions function

Using Cloud Run functions, create a function that translates input text intoSpanish.

1. Create a Cloud Run functions functionwith the following specifications:

   • ForEnvironment, select2nd gen.
   • ForFunction name, entertranslation-handler.
   • ForRegion, selectus-central1.

   • ForMaximum number of instances, enter10.

     This setting is in theRuntime, build, connections and securitysettings section.

     In this tutorial, we use a lower value than the default to control therequest rate sent to Translation.

   • ForRuntime, selectPython 3.10.

   • ForEntry point, enterhandle_translation.

2. In the file list, selectmain.py, and then paste the following code.

   Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

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

   from__future__importannotationsimportflaskimportfunctions_frameworkfromgoogle.api_core.retryimportRetryfromgoogle.cloudimporttranslate# Construct a Translation Client objecttranslate_client=translate.TranslationServiceClient()# Register an HTTP function with the Functions Framework@functions_framework.httpdefhandle_translation(request:flask.Request)->flask.Response:"""BigQuery remote function to translate input text.    Args:        request: HTTP request from BigQuery        https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#input_format    Returns:        HTTP response to BigQuery        https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#output_format    """try:# Parse request data as JSONrequest_json=request.get_json()# Get the project of the querycaller=request_json["caller"]project=extract_project_from_caller(caller)ifprojectisNone:returnflask.make_response(flask.jsonify({"errorMessage":('project can\'t be extracted from "caller":'f"{caller}.")}),400,)# Get the target language code, default is Spanish ("es")context=request_json.get("userDefinedContext",{})target=context.get("target_language","es")calls=request_json["calls"]translated=translate_text([call[0]forcallincalls],project,target)returnflask.jsonify({"replies":translated})exceptExceptionaserr:returnflask.make_response(flask.jsonify({"errorMessage":f"Unexpected error{type(err)}:{err}"}),400,)defextract_project_from_caller(job:str)->str:"""Extract project id from full resource name of a BigQuery job.    Args:        job: full resource name of a BigQuery job, like          "//bigquery.googleapi.com/projects/<project>/jobs/<job_id>"    Returns:        project id which is contained in the full resource name of the job.    """path=job.split("/")returnpath[4]iflen(path) >4elseNonedeftranslate_text(calls:list[str],project:str,target_language_code:str)->list[str]:"""Translates the input text to specified language using Translation API.    Args:        calls: a list of input text to translate.        project: the project where the translate service will be used.        target_language_code: The ISO-639 language code to use for translation          of the input text. See          https://cloud.google.com/translate/docs/advanced/discovering-supported-languages-v3#supported-target            for the supported language list.    Returns:        a list of translated text.    """location="<your location>"parent=f"projects/{project}/locations/{location}"# Call the Translation API, passing a list of values and the target languageresponse=translate_client.translate_text(request={"parent":parent,"contents":calls,"target_language_code":target_language_code,"mime_type":"text/plain",},retry=Retry(),)# Convert the translated value to a list and return itreturn[translation.translated_textfortranslationinresponse.translations]

   Update<your location> withus-central1.

3. In the file list, selectrequirements.txt, and then paste the followingtext:

   Flask==2.2.2functions-framework==3.9.2google-cloud-translate==3.18.0Werkzeug==2.3.8

4. ClickDeploy and wait for the function to deploy.

5. Click theTrigger tab.

6. Copy theTrigger URL value and save it for later. You must use this URLwhen you create a BigQuery remote function.

Create a BigQuery dataset

Create a BigQuery datasetthat will contain the remote function. When you create the dataset, includethese specifications:

• ForDataset ID, enterremote_function_test.
• ForLocation type, selectMulti-region.
• ForMulti-region, selectUS (multiple regions in United States).

Create a BigQuery connection and service account

Create a BigQuery connection so that you can implement aremote function with any supported languages in Cloud Run functions andCloud Run. When you create a connection, a service account is createdfor that connection.

1. Create a Google Cloud resource connectionwith the following specifications:

   • ForConnection type, selectBigLake and remote functions (Cloud Resource)
   • ForConnection ID, enterremote-function-connection.
   • ForLocation type, selectMulti-region.
   • ForMulti-region, selectUS (multiple regions in United States).

2. Open theConnections listand selectus.remote-function-connection.

3. Copy the service account ID and save it for later. You must grantpermissions to this ID in the next step.

Grant permissions to the BigQuery service account

The service account that you created in the previous step needs permission to useCloud Run so that the BigQuery remote function can usethe Cloud Run functions function. To grant permissions to the service account,complete the following steps:

1. Go to theCloud Run page.

   Go to Cloud Run

2. Select your project.

3. Select the checkbox next totranslation-handler.

4. In thePermissions panel, clickAdd principal.

5. In theNew principals field, enter the service account ID that youcopied earlier.

6. In theAssign roles list, search for and selectCloud Run Invoker.

7. ClickSave.

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following query:

   CREATEORREPLACEFUNCTION`remote_function_test.translate_text`(xSTRING)RETURNSSTRINGREMOTEWITHCONNECTION`us.remote-function-connection`OPTIONS(endpoint='TRIGGER_URL',max_batching_rows=10);

   ReplaceTRIGGER_URL with the trigger URL that yousaved earlier when you created a Cloud Run functions function.

3. ClickRun. A message similar to the following is displayed:

   This statement created a new function namedyour_project.remote_function_test.translate_text.

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 anappspot.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 theManage resources page.

   Go to Manage resources

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

What's next

• Learn how to useremote functions in BigQuery.
• Learn aboutTranslation.
• Learn aboutCloud Run functions.
• Learn aboutCloud Run.