The ML.TRANSLATE function

This document describes theML.TRANSLATE function, which lets you translatetext stored in BigQuery tables by using the Cloud Translation API.

Syntax

ML.TRANSLATE(  MODEL `PROJECT_ID.DATASET.MODEL`,  { TABLE `PROJECT_ID.DATASET.TABLE` | (QUERY_STATEMENT) },  STRUCT('MODE_NAME' AS translate_mode [, 'TARGET_LANGUAGE_CODE' AS target_language_code ]))

Arguments

ML.TRANSLATE takes the following arguments:

• PROJECT_ID: the project that contains theresource.

• DATASET: the dataset that contains theresource.

• MODEL: the name of aremote modelwith aREMOTE_SERVICE_TYPEofCLOUD_AI_TRANSLATE_V3.

• TABLE: the name of the BigQuery table that contains textdata. The text analysis is applied on the column namedtext_content inthis table. If your table does not have atext_content column, use aSELECTstatement for this argument to provide an alias for an existing table column,as shown in the following example:

  SELECT*fromML.TRANSLATE(MODEL`mydataset.mymodel`,(SELECTcommentAStext_contentfrommydataset.mytable),STRUCT('translate_text'AStranslate_mode,'en'AStarget_language_code));

  An error occurs if notext_content column is available.

• QUERY_STATEMENT: a query whose result contains thetext data. The text analysis is applied on the column in the query namedtext_content. You can alias an existing table column astext_content ifnecessary. For information about the supported SQL syntax of theQUERY_STATEMENT clause, seeGoogleSQL query syntax.

• MODE_NAME: aSTRING value that specifies one of thefollowing supported translation modes:

  • TRANSLATE_TEXT

  • DETECT_LANGUAGE

• TARGET_LANGUAGE_CODE: aSTRING value that specifiesasupported language code for translation. Thisargument is only required when you use theTRANSLATE_TEXT translation mode.

Output

ML.TRANSLATE returns the input table plus the following columns:

• ml_translate_result: aJSON value that contains the translation resultfrom Cloud Translation API.
• ml_translate_status: aSTRING value that contains the API response statusfor the corresponding row. This value is empty if the operation wassuccessful.

Quotas

SeeCloud AI service functions quotas and limits.

Known issues

Sometimes after a query job that uses this function finishes successfully,some returned rows contain the following error message:

Aretryableerroroccurred:RESOURCEEXHAUSTEDerrorfrom<remoteendpoint>

This issue occurs because BigQuery query jobs finish successfullyeven if the function fails for some of the rows. The function fails when thevolume of API calls to the remote endpoint exceeds the quota limits for thatservice. This issue occurs most often when you are running multiple parallelbatch queries. BigQuery retries these calls, but if the retriesfail, theresource exhausted error message is returned.

To iterate through inference calls until all rows are successfully processed,you can use theBigQuery remote inference SQL scriptsor theBigQuery remote inference pipeline Dataform package.

Locations

ML.TRANSLATE must run in the same region as the remote model that thefunction references. For more information about supported locations for modelsbased on the Cloud Translation API, seeLocations for remote models.

Example

Example 1

The following example applies text translation on the column nametext_content on the bq tablemybqtable inmydataset to Chinese.

#CreatemodelCREATEORREPLACEMODEL`myproject.mydataset.mytranslatemodel`REMOTEWITHCONNECTION`myproject.myregion.myconnection`OPTIONS(remote_service_type='cloud_ai_translate_v3')

#TranslatetextSELECT*FROMML.TRANSLATE(MODEL`mydataset.mytranslatemodel`,TABLE`mydataset.mybqtable`,STRUCT('translate_text'AStranslate_mode,'zh-CN'AStarget_language_code));

The output is similar to the following:

ml_translate_result|ml_translate_status|text_content|-------|--------|--------{"glossary_translations":[],"translation_memory_translations":[],"translations":[{"detected_language_code":"en","translated_text":"苹果"}]}||apple

Example 2

The following example translates the text in the columntext_content in thetablemybqtable to Chinese, and parses the JSON results into separate columns.

#TranslatetextandparsethejsonCREATETABLE`mydataset.translate_result`AS(SELECTSTRING(ml_translate_result.translations[0].detected_language_code)AS`OriginalLanguage`,text_contentAS`OriginalText`,"zh-CN"AS`DestinationLanguage`,STRING(ml_translate_result.translations[0].translated_text)ASTranslation,ml_translate_statusas`Status`FROMML.TRANSLATE(MODEL`mydataset.mytranslatemodel`,TABLE`mydataset.mybqtable`,STRUCT('translate_text'AStranslate_mode,'zh-CN'AStarget_language_code)));SELECT*FROM`mydataset.translate_result`;

The output is similar to the following:

OriginalLanguage|OriginalText|DestinationLanguage|Translation|Status|-------|--------|--------|--------|--------en|apple|zh-cn.|苹果||

If you get an error likequery limit exceeded, you might have exceeded thequota for this function, whichcan leave you with unprocessed rows. Use the following query to completeprocessing the unprocessed rows:

CREATETABLE`mydataset.translate_result_next`AS(SELECTSTRING(ml_translate_result.translations[0].detected_language_code)AS`OriginalLanguage`,text_contentAS`OriginalText`,'zh-CN'AS`DestinationLanguage`,STRING(ml_translate_result.translations[0].translated_text)ASTranslation,ml_translate_statusas`Status`FROMML.TRANSLATE(MODEL`mydataset.mytranslatemodel`,(SELECT`OriginalText`AStext_contentFROM`mydataset.translate_result`WHEREStatus!=''),STRUCT('translate_text'AStranslate_mode,'zh-CN'AStarget_language_code)));SELECT*FROM`mydataset.translate_result_next`;

What's next

• Get step-by-step instructions on how totranslate text in a BigQuery tableusing theML.TRANSLATE function.
• Learn more aboutother functions you can useto analyze BigQuery data.
• For information about model inference, seeModel inference overview.
• For more information about supported SQL statements and functions forgenerative AI models, seeEnd-to-end user journeys for generative AI models.