🤖

[BQML] Gemini の Grounding (Google検索) で欠損値を補完する

2025/12/25に公開

 はじめにこんにちは、クラウドエース株式会社 第四開発部の多賀です。
データ分析や活用において、頻繁に課題となるのが 「データの欠損」 です。

「顧客リストの電話番号が抜けている」「住所が入っていない」といった不完全なデータに対し、Web 検索等を用いて情報を補完しようとする場合、これまでは外部スクリプトを作成して API を利用したり、あるいは手作業で検索してデータを更新するといった対応が必要でした。
しかし、BigQuery ML (BQML) と Gemini、そして Grounding with Google Search を組み合わせることで、このプロセスをSQLだけで完結させることが可能になりました。
本記事では、不完全な企業データを、「既知の情報をもとに Gemini に Google 検索を行わせ、欠損部分を埋める」 というアプローチで補完する方法をご紹介します。

 対象読者BigQuery を日常的に利用しているデータアナリストやエンジニア
データの欠損や不備の補完作業に課題を感じている方
Python などの外部スクリプトを書かずに、SQL だけで生成 AI (Gemini) を活用したい方

 検証
 アーキテクチャとアプローチ今回のアプローチは非常にシンプルです。BigQuery 上にある欠損を含むデータに対し、以下のフローで処理を行います。

プロンプト生成: 欠損している項目を特定し、「〇〇（既知の情報）をもとに、××（欠損項目）を調べて」という命令文を動的に作成。

Grounding 実行: Gemini が Google 検索を実行し、最新かつ正確な情報を取得。

データ補完: 検索結果から必要な情報を抽出し、テーブルの NULL を埋める。

 前提設定BQML と Gemini の連携設定（Cloud リソース接続の作成や権限付与）については、以下の公式ドキュメントなどを参考に設定を行ってください。
参考: Gemini モデルと AI.GENERATE_TEXT 関数を使用してテキストを生成する
接続設定が完了している前提で、まずはデータセットと Gemini のリモートモデルを作成します。
!本記事では gemini-2.5-pro を使用します。

なお、2025/12/24 時点で gemini-3.0 系モデルはプレビュー版での提供となっています。

利用可能なモデルについては、公式ドキュメント を参照してください。
CREATE SCHEMA IF NOT EXISTS `[PROJECT_ID].demo_dataset`
OPTIONS (
  location = 'asia-northeast1',
  description = 'デモ作成用のデータセット'
);

CREATE OR REPLACE MODEL `demo_dataset.gemini`
  REMOTE WITH CONNECTION `projects/[PROJECT_ID]/locations/asia-northeast1/connections/[CONNECTION_ID]`
  OPTIONS (ENDPOINT = 'gemini-2.5-pro');

 検証用データの準備検証のために、「虫食い状態」のテーブルを作成します。

企業名、電話番号、郵便番号、住所のカラムを用意し、いくつかのパターンで NULL を混入させます。
入力データ (demo_dataset.missing_values_table)


id
company_name
phone_number
post_code
address


1
クラウドエース株式会社
0362805939
1000004
東京都千代田区大手町１丁目７−２ 東京サンケイビル 26階

2
NULL
0362805939
1000004
東京都千代田区大手町１丁目７−２ 東京サンケイビル 26階

3
クラウドエース株式会社
NULL
1000004
東京都千代田区大手町１丁目７−２ 東京サンケイビル 26階

4
クラウドエース株式会社
0362805939
NULL
東京都千代田区大手町１丁目７−２ 東京サンケイビル 26階

5
クラウドエース株式会社
0362805939
1000004
NULL

6
クラウドエース株式会社
NULL
NULL
NULL


 実行クエリの解説SQL だけで「動的なプロンプト作成」から「検索結果の反映」までを一気通貫で行います。

 ポイント
動的なプロンプト生成 (prompt_components)

全ての行に対して同じプロンプトを投げるのではなく、CASE WHEN 句を使用して 「何が欠けているか（何を調べるべきか）」 と 「何がわかっているか（検索の手がかり）」 を行ごとに組み立てています。

コスト効率を考慮した処理フロー

欠損がない行に対してGeminiを呼び出すと不要なコストが発生するため、WHERE missing_labels != '' でフィルタリングし、必要な行だけ推論を実行しています。

Grounding の有効化

ML.GENERATE_TEXT 関数の引数で ground_with_google_search: TRUE を指定することで、Gemini が学習データだけでなく、リアルタイムな Google 検索結果を利用できるようになります。

 実際のクエリWITH prompt_components AS (
  SELECT
    *,
    -- 既知の情報を連結して「検索の手がかり」を作成
    CONCAT(
      IF(company_name IS NOT NULL, CONCAT('\n企業名: ', company_name), ''),
      IF(post_code IS NOT NULL, CONCAT('\n郵便番号: ', post_code), ''),
      IF(address IS NOT NULL, CONCAT('\n住所: ', address), ''),
      IF(phone_number IS NOT NULL, CONCAT('\n電話番号: ', phone_number), '')
    ) AS known_info,
    -- 欠損している項目をリストアップして「調査対象」を明示
    ARRAY_TO_STRING(
      [
        CASE WHEN company_name IS NULL THEN '「企業名」' END,
        CASE WHEN post_code IS NULL THEN '「郵便番号」' END,
        CASE WHEN address IS NULL THEN '「住所」' END,
        CASE WHEN phone_number IS NULL THEN '「代表電話番号」' END
      ], 'と'
    ) AS missing_labels,
    -- JSON出力用のキー指定
    ARRAY_TO_STRING(
      [
        CASE WHEN company_name IS NULL THEN '"company_name"' END,
        CASE WHEN post_code IS NULL THEN '"post_code"' END,
        CASE WHEN address IS NULL THEN '"address"' END,
        CASE WHEN phone_number IS NULL THEN '"phone_number"' END
      ], ', '
    ) AS missing_keys
  FROM
    `[PROJECT_ID].demo_dataset.missing_values_table`
),

