😎
BigQuery は Gemini で日本語プログラミングの夢を見るか

2024/12/23に公開
Google Cloud
 概要Gemini では、画像やPDFからマルチモーダルで OCR を実施できるようになりました。

これを使うことで、Vision API で OCR をしたり、Document AI で解釈するより手間を少なく情報を抽出できます。
プロンプトでキャプチャ内容を指示すると、そこはまるで、昔に多くの日本人技術者が夢を見て、実現はしたものの普及はしなかった日本語プログラミング、その世界がここに現出したかのような錯覚を得ることが出来ます。

この方法での画面キャプチャは、実は、非エンジニアにおすすめです。
本稿では、BigQuery によるサーバレス環境にて Gemini でプロンプトを使った OCR をすることにより、お手軽に日本語プログラミングの夢を見たいと思います。

 忙しい人向け本稿のクエリの完成形は、以下のようになります。
WITH gemini AS (
  SELECT JSON_EXTRACT_SCALAR(ml_generate_text_result.candidates[0].content.parts[0].text, "$.") AS csv
  FROM ML.GENERATE_TEXT(
    MODEL asia_northeast1.gemini_1_5_pro,
    (
      SELECT *
      FROM asia_northeast1.shisen
      WHERE uri LIKE '%.pdf'
    ),
    STRUCT(
      """
各画像には、左から順に順位、プレイヤー名、スコアが複数行記録されています。
順位は数字と「位」でできており、「位」は不要です。
スコアは上部に「スコア」があり、下部に数字があり、「スコア」は不要です。

プレイヤー名は、次の行にカンマ区切りで示します。
*****,*****,*****,*****,*****...

以上を元にして、CSV形式で以下の内容を出力してください。
ヘッダ行は不要です。
重複は排除してください。
順位の降順で出力してください。
・順位
・プレイヤー名
・スコア

      """ AS prompt
      , 8192 AS max_output_tokens
      , 0 AS temperature
    )
  )
), r AS (
  SELECT SPLIT(csv, "\n") as ro
  FROM gemini
), c AS (
  SELECT SPLIT(ro, ",") AS cols 
  FROM r, r.ro
)

SELECT cols[0] AS `順位`, cols[1] AS `プレイヤー名`, cols[2] AS `スコア`
FROM c
WHERE ARRAY_LENGTH(cols) = 3
それぞれ、何をしているのかを以下で解説していきます。

 アーキテクチャBigQuery から Gemini を使うためには、いくつかの準備が必要です。

ここでは、本稿特有の部分と、BigQuery から Gemini を使うための特有の部分に分けてアーキテクチャを提示します。

 本稿特有のアーキテクチャまずは本稿特有の部分を確認します。
今回対象にするのは、スマートフォンゲームのランキングです。

スマートフォンのゲームの情報は、スクリーンショットで画像として取得するしかないでしょう。

スクリーンショットは Google Photos に自動連携にすることで、少ない手間で取り込むことが出来ます。

BigQuery でスクリーンショットの画像を扱うためには、Cloud Storage にアップロードする必要があります。

ここで、Google Photos への連動後に Google Apps Script で自動的に Cloud Storage に連動できればシステムとして効率は非常に上がるとは思いますが、本稿ではここの自動化はしません。

そこで、Google Photos から PC に手動でダウンロードし、手動で Cloud Storage にアップロードします。

 BigQuery から Gemini を扱うためのアーキテクチャ次に、BigQuery から Gemini を使うためのアーキテクチャを確認します。
BigQuery から Gemini を使うには、大きく 2 つの手順が必要です。

まず、BigQuery から Gemini を参照するためのリモートモデルを作ります。

次に、BigQuery から Cloud Storage を参照するためのオブジェクトテーブルを作ります。

ここまで出来たら、Gemini から ML.GENERATE_TEXT() 関数で OCR をします。

最後に、出力結果を応用するために、出力内容を BigQuery のテーブルに出力します。
では、実際に作業をしていきましょう。

 準備準備では、3つの作業をします。

本稿ではこの準備部分は本質的ではないので、稿を改めることとしまして、後日それぞれに付いて解説します。

