📮
BigQuery で始めるベクトル検索〜郵便番号検索機能を作ってみよう〜

Mario
2024/12/01に公開
Google Cloud Champion Innovators Advent Calendar 2024 の 1 日目の記事です。
三度の飯より BigQuery が大好き、Mario です。
皆さん、そろそろ年末に向けて年賀状の準備を考えてらっしゃるのではないでしょうか？
最近は電子年賀状や SNS での挨拶が定番化してきていますが、やはり一手間を感じる心がこもった紙の年賀状をもらえると嬉しいなーと思う派です。（もらえるとは言ってない）
でも電子にしろ紙にしろ、ちょっと厄介なのが宛先の住所管理です。住所リストを作成していても、郵便番号がなかったり、逆に住所表記が怪しかったり、加筆修正が必要なときってありますよね。（そもそも若干の不足や誤記があっても届くのが日本郵政さんの素晴らしいところ）
そんな問題は BigQuery でテキストエンベディングによる情報検索で解決しちゃいましょう。
※ 執筆時点でプレビューの機能については今後仕様が変更される可能性もありますことをご留意ください。

 本記事のゴールBigQuery でのエンべディングを使った検索（ベクトル検索）の方法を理解する。
LIKE 検索、正規文字列検索との違いを理解する。
RAG の根幹の仕組みを理解する。
※ すでに Google Cloud のプロジェクトがあり、BigQuery が使える状態にあることを想定しています。

 郵便情報マスタの作成それでは早速作成していきましょう。今回の作業用に BigQuery のデータセットを作成しておくことをお勧めします。

 郵便情報データのダウンロード日本郵政公式サイトから郵便番号最新全データをダウンロードし解凍します。
ファイルは CSV 形式、UTF-8（BOM 無し）、ヘッダ無しで、特段の加工は不要で BigQuery に入れられます。フィールドの仕様は以下の通り。
全国地方公共団体コード（JIS X0401、X0402）………　半角数字
（旧）郵便番号（5桁）………………………………………　半角数字
郵便番号（7桁）………………………………………　半角数字
都道府県名　…………　全角カタカナ（コード順に掲載）　（※1）
市区町村名　…………　全角カタカナ（コード順に掲載）　（※1）
町域名　………………　全角カタカナ（五十音順に掲載）　（※1）
都道府県名　…………　漢字（コード順に掲載）　（※1,2）
市区町村名　…………　漢字（コード順に掲載）　（※1,2）
町域名　………………　漢字（五十音順に掲載）　（※1,2）
一町域が二以上の郵便番号で表される場合の表示　（※3）　（「1」は該当、「0」は該当せず）
小字毎に番地が起番されている町域の表示　（※4）　（「1」は該当、「0」は該当せず）
丁目を有する町域の場合の表示　（「1」は該当、「0」は該当せず）
一つの郵便番号で二以上の町域を表す場合の表示　（※5）　（「1」は該当、「0」は該当せず）
更新の表示（※6）（「0」は変更なし、「1」は変更あり、「2」廃止（廃止データのみ使用））
変更理由　（「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整等、「5」訂正、「6」廃止（廃止データのみ使用））
※1
文字コードには、UTF-8（BOM無し）を使用しています。
※2
文字セットとして、JIS X0208-1983を使用し、規定されていない文字はひらがなで表記しています。
※3
「一町域が二以上の郵便番号で表される場合の表示」とは、町域のみでは郵便番号が特定できず、丁目、番地、小字などにより番号が異なる町域のことです。
※4
「小字毎に番地が起番されている町域の表示」とは、郵便番号を設定した町域（大字）が複数の小字を有しており、各小字毎に番地が起番されているため、町域（郵便番号）と番地だけでは住所が特定できない町域のことです。
引用：郵便番号データ（1レコード1行、UTF-8形式）の説明

 BigQuery へのインポート以降のイメージやスニペットに記載の、Google Cloud で使うパラメータを記載しておきます。ここを皆さんの環境に応じて変えてください。
Project : gc-champions-advent-2024
BigQuery Dataset : work
Region : asia-northeast1
解凍したファイル utf_ken_all.csv を BigQuery にインポートします。
コンソールの場合、作業用データセットの右の3点メニューを押してテーブルの作成ウィジェットを開き、郵便番号データファイルをアップロードします。
それぞれ以下のように入力します。その他は原則デフォルトのままで、テーブルを作成します。
テーブルの作成元：アップロード
ファイルを選択：ローカルのファイル utf_ken_all.csv を選択
ファイル形式：CSV（ファイルを指定すると自動で選択されるはず）
テーブル：jp-postal-code
テーブルタイプ：ネイティブ テーブル
スキーマ：
ファイル中にはヘッダがないため、自身でスキーマ（カラムの名前と型）をセットします。
「テキストとして編集」を押し、以下をコピペしてください。この通りでなくて問題ないですが、以後はこのフィールド名を使って説明していきます。（other 以後は今回使わないので適当）

