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

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?

Solution

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

My idea is the following

Create a new Google Sheet with a connected sheet to BigQuery
Get all the data of the table in Google Sheet
Use Google Sheet translation capacity (GOOGLETRANSLATE function)
Create a federated table in BigQuery to read in your Google Sheet

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.