Zenn
🗂

BigQuery ML から LLM を呼び出した時の出力スキーマ、制御できる？

に公開
Google Cloud
BigQuery
Vertex AI
BigQuery ML
Gemini
tech

TL; DR

  • できます
  • ただし、BigQuery 標準テーブルに入っているデータに対してのみであり、かつプレビュー機能を使う必要があります

はじめに

こんにちは。クラウドエースの田中です。

この記事では BigQuery にて 2025 年 4 月 3 日および 2025 年 4 月 4 日にリリースのあった機能の紹介を通して、BigQuery ML(以下、BQML) から LLM を呼び出した際の出力を、一定の形式にコントロールできるか検証していきます。

最後までお読みいただけたら幸いです。

リリース概要

4 月 3 日、4 月 4 日のリリースは、下記の関数が BigQuery にて利用できるようになった、という内容です。

それぞれ、BQML を使って Gemini モデルを呼び出す際に利用する関数です。
これらの関数により、BigQuery に保存されたデータを使った分析において、Gemini からの出力を一定のスキーマに従わせることができるようになった、というわけです。

ただし公式ドキュメントの通り、この関数で分析できるのは、BigQuery 標準テーブルに限ります。

実はこれと似た機能として、Gemini 2.0 Flash や Gemini 2.0 Flash-Lite モデルを API から呼び出すときに使える Controlled Generation という機能が既に実装されています。

LLM からの出力は確率的に決定されるため、これまでは出力形式の安定性が保証されていませんでした。
例えば同じスキーマを持った JSON を常に返してほしい場合でも、プロンプトでそのスキーマを LLM に伝えることはできますが、必ずそのスキーマで JSON が返ってくるかはわかりません。

しかし Gemini では Controlled Generation 機能によって、一定のスキーマに従った形で出力を返すことを強制し、呼び出し側でデータのパースエラーを起こさないようにできます。

Controlled Generation 機能についての詳しい話は、こちらの記事が大変参考になります。

わたしは今回のリリースによって、Controlled Generation のような機能が BigQuery でも利用できるようになった、というふうに考えています。
「のような」というのが大事で、全く同じというわけではありません。

各関数の紹介

今回追加された各関数はどんなものなのでしょうか。さっそく見ていきましょう。

AI.GENERATE

この関数は BigQuery 標準テーブルの各行を Gemini に送信し、結果を構造体型で返します。
構造体の result フィールドに、モデルからのレスポンスが格納されています。

AI.GENERATE_TABLE

特定のテーブルもしくはプロンプト文字列を与えたときの出力を、テーブル形式で返します。いわゆるテーブル関数というやつです。
BQML のモデルとインプットテーブルは、同じリージョンになければいけません。

上記 2 つの関数については試してみたセクションで取り上げます。

AI.GENERATE_BOOL

この関数も AI.GENERATE のように BigQuery 標準テーブルの各行を Gemini に送信し、結果を構造体型で返します。
構造体の result フィールドに、モデルからのレスポンスが格納されているところも同じです。

ただしこの関数は result フィールドの中に、true、false もしくは NULL だけが入るのが違うポイントです。

具体的な使用例については公式ドキュメントをご覧ください。

AI.GENERATE_DOUBLE

この関数も AI.GENERATE や AI.GENERATE_BOOL のように BigQuery 標準テーブルの各行を Gemini に送信し、結果を構造体型で返します。
構造体の result フィールドに、モデルからのレスポンスが格納されているところも同じです。

ただしこの関数は result フィールドの中に、FLOAT64 型の浮動小数点数 もしくは NULL だけが入るのが違うポイントです。

具体的な使用例については公式ドキュメントをご覧ください。

AI.GENERATE_INT

この関数も AI.GENERATE や AI.GENERATE_BOOL、AI.GENERATE_DOUBLE のように BigQuery 標準テーブルの各行を Gemini に送信し、結果を構造体型で返します。
構造体の result フィールドに、モデルからのレスポンスが格納されているところも同じです。

ただしこの関数は result フィールドの中に、INT64 型の整数 もしくは NULL だけが入るのが違うポイントです。

具体的な使用例については公式ドキュメントをご覧ください。

試してみた

今回は BigQuery の公開データセットにある映画レビューデータである、bigquery-public-data.imdb.reviews テーブルを使い、AI.GENERATE 関数と AI.GENERATE_TABLE 関数の様子を観察してみます。

下準備をお忘れなく。

下準備の詳細

BigQuery から LLM を呼び出す際は、必要な IAM 権限を持ったうえで、外部接続と BQML にてモデル作成が必要になります。

チュートリアルの公式ドキュメントによると、お手元で試す際には以下の IAM 権限が必要なようです。
今回は自分がより大きな権限であるオーナー権限を持っている Google Cloud プロジェクトにて検証したので IAM 周りでのトラブルは起きていませんが、トラブルシュートする際は公式ドキュメントを参考にして、自分が必要な権限を持っているのか確認してください。

その後、BQML から LLM を呼び出せるように、外部リソースとの接続および CREATE MODELによる BQML モデル作成が必要になります。

なお、BigQuery の外部接続用のサービスアカウントに必要な権限が不足していることで、CREATE MODEL 句が失敗することもありえます。
その際はこちらの公式ドキュメントのように、IAM のページから Vertex AI User のロールを BigQuery 接続のサービスアカウントに付与してください。

ご自身で試される際は上記リンク先をご覧になり、必要なステップを踏んでください。