スキーマ例
local_gov_code:STRING,
postal_code_old:STRING,
postal_code:STRING,
pref_kana:STRING,
city_kana:STRING,
oaza_kana:STRING,
prefecture:STRING,
city:STRING,
oaza:STRING,
other1:INTEGER,
other2:INTEGER,
other3:INTEGER,
other4:INTEGER,
other5:INTEGER,
other6:INTEGER

 エンべディング関数の作成
 そもそもエンべディングって？エンべディングとは、テキストや画像などのデータを数値のベクトル（数列）に変換する技術です。このベクトル化により、機械がデータを理解しやすくなり、データ同士の類似性を数値的に評価することが可能になります。特にテキストデータでは、単語や文章をベクトルに変換し、その意味や文脈を機械学習モデルが理解できるようにします。
LLM（大規模言語モデル）によるエンべディングは、非常に多くのテキスト情報を学習したモデルを用いて、より高度で文脈を考慮したベクトルを生成します。これにより、単語や文章の意味をより正確に捉え、検索や分類、推薦システムなど様々な用途で利用することができます。
たとえば、郵便番号検索の文脈では、住所の一部だけでもエンべディングを用いて類似する住所を探し出し、正確な郵便番号を見つけることが可能です。これは、単なる文字列一致ではなく意味的な一致を考慮するため、多少の誤記や異なる表現でも正しく対応できる点が強みです。

 エンべディング用の関数の作成BigQuery ML の機能では機械学習モデルの開発・利用向けの関数だけでなく、LLM を呼び出してのテキスト生成やエンべディングも可能です。これが本当に便利。
モデルにもよりますが、例えば Gemini 1.5 pro / flash であれば、テキスト生成・エンべディングだけでなく、画像や動画といったマルチモーダルなエンべディングもできます。
ちなみに本論では触れませんが、BigQuery では Cloud Run や Cloud Run Functions にデプロイされた関数をリモート関数として呼び出すことで、標準の関数にはない様々な処理を行うことができます。また、PySpark Procedure や BigQuery Workflow といった、従来の SQL にとらわれないデータ処理の拡張性の高さが BigQuery の良いところです。まさに unified AI-ready data platform。
関数のフレーム（ML.TEXT_EMBEDDING, ML.GENERATE_TEXT など） は用意されているのですが、使えるようにするには数ステップかかります。こちらガイドページの通り進めば作成できますので省略しますが、ステップは以下になります。
ユーザーアカウントへの権限の設定
接続の作成
接続で使われるサービスアカウントへの権限の設定
モデルの作成
最後のモデル作成のパートでは、使うモデルのエンドポイントを選択できます。
サポートされているモデルはいくつかありますが、今回は最新版かつ日本語（多言語）に対応したtext-multilingual-embedding-002 を用います。
モデル作成のクエリ例
CREATE OR REPLACE MODEL `gc-champions-advent-2024.work.text-multilingual-embedding`
REMOTE WITH CONNECTION `projects/gc-champions-advent-2024/locations/asia-northeast1/connections/postal-matching`
OPTIONS (ENDPOINT = 'text-multilingual-embedding-002');

 郵便情報データのエンべディングでは早速作ったモデルを使って郵便情報データのエンべディングを行なっていきましょう。
今回は【都道府県（prefecture）＋市区町村（city）＋大字（oaza）】の 3 フィールドを結合させたものを数値化します。
エンべディングする対象フィールドの名前をcontentとする必要があります。
複数の文字列フィールドを結合する際は || を挟むだけでできます。こんな感じ。
SELECT 
  postal_code,
  prefecture,
  city,
  oaza,      
  prefecture || city || oaza content
