🐙

BigQuery ML 関数のご紹介

2024/03/04に公開

GCP

Google Cloud

BigQuery

tech

はじめに

こんにちは、クラウドエースデータソリューション部の穂戸田です。

クラウドエースデータソリューション部について

クラウドエースのITエンジニアリングを担うシステム開発統括部の中で、特にデータ基盤構築・分析基盤構築からデータ分析までを含む一貫したデータ課題の解決を専門とするのがデータソリューション部です。
弊社では、新たに仲間に加わってくださる方を募集しています。もし、ご興味があればエントリーをお待ちしております！

本記事では、BigQuery ML 関数を一部ご紹介し、SQL のみで画像からのテキスト抽出や言語翻訳、テキスト解析などを行ってみたいと思います。
なお、本記事の最後には画像から抽出したテキストから回答精度を上げる RAG の構築方法をご紹介しておりますので、ご興味のある方はご一読いただけますと幸いです。

この記事はこんな人におすすめ

BigQuery ML 関数にどのようなものがあるか知りたい
BigQuery の SQL でテキスト生成、テキスト解析、言語翻訳、OCR(光学文字認識)、テキスト埋め込みを行う方法を知りたい

BigQuery ML 関数のご紹介

BigQuery ML とは

BigQuery の SQL クエリを通じて機械学習（ML）モデルを作成し、実行することができます。
また、LLM と Cloud AI API を参照できるため、テキスト生成や機械翻訳などの実行も可能です。
主に以下のような利点があります。

SQL クエリで AI モデルの訓練、推論が完結するため、専門知識がなくとも手軽に機械学習モデルの作成、利用が可能
BigQuery のデータに直接アクセスするため、訓練に必要な大量のデータの移動などが必要ない
Cloud AI API を呼び出せるので、SQL で言語翻訳、画像解析、音声解析など幅広いデータソースの形式の分析ができる

詳しくはこちらをご覧ください。

事前準備

後述する各 BigQuery ML 関数を使用するにあたり、以下の事前準備が必要です。

1. BigQuery Connection API の有効化

以下画像ではすでに API が有効になっているため、「管理」の表示になっています。
※API 有効化の詳細についてはこちら
enable_bq_connection

2. 外部接続の作成

BigQuery UI から追加を押下し、
add_bottan
外部データソースへの接続 を選択、
data_source_connection
以下の通り選択し、接続を作成 します。

接続タイプ：Vertex AI リモートモデル、リモート関数、BigLake（Cloud リソース）
接続 ID：任意の名称
ロケーションタイプ：任意のリージョン

3. 外部接続を行うサービスアカウントに Vertex AI へのアクセス権限を付与

「2. 外部接続の作成」時に作成されたサービスアカウントに、IAM コンソールから Vertex AI ユーザー 権限を付与し、保存します。
add_role

4. データセットの作成

後述するリモートモデルを置くためのデータセットを作成します。
データセットID、ロケーションタイプは任意で問題ありません。

5. リモートモデルの作成

リモートモデルとは

リモートモデルとは、BigQuery 上で機械学習モデルなどの API を呼び出す機能です。
リモートモデルを作成することで、BigQuery 上から様々な API を用いた処理が可能になります。
remote_model

「4. データセットの作成」で作成したデータセットにリモートモデルを作成します。
以下クエリでは、例として、CLOUD_AI_LARGE_LANGUAGE_MODEL_V1を呼び出すリモートモデルgenerate_text_modelを作成しています。

