はじめに
こんにちは、つばさです。
BigQueryには、SQL内で直接AIモデル（Gemini等）を呼び出してテキスト生成ができる ML.GENERATE_TEXT という関数があります。
本記事では、この関数の概要から、メリット・デメリット、セットアップ手順、実践的な使い方まで紹介します。
ML.GENERATE_TEXTとは？
BigQueryのSQL内からVertex AI上のAIモデルを呼び出し、テキストの生成・要約・分類・感情分析などをおこなえる関数です。
事前にAIモデルとの接続設定が必要ですが、一度設定してしまえばSQLを書くだけでAIの活用が可能です。
メリット
SQLに慣れていればすぐに利用可能
既存のBigQuery環境・データパイプラインにそのまま組み込みやすい
幅広い用途に対応
感情分析・要約・分類など多様なユースケースに対応可能
パラメータで出力をコントロールできる
temperatureやmax_output_tokensの調整で、出力の詳しさや創造性を柔軟に変更可能
デメリット
コストについて
以下の2つのコストが発生します。
- BigQueryの処理コスト：スキャンしたデータ量に応じた課金
- Vertex AIの利用料金：入出力トークン数に応じた課金
環境セットアップ
初期設定はやや手間がかかりますが、一度セットアップすればプロンプトの変更だけでAIの挙動を調整できます。
APIの有効化
以下APIを有効化します。
- BigQuery API
- BigQuery Connection API
- Vertex AI API
ユーザー権限
以下のロールが必要です。
| ロール |
用途 |
| BigQuery データ編集者 |
データセット・テーブル・モデルの作成・使用 |
| BigQuery 接続管理者 |
BigQuery接続の作成・委任・使用 |
| BigQuery ジョブユーザー |
BigQueryジョブの作成 |
⚠️ デフォルト接続が未構成の場合、CREATE MODEL実行時に BigQuery 管理者が必要になる場合があります。
セットアップ手順
接続を作成する
BigQueryコンソール上でVertex AI向けの接続を作成します。
サービスアカウントに権限を付与する
接続作成時に自動生成されるサービスアカウントに Vertex AI ユーザー ロールを付与します。
この設定により、BigQueryがVertex AI上のAIモデルを呼び出せるようになります。
リモートモデルを作成する
BigQueryのSQLエディタから CREATE MODEL 文を実行し、使用するAIモデルを登録します。
CREATE OR REPLACE MODEL
`PROJECT_ID.DATASET_ID.MODEL_NAME`
OPTIONS (ENDPOINT = 'ENDPOINT');
ML.GENERATE_TEXTの使い方
基本構文
ML.GENERATE_TEXT は以下の3つの引数を順番に指定します。
SELECT *
FROM ML.GENERATE_TEXT(
MODEL `PROJECT_ID.DATASET.MODEL_NAME`,
( QUERY_STATEMENT ),
STRUCT(
[MAX_OUTPUT_TOKENS AS max_output_tokens]
[, TOP_P AS top_p]
[, TEMPERATURE AS temperature]
[, STOP_SEQUENCES AS stop_sequences]
[, GROUND_WITH_GOOGLE_SEARCH AS ground_with_google_search]
[, SAFETY_SETTINGS AS safety_settings]
[, FLATTEN_JSON_OUTPUT AS flatten_json_output]
[, REQUEST_TYPE AS request_type]
)
);
各引数の説明
第1引数：MODEL
事前に CREATE MODEL で作成したリモートモデルを指定します。
MODEL `プロジェクトID.データセット名.モデル名`
第2引数：入力データ（プロンプトの指定）
AIに渡すプロンプトを含む入力データを指定します。prompt という名前のカラムが必須で、このカラムの文字列がAIへの指示文として送信されます。
サブクエリで動的に生成すれば、データを加工しながら prompt カラムを組み立てられるため、柔軟なプロンプト生成が可能です。
(
SELECT CONCAT('レビューの感情を分析して: ', review_text) AS prompt
FROM `プロジェクトID.データセット名.テーブル名`
)
第3引数：STRUCT
AIモデルの動作を制御するパラメータを STRUCT(...) の中に指定します。すべて省略可能で省略した場合、デフォルト値が適用されます。
STRUCT(
1000 AS max_output_tokens,
0.2 AS temperature,
TRUE AS flatten_json_output
)
パラメータ一覧（一部）
| パラメータ |
型 |
デフォルト |
説明 |
MAX_OUTPUT_TOKENS |
INT64 |
1024 |
生成されるレスポンスの最大トークン数（1〜8192） |
TOP_P |
FLOAT64 |
0.95 |
出力トークンの多様性を制御。値が低いほどランダム性が低い |
TEMPERATURE |
FLOAT64 |
0 |
ランダム性の制御。0に近いほど一貫性のある回答、1に近いほど多様な回答になる |
STOP_SEQUENCES |
ARRAY |
[] |
モデルからの応答に指定された文字列が含まれている場合に、それらを削除する |
GROUND_WITH_GOOGLE_SEARCH |
BOOL |
FALSE |
Google検索を使って回答を補完する |
FLATTEN_JSON_OUTPUT |
BOOL |
FALSE |
TRUEにすると回答が個別列に分割される（後述） |
出力カラム
関数を実行すると、入力テーブルの全カラムに加え、以下のカラムが追加されます。
flatten_json_output の設定によって返却されるカラムが異なります。
flatten_json_output = FALSE（デフォルト）の場合
| カラム名 |
内容 |
ml_generate_text_result |
AIの回答を含むJSON全体 |
ml_generate_text_status |
APIのレスポンスステータス |
回答テキストを取り出すには、JSONを直接パースする必要があります。
SELECT
ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS generated_text
FROM ML.GENERATE_TEXT( ... )
flatten_json_output = TRUE の場合
| カラム名 |
内容 |
ml_generate_text_llm_result |
AIの回答テキストのみ |
ml_generate_text_rai_result |
安全性フィルターの評価結果、SAFETY_SETTINGS引数を指定した場合に返される |
ml_generate_text_status |
APIのレスポンスステータス |
ml_generate_text_grounding_result |
参照したソース情報、GROUND_WITH_GOOGLE_SEARCH がTRUEの場合に返される |
回答テキストをシンプルに取り出したい場合は flatten_json_output = TRUE がおすすめです。
ml_generate_text_llm_result カラムに回答テキストが直接入るため、後続の処理がシンプルになります。
完全なクエリ例
以下は、商品レビューに対して感情分析をおこなう例です。
各レビューに対して「ポジティブ」「ネガティブ」「中立」の3つのラベルを付与します。
SELECT
ml_generate_text_llm_result,
prompt
FROM
ML.GENERATE_TEXT(
MODEL `my_project.my_dataset.my_model`,
(
SELECT
CONCAT(
'あなたはレビュー分析の専門家です。',
'以下のレビューの感情を「ポジティブ」「ネガティブ」「中立」の3つから1つ選んで答えてください。',
'理由は不要です。1単語のみ回答してください。\\n',
'レビュー：', review_text
) AS prompt
FROM `my_project.my_dataset.reviews`
),
STRUCT(
10 AS max_output_tokens,
TRUE AS flatten_json_output
)
);
実際に使ってみた
パフォーマンス検証
検証を行ったところ、処理時間はデータ量・プロンプトの長さに比例して増加しました。
| 行数 |
プロンプトの長さ |
処理時間 |
| 1行 |
- |
約20秒 |
| 1万行 |
5行程度 |
約2分22秒 |
| 1万行 |
30行程度 |
約4分47秒 |
また、1万行生成した際に68レコードがNullになるケースも確認しています。AIの回答が意図と異なる場合（例：思考プロセスを含んだ回答が出力される）も発生しましたが、プロンプトの改善で制御できました。
データのサマリーを出力するクエリ例
集計データをAIに渡してサマリーを生成する実例です。
ポイント： 分析したい複数カラムを1つのプロンプトカラムにまとめて渡す必要があります。
WITH totalling AS (
SELECT
ARRAY_AGG(
TO_JSON_STRING(
STRUCT(month, order_count, prefecture, category)
)
) AS json_record
FROM (
SELECT
FORMAT_DATE('%Y-%m', order_date) AS month,
COUNT(DISTINCT order_id) AS order_count,
prefecture,
category
FROM
`my_project.my_dataset.my_table`
WHERE
order_date BETWEEN "2018-01-01" AND "2018-02-28"
GROUP BY
month, prefecture, category
)
)
SELECT
ml_generate_text_llm_result,
prompt
FROM
ML.GENERATE_TEXT(
MODEL `my_project.my_dataset.my_model`,
(
SELECT
CONCAT(
'あなたはデータ分析の専門家です。',
'与えられたデータをもとにデータ分析をしてください。\n',
'出力は日本語とし、3行程度でまとめてください。\n',
'データ：\n',
ARRAY_TO_STRING(json_record, '\n')
) AS prompt
FROM totalling
),
STRUCT(
2000 AS max_output_tokens,
0.2 AS temperature,
TRUE AS flatten_json_output
)
);
出力結果
実際に上記のクエリを実行した結果が以下です。
| ml_generate_text_llm_result |
prompt |
与えられたデータから、2018年1月から2月にかけて総注文数が増加傾向にあることが分かります。
カテゴリ別では、「事務用品」が両月ともに最も注文数が多いですが、2月には減少しています。
一方で「家電」と「家具」は2月に注文数を伸ばしており、特に「家具」の成長が顕著です。 |
あなたはデータ分析の専門家です。与えられたデータをもとにデータ分析をしてください。
出力は日本語とし、3行程度でまとめてください。
データ：
{"month":"2018-01","order_count":7,"prefecture":"静岡県","category":"事務用品"}
{"month":"2018-01","order_count":4,"prefecture":"福岡県","category":"事務用品"}
{"month":"2018-01","order_count":2,"prefecture":"三重県","category":"事務用品"}
{"month":"2018-01","order_count":1,"prefecture":"茨城県","category":"家電"}
{"month":"2018-01","order_count":1,"prefecture":"長野県","category":"事務用品"}
... (以下省略) |
SQLのみでデータの集計からAIによる分析サマリーの生成まで完結できることが確認できました。定期的なレポート作成や、大量データの傾向把握など、様々な場面での活用が期待できます。
まとめ
ML.GENERATE_TEXT を使うことで、BigQueryのSQL内に自然な形でAI処理を組み込むことができます。 初期セットアップの手間はあるものの、一度設定してしまえば以降はSQLとプロンプトの調整だけで多様なAI活用が可能です。
データ分析の自動化や、大量データへのAI一括処理を検討している方は、ぜひお試しください。
