CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerytranslation
Fei
Fei

Reputation: 1071

How to use GCP Translation API to process a table in BigQuery?

I have a table in my BigQuery like the following

article_title author
いい天気です    Inoue
富士山絶景      Kojiro
...           ...

The article title column is some Japanese articles. I'd like to use GCP Translation API to translate the article_title column into English and convert the table into the following

article_title_en author
Good weather     Inoue
Mt. Fuji view    Kojiro
...

How do I do this?

Upvotes: 0

Views: 1595

Answers (2)

SANN3
SANN3

Reputation: 10099

We can translate text stored in BigQuery tables by using the Cloud Translation API using ML.TRANSLATE function

# Translate text and parse the json
CREATE TABLE
  `mydataset.translate_result` AS (
  SELECT
    STRING(ml_translate_result.translations[0].detected_language_code) AS `Original Language`,
    text_content AS `Original Text`,
    "zh-CN" AS `Destination Language`,
    STRING(ml_translate_result.translations[0].translated_text) AS Translation,
    ml_translate_status as `Status`
  FROM ML.TRANSLATE(
    MODEL `mydataset.mytranslatemodel`,
    TABLE `mydataset.mybqtable`,
    STRUCT('translate_text' AS translate_mode, 'zh-CN' AS target_language_code)));

SELECT * FROM `mydataset.translate_result`;

Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-translate

Upvotes: 0

guillaume blaquiere
guillaume blaquiere

Reputation: 75960

BigQuery can't call external API. So you can't use Translation API directly from BigQuery.

My idea is the following

Sadly, I can't test the connected sheet feature because it's reserved to paid user, and I no longer have a paid corporate account (I switched company 1 month ago). I can't validate this solution end to end and help you further.

Upvotes: 2

Related Questions