Google は AI 技術を使用して、コンテンツをご希望の言語に翻訳しています。AI 翻訳には誤りが含まれる場合があります。

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>

ブラウザに https://console.cloud.google.com/bigquery と入力して、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 ワークスペース] の [クエリエディタ] セクションで、次のクエリを実行します。

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 関数は、実際のデータと比較して予測値を評価します。

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

主な指標の概要:

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

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 へ

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

作成したテーブル 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');

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

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）の両方が含まれます。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));