BigQuery SQL と Vertex AI を使用した生成インサイト

1. はじめに

この Codelab では、BigQuery SQL クエリと Vertex AI PaLM API を使用して、映画の成功評価を予測して対策するアプリを作成します。テキスト生成に使用されるモデルは text-bison で、BigQuery のリモート関数としてホストされます。

使用するサービスのリストは次のとおりです。

BigQuery ML
Vertex AI PaLM API
Cloud Shell

作成するアプリの概要

次のものを作成します。

モデルを含む BigQuery データセット
映画の GENRE 属性と RUNTIME 属性に基づいて映画の成功スコアを予測する BigQuery ML モデル
Vertex AI PaLM API をリモート関数としてホストする BigQuery モデル
BigQuery と Vertex AI の接続を確立するための外部接続

2. 必要なもの

ブラウザ（Chrome、Firefox など）
課金を有効にした Google Cloud プロジェクト

始める前に

[Google Cloud コンソール] の [プロジェクトセレクタ] ページで、Google Cloud プロジェクトを選択または作成します。
Cloud プロジェクトに対して課金が有効になっていることを確認します。プロジェクトで課金が有効になっているかどうかを確認する方法について学習する
必要な API（BigQuery API、Vertex AI API、BigQuery Connection API）がすべて有効になっていることを確認します。
Cloud Shell（Google Cloud で動作するコマンドライン環境）を使用します。この環境には bq がプリロードされています。gcloud コマンドとその使用方法については、ドキュメントをご覧ください。

Cloud コンソールで、[Cloud Shell をアクティブにする] をクリックします。

プロジェクトが設定されていない場合は、次のコマンドを使用して設定します。

gcloud config set project <YOUR_PROJECT_ID>

ブラウザに次の URL を入力して、BigQuery コンソールに直接移動します。https://console.cloud.google.com/bigquery

3. データの準備

このユースケースでは、movies データセットをmovielensソースから派生したものを使用します。

データセットを作成します。

BigQuery データセットはテーブルのコレクションです。データセット内のすべてのテーブルは、同じデータのロケーションに保存されます。また、カスタムアクセス制御をアタッチして、データセットとそのテーブルへのアクセスを制限することも可能です。

Cloud Shell で bq mk コマンドを使用して、「movie_insights」というデータセットを作成します。

bq mk --location=us-central1 movie_insights

ソースファイルを Cloud Shell マシンにクローンします。

git clone https://github.com/AbiramiSukumaran/movie_score_genai_insights

Cloud Shell マシンに作成された新しいプロジェクトディレクトリに移動します。

cd movie_score_genai_insights

bq load コマンドを使用して、CSV ファイルを BigQuery テーブルに読み込みます（BigQuery UI から直接アップロードすることもできます）。

bq load --source_format=CSV --skip_leading_rows=1 movie_insights.movie_score \
./movies_data.csv \ Id:numeric,name:string,rating:string,genre:string,year:numeric,released:string,score:string,director:string,writer:string,star:string,country:string,budget:numeric,company:string,runtime:numeric,data_cat:string

サンプルをクエリして、テーブル movie_score とデータがデータセットに作成されているかどうかを確認できます。

bq query --use_legacy_sql=false \
SELECT name, rating, genre, runtime FROM movie_insights.movie_score limit 3;

4. データから ML へ

GENRE 属性と RUNTIME 属性に基づいて映画の成功スコアを予測する分類モデルを作成しましょう。CREATE MODEL ステートメントを ‘LOGISTIC_REG' オプション付きで使用することで、ロジスティック回帰モデルの作成とトレーニングが行えます。

BigQuery コンソールの [SQL ワークスペース] の [クエリエディタ] セクションで次のクエリを実行します。

CREATE OR REPLACE MODEL
  `movie_insights.model_rating_by_runtime_genre`
OPTIONS
  ( model_type='LOGISTIC_REG',
    auto_class_weights=TRUE,
    data_split_method='NO_SPLIT',
    model_registry='vertex_ai',   
    vertex_ai_model_version_aliases=['logistic_reg', 'experimental'],
    input_label_cols=['score']
  ) AS
SELECT name, genre,runtime, score
FROM
  movie_insights.movie_score
WHERE
  data_cat = 'TRAIN';

クエリの詳細:

