このページは Cloud Translation API によって翻訳されました。

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 を使用します。Cloud Shell は Google Cloud で動作するコマンドライン環境で、bq がプリロードされています。gcloud のコマンドと使用方法については、ドキュメントをご覧ください。

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

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

gcloud config set project <YOUR_PROJECT_ID>

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

3. データの準備

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

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

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 Workspace クエリエディタ] セクションで、以下のクエリを実行します。

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 ステートメントでラベル列として使用する列を指定します。ここで、ラベル列はスコアであるため、モデルは各行に存在する他の値に基づいて、10 個のスコアの値のうちどれが最も可能性が高いかを学習します。
「auto_class_weights=TRUE」というトレーニングデータ内のクラスラベルのバランスを取ります。デフォルトでは、トレーニングデータは重み付けされません。トレーニングデータ内のラベルが不均衡である場合、モデルは最も人気のあるクラスのラベルをより重く予測するように学習する可能性があります。
SELECT ステートメントは、CSV データを使用して読み込んだテーブルをクエリします。このステップでは TRAIN データセットのみが選択されるように、WHERE 句で入力テーブルの行がフィルタされます。
次の構造は省略可能であるため、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 Workspace クエリエディタ] セクションで、以下のクエリを実行します。

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 へ

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

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

7. 外部接続を作成する

BQ Connection API がまだ有効になっていない場合は有効にし、接続構成の詳細に表示されているサービスアカウント ID を書き留めます。

BigQuery コンソールの左側の [エクスプローラ] ペインで [+ 追加] ボタンをクリックし、[外部データソースへの接続] をクリックします。記載されている人気のあるソースで
[接続タイプ] で [BigLake とリモート関数] を選択し、ロケーションタイプに [リージョン] を指定します。値は「us-central1 (アイオワ)」です。および「bq_llm_connection」（接続 ID として使用）

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

権限を付与する

このステップでは、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');

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

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 は、生成されたテキストの結果 b を表します。安全性属性は、調整可能なしきい値を持つ組み込みのコンテンツフィルタを表します。このフィルタは、大規模言語モデルからの意図しないレスポンスや予期しないレスポンスを回避するために 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 Endpoint] ページに移動して、ML のステップで作成した Vertex AI エンドポイントを削除します。

12. 完了

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