BigQueryとGeminiの連携で広がるSQLの世界

近年、生成AIの活用が企業のデータ分析において重要なトレンドとなっています。その中で、BigQueryが提供する”ML.GENERATE_TEXT”関数は、データベースとAIの融合という新しい可能性を開きました。

この関数を使うには「存在そのものがあまり知られていない」「使い方が難しい」「利用するためには別途”BigQuery ML リモートモデル”を作成する必要がある」というハードルがあります。そのため、あまり使われていない印象です。

しかしながら、この”ML.GENERATE_TEXT”関数はとても強力です。本記事では、「この”ML.GENERATE_TEXT”関数で何ができるのか？」「どのように活用するのか？」「利用方法と注意点」を解説していきます。

ML.GENERATE_TEXT関数で何ができるのか？

“ML.GENERATE_TEXT”関数は、BigQueryのSQLクエリを介してGeminiやClaude、Llamaなどの生成AIサービスを実行し、テキストを生成することができる関数です。

例えば、「株式会社プリンシプルについて知っていることを教えて」というプロンプトを、”ML.GENERATE_TEXT”で実行するときのクエリと結果は以下のようになります（principle-c.shared.gemini-20-flashは、弊社のBigQueryに置いているリモートモデルのため、試す際はご自身でリモートモデルを作成し、置き換える必要があります）。

このように、普段ブラウザやアプリで入力しているプロンプトを、SQL経由で実行し、生成AIの結果をBigQuery上で取得できるようになります。

どのように活用するのか？

ここまでで、BigQueryのSQLクエリを使って生成AIを呼び出せることを説明しました。「それなら、わざわざBigQueryを使わなくても、Chat GPTやGeminiを直接使えば良いのでは？」と感じた方もいるかもしれません。

しかし、BigQuery経由で生成AIを活用できるメリットは非常に大きいです。例えば以下のようなLooker Studioダッシュボードをたった1時間程度で作成できるようになります（※実際に使っているものとは異なり、要点のみ抜き出したキャプチャとしています）。

このLooker Studioダッシュボードでは、左側にGA4の一定期間における「クライアントID」「ページビュー数」を表形式で置いています（実務的には、「コンバージョンした人」など特徴的な行動をした人を絞り込んで表示する形を想定しています）。

この一覧から誰か1人を定めてクライアントIDをコピーし、右上のクライアントID欄に入力すると、右側の「ユーザープロファイリング」が表示されるようになっています。この右側の「ユーザープロファイリング」が、Geminiが生成した結果となっています。

Geminiに渡すプロンプトは、

あなたは、Googleアナリティクス4がエクスポートしたBigQueryデータを使って、サイト利用者のユーザー行動を分析するプロフェッショナルです。

これから、株式会社プリンシプルのWebサイトにアクセスした、1人のユーザーの閲覧ログをもとに、そのユーザーのプロファイリングを行ったうえで、営業アプローチの方法を検討しようとしています。
アウトプットは、Webにそのまま出力したいため、HTMLで構造化マークアップした形としてください。

## 閲覧ログのデータの定義

– ‘scroll’というイベントは該当ページを90%以上スクロールしたことを表しており、ページの内容に興味を持っていることを意味しています。
– ‘form_visible’というイベントは、お問合せフォームなどを閲覧したことを表しており、問合せを検討したことを意味しています。
– ‘generate_lead’というイベントは、お問合せフォームから実際に問合せを行ったことを表しています。

## 閲覧ログデータ詳細