準備内容は、
接続の作成
オブジェクトテーブルの作成
リモートモデルの作成
です。

 テストここまでで準備が完了しました。

では、テストをしていきましょう。

 テストを実行するテスト用に用意したスクリーンショットを15個取って、文字列に起こします。

スクリーンショットを取ったゲーム内の画面はランキングです。

ランキングは、順位、プレイヤー名、スコアの3つが並んでいます。

プロンプトでは、これをそのまま、日本語で表現します。

出力はCSV形式にしてみます。
SELECT *
FROM ML.GENERATE_TEXT(
  MODEL asia_northeast1.gemini_1_5_pro,
  TABLE asia_northeast1.shisen,
  STRUCT(
    """
各画像には、左から順に順位、プレイヤー名、スコアが複数行記録されています。
順位は数字と「位」でできており、「位」は不要です。
スコアは上部に「スコア」があり、下部に数字があり、「スコア」は不要です。

以上を元にして、CSV形式で以下の内容を出力してください。
ヘッダ行は不要です。
重複は排除してください。
順位の降順で出力してください。
・順位
・プレイヤー名
・スコア

    """ AS prompt
    , 8192 AS max_output_tokens
    , 0 AS temperature
  )
)
プロンプト内容は、画像の内容をそのままです。

いえ、それこそが重要です。

OCR 結果を解釈するためには、今まではプログラムで文字列を解釈したり座標を解釈することが主でした。

それが、このように日本語で指示するだけで、構造を整理することが出来るわけです。
結果を見てみます。
出力項目は、
ml_generate_text_result
ml_generate_text_status
uri
generation
content_type
size
md5_hash
updated
metadata
name
vakue

という構造です。

重要なのは、このうち２つ。ml_generate* の２つです。
ml_generate_text_result は、以下のような形式です。

プレイヤー名は、マスクします。
{
	"candidates": [
		{
			"avg_logprobs": -0.0000872053616844556,
			"content": {
				"parts": [
					{
						"text": "61,****,12050\n62,****,11690\n63,****,11690\n64,****,11020\n65,s,10410\n66,****,10410"
					}
				],
				"role": "model"
			},
			"finish_reason": "STOP",
			"score": -0.00592996459454298
		}
	],
	"model_version": "gemini-1.5-pro-002",
	"usage_metadata": {
		"billable_prompt_usage": {
			"image_count": 1,
			"text_count": 146
		},
		"candidates_token_count": 68,
		"prompt_token_count": 350,
		"total_token_count": 418
	}
}
OCR した結果は、 $.candidates[0].content.parts[0].text に入っているようです。

しかし、結果が入ってないものもあります。
かわりに、ml_generate_text_status に値が入っています。

内容は
A retryable error occurred: RESOURCE_EXHAUSTED error from remote service/endpoint.
でした。

では、結果を検討していきます。

 問題点を考える１つ目の問題点は、ml_generate_text_status に入っている場合です。

なんらかの Quota に引っかかっているようですが、どの Quota にひかかったかは調べましたが不明でした。

ひとまず、これを 10 秒間のリクエスト数によるQuota と仮定して考えます。
２つ目の問題点は、結果の値です。

CSV が素直に入っているのはいいのですが、文字列で入っていますので、これを抽出して TABLE 型に変換しないこと
３つ目の問題点は、マスキングしているのでわかりにくいですが、固有名詞が正しく処理できていない問題です。

ビジネスで使う場合は、結構問題になる点かと思います。

傾向としては、どうやら辞書に載っている単語から外れる言葉を使っている場合に、ずれるようです。

複数回実験しましたが、この手のものはどうも、同じ画像でも解釈がかわって違う単語が出てくる事があるようで、厄介です。

 リクエスト上限に対処する現状、ML.GENERATE_TEXT 関数はファイルを複数纏めず、1つづつリクエストで送るようだ。

そこで、これを一つで処理するのではなく複数まとめることで軽減をはかってみたい。

Gemini では、PDFを処理できる。

そこでPDFで一つにまとめてみる。