CREATE MODEL ステートメントは、SELECT ステートメントのトレーニングデータを使用してモデルをトレーニングしています。
OPTIONS 句は、モデルタイプとトレーニングオプションを指定します。ここでは、LOGISTIC_REG オプションでロジスティック回帰モデルタイプを指定しています。2 項ロジスティック回帰モデルか多項ロジスティック回帰モデルかを指定する必要はありません。BigQuery ML はラベル列の一意の値の数に基づいてどちらをトレーニングするかを判断できます。
data_split_method=‘NO_SPLIT' を指定すると、BQML はクエリ条件（data_cat = ‘TRAIN'）に基づいてデータをトレーニングします。また、このオプションで ‘AUTO_SPLIT' を使用して、フレームワーク（この場合はサービス）がトレーニング/テスト分割のパーティションをランダム化することをおすすめします。
input_label_cols オプションは、SELECT ステートメントでラベル列として使用する列を指定します。ここでは、ラベル列は score であるため、モデルは他の行の値に基づいて、score の 10 個の値のどれに分類される可能性が高いかを学習します。
‘auto_class_weights=TRUE' オプションは、トレーニングデータ内のクラスラベルのバランスをとります。デフォルトでは、トレーニングデータは重み付けされません。トレーニングデータ内のラベルが不均衡である場合、モデルは最も出現回数の多いラベルクラスをより重視して予測するように学習することがあります。
SELECT ステートメントは、CSV データで読み込んだテーブルをクエリします。WHERE 句は入力テーブルの行をフィルタして、このステップで TRAIN データセットのみが選択されるようにします。
次の構造は省略可能です。BigQuery ML は、Vertex AI Model Registry に明示的に登録できます。詳細については、こちらのブログ. model_registry='vertex_ai', vertex_ai_model_version_aliases=['logistic_reg', 'experimental']をご覧ください。

作成すると、BigQuery SQL ワークスペースの [SCHEMA] セクションに以下が表示されます。

モデルを作成したら、 ML.EVALUATE 関数を使用してモデルの性能を評価します。ML.EVALUATE 関数は、実際のデータと比較して予測値を評価します。

モデルの評価指標は、[MODEL] ページで確認することもできます。

主な指標の概要:

適合率 - 陽性と特定されたもののうち、実際に陽性だった割合はいくつか。適合率 = 真陽性 /（真陽性 + 偽陽性）再現率 - 実際に陽性だったもののうち、陽性と正しく特定された割合はいくつか。再現率 = 真陽性 /（真陽性 + 偽陰性）精度 - 分類モデルを評価する指標。モデルが実際に正しく予測した割合。精度 = 正しい予測数 / 予測の総数

5. モデルを使用して映画のスコアを予測する

予測する!!!!次のクエリは、「TEST」データとして分類されるデータセット内の各映画のスコアを予測します。

BigQuery コンソールの [SQL ワークスペース] の [クエリエディタ] セクションで次のクエリを実行します。

SELECT
  *
FROM
  ML.PREDICT (MODEL movie_insights.model_rating_by_runtime_genre,
    (
    SELECT
      *
    FROM
      movie_insights.movie_score
    WHERE
      data_cat= 'TEST'
     )
  );

結果は次のようになります。

モデルの結果には、映画の predicted_score が 1 ～ 10 のスケール（分類）で表示されます。各映画に対して複数の予測行があるのはなぜでしょうか。これは、モデルが予測されるラベルと、それぞれの発生確率を降順で返したためです。

予測結果とモデルを分析する:

予測を使用して、結果を理解するための 2 つの優れた分析ステップを実行できます。

モデルがこれらの予測結果を生成する理由を理解するには、ML.EXPLAIN_PREDICT 関数を使用します。
一般に、所得階層を決定するうえで最も重要な特徴量を特定するには、ML.GLOBAL_EXPLAIN 関数を使用します。

これらの手順の詳細については、ドキュメントをご覧ください。

6. データから生成 AI へ

SQL クエリのみを使用して Vertex AI の text-bison（最新）モデルを使用する生成 AI を使用して、映画のスコアが 5 より大きくなる要因の概要を LLM（大規模言語モデル）に問い合わせて、映画データセットに関する分析情報を提供しましょう。

作成したテーブル movie_score は、このステップの入力にもなります。
BigQuery ML サービスと Vertex サービス間のアクセスを確立するために、外部接続が作成されます。
BigQuery GENERATE_TEXT 構造を使用して、Vertex AI から PaLM API をリモートで呼び出します。