– {“event_dt”:”2025-02-21T11:31:09.341685″,”event_name”:”page_view”,”page_location”:”https://www.principle-c.com/service/analytics/googleanalytics4-2/”,”page_title”:”GA4活用支援（導入～分析・レポーティング） | 株式会社プリンシプル”}
– {“event_dt”:”2025-02-21T11:31:48.331432″,”event_name”:”scroll”,”page_location”:”https://www.principle-c.com/service/analytics/googleanalytics4-2/”,”page_title”:”GA4活用支援（導入～分析・レポーティング） | 株式会社プリンシプル”}
– {“event_dt”:”2025-02-21T11:31:52.863919″,”event_name”:”page_view”,”page_location”:”https://www.principle-c.com/company/”,”page_title”:”会社情報 | 株式会社プリンシプル”}
– {“event_dt”:”2025-02-21T11:33:19.065043″,”event_name”:”page_view”,”page_location”:”https://www.principle-c.com/company/information/”,”page_title”:”会社概要 | 株式会社プリンシプル”}
– {“event_dt”:”2025-02-21T11:33:33.695024″,”event_name”:”page_view”,”page_location”:”https://www.principle-c.com/company/”,”page_title”:”会社情報 | 株式会社プリンシプル”}

… （省略）

のようなものを用いています。また、「閲覧ログデータ詳細」は、GA4のBigQueryエクスポートテーブルから、動的に生成するようになっています。

また、プロンプト中で「HTMLで構造化した形でアウトプットするように」と出力形式を指定しているのは、（多少レスポンスを整形する必要はありますが）HTMLでそのまま表示するためです。

これにより、前回私が執筆したブログ「Looker Studioのコミュニティ・ビジュアライゼーション「Templated Record」の紹介」と合わせることで、BigQueryから取得したGeminiの回答をほぼそのままLooker Studioに反映させることができるようになっています。

上記のダッシュボードはGA4データを使った例ですが、弊社では他にも

「Zoom Phoneに記録されたインサイドセールスの通話内容のテキストデータ」を使い、架電者へのフィードバックをAIで生成する。
「Salesforceに記録されたリード情報やキャンペーン情報、過去の通話・打合せログ」を使い、営業アプローチの戦略案を立てる。

といった用途で同様の仕組みを構築し、運用しています（Zoom PhoneやSalesforceなどのデータは全て、BigQueryに集約したうえで、SQLクエリを使って生成AIを呼び出しています）。

当然、PythonやRubyなどのプログラミング言語を使って、OpenAI APIやGemini APIを呼び出す仕組みを構築することも可能です。

しかし、そのためには、API利用に関する知識やWebサイト構築の知識が必要となるなど、高いハードルを乗り越える必要があります。

それが、今回の構成であれば、データ集約から生成AIの呼び出しまで、全てSQLクエリだけで完結できます。さらに、Looker Studioを使うことで、今まで使っていたダッシュボードの中に、生成AIを取り入れることができるようになります。

ML.GENERATE_TEXT 関数の利用方法と注意点

事前準備

事前の設定として、

BigQuery UI上で、外部データソースへの接続を作成
サービスアカウントへのアクセス権の付与
BigQuery ML リモートモデルの作成
Vertex AIサービスの有効化

などが必要です。公式ヘルプページ「ML.GENERATE_TEXT 関数を使用してテキストを生成する」を参考に設定してみてください。

利用方法（クエリ実装）

元となるデータを使いながら、

CONCAT関数
ARRAY_AGG関数
ARRAY_TO_STRING関数

などを使い、1つのテキストになるようにプロンプトを生成していきます。最後に、”prompt”という名前の列が1つだけあれば十分です。

プロンプト部分ができあがれば、”ML.GENERATE_TEXT”関数の呼び出しです。呼び出し時に引数パラメーターの仕様などは、公式ヘルプページ「ML.GENERATE_TEXT 関数を使用してテキストを生成する」を参考に設定してみてください。

注意点：料金には特に注意が必要

最大の注意点は料金です。”ML.GENERATE_TEXT”関数は、GoogleクラウドのAI関連サービスの1つであるVertex AIのAPIを利用しています。そのため、BigQueryのクエリ実行料金だけでなく、Vertex AIの料金も発生します。

具体的な料金は、公式ヘルプページ「Vertex AI の料金」を参考にしていただきたいですが、入力・出力の文字数に応じて料金が発生します。例えば、上のユーザープロファイルを1人分行う例では、入力のプロンプト部分が約6,000文字、出力のレスポンス部分が約10,000文字程度で、1回あたり約0.0008625ドル（約0.11円）程度となります。

ーユーザー全体に対する処理は高額になる恐れ

この金額は一見安く思えますが、対象人数が増えるとコストも急増します。例えば、1ヶ月分の全ユーザーに対してユーザープロファイルを一度に取得するような作りにしてしまうと、月100万人のサイトの場合、これだけで10万円を超えてしまいます。さらに、1年分もしくは全期間分の全ユーザーに対して実行してしまうと、悲惨な結果が待っています。

ークエリの書き方にも要注意

通常GA4は、1人のユーザー行動が複数の行に分かれてBigQueryに格納されています。これを適切に集約せずにプロンプトを作ると、1ユーザーにつき何度も関数を呼び出すことになり、意図せず料金が膨れ上がる原因になります。

通常のクエリ実行であれば、BigQueryのクエリ実行料金だけを気にすれば良かったですが、”ML.GENERATE_TEXT”関数を使うことで、Vertex AIの料金も気にしなければいけなくなります。そして、Vertext AIの料金はクエリの書き方によって大きく変わることもあるので、注意が必要です。