ここでは対象が png なので、img2pdf を使って以下のように処理する。
sudo apt update && sudo apt install img2pdf -y
ls *.png | xargs -i -n 100 img2pdf --pagesize A4 --rotation=ifvalid -o 2024-12-20.pdf
これを Cloud Storage にアップロードし、処理対象を当該 pdf だけに絞るため、以下のように処理対象のファイルをクエリで指定します。

また複数の画像にまたがって一挙に OCR するため、データの順番と重複排除を指示します。
SELECT *
FROM ML.GENERATE_TEXT(
  MODEL asia_northeast1.gemini_1_5_pro,
  (
    SELECT *
    FROM asia_northeast1.shisen
    WHERE uri LIKE '%2024-12-20.pdf'
  ),
  STRUCT(
    """
各画像には、左から順に順位、プレイヤー名、スコアが複数行記録されています。
順位は数字と「位」でできており、「位」は不要です。
スコアは上部に「スコア」があり、下部に数字があり、「スコア」は不要です。

以上を元にして、ヘッダ無しのCSV形式で全ての画像に含まれる以下の内容を、重複を排除して、順位の降順で出力してください。
・順位
・プレイヤー名
・スコア

    """ AS prompt
    , 8192 AS max_output_tokens
    , 0 AS temperature
  )
)
すると、全てのファイルが出力されるようになりました。


 出力をテーブルに格納するコンテンツをJSON から STRING 型として抽出するため、JSON_EXTRACT_SCALAR() 関数で値を出します。
SELECT JSON_EXTRACT_SCALAR(ml_generate_text_result.candidates[0].content.parts[0].text, "$.") AS csv
....
後半は省略しました。

すると、値は
｀｀｀csv
(CSV 本体)
｀｀｀
と出力されました。

(マークダウン文法の問題で、バッククウォートを全角に変換しています)
ここから文字列を CSV で取り出し、テーブルに変換します。

残りを改行で分割し、カンマで分割して各行と列に変換します。

最初の行、最後の行や、うまく取り込めてない行は不要な行とし、, 区切りで想定してないカラム数のものは排除します。

そのうえで、カラムに名前を付けます。

この完成形は、こうなります。
), txt AS (
  SELECT JSON_EXTRACT_SCALAR(ml_generate_text_result.candidates[0].content.parts[0].text, "$.") AS csv
  FROM gemini
), r AS (
  SELECT SPLIT(csv, "\n") as ro
  FROM txt
), c AS (
  SELECT SPLIT(ro, ",") AS cols 
  FROM r, r.ro
  WHERE ro NOT IN ('csv```', '```', '')
), ra AS (
  SELECT c.cols, RANK() OVER (ORDER BY cols[0]) ranc
  FROM c
)

SELECT cols[0] AS `順位`, cols[1] AS `プレイヤー名`, cols[2] AS `スコア`
FROM c
WHERE ARRAY_LENGTH(cols) = 3

 固有名詞を正しくするプレイヤー名は、日本語の単語から外れるような言葉が使われている場合が多々あります。

OCRは、標準的な単語に吸い寄せられるように文字列化します。

このような場合は、直接単語を指示することで、意図した文字列にすることが出来ます。

そのために、
プレイヤー名は、次の行にカンマ区切りで示します。
*****,*****,*****,*****,*****...
プロンプトに、この一文を加えます。
こうして、テーブルに収められる形で抽出でき、更に抽出したデータを応用できるようになりました。

 更に改善するために本稿ではできなかった部分がいくつかあります。
ML.GENERATE_TEXT() に、PDFを経由せずに複数画像を一括で渡す
プレイヤー名などの固有名詞を、RAG のような仕組みでプロンプトに対して外部化することで、データと指示の分離を図る
出力を JSON にしてから BigQuery の組み込み関数でテーブルに変換するか、複雑な CSV でもパースできるように UDF を使った関数で処理できるようにする
法定文章などの、更に複雑な文章を OCR で分析し、問題点を検討する
これらは、後日稿を改めて検討してくこととしたいと思います。
概要

忙しい人向け

アーキテクチャ

本稿特有のアーキテクチャ

BigQuery から Gemini を扱うためのアーキテクチャ

準備

テスト

テストを実行する

問題点を考える

リクエスト上限に対処する

出力をテーブルに格納する

固有名詞を正しくする

更に改善するために

Discussion