7. 外部接続を作成する

BigQuery Connection API がまだ有効になっていない場合は有効にし、接続の構成情報を参照してサービスアカウント ID を書き留めます。

BigQuery コンソールの左側の [エクスプローラ] ペインで [+ 追加] ボタンをクリックし、表示された一般的なソースの中から、[外部データソースへの接続] をクリックします。
接続タイプとして [BigLake とリモート関数] を選択し、ロケーションタイプとして [リージョン]、値として [us-central1（アイオワ）]、接続 ID として「bq_llm_connection」を指定します。

接続を作成したら、接続の構成情報を参照して、生成されたサービスアカウントを書き留めます

権限を付与する

このステップでは、サービスアカウントに Vertex AI サービスへのアクセス権を付与します。

IAM を開き、外部接続の作成後にコピーしたサービスアカウントをプリンシパルとして追加して、[Vertex AI ユーザー] ロールを選択します。

8. リモート ML モデルを作成する

ホストされる Vertex AI 大規模言語モデルに相当するリモートモデルを作成します。

CREATE OR REPLACE MODEL
  movie_insights.llm_model REMOTE
WITH CONNECTION `us-central1.bq_llm_connection` OPTIONS (remote_service_type = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1');

これにより、データセット movie_insights に llm_model という名前のモデルが作成されます。このモデルは、Vertex AI の CLOUD_AI_LARGE_LANGUAGE_MODEL_V1 API をリモート関数として利用します。完了まで数秒かかることがあります。

9. ML モデルを使用してテキストを生成する

モデルの作成が完了したら、そのモデルを使ってテキストを生成、要約、分類します。

SELECT
  ml_generate_text_result['predictions'][0]['content'] AS generated_text,
  ml_generate_text_result['predictions'][0]['safetyAttributes']
    AS safety_attributes,
  * EXCEPT (ml_generate_text_result)
FROM
  ML.GENERATE_TEXT(
    MODEL `movie_insights.llm_model`,
    (
 SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES
    ),
    STRUCT(
      0.2 AS temperature,
      100 AS max_output_tokens));

**説明:**

**ml_generate_text_result** は、テキスト生成モデルから返される JSON 形式のレスポンスで、コンテンツ（content）と安全性属性（safetyAttributes）の両方が含まれます。a. content は、生成されたテキストの結果を表します。b. safetyAttributes は、調整可能なしきい値を持つ Vertex AI PaLM API に付属のコンテンツフィルタを表します。このフィルタを使って、大規模言語モデルからの想定外の予期せぬレスポンスを除外することができます。具体的には、安全性のしきい値に収まらないレスポンスはブロックされます。

ML.GENERATE_TEXT は、BigQuery 内で使用する構造で、Vertex AI LLM にアクセスしてテキスト生成タスクを実行します。

CONCAT は、PROMPT 文とデータベースレコードを連結します。

movie_insights は、データセット名です。movie_score は、このプロンプト設計で使用するデータを含むテーブルの名前です。

temperature は、レスポンスのランダム性を制御するプロンプトパラメータです。この値が小さいほど、関連度が高くなります。

Max_output_tokens は、レスポンスに含める単語の数です。

クエリのレスポンスは以下のようになります。

ご覧のとおり、レスポンスはネストされていて、フォーマットされていません。

10. クエリ結果をフラット化する

結果をフラット化し、クエリ内で JSON を明示的にデコードせずに済ませるには、以下のように記述します。

SELECT
  *
FROM
  ML.GENERATE_TEXT( MODEL movie_insights.llm_model,
    (
    SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES),
    STRUCT( 0.2 AS temperature,
      100 AS max_output_tokens,
      TRUE AS flatten_json_output));

**説明:**

**flatten_json_output** はブール値で、true に設定すると、JSON のレスポンスから平坦でわかりやすいテキストが抽出されて返されます。

クエリのレスポンスは以下のようになります。

11. クリーンアップ

この投稿で使用したリソースについて、Google Cloud アカウントに課金されないようにするには、Vertex AI エンドポイントページに移動して、ML ステップで作成した Vertex AI エンドポイントを削除します。

12. 完了

おめでとうございます！BQML モデルを作成し、SQL クエリのみを使用して、映画データセットに対して Vertex AI API を使用して LLM ベースの分析を実行できました。使用可能なモデルの詳細については、Vertex AI LLM プロダクトのドキュメントをご覧ください。