FROM `gc-champions-advent-2024.work.jp-postal-code`
LIMIT 10;
エンべディングをする際は以下のように関数と対象のテーブルを呼び出してきて使います。
SELECT *
FROM ML.GENERATE_EMBEDDING(
  MODEL `gc-champions-advent-2024.work.text-multilingual-embedding`,
  TABLE target_table,
  STRUCT('SEMANTIC_SIMILARITY' AS task_type)
);
STRUCT〜 の部分はオプション パラメータが入ります。
今回のケースでは指定したテキストが単純な文字列としての類似性だけでなく、住所の階層構造や地理空間などの関係性の理解への期待から、意味論的テキスト類似性（Semantic Textual Similarity）評価を選択するためtask_type を SEMANTIC_SIMILARITY にしています。
エンべディングの結果は例えば、
[
	0.060680907219648361,
	-0.0051814126782119274,
	-0.035163462162017822,
	...,
	0.034679889678955078,
	-0.037007220089435577,
	0.0031336524989455938
] 
と全部で 768 次元のベクトルで表現され、配列として格納されます。
text-embedding-004 以降、ext-multilingual-embedding-002 以降ではオプションoutput_dimensionality で次元数を 1 〜 768 の間で任意に変えることも可能です。（デフォルトは 768 次元）
エンべディングをした結果を直接テーブルとして書き込むようにクエリを書いてみましょう。
CREATE OR REPLACE TABLE `gc-champions-advent-2024.work.jp-postal-code-embedded` AS

WITH
  target_table AS (
  SELECT 
      postal_code,
      prefecture,
      city,
      oaza,      
      prefecture || city || oaza content
    FROM `gc-champions-advent-2024.work.jp-postal-code`
  )

SELECT *
FROM ML.GENERATE_EMBEDDING(
  MODEL `gc-champions-advent-2024.work.text-multilingual-embedding`,
  TABLE target_table,
  STRUCT('SEMANTIC_SIMILARITY' AS task_type)
);
郵便情報は全件で12万件超で、テーブル作成まで約4時間半ほどかかりました。ちなみに手前の環境では、一度目の実行で約1,400件のレコードで A retryable error occurred: RESOURCE_EXHAUSTED error from remote service/endpoint. のエラーが発生し、エンべディングが失敗していました。なので以下のクエリを実行し、失敗したレコードに再実行をかけます。
CREATE OR REPLACE TABLE `gc-champions-advent-2024.work.jp-postal-code-embedded` AS
WITH
  t0 AS (
  SELECT *
    FROM `gc-champions-advent-2024.work.jp-postal-code-embedded`
  )

SELECT *
FROM ML.GENERATE_EMBEDDING(
  MODEL `gc-champions-advent-2024.work.text-multilingual-embedding`,
  (
  SELECT 
    * EXCEPT(ml_generate_embedding_result, ml_generate_embedding_statistics, ml_generate_embedding_status) 
    FROM t0 
    WHERE ARRAY_LENGTH(ml_generate_embedding_result) != 768
  ),
  STRUCT('SEMANTIC_SIMILARITY' AS task_type)
)

UNION ALL

SELECT * 
FROM t0 
WHERE ARRAY_LENGTH(ml_generate_embedding_result) = 768;
うまく調整すればもっと短縮できるかもしれません。
ちなみに、この作成にかかる金額は5円程度と驚安。

 検索検証ようやく検索の準備が整いましたので、色々試してみましょう。
検索の際のデータフローは以下になります。
検索キーワードを同様にエンべディングする。
クロスジョインで全郵便情報のレコードと検索キーワードを紐付ける。
ML.DISTANCE を用いて類似性の値distanceを計算。
QUALIFY句を使い、類似性 distance が最も小さくなる郵便情報を解とする。
恐らく日本で一番有名なランドマーク「東京タワー」の住所【東京都港区芝公園４丁目２】を例に取って色々変えながら結果を見てみましょう。正しい郵便番号は【〒105-0011】となります。
検索キーワードリスト
東京都港区芝公園４丁目２
東京都港区芝公園4-2
東京都港区芝公園
東京都港区公園
東京都港区芝
港区芝公園
芝公園

 クエリ例WITH
  poi AS (
  SELECT *
    from unnest([
      '東京都港区芝公園４丁目２',
      '東京都港区芝公園4-2',
      '東京都港区芝公園',
      '東京都港区公園',
      '東京都港区芝',
      '港区芝公園',
      '芝公園'
    ]) content
  )

SELECT 
  t1.content search_word,
  postal_code,
  prefecture,
  city,
  oaza,
  ml.distance(
    t0.ml_generate_embedding_result, 
    t1.ml_generate_embedding_result, 
    'COSINE'
  ) distance
FROM `gc-champions-advent-2024.work.jp-postal-code-embedded` t0,
(
SELECT content, ml_generate_embedding_result
  FROM 
  ML.GENERATE_EMBEDDING(
      MODEL `gc-champions-advent-2024.work.text-multilingual-embedding`,
      TABLE poi,
    STRUCT(
      TRUE AS flatten_json_output, 
      'SEMANTIC_SIMILARITY' AS task_type
    )
  )
) t1
QUALIFY row_number() OVER (PARTITION BY search_word ORDER BY distance) = 1
ORDER BY postal_code, search_word DESC

 実行結果