-- 事前準備で作成したデータセット内に、リモートモデルを作成する
CREATE MODEL `${PROJECT_ID}.${DATASET_ID}.generate_text_model`
  -- 外部接続を設定したリージョン、接続ID
  REMOTE WITH CONNECTION `us.ai_api_connection`
  -- 使用するモデルのタイプを設定
  OPTIONS(REMOTE_SERVICE_TYPE = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1')

リモートモデル作成時に設定する REMOTE_SERVICE_TYPE ごとに、利用できる BigQury ML の関数は異なるため、実行したい関数にあったリモートモデルを作成しましょう。
以下は本記事でご紹介する BigQuery ML 関数の一覧です。

利用可能な関数	関数の機能	REMOTE_SERVICE_TYPE/END_POINT	API
ML.GENERATE_TEXT	テキスト生成、感情分析	`CLOUD_AI_LARGE_LANGUAGE_MODEL_V1`	`text-bison`, `gemini-pro`
ML.UNDERSTAND_TEXT	テキストの解析	`CLOUD_AI_NATURAL_LANGUAGE_V1`	`CLoud Natural Language API`
ML.TRANSLATE	テキストの翻訳	`CLOUD_AI_TRANSLATE_V3`	`Cloud Translation API`
ML.ANNOTATE_IMAGE	画像解析、ラベル生成	`CLOUD_AI_VISION_V1`	`Cloud Vision API`
ML.GENERATE_EMBEDDING	テキスト埋め込み	`textembedding-gecko(multilingual)`	左記同様

1. ML.GENERATE_TEXT

LLM の API を呼び出し、入力されたテキストに対して LLM からの応答を得ることができます。

テキストの生成

下記クエリで、「こんにちは、世界！」という入力に対して出力を生成してみます。

LLM モデルを使用したテキスト生成

SELECT
  -- 出力から入力に対して生成された部分のみを抽出
  JSON_EXTRACT_SCALAR(ml_generate_text_result, '$.predictions[0].content') 
AS extracted_content
FROM
  ML.GENERATE_TEXT(
    -- REMOTE_SERVICE_TYPE = CLOUD_AI_LARGE_LANGUAGE_MODEL_V1 のリモートモデル
    MODEL `${PROJECT_ID}.${DATASET_ID}.generate_text_model`,
    (SELECT 'こんにちは、世界！' AS prompt),
    STRUCT(
      0.8 AS temperature,
      30 AS max_output_tokens));

各パラメータ

temperature：生成するテキストのランダム性を制御する値。設定可能な値は[0.0, 1.0]であり、値が小さいほどランダム性が低い。
number_of_output_tokens：出力されるレスポンスの長さを制御する。設定可能な値は[1, 1024]。

出力

テキスト生成クエリの実行時におけるパラメータによって回答を制御できます。
今回は max_output_tokens を 30 に設定したため、回答が途中で途切れています。
当たり前ですが、入力テキストで「簡潔に回答して」といった旨の文言を追加することで、短い回答を生成させることも可能です
output_gen_text

2. ML.UNDERSTAND_TEXT

Cloud Natural Language API を呼び出し、入力されたテキストを解析することができます。

なお、ML.UNDERSTAND_TEXT 関数を使用するには、事前準備に加えて以下の対応が必要です。

Cloud Natural Language API の有効化
外部接続を行うサービスアカウントに Service Usage ユーザー権限を付与

テキストの解析

関数実行時には、以下の解析方法のいずれかを指定する必要があります。

ANALYZE_ENTITIES：固有名詞の抽出と分類
ANALYZE_ENTITY_SENTIMENT：エンティティ分析と感情分析を組み合わせて感情を分析（肯定的/否定的）
ANALYZE_SENTIMENT：感情分析（肯定的/否定的/中立）
ANALYZE_SYNTAX：テキストの構造化
CLASSIFY_TEXT：文章で言及されているトピックを分類（分類リスト）

今回は ANALYZE_SYNTAX を使用し、AI によりテキストを構造ごとに細分化してみます。

テキスト解析

WITH text_analysis AS (
  SELECT
    JSON_EXTRACT_ARRAY(ml_understand_text_result, '$.tokens') AS tokens
  FROM
    ML.UNDERSTAND_TEXT(
      -- REMOTE_SERVICE_TYPE = CLOUD_AI_NATURAL_LANGUAGE_V1 のリモートモデル
      MODEL `${PROJECT_ID}.${DATASET_ID}.understand_text`,
      (
        SELECT
          -- 解析対象テキスト
          
'私は穂戸田です。東京都にあるクラウドエースという会社で働いています。' AS 
text_content
      ),
      -- 言語の構造を分析するよう指定
      STRUCT('ANALYZE_SYNTAX' AS nlu_option)
  )
)

SELECT
  -- 出力された文の構造ごとに / で結合
  STRING_AGG(JSON_EXTRACT_SCALAR(token, '$.text.content'), ' / ') AS 
extracted_contents
FROM
  text_analysis,
  UNNEST(tokens) AS token

出力

以下の出力からわかるとおり、かなり正確に文の構造を理解していそうです。
本来の出力には、解析されたテキスト言語や、構造同士の依存関係、品詞などを表すパラメータもあるため、より詳細な文節ごとの分析も可能です。
output_understand

3. ML.TRANSLATE

Cloud Translation API を呼び出し、入力されたテキストを特定の言語に翻訳することができます。

なお、ML.TRANSLATE 関数を使用するには、事前準備に加えて以下の対応が必要です。

Cloud Translation API の有効化
外部接続を行うサービスアカウントに Cloud Translation API ユーザー権限を付与

言語翻訳

関数実行時に以下のいずれかを指定する必要があります。

TRANSLATE_TEXT：テキストの翻訳
DETECT_LANGUAGE：言語の検出、判別

今回は、TRANSLATE_TEXT を使用し、日本語を英語に翻訳してみます。

言語翻訳

SELECT
  -- 翻訳された文章
  JSON_EXTRACT_SCALAR(ml_translate_result, '$.translations[0].translated_text') AS translated_text,
  -- 入力されたテキストの言語の種類
  JSON_EXTRACT_SCALAR(ml_translate_result, '$.translations[0].detected_language_code') AS detected_language_code
FROM
  ML.TRANSLATE(
    -- REMOTE_SERVICE_TYPE = CLOUD_AI_TRANSLATE_V3 のリモートモデル
    MODEL `${PROJECT_ID}.${DATASET_ID}.translate`,
    (
      SELECT
        '私の名前はほたてです。東京都にあるクラウドエースという会社で働いています。' AS text_content
    ),
    STRUCT(
      -- 入力テキストの翻訳を行うよう指定
      'TRANSLATE_TEXT' AS translate_mode,
      -- なんの言語に翻訳するかを指定
      'en' AS target_language_code
    )
  );

出力

以下の出力の通り、入力テキストを日本語と認識し、英語に翻訳できていることが分かります。
output_translate

4. ML.ANNOTATE_IMAGE

Cloud Vision API を呼び出し、画像解析を行うことができます。

なお、ML.ANNOTATE_IMAGE 関数を使用するには、事前準備に加えて以下の対応が必要です。

Cloud Vision API の有効化
外部接続を行うサービスアカウントに以下権限を付与
- Cloud Vision API ユーザー
- Storage Object ユーザー

画像解析

関数実行時には、以下の解析方法のいずれかを指定する必要があります。

FACE_DETECTION：顔、感情の検出
LANDMARK_DETECTION：人工物や自然構造物体の検出
LOGO_DETECTION：ロゴの検出
LABEL_DETECTION：物体の検出、判別
TEXT_DETECTION：画像ファイルからテキストを検出
DOCUMENT_TEXT_DETECTION：PDF ファイルからテキストを検出
IMAGE_PROPERTIES：画像内の色の割合を検出
OBJECT_LOCALIZATION：画像における各物体の領域、境界を識別

今回は TEXT_DETECTION を使用し、画像ファイルからテキストの検出を行います。
※TEXT_DETECTION で解析できる画像ファイルの形式
今回、画像解析には就業規則の書式・文例テンプレート（Word・ワード）から就業規則のテンプレートを読み込み対象として使用しました。

実際に読み込んだ画像

就業規則の条項ごとに画像ファイルを分けて読み込みんでいます。

画像 1
画像 2
画像 3
画像 4
画像 5
画像 6

画像からテキストを検出する

SELECT
  -- 検出されたテキストを出力
  JSON_EXTRACT_SCALAR(ml_annotate_image_result, '$.full_text_annotation.text') AS extracted_text
FROM
  ML.ANNOTATE_IMAGE(
    -- REMOTE_SERVICE_TYPE = CLOUD_AI_VISION_V1 のリモートモデル
    MODEL `${PROJECT_ID}.${DATASET_ID}.annotate_image`,
  -- 非構造化データを参照するオブジェクトテーブルパス
  TABLE `${PROJECT_ID}.${DATASET_ID}.sample_image`,
  -- 画像内からテキストを検出するよう指定
  STRUCT(['TEXT_DETECTION'] AS vision_features));

クエリで画像データを参照するには

Cloud Storage に画像ファイルをアップロード
外部データソースとして Cloud Storage を参照するテーブルを作成

画像データからテキストを抽出

-- 画像データを持つテーブル名
CREATE OR REPLACE EXTERNAL TABLE `${DATASET}.sample_image`
-- Cloud Resource に接続できる外部接続ID
WITH CONNECTION `us.ai_api_connection`
OPTIONS(
  object_metadata = 'SIMPLE',
-- 非構造化データを持つバケットの URI
  uris = ['gs://hoge/*']
);

参考：オブジェクトテーブルを作成する

出力

以下の出力の通り、画像ファイルからテキストの抽出が完了しました。
output_annotate_image

5. ML.GENERATE_EMBEDDING

textembedding-gecko または textembedding-gecko-mutilingual を参照するリモートモデルでテキスト埋め込みを行い、1 つの文章から 768 個(次元)のベクトルを生成します。

なお、ML.GENERATE_EMBEDDING 関数を使用するには、
REMOTE_SERVICE_TYPE の指定ではなく、END_POINT によるリモートモデルの作成が必要です。
これは、関数実行時にリモートモデル用の API が参照されるのではなく、Vertex AI にある基盤モデルを参照されるためです。
以下のクエリにより作成できます。

CREATE OR REPLACE MODEL `${PROJECT_ID}.${DATESET_ID}.text_embedding`
REMOTE WITH CONNECTION `us.ai_api_connection`
  OPTIONS (ENDPOINT = 'textembedding-gecko-multilingual');

テキストのベクトル化（text-embedding）

以下クエリで、自然言語テキストをベクトル化(数値化)することができます。

テキスト埋め込み

SELECT
    *
FROM
  ML.GENERATE_EMBEDDING(
    -- ENDPOINT = textembedding-gecko-multilingual のリモートモデル
    MODEL `${PROJECT_ID}.${DATASET_ID}.embedding`,
      (
      SELECT
        -- ベクトル化するテキスト
        '私は穂戸田です。東京都にあるクラウドエースという会社で働いています。' AS content
      ),
      STRUCT(TRUE AS flatten_json_output));

出力

先程のクエリにより、以下のように入力テキストのベクトル化が完了しました。
また、ベクトル化されたデータに対して、ML_DISTANCE 関数などによりベクトル検索を行うことで、ベクトルデータ間の距離を測ることができます。
output_embedding

非構造化データから得たデータをもとに自然言語テキストの応答を生成

最後に、本記事でご紹介した関数を用いて、画像ファイルから抽出したテキストを利用して LLM から回答を得る RAG を構築してみます。
なお、RAG の構築に関しては以下の記事を参考にしているため、詳細は以下をご覧ください。

rag_pipeline_image
画像ファイル内のテキストをもとに回答を生成

質問文は「採用の際に必要なものはなんですか」とするため、以下の画像から抽出したテキストがベクトル検索結果から選択され、LLM に参考情報として入力されれば成功です。

以下のクエリで、画像ファイルからテーブルを作成し、LLM に質問文を投げるまで一貫して行います。

DECLARE question_text STRING 
  DEFAULT "採用の際に必要なものはなんですか？";

-- 画像データのあるバケットからテーブルを作成
CREATE OR REPLACE EXTERNAL TABLE `${DATASE_ID}.text_image`
WITH CONNECTION `us.ai_api_connection`
OPTIONS(
  object_metadata = 'SIMPLE',
-- 非構造化データを持つバケットの URI
  uris = ['gs://hoge/*.png']
);

-- 質問文をベクトル化
WITH embedded_question AS (
  SELECT
    *
  FROM
    ML.GENERATE_EMBEDDING(
      MODEL `${PROJECT_ID}.${PROJECT_ID}.embedding`,
      (
        SELECT
          question_text AS content
      ),
      STRUCT(TRUE AS flatten_json_output))
),

-- 画像データからテキストを抽出
annotate_image AS (
  SELECT
    JSON_EXTRACT_SCALAR(ml_annotate_image_result, '$.full_text_annotation.text') AS text_content
  FROM
    ML.ANNOTATE_IMAGE(
      MODEL `${PROJECT_ID}.${PROJECT_ID}.annotate_image`,
      TABLE `${PROJECT_ID}.${PROJECT_ID}.text_image`,
      STRUCT(['TEXT_DETECTION'] AS vision_features))
),


-- 画像から抽出したテキストをベクトル化
embedded_translated AS (
  SELECT
    *
  FROM
    ML.GENERATE_EMBEDDING(
      MODEL `${PROJECT_ID}.${PROJECT_ID}.embedding`,
      (
        SELECT
          text_content AS content
        FROM
          annotate_image
      ),
      STRUCT(TRUE AS flatten_json_output))
),

-- ベクトル化したテキストから質問文とベクトル距離(関連度合い)がもっとも近いデータを取得
vector_search_result AS (
  SELECT
    q.content AS question,
    t.content AS reference,
    -- コサイン類似度をもとにベクトル間の距離を算出
    ML.DISTANCE(q.ml_generate_embedding_result, t.ml_generate_embedding_result, 'COSINE') AS cosine_vector_distance
  FROM
    embedded_question AS q,
    embedded_translated AS t
  ORDER BY
    cosine_vector_distance
  LIMIT 1
),

-- プロンプトを作成する
prompt_text AS (
  SELECT
    CONCAT(
      '上記の情報をもとに、下記の質問文に回答してください。',
      ' 質問文：', question,
      ' 参考情報：', reference
    ) AS prompt
  FROM
    vector_search_result
)

-- 作成したプロンプトを LLM に入力
SELECT 
STRING(ml_generate_text_result.predictions[0].content) AS answer
FROM
ML.GENERATE_TEXT( MODEL `${PROJECT_ID}.${PROJECT_ID}.llm_model`,
  (SELECT * FROM prompt_text),
  STRUCT(
    0.2 AS temperature,
    1000 AS max_output_tokens)
);

出力

以下の通り、正確な回答が出力されており、べクトル検索結果から適切な参考情報を取得できているようです。
llm_answer

参考

まとめ

今回は BigQuery ML 関数を一部ご紹介しました。
本記事で紹介した RAG の構築では ML.ANNOTATE_IMAGE 関数を用いて画像ファイルからテキストを抽出していますが、他にも音声ファイルからテキストを抽出する ML.TRANSCRIBE 関数(2024/02/26時点 Preview)などもあるため、RAG を作成する際のデータソースの幅は非常に広いと言えるでしょう。
また、記事執筆(2024/02/26)時点においては ML.GENERATE_TEXT 関数のみ gemini-pro を利用可能ですが、Gemini はマルチモーダルな LLM のため、LLM 関連の API が全て Gemini API に統一されることも夢ではないかもしれません。

はじめに

この記事はこんな人におすすめ

BigQuery ML 関数のご紹介

BigQuery ML とは

事前準備

1. BigQuery Connection API の有効化

2. 外部接続の作成

3. 外部接続を行うサービスアカウントに Vertex AI へのアクセス権限を付与

4. データセットの作成

5. リモートモデルの作成

1. ML.GENERATE_TEXT

テキストの生成

各パラメータ

出力

2. ML.UNDERSTAND_TEXT

テキストの解析

出力

3. ML.TRANSLATE

言語翻訳

出力

4. ML.ANNOTATE_IMAGE

画像解析

出力

5. ML.GENERATE_EMBEDDING

テキストのベクトル化（text-embedding）

出力

非構造化データから得たデータをもとに自然言語テキストの応答を生成

出力

参考

まとめ

Discussion