BigQueryでGemini flashを使用して住所を分割する

WED株式会社でデータエンジニアをしているthimi0412です。
レシート買取アプリONEではレシート画像に対してOCRを行い、LLMのモデルを使用してOCRしたテキストデータを構造化を行なっています。

構造化したデータはデータベースに保存されており、そのデータはBigQueryに連携されています。
その中にはレシートに記載されている地点情報もあり、1行の住所のテキストデータがありますが、都道府県、市区町村単位で分析や抽出を行うときには、扱いづらい形式になっています。

-東京都渋谷区千駄ケ谷4丁目21-5
+東京都 渋谷区 千駄ケ谷 4丁目 21-5

住所の分割=住所の正規化ということなので多くの方が苦労していると思います。
今回はWEDのルールに沿ってGeminiを使用して住所情報を分割・整形をおこなおうと思います。

WEDでの地点情報のデータの持ち方について

WEDでは電話番号をkeyとして、地点情報を管理しています。
カラムとしては以下のような構成です。

• phone_number: 電話番号
• store_name: 店舗名
• chain_name: チェーン名
• address: 店舗の住所
• address_level_1: 都道府県
• address_level_2: 市区
• address_level_3: 町村
• address_level_4: 丁目
• address_level_5: 番地
• industory_name: 業態(コンビニやスーパーなど)
• location: 経緯度(Geography型で格納)
• etc...

前述した通りaddressからaddress_level_1~5を分割して作成していきます。
もちろんOCRの読み取りミスやLLMのモデルが抽出した住所情報が間違っているなどがあり、正確な住所情報ではない可能性がありますが分割をしてみます。

リソースの作成

なるべくTerraformを使用してリソースを作成していきます。
BigQuery Connectionと作成時に作成されるサービスアカウントにroles/aiplatform.userの権限をつけます。

resource "google_bigquery_connection" "data_lab_gemini_flash" {
  connection_id = "connect-data-lab-gemini-flash"
  friendly_name = "connect to cloudrun data-lab-gemini-flash"
  location      = "us"
  project       = var.project_id
  description   = "Gemini flashへリクエストを送る関数"
  cloud_resource {
  }
}

resource "google_project_iam_member" "data_lab_gemini_flash" {
  project = var.project_id
  role    = "roles/aiplatform.user"

  member = "serviceAccount:${google_bigquery_connection.data_lab_gemini_flash.cloud_resource[0].service_account_id}"
}

モデルの作成

SQLを使ってモデルを作成します。最近(2024/12/16現在)gemini-2.0-flash-expが出たので使ってみます。

CREATE OR REPLACE MODEL data_lab.gemini_flash
REMOTE WITH CONNECTION `us.connect-data-lab-gemini-flash`
OPTIONS(
  ENDPOINT = 'gemini-2.0-flash-exp'
)

コンソールで確認するとdata_labというデータセットの中にモデルが追加されました。

BigQueryからGeminiを使用する

SQL

create or replace table sandbox.shimizu_gemini_test
as
WITH input_data AS (
  SELECT
    attached_address,
    CONCAT(
      '''
      住所を以下のルールに従って住所を分割してjsonで出力してください
      - addres_level_1には都道府県
      - addres_level_2には市と区
        - 市と区が両方含まれている場合は合わせて入れる
      - addres_level_3 町の名前
      - addres_level_4 丁目
        - 丁目という文字がある場合に入れる、ない場合はnullにする
      - addres_level_5 番地名
        - 111-1のようなものも含みます
      - 住所情報のみjsonに格納してください
      ''',
      address
    ) AS prompt
  FROM dmt_v2.receipts
  WHERE
    updated_at >= '2024-12-15'
    and address is not null
  LIMIT 20
),
generated_output AS (
  SELECT
    *
  FROM ML.GENERATE_TEXT(
    MODEL data_lab.gemini_flash,
    TABLE input_data,
    STRUCT(
      0.0 AS temperature,
      200 AS max_output_tokens,
      0.0 AS top_p,
      1 AS top_k)
  )
)
SELECT
  attached_address,
  JSON_VALUE(ml_generate_text_result.candidates[0].content.parts[0].text) as response
FROM generated_output

着目するところは以下の2つ

プロンプトを作成

CONCATでaddress(住所の文字列)をくっつけてプロンプトを作成しています。

CONCAT(
  '''
    住所を以下のルールに従って住所を分割してjsonで出力してください
    - addres_level_1には都道府県
    - addres_level_2には市と区
    - 市と区が両方含まれている場合は合わせて入れる
    - addres_level_3 町の名前
    - addres_level_4 丁目
      - 丁目という文字がある場合に入れる、ない場合はnullにする
    - addres_level_5 番地名
      - 111-1のようなものも含みます
    - 住所情報のみjsonに格納してください
  ''',
  address
) AS prompt

レスポンス

geminiから返ってきたレスポンスはJSON形式なのでJSON_VALUEを使いstringとして値を取得します。

SELECT
  attached_address,
  JSON_VALUE(ml_generate_text_result.candidates[0].content.parts[0].text) as response
FROM generated_output

結果を見る

割と良さそうな雰囲気で入ってますね。

中身を見るとこのような感じです。

```json
{
  "address_level_1": "静岡県",
  "address_level_2": "袋井市",
  "address_level_3": "旭町",
  "address_level_4": null,
  "address_level_5": "21"
}
```

しかし、現状はSTRINGの文字列として格納されているのでJSON形式にしてaddress_level_1~5を取得できるようにします。

create or replace table sandbox.shimizu_gemini_test_json
as
with response_json as (
  SELECT
    attached_address,
    PARSE_JSON(REGEXP_REPLACE(response, r'```|json', '')) AS address_json
  FROM
    sandbox.shimizu_gemini_test
  where
    response is not null
)
select
  attached_address,
  json_extract_scalar(address_json, '$.address_level_1') as addres_level_1,
  json_extract_scalar(address_json, '$.address_level_2') as addres_level_2,
  json_extract_scalar(address_json, '$.address_level_3') as addres_level_3,
  json_extract_scalar(address_json, '$.address_level_4') as addres_level_4,
  json_extract_scalar(address_json, '$.address_level_5') as addres_level_5,
from
  response_json

PARSE_JSONを使いSTRINGのJSONをJSON型に変換したいですが、```とjsonの文字列のせいで変換ができないので、REGEXP_REPLACEを使用して削除したのちにJSON型に変換しています。

最終アウトプットはこんな感じ

終わりに

Geminiを使用してOCRして得られた1行の住所のテキストデータを分割することができました。
しかし、どうしてもOCRの読み取りミスやLLMのモデルが抽出した住所情報が間違っている可能性もあるので最終的には人目で確認を行い住所情報の充実させていこうと思っています。