`reviews` テーブルのスキーマ
[
  {
    "name": "review",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "User review's in IMDb.",
    "fields": []
  },
  {
    "name": "split",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "It has two categories test and train.",
    "fields": []
  },
  {
    "name": "label",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "It has three categories Negative, Positive and Unsupervised. All Unsupervised label has only split equals-to train.",
    "fields": []
  },
  {
    "name": "movie_id",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "UniqueId for the movie in IMDb.",
    "fields": []
  },
  {
    "name": "reviewer_rating",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": "Reviewer rating for particular movie in IMDb. For train-unsupervised, reviewer_rating is NULL.",
    "fields": []
  },
  {
    "name": "movie_url",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "Movie url for corresponding movie_id",
    "fields": []
  },
  {
    "name": "title",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "Title of the movie for corresponding movie_id",
    "fields": []
  }
]

AI.GENERATE() を試してみる

WITH extract_movies_contains_gundam AS (
  SELECT
    title
    , STRING_AGG(review, "," ORDER BY LENGTH(review)) as review
  FROM
    `bigquery-public-data.imdb.reviews`
  WHERE
    title LIKE '%Gundam%'
  GROUP BY
    title
)
SELECT
  title
  , AI.GENERATE(
    -- プロンプト。これは CONCAT()で文字列を結合しているのと同じことをしています
    ('以下のレビューを読んで、短い1文に要約しなさい。', review, 'The Output must be written in Japanese'),
    connection_id => 'us.test_ai_generate',
    endpoint => 'gemini-2.0-flash',
    output_schema => 'title STRING, result STRING').result
FROM
  extract_movies_contains_gundam

AI.GENERATE_TABLE() を試してみる

例えば、映画に寄せられたレビューのそれぞれを Gemini に読ませ、各映画のジャンルを判定してもらいましょう。
……と思いましたが、エラーが出ました。

これは、今回読み込ませようとした bigquery-public-data.imdb.reviews テーブルに、prompt という名前の列がないことによります。

最初試そうとしたクエリはこちらです。
dataset.model および project_id.dataset.table は、ご自身の環境でのものに読み替えてください。
わたしは試していませんが、もし project_id.dataset.table に prompt 列がある、もしくは UPDATE ~ SET などで prompt 列を追加しているならば、これでも動くはずです。

SELECT
  title
  , genre
FROM
AI.GENERATE_TABLE( MODEL `dataset.model`,
  TABLE `bigquery-public-data.imdb.reviews`,
  STRUCT("title STRING, genre STRING, " AS output_schema));

このエラーに対しては 2 つの解決策がありそうです。

  1. テーブル側でなんとかする
    1. テーブルに prompt 列を追加してあげれば、問題は解決します
    2. チュートリアルの公式ドキュメントを見た限り、こちらが推奨なようです
  2. クエリ側でなんとかする
    1. prompt 列がないというだけなら、クエリ側でカバーすることも可能なはずです

今回は BigQuery のパブリックデータセットにデータが溜まっていることもあり、列追加をせずクエリ側で頑張ったらどうなるのか試してみました。
ただあまり正攻法ではないかなと思いますし、クエリの処理時間も長くなります。
実務では適切な前処理などで Gemini 呼び出し回数を減らすことが大事になってきそうです。

なお、どれくらい処理時間が長くなるかというと、10 万件のデータに対してクエリを発行した場合、処理時間が 6 時間を超えます。
つまりクエリの実行時間制限に引っかかって、クエリが失敗する程度には長いです。

というのも review テーブルには 10 万行入っているのですが、Vertex AI API のページで API の呼び出し回数を見たところ、AI.GENERATE_TABLE 関数ではその 1 行ごとに Gemini に処理を投げているようです。


クエリ失敗直後の Vertex AI API メトリクスページ

下記の例では review テーブルの中から 1 万件だけ選んで処理してみたのですが、それでもクエリの処理に 2 時間 35 分かかっています。

今回実行したクエリとその説明
WITH genre_per_each_review AS (
  SELECT
    title
    , ai_output.genre
  FROM
  AI.GENERATE_TABLE( MODEL `dataset.model`,
    (
      SELECT
        title
        , CONCAT('Determine the genre of the movie titled "', title, '" based on the following reviews', review) as prompt
      FROM
        `bigquery-public-data.imdb.reviews`
      LIMIT
        -- とりあえず 1 万件にデータを絞れればいいので、今回は LIMIT を使用
        10000
    ),
    STRUCT("genre STRING" AS output_schema)) as ai_output
)
SELECT
  title
  , APPROX_TOP_COUNT(genre, 1)[OFFSET(0)].value AS predicted_genre
FROM
  genre_per_each_review
WHERE
  genre IS NOT NULL AND genre != ''
GROUP BY
  title

素直に prompt 列を足すか、STRING_AGG 関数などを使った前処理をして、クエリ実行時間を短くするような工夫をするのがよさそうですね。

まとめ

ここまで BQML で Gemini を使う際に出力を制御できる各種関数を見てきました。

欲を言えば BigQuery の外部テーブルにも対応してもらって、Gemini の Controlled Generation 機能のように非構造化データに対する出力の形式も強制できると、さらに嬉しくなりそうです。

あるいは Google Cloud NEXT '25 で発表のあった Object Ref と組み合わせることで、そもそも構造化・非構造化データといった線引きが不要になってくるのかもしれません。

ちなみに Object Ref については以下の記事も併せてお読みいただけると幸いです。
https://zenn.dev/cloud_ace/articles/google-cloud-next25-bq

ただそれを差し引いてもそれぞれ後続での処理などで使いどころがありそうな関数で、GA が待ち遠しいですね。

Discussion

ログインするとコメントできます