{"id":5666,"date":"2024-10-08T15:05:18","date_gmt":"2024-10-08T06:05:18","guid":{"rendered":"https:\/\/blog.since2020.jp\/?p=5666"},"modified":"2024-10-08T15:59:32","modified_gmt":"2024-10-08T06:59:32","slug":"bigquery-translate","status":"publish","type":"post","link":"https:\/\/since2020.jp\/media\/bigquery-translate\/","title":{"rendered":"BigQuery\u3067\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u3092\u7ffb\u8a33ML.TRANSLATE\u95a2\u6570\u306e\u5229\u7528"},"content":{"rendered":"\n

\u672c\u8a18\u4e8b\u3067\u306f\u3001Google Cloud\u4e0a\u3067BigQuery\u3068Cloud Translation API\u3092\u63a5\u7d9a\u3057\u3001US\u30ea\u30fc\u30b8\u30e7\u30f3\u3067\u306e\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u30c7\u30fc\u30bf\u3092\u65e5\u672c\u8a9e\u306b\u7ffb\u8a33\u3057\u3001\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002\u7279\u306b\u3001\u9069\u5207\u306a\u6a29\u9650\u8a2d\u5b9a\u3001\u63a5\u7d9a\u8a2d\u5b9a\u3001\u305d\u3057\u3066ML.TRANSLATE\u95a2\u6570\u3092\u7528\u3044\u305f\u7ffb\u8a33\u3068\u30c7\u30fc\u30bf\u4fdd\u5b58\u307e\u3067\u306e\u6d41\u308c\u3092\u8aac\u660e\u3057\u307e\u3059\u3002<\/p>\n\n\n

\u306f\u3058\u3081\u306b<\/h2>\n

\u672c\u8a18\u4e8b\u3067\u306f\u3001Google Cloud\u4e0a\u3067BigQuery\u3068Cloud Translation API\u3092\u63a5\u7d9a\u3057\u3001US\u30ea\u30fc\u30b8\u30e7\u30f3\u3067\u306e\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u30c7\u30fc\u30bf\u3092\u65e5\u672c\u8a9e\u306b\u7ffb\u8a33\u3057\u3001\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002\u7279\u306b\u3001\u9069\u5207\u306a\u6a29\u9650\u8a2d\u5b9a\u3001\u63a5\u7d9a\u8a2d\u5b9a\u3001\u305d\u3057\u3066ML.TRANSLATE\u95a2\u6570\u3092\u7528\u3044\u305f\u7ffb\u8a33\u3068\u30c7\u30fc\u30bf\u4fdd\u5b58\u307e\u3067\u306e\u6d41\u308c\u3092\u8aac\u660e\u3057\u307e\u3059\u3002<\/span><\/p>\n\n

2. \u5fc5\u8981\u306aAPI\u3068\u6a29\u9650\u306e\u8a2d\u5b9a<\/h2>\n

\u7ffb\u8a33\u3068\u30c7\u30fc\u30bf\u4fdd\u5b58\u306e\u305f\u3081\u306b\u3001\u4ee5\u4e0b\u306eAPI\u3092\u6709\u52b9\u5316\u3057\u3001\u5fc5\u8981\u306a\u6a29\u9650\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/span><\/p>\r\n\u5fc5\u8981\u306aAPI\u306e\u6709\u52b9\u5316<\/span><\/b>\r\n

    \r\n\t
  1. BigQuery API<\/strong><\/span><\/li>\r\n\t
  2. BigQuery Connection API<\/strong><\/span><\/li>\r\n\t
  3. Cloud Translation API<\/strong><\/span><\/li>\r\n<\/ol>\r\n

    Google Cloud\u30b3\u30f3\u30bd\u30fc\u30eb\u304b\u3089\u3001\u5404API\u3092\u6709\u52b9\u5316\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/span><\/p>\r\n\u5fc5\u8981\u306a\u6a29\u9650\u306e\u8a2d\u5b9a<\/span><\/b>\r\n

    \u30b5\u30fc\u30d3\u30b9\u30a2\u30ab\u30a6\u30f3\u30c8\u306b\u306f\u3001\u4ee5\u4e0b\u306e\u6a29\u9650\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002<\/span><\/p>\r\n

      \r\n\t
    • BigQuery Connection\u7ba1\u7406\u8005<\/strong> (roles\/bigquery.connectionAdmin<\/code>)<\/span><\/li>\r\n\t
    • BigQuery \u30e2\u30c7\u30eb\u306e\u4f5c\u6210\u30fb\u5b9f\u884c\u6a29\u9650<\/strong>:<\/span>\r\n
        \r\n\t
      • bigquery.models.create<\/code><\/span><\/li>\r\n\t
      • bigquery.jobs.create<\/code><\/span><\/li>\r\n\t
      • bigquery.models.getData<\/code><\/span><\/li>\r\n<\/ul>\r\n<\/li>\r\n\t
      • Cloud Translation API\u30e6\u30fc\u30b6\u30fc<\/strong> (roles\/cloudtranslate.user<\/code>)<\/span><\/li>\r\n\t
      • Service Usage Consumer<\/strong> (roles\/serviceusage.serviceUsageConsumer<\/code>)<\/span><\/li>\r\n<\/ul>\r\n

        \u8a2d\u5b9a\u624b\u9806<\/strong>: IAM\u30da\u30fc\u30b8\u306b\u3066\u3001\u30b5\u30fc\u30d3\u30b9\u30a2\u30ab\u30a6\u30f3\u30c8\u306b\u4e0a\u8a18\u306e\u30ed\u30fc\u30eb\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002Google Cloud\u30b3\u30f3\u30bd\u30fc\u30eb\u3067\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u306eIAM\u30da\u30fc\u30b8\u3092\u958b\u304d\u3001\u9069\u5207\u306a\u30b5\u30fc\u30d3\u30b9\u30a2\u30ab\u30a6\u30f3\u30c8\u306b\u3053\u308c\u3089\u306e\u30ed\u30fc\u30eb\u3092\u8ffd\u52a0\u3057\u307e\u3059\u3002<\/span><\/p>\r\n

        <\/p>\n\n

        3. \u63a5\u7d9a\u306e\u4f5c\u6210 (US\u30ea\u30fc\u30b8\u30e7\u30f3)<\/h2>\n

        \u6b21\u306b\u3001BigQuery\u3068Cloud Translation API\u306e\u63a5\u7d9a\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u4eca\u56de\u306fUS\u30ea\u30fc\u30b8\u30e7\u30f3\u3067\u306e\u63a5\u7d9a\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/span><\/p>\r\n

          \r\n\t
        1. Google Cloud\u30b3\u30f3\u30bd\u30fc\u30eb\u306b\u30a2\u30af\u30bb\u30b9<\/strong>\u3057\u3001[BigQuery]\u30da\u30fc\u30b8\u306b\u79fb\u52d5\u3057\u307e\u3059\u3002<\/span><\/li>\r\n\t
        2. \u5de6\u5074\u306e\u30e1\u30cb\u30e5\u30fc\u304b\u3089\u300c\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u300d\u2192\u300c\u5916\u90e8\u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u3078\u306e\u63a5\u7d9a\u300d\u3092\u9078\u629e\u3057\u3001add<\/code>\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3002<\/span><\/li>\r\n\t
        3. \u63a5\u7d9a\u30bf\u30a4\u30d7<\/strong>\u3068\u3057\u3066\u300c\u30ea\u30e2\u30fc\u30c8\u30e2\u30c7\u30eb\u300d\u3092\u9078\u629e\u3057\u3001\u63a5\u7d9aID\u306b ML_TRA<\/code> \u3092\u8a2d\u5b9a\u3002<\/span><\/li>\r\n\t
        4. \u30ea\u30fc\u30b8\u30e7\u30f3<\/strong>\u306f us<\/code> \u306b\u8a2d\u5b9a\u3057\u3001\u63a5\u7d9a\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/span><\/li>\r\n<\/ol>\r\n

          \u63a5\u7d9a\u304c\u4f5c\u6210\u3055\u308c\u308b\u3068\u3001\u63a5\u7d9aID\u304c projects\/project-dev\/locations\/us\/connections\/ML_TRA<\/code> \u3068\u306a\u308a\u307e\u3059\u3002<\/span><\/p>\r\n

          <\/p>\n\n

          4. \u30a2\u30f3\u30b1\u30fc\u30c8\u30c7\u30fc\u30bf\u306e\u4f5c\u6210<\/h2>\n

          \u7ffb\u8a33\u5bfe\u8c61\u306e\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u306b\u95a2\u3059\u308b\u30a2\u30f3\u30b1\u30fc\u30c8\u30c7\u30fc\u30bf\u3092BigQuery\u306b\u4f5c\u6210\u3057\u307e\u3059\u3002<\/span><\/p>\r\n

          \r\n
          CREATE OR REPLACE TABLE `since-dev.dataset.product_feedback` AS\r\nSELECT \"How easy is it to use the product?\" AS ease_of_use, \"Very easy\" AS feedback, \"en\" AS language\r\nUNION ALL\r\nSELECT \"\u00bfQu\u00e9 tan f\u00e1cil es usar el producto?\", \"Muy f\u00e1cil\", \"es\" AS language\r\nUNION ALL\r\nSELECT \"Quelle est la facilit\u00e9 d'utilisation du produit?\", \"Tr\u00e8s facile\", \"fr\" AS language\r\nUNION ALL\r\nSELECT \"Wie einfach ist es, das Produkt zu verwenden?\", \"Sehr einfach\", \"de\" AS language\r\nUNION ALL\r\nSELECT \"\u88fd\u54c1\u306e\u4f7f\u3044\u3084\u3059\u3055\u306f\u3044\u304b\u304c\u3067\u3059\u304b\uff1f\", \"\u975e\u5e38\u306b\u7c21\u5358\", \"ja\" AS language\r\nUNION ALL\r\nSELECT \"How easy is it to use the product?\", \"Not easy at all\", \"en\" AS language\r\nUNION ALL\r\nSELECT \"\u00bfQu\u00e9 tan f\u00e1cil es usar el producto?\", \"No es f\u00e1cil\", \"es\" AS language\r\nUNION ALL\r\nSELECT \"Quelle est la facilit\u00e9 d'utilisation du produit?\", \"Pas facile\", \"fr\" AS language\r\nUNION ALL\r\nSELECT \"Wie einfach ist es, das Produkt zu verwenden?\", \"Nicht einfach\", \"de\" AS language;<\/code><\/pre>\r\n<\/div>\r\n
           <\/p>\r\n
          \u3053\u308c\u3067\u3001project-dev.dataset.product_feedback<\/span> \u30c6\u30fc\u30d6\u30eb\u304c\u4f5c\u6210\u3055\u308c\u3001\u7570\u306a\u308b\u8a00\u8a9e\u3067\u8a18\u8ff0\u3055\u308c\u305f\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u304c\u4fdd\u5b58\u3055\u308c\u307e\u3059\u3002<\/span><\/p>\r\n
          \"\"<\/p>\n\n
          5. \u30ea\u30e2\u30fc\u30c8\u30e2\u30c7\u30eb\u306e\u4f5c\u6210<\/h2>\n
          \u6b21\u306b\u3001ML.TRANSLATE\u95a2\u6570\u3092\u4f7f\u7528\u3059\u308b\u305f\u3081\u306e\u30ea\u30e2\u30fc\u30c8\u30e2\u30c7\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/span><\/p>\r\n
          \r\n
          CREATE OR REPLACE MODEL\r\n`project-dev.dataset.translation_model`\r\nREMOTE WITH CONNECTION `projects\/project-dev\/locations\/us\/connections\/ML_TRA`\r\nOPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_TRANSLATE_V3');<\/code><\/pre>\r\n<\/div>\r\n
          <\/code><\/span>\u30ea\u30e2\u30fc\u30c8\u30e2\u30c7\u30eb\u4f5c\u6210\u306e\u30dd\u30a4\u30f3\u30c8<\/strong>:<\/span><\/p>\r\n
            \r\n\t
          • REMOTE WITH CONNECTION<\/code> \u3067\u3001\u63a5\u7d9aID projects\/since-dev\/locations\/us\/connections\/ML_TRA<\/code> \u3092\u6307\u5b9a\u3002<\/span><\/li>\r\n\t
          • REMOTE_SERVICE_TYPE<\/code> \u306b CLOUD_AI_TRANSLATE_V3<\/code> \u3092\u8a2d\u5b9a\u3057\u3001Cloud Translation API\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/span><\/li>\r\n<\/ul>\r\n
            <\/p>\n\n
            6. \u7ffb\u8a33\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58<\/h2>\n
            \u7ffb\u8a33\u3055\u308c\u305f\u30c7\u30fc\u30bf\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\u305f\u3081\u306eSQL\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u3067\u3059\u3002<\/span><\/p>\r\n
            \r\n
            CREATE OR REPLACE TABLE `project-dev.dataset.translated_feedback` AS\r\nSELECT *\r\nFROM ML.TRANSLATE(\r\nMODEL `project-dev.dataset.translation_model`,\r\nTABLE `project-dev.dataset.product_feedback`,\r\nSTRUCT('translate_text' AS translate_mode, 'ja' AS target_language_code)\r\n);<\/code><\/pre>\r\n<\/div>\r\n
            \u8aac\u660e<\/strong>:<\/span><\/p>\r\n
              \r\n\t
            • CREATE OR REPLACE TABLE<\/strong>: \u7ffb\u8a33\u3055\u308c\u305f\u30c7\u30fc\u30bf\u3092\u4fdd\u5b58\u3059\u308b\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb prodect-dev.dataset.translated_feedback<\/code> \u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u65e2\u306b\u540c\u540d\u306e\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3059\u308b\u5834\u5408\u306f\u4e0a\u66f8\u304d\u3055\u308c\u307e\u3059\u3002<\/span><\/li>\r\n\t
            • ML.TRANSLATE<\/strong>: \u3053\u306e\u90e8\u5206\u3067\u3001\u7ffb\u8a33\u51e6\u7406\u3092\u884c\u3044\u3001\u305d\u306e\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3057\u307e\u3059\u3002<\/span><\/li>\r\n<\/ul>\r\n
              <\/p>\r\n
              <\/p>\n\n
              7. \u7ffb\u8a33\u7d50\u679c\u306e\u78ba\u8a8d<\/h2>\n
              \u4fdd\u5b58\u3055\u308c\u305f\u30c6\u30fc\u30d6\u30eb project-dev.dataset.translated_feedback<\/span> \u3092\u78ba\u8a8d\u3057\u3001\u7ffb\u8a33\u304c\u6b63\u3057\u304f\u884c\u308f\u308c\u305f\u3053\u3068\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/span><\/p>\r\n
              \r\n
              SELECT * FROM `project-dev.dataset.translated_feedback`;<\/code><\/span><\/pre>\r\n<\/div>\r\n
              \u7ffb\u8a33\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/span><\/p>\r\n
              \u7ffb\u8a33\u7d50\u679c<\/span>:<\/span><\/p>\r\n
              \"\"<\/p>\n\n
              \u307e\u3068\u3081<\/h2>\n
              \u672c\u8a18\u4e8b\u3067\u306f\u3001BigQuery\u3068Cloud Translation API\u3092\u63a5\u7d9a\u3057\u3066\u3001\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u30c7\u30fc\u30bf\u3092\u65e5\u672c\u8a9e\u306b\u7ffb\u8a33\u3057\u3001\u305d\u306e\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\u65b9\u6cd5\u3092\u89e3\u8aac\u3057\u307e\u3057\u305f\u3002\u7ffb\u8a33\u306e\u7d50\u679c\u3092\u6c38\u7d9a\u5316\u3059\u308b\u3053\u3068\u3067\u3001\u5f8c\u7d9a\u306e\u5206\u6790\u3084\u30ec\u30dd\u30fc\u30c8\u4f5c\u6210\u306b\u5f79\u7acb\u3066\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u6a29\u9650\u306e\u8a2d\u5b9a\u3084API\u306e\u6709\u52b9\u5316\u306a\u3069\u3001\u4e8b\u524d\u6e96\u5099\u3092\u3057\u3063\u304b\u308a\u884c\u3046\u3053\u3068\u304c\u91cd\u8981\u3067\u3059\u3002<\/span><\/p>","protected":false},"excerpt":{"rendered":"
              \u672c\u8a18\u4e8b\u3067\u306f\u3001Google Cloud\u4e0a\u3067BigQuery\u3068Cloud Translation API\u3092\u63a5\u7d9a\u3057\u3001US\u30ea\u30fc\u30b8\u30e7\u30f3\u3067\u306e\u88fd\u54c1\u30d5\u30a3\u30fc\u30c9\u30d0\u30c3\u30af\u30c7\u30fc\u30bf\u3092\u65e5\u672c\u8a9e\u306b\u7ffb\u8a33\u3057\u3001\u7d50\u679c\u3092\u65b0\u3057\u3044\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002\u7279 […]<\/p>\n","protected":false},"author":87,"featured_media":2995,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"content-type":"","swell_btn_cv_data":"","footnotes":"","_wp_rev_ctl_limit":""},"categories":[1249],"tags":[108,302,751,750],"class_list":["post-5666","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-knowledge","tag-bigquery","tag-bigquery-ml","tag-translation","tag-750"],"_links":{"self":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/5666","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/users\/87"}],"replies":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/comments?post=5666"}],"version-history":[{"count":0,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/5666\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media\/2995"}],"wp:attachment":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media?parent=5666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/categories?post=5666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/tags?post=5666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}