gemini_results AS (
  SELECT
    id, -- 後で結合するためにIDを取得
    -- Geminiの出力からJSON部分のみを抽出・整形
    REGEXP_REPLACE(
      JSON_VALUE(ml_generate_text_result['candidates'][0]['content'], '$.parts[0].text'),
      r'^```json\s*|\s*```$', '' 
    ) AS gemini_json_cleaned,
    -- 実際にGeminiが実行した検索クエリ（デバッグ用）
    ml_generate_text_result['candidates'][0]['grounding_metadata']['web_search_queries'] AS used_search_query,
  FROM
    ML.GENERATE_TEXT(
      MODEL `demo_dataset.gemini`,
      (
        SELECT
          *,
          -- プロンプトの最終組み立て
          CONCAT(
            '以下の情報をもとに、Google検索を用いて', missing_labels, 'を調査してください。',
            '\n結果はJSON形式で出力してください。',
            '\nJSONのキーは ', missing_keys, ' としてください。',
            '\n余計な文章は含めず、JSONのみを返してください。',
            '\n検索時は引用符を用いないでください。',
            '\n\n--- 既知の情報 ---',
            known_info
          ) AS prompt
        FROM
          prompt_components
        WHERE
          missing_labels != '' -- 欠損がない行はGeminiに投げない
      ),
      STRUCT(
        TRUE AS ground_with_google_search,
        0.0 AS temperature
      )
    )
)

SELECT
  -- baseテーブルの全カラムを取得しつつ、指定したカラムだけロジック適用後の値に置き換える
  base.* REPLACE (
    -- 企業名：元があれば元、なければGemini
    COALESCE(base.company_name, JSON_VALUE(gemini.gemini_json_cleaned, '$.company_name')) AS company_name,
    
    -- 郵便番号：クレンジング付きで置換
    REGEXP_REPLACE(
      COALESCE(base.post_code, JSON_VALUE(gemini.gemini_json_cleaned, '$.post_code')),
      r'[^0-9]', ''
    ) AS post_code,
    
    -- 住所
    COALESCE(base.address, JSON_VALUE(gemini.gemini_json_cleaned, '$.address')) AS address,
    
    -- 電話番号：クレンジング付きで置換
    REGEXP_REPLACE(
      COALESCE(base.phone_number, JSON_VALUE(gemini.gemini_json_cleaned, '$.phone_number')),
      r'[^0-9]', ''
    ) AS phone_number
  ),
  
  -- Gemini側のデバッグ情報
  gemini.used_search_query

FROM
  [PROJECT_ID].demo_dataset.missing_values_table AS base
LEFT JOIN
  gemini_results AS gemini
ON
  base.id = gemini.id
;

 プロンプトの生成例上記のクエリによって、実際に Gemini に投げられるプロンプトは以下のようになります。

例えば、ID: 2（社名のみ欠損している行） の場合、以下のようなテキストが動的に構成されます。
以下の情報をもとに、Google検索を用いて「企業名」を調査してください。
結果はJSON形式で出力してください。
JSONのキーは "company_name" としてください。
余計な文章は含めず、JSONのみを返してください。
検索時は引用符を用いないでください。

--- 既知の情報 ---
郵便番号: 1000004
住所: 東京都千代田区大手町１丁目７−２ 東京サンケイビル 26階
電話番号: 0362805939
このように、行ごとに「何を知りたいか」と「何を知っているか」がモデルに入力されます。

 実行結果と考察上記のクエリを実行すると、NULL だった箇所が補完されたテーブルが生成されます。
実行結果

特に注目すべきは以下の挙動です。

逆引き検索の成功:

company_name が欠損していた行（ID:2）において、電話番号や住所をキーにした検索が行われ、そこから「クラウドエース株式会社」を正しく特定できています。

自律的な検索キーワード生成:

used_search_query を見ると、Gemini が「今、何を知っているか」「何を知りたいか」を理解し、最適なキーワードを自分で考えて検索していることがわかります。

 まとめBQML と Gemini の Grounding 機能を活用することで、これまで手作業や外部スクリプトで行っていた「Web 検索を伴うデータ補完」を SQL 内で完結させることができました。
生成 AI なので 100% の精度を保証するものではありませんが、大量のデータに対する一次補完としては強力なソリューションになります。

「データが汚くて使い物にならない」と諦める前に、ぜひ BQML と Gemini でデータの掃除を試してみてください。

id	company_name	phone_number	post_code	address
1	クラウドエース株式会社	0362805939	1000004	東京都千代田区大手町１丁目７−２東京サンケイビル 26階
2	NULL	0362805939	1000004	東京都千代田区大手町１丁目７−２東京サンケイビル 26階
3	クラウドエース株式会社	NULL	1000004	東京都千代田区大手町１丁目７−２東京サンケイビル 26階
4	クラウドエース株式会社	0362805939	NULL	東京都千代田区大手町１丁目７−２東京サンケイビル 26階
5	クラウドエース株式会社	0362805939	1000004	NULL
6	クラウドエース株式会社	NULL	NULL	NULL

はじめに

対象読者

検証

アーキテクチャとアプローチ

前提設定

検証用データの準備

実行クエリの解説

ポイント

実際のクエリ

プロンプトの生成例

実行結果と考察

まとめ

Discussion