search_word
postal_code
prefecture
city
oaza
distance


東京都港区芝公園４丁目２
1050011
東京都
港区
芝公園
0.054764782242168408

東京都港区芝公園4-2
1050011
東京都
港区
芝公園
0.040964237275455173

東京都港区芝公園
1050011
東京都
港区
芝公園
0.0

東京都港区公園
1050011
東京都
港区
芝公園
0.047234033209324733

東京都港区芝
1050012
東京都
港区
芝大門
0.038125100041605076

港区芝公園
1050011
東京都
港区
芝公園
0.02866217506051516

芝公園
1050011
東京都
港区
芝公園
0.067336377562225

東京都港区芝 のみ外れてしまいましたが、このように多少の表記揺れや欠落、誤字があっても正確に郵便番号を紐づけることが出来ます。

 LIKE検索や正規文字列検索との違いGoogle SQL（旧称 Standard SQL）にはLIKE検索や正規文字列検索の論理式や関数がありますが、これらは条件に当てはまるものは全て抽出されてしまうこと、また指定した文字列は完全に一致しているものが抽出されてしまうため、そもそも検索に引っ掛からなかったり、複数出てきてしまってどれが最も近しいものか分かりません。
例えば以下は正しく検索できますが、
SELECT 
  postal_code,
  prefecture,
  city,
  oaza,
FROM `gc-champions-advent-2024.work.jp-postal-code-embedded`
WHERE content LIKE ('%港区芝公園%')


postal_code
prefecture
city
oaza


1050011
東京都
港区
芝公園

以下のように【芝】が抜けた状態で検索をすると
SELECT 
  postal_code,
  prefecture,
  city,
  oaza,
FROM `gc-champions-advent-2024.work.jp-postal-code-embedded`
WHERE content LIKE ('%港区公園%')
結果が返ってきません。

 まとめ従来の正規表現検索や曖昧検索よりも検索品質が上がり、エラーが返ることもなく、多少住所内の表現が実際と異なっていても正しく紐づけられるようになりました。真に曖昧な検索ができるのがベクトル検索の強みであることが分かって頂けたかと思います。
今回は日本郵政の郵便情報データを用いましたが、丁目まで入った住所マスタがあれば正規化も簡単にできます。もちろんその他の住所正規化サービスもありますが、自前の環境でできるのは嬉しいですね。
料金体系も抑えておくと、BigQuery からのテキスト用エンベディングなので、オンライン リクエスト: $0.000025 / 1,000文字となります。今回の対象 content 列の文字総数が 1,542,564 文字でしたので、約 5 円がかかったということになります。
そう、そして何よりこのベクトル検索が話題の RAG の根幹をなす仕組みです。ドキュメントや画像といった様々なリソースをベクトル化し、ベクトル データベースとして持っておくことで、自然言語での問い合わせに対して類似した回答を返すことができるというのが RAG の基礎的なお話です。
これで少しでも師走を乗り切れる力になれれば幸いです。
年賀状、お待ちしてます！
search_word	postal_code	prefecture	city	oaza	distance
東京都港区芝公園４丁目２	1050011	東京都	港区	芝公園	0.054764782242168408
東京都港区芝公園4-2	1050011	東京都	港区	芝公園	0.040964237275455173
東京都港区芝公園	1050011	東京都	港区	芝公園	0.0
東京都港区公園	1050011	東京都	港区	芝公園	0.047234033209324733
東京都港区芝	1050012	東京都	港区	芝大門	0.038125100041605076
港区芝公園	1050011	東京都	港区	芝公園	0.02866217506051516
芝公園	1050011	東京都	港区	芝公園	0.067336377562225
postal_code	prefecture	city	oaza
1050011	東京都	港区	芝公園
Google Cloud JapanPublication
Google Cloud Japan のエンジニアを中心に情報発信をしている Publication です。Google Cloud サービスの技術情報を中心に公開しています。各記事の内容は個人の意見であり、企業を代表するものではございません。
本記事のゴール

郵便情報マスタの作成

郵便情報データのダウンロード

BigQuery へのインポート

エンべディング関数の作成

そもそもエンべディングって？

エンべディング用の関数の作成

郵便情報データのエンべディング

検索検証

クエリ例

実行結果

LIKE検索や正規文字列検索との違い

まとめ

Discussion
