【Google Cloud】BigQueryで図書画像のデータを分析してみた！

【Google Cloud】BigQueryで図書画像のデータを分析してみた！

by on with コメントはまだありません
こんにちは。Google Cloud研究開発チームです。

Google Cloud Next'22において『データウェアハウスであるBigQueryを使用して、非構造化データに対し直接クエリができるようになった』旨の発表がありました。
今回は、実際にBigQueryのクエリを使用して図書画像のデータを分析してみました。

前提知識と全体アーキテクト

前提知識

まずはじめに、データの種類について確認をしておきたいと思います。
データは大きく分けて 構造化データ・半構造化データ・非構造化データ の３種類に分けられます。
今回扱う画像データは、そのうちの 非構造化データ に属します。

 
構造化データ 半構造化データ 非構造化データ
構造化データ 半構造化データイメージ画像 非構造化データイメージ画像
  • 「列」と「行」で管理できるため、そのままで集計、比較、解析、分析が可能。
  • リレーショナルデータベース（RDBMS）に格納でき、SQLで扱える。
  • 表形式ではないが、データに規則性がある。
  • NoSQLデータベースに格納される。
  • そのままでは扱えないため、非構造化データに含まれる。
  • 構造定義がされていないため、そのままでは扱えない。
  • 利用するには構造化データへの変換や加工が必要。
CSV、Excelの表形式データ、固定長など  

JSON、HTML、XMLなど

 		 画像、音声、動画、SNSのデータ、センサーログ、PDF、eメール、提案書、企画書、発注書、契約書、デザインデータ、CADデータなど
 

いかがでしょうか。こう見ると、わたしたちが普段扱っているデータの大半が、非構造化データのように思えてきます。実は、非構造化データは半構造化データと合わせて全データの80％に及ぶともいわれています。
わざわざ非構造化データをBigQueryに転送しなくてもデータ分析ができるようになったことは、大きなメリットといえるでしょう。

全体アーキテクト

今回の検証の目的は、図書画像のデータから、テキストの全文やエンティティを抽出し、BigQuery上で分析を行うことです。

具体的には、図書画像のデータにあるテキストをCloud Vision API（OCR）で抽出し、さらにNatural Language APIを使用してエンティティ抽出を行いました。
処理結果を画像URLとともにBigQueryに保存することで、抽出したテキストの内容から画像を検索したり、エンティティから単語の重要度を加味した検索をしたり・・と、様々な分析が可能になります。

今回はこれらを実現するため、以下を全体アーキテクトとして検証を進めました。
※参考
オブジェクト テーブルとは：Google Cloud - オブジェクト テーブルの概要
Cloud Vision API（OCR）とは：Google Cloud - Cloud Vision API 光学式文字認識（OCR）
Natural Language API（エンティティ分析）とは：Google Cloud - Natural Language API エンティティ分析

検証方法

今回は以下のSTEPで検証を進めました。

  1. 画像ファイルのアップロード
  2. 外部接続の作成
  3. 外部接続への権限付与
  4. オブジェクト テーブルの作成
  5. リモート関数の作成
  6. リモート関数で画像データを分析！

STEP０．前提条件

検証方法の記載は、以下の設定を前提としています。
本ブログを参考に実装される際は、必要に応じて読み替えてください。
以降の作業は、いずれもオーナーのIAM権限を持つユーザーで実行しています。
項目 設定値
プロジェクトID 変数$PROJECT_ID
初めにCloud Shellで以下のコマンドを実行し、変数$PROJECT_IDを定義しておきます。
PROJECT_ID=$(gcloud config get-value project)
※複数のプロジェクトを所有している方は、変数の値をご自身の環境に合わせて置き換えてください。
※Cloud Shellを使用する場合は上記の変数を利用できますが、
その他の場合は、[PROJECT_ID]と記載されている箇所を実際のプロジェクトIDに置き換えてください。
データセット名 tosho
ロケーション asia-northeast1
 

また、今回の検証では「パブリックドメインOCR学習用データセット（令和3年度OCRテキスト化事業分）」という国立国会図書館のデータを利用しています。
こちらの画像データは、刊行年代ごと（1870年～1940年）に理系・文系に分けて保存されています。
以下のリンクから取得が可能です。

GitHub - パブリックドメインOCR学習用データセット

STEP１．画像ファイルのアップロード

まずはじめにCloud Storageに検証用のバケットを新規作成し、事前にダウンロードしておいた画像ファイル一式をアップロードします。

今回の検証で作成したバケットは、以下のような階層構造となっています。
　gs://[バケット名]/　         ----------> バケット
　└─tosho_all_linejson/
　　└─img/
　　　└─tosho_1940_rikei/
　　　　└─*.jpg　　　　　　　　----------> 複数の画像ファイル
バケットの詳細を開くと、アップロードした画像データを確認することができます。
Cloud Storage - バケットの詳細

STEP２．外部接続の作成

外部接続を使用することで、BigQueryから外部サービス（Cloud Functions等）の実行や、外部データソース（Cloud Storage等）にクエリを送信することが可能となります。
今回は、以下の用途でBigQueryから外部のサービスおよびデータソースへの接続が必要になるため、外部接続を作成します。
  • オブジェクトテーブルの作成
  • BigQueryからリモート関数（OCR）の実行
Cloud Shellで以下のコマンドを実行します。
bq mk --connection --location=asia-northeast1 --connection_type=CLOUD_RESOURCE sts-connection-tosho
参考：Google Cloud - bqコマンドライン ツール リファレンス
コマンド実行後、BigQueryの外部接続を確認すると、sts-connection-toshoという名称で外部接続が作成されていることがわかります。
BigQuery - 外部接続

STEP３．外部接続への権限付与

外部接続が使用するサービス アカウントに対し、プロジェクトレベルで必要な権限を付与します。
◆ 付与対象のサービス アカウント IDの確認
まず、付与対象のサービス アカウント IDを確認します。
Cloud ConsoleでBigQueryを開き、『STEP２．外部接続の作成』で作成した外部接続を選択することにより、確認が可能です。
このサービス アカウントに対して、権限の付与を行います。
外部接続 - サービス アカウント ID
◆ 付与する権限の種類
今回の検証では、外部接続を使用するために以下３つの権限が必要です。
確認したサービス アカウントに対して、これらの権限を追加します。
  • Cloud Functions 起動元
  • Cloud Run 起動元
  • Storage オブジェクト閲覧者
◆ 権限の編集と確認
Cloud ConsoleのIAMと管理で、権限の編集と確認を行うことが可能です。
IAMと管理 - 権限の付与

STEP４．オブジェクト テーブルの作成

BigQueryに検証用のオブジェクト テーブルを新規作成します。

Cloud Shellで以下のコマンドを実行します。
bq mk --table \
--project_id=$PROJECT_ID \
--external_table_definition="gs://[バケット名]/tosho_all_linejson/img/tosho_1940_rikei/*@asia-northeast1.sts-connection-tosho" \
--object_metadata=SIMPLE \
--max_staleness="0-0 0 4:0:0" \
--metadata_cache_mode=AUTOMATIC \
$PROJECT_ID:tosho.1940_rikei
※オプション--external_table_definitionは以下のように設定します。
gs://Cloud Storageバケットにアップロードした画像ファイルの格納先@リージョン.外部接続名
上に記載したコマンド例では、tosho_1940_rikeiディレクトリに複数の画像ファイルを格納しているため、格納先の末尾にワイルドカード「*」を指定している点に注意してください。
一度に複数のバケットやディレクトリを指定することも可能です。
参考：Google Cloud - オブジェクト テーブルを作成する
 

コマンド実行後、BigQueryのデータセットtoshoを確認すると、1940_rikeiという名称のテーブルが作成されていることがわかります。
BigQuery - テーブルの作成

STEP５．リモート関数の作成

オブジェクト テーブルの非構造化データを分析するために、リモート関数を使用します。
今回は、Pythonを使用して以下の結果を返す関数を実装し、Cloud Functionsをデプロイします。
  • Vision APIを用いてOCRを実行した結果 
  • Natural Language APIを用いてエンティティ分析した結果
その後、BigQueryのクエリから呼び出せるようにリモート関数を構成します。
参考：Google Cloud - リモート関数を使用してオブジェクト テーブルを分析する
参考：Google Cloud - リモート関数の操作
◆ Pythonコードの作成
前述の関数を、Pythonを使用して実装します。
今回は、以下のような関数を持つmain.pyを作成しました。
関数名 処理概要 戻り値
detect_ocr Vision APIを用いて画像ファイルのOCRを実行する JSON
detect_entites Natural Language APIを用いてテキストのエンティティ分析を行う JSON
main.py 
"""
OCR ＆ Natural Language 

リモート関数
https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions?hl=ja
"""
import io
import os
import traceback
import logging
import sys
import urllib.request


すべてのコードを表示する▼
from google.cloud import vision
from google.cloud import language_v1

import functions_framework
import google.cloud.logging
from flask import jsonify, make_response

# 標準 Logger の設定
logging.basicConfig(
        format = "[%(asctime)s][%(levelname)s] %(message)s",
        level = logging.DEBUG
    )
logger = logging.getLogger()
 
# Cloud Logging ハンドラを logger に接続
logging_client = google.cloud.logging.Client()
logging_client.setup_logging()

@functions_framework.http
def detect_ocr(request):
    """リモート関数 OCR
    """
    try:
        replies = []
        request_json = request.get_json()
        logger.info(request_json)

        calls = request.get_json()['calls']
        for call in calls:
            # 引数の署名付きURLでGCSの画像データ取得
            content = urllib.request.urlopen(call[0]).read()
            # OCR処理
            ocr_texts = detect_text_ocr(content)
            # OCRしたテキストを結合
            text = aggregation_text(ocr_texts)
            replies.append(text)
        
        return_json = jsonify( { "replies":  replies })
        return make_response(return_json, 200)

    # その他エラー
    except Exception as e:
        logger.error(traceback.format_exc())
        tb = sys.exc_info()[2]
        logger.error(e.with_traceback(tb))
        return make_response(jsonify({"errorMessage": str(e)}), 500)

@functions_framework.http
def detect_entites(request):
    """リモート関数 Entities
    """
    try:
        replies = []
        request_json = request.get_json()
        logger.info(request_json)

        calls = request.get_json()['calls']
        for call in calls:
            # 引数は分析対象のText
            text = call[0]
            # Entitie分析
            entities = analyze_entities(text)
            replies.append({"entities":entities})
        
        return_json = jsonify( { "replies":  replies })
        return make_response(return_json, 200)

    # その他エラー
    except Exception as e:
        logger.error(traceback.format_exc())
        tb = sys.exc_info()[2]
        logger.error(e.with_traceback(tb))
        return make_response(jsonify({"errorMessage": str(e)}), 500)

def detect_text_ocr(content):
    """
    OCR実行
    content:画像データ
    """
    client_options = {'api_endpoint': 'eu-vision.googleapis.com'}
    client = vision.ImageAnnotatorClient(client_options=client_options)
    image = vision.Image(content=content)
    response = client.text_detection(image=image)
    texts = response.text_annotations
    return texts

def aggregation_text(texts):
    """
    OCR結果のテキストをすべて結合
    """
    work_text = ""
    for text in texts:
        work_text += text.description
    return work_text

def analyze_entities(text_content):
    """
    Entities分析
    """
    type_ = language_v1.Document.Type.PLAIN_TEXT
    client = language_v1.LanguageServiceClient()
    document = {"content": text_content, "type_": type_}
    encoding_type = language_v1.EncodingType.UTF8

    response = client.analyze_entities(
        request={"document": document, "encoding_type": encoding_type}
    )
    entities = []
    for entity in response.entities:

        metadatas = []
        for metadata_name, metadata_value in entity.metadata.items():
            metadatas.append({"metadataName": metadata_name, 
                              "metadataValue": metadata_value})
        
        mentions = []
        for mention in entity.mentions:
            mentions.append({"text":mention.text.content, 
                             "type":language_v1.EntityMention.Type(mention.type_).name})

        entitie = {
            "name": entity.name, 
            "type": language_v1.Entity.Type(entity.type_).name,
            "SalienceScore": entity.salience,
            "metadatas": metadatas,
            "mentions": mentions
        }
        entities.append(entitie)

    return entities
◆ Cloud Functionsのデプロイ
main.pyが存在するディレクトリで、以下のコマンドを使用してデプロイします。
このコマンド例では、Pythonで実装したdetect_ocr関数を、detect_ocr_gcfというファンクション名でデプロイしています。
detect_entities関数についても、同様の方法でデプロイします。
gcloud functions deploy detect_ocr_gcf \
--project=$PROJECT_ID \
--gen2 \
--runtime=python310 \
--region=asia-northeast1 \
--source=. \
--entry-point=detect_ocr \
--memory=4096MiB \
--trigger-http \
--timeout=1800 \
--min-instances=0 \
--max-instances=200 \
--no-allow-unauthenticated
参考：Google Cloud - gcloud functions deploy
◆ リモート関数の作成
クエリを実行してリモート関数を作成します。

事前に、クエリに指定する必要のあるCONNECTIONとendpointの内容を確認しておきます。
CONNECTION：『STEP３．外部接続の作成』で作成した外部接続sts-connection-toshoを使用します。
endpoint：前の手順でデプロイした、ファンクションのURLを指定します。

ファンクションのURLは、Cloud ConsoleのCloud Functionsにて確認することができます。
ファンクションの詳細を開くと、URLが表示されます。
Cloud Functions -URLの確認
Cloud ConsoleのBigQueryを開き、クエリエディタで以下のクエリを実行します。
CREATE FUNCTION `[PROJECT_ID].tosho`.detect_ocr(signed_url STRING) RETURNS JSON
REMOTE WITH CONNECTION `asia-northeast1.sts-connection-tosho`
OPTIONS (
endpoint = 'https://detect-ocr-gcf-xxxxxxxxxxxx.a.run.app',

max_batching_rows=1
)

STEP６．リモート関数で画像データを分析！

作成したリモート関数を呼び出して、BigQuery上で画像分析の結果を参照することが可能となりました。
以下のクエリ例のように、画像分析の結果をテーブルに保存したり、OCRを実行した結果やエンティティ分析結果を検索したりすることができます。
◆ 画像分析結果クエリ例
リモート関数を呼び出して、画像分析の結果セットをテーブルに保存してみます。
CREATE OR REPLACE TABLE `[PROJECT_ID].tosho.1940_rikei_meta`
AS
SELECT uri, `[PROJECT_ID].tosho`.detect_ocr(signed_url) AS text,
`[PROJECT_ID].tosho`.detect_entites(`[PROJECT_ID].tosho`.detect_ocr(signed_url)) AS entites
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE `[PROJECT_ID].tosho.1940_rikei`, ["SIGNED_URL"])
LIMIT 100;
1940_tosho_metaテーブルが作成されました。
BigQuery - 画像分析結果をもとにテーブルを作成
プレビューで、画像分析の結果セットが保存されていることを確認できました。
BigQuery - 画像分析結果をもとにテーブルを作成 - データプレビュー
◆ 検索クエリ例
（１）OCRの実行結果の全文から、文字列「雪」が含まれるデータを検索してみます。
SELECT text FROM `[PROJECT_ID].tosho.1940_rikei_meta` where text like "%雪%"
画像内のテキストを、データとして取得することができました。
BigQuery - OCR結果の検索
検索された２つの画像を確認してみると、確かに「雪」の文字を見つけることができました！
  • 1872253_R0000111.jpg　※以下は元画像の一部抜粋です。
検索結果 - 1872253_R0000111
  • 1073085_R0000003.jpg　※以下は元画像の一部抜粋です。
検索結果 - 1073085_R0000003 （２）エンティティ分析結果から、エンティティの代表的な名前・顕著性スコア・エンティティ タイプを検索してみます。
SELECT entities_info.name name, entities_info.type type, entities_info.SalienceScore score
FROM `[PROJECT_ID].tosho.1940_rikei_meta`, UNNEST(JSON_QUERY_ARRAY(entites.entities)) AS entities_info
LIMIT 1000
参考：Google Cloud - JSON から配列を抽出する
エンティティ分析結果から、代表的な名前ごとに情報を取得することができました。
BigQuery - エンティティ分析結果の検索

まとめ

いかがでしたでしょうか。
BigQueryのクエリで、シームレスに図書画像のデータから分析ができました。
今回はBigQuery リモート関数を使いましたが、BigQuery MLでも行うことができます。
BigQuery MLは定額制プランが必要ですが、リモート関数を使用する必要がなく、よりかんたんにBigQueryで非構造化データの分析が可能です。
BigQuery リモート関数は、オンデマンド分析料金で使用することができます。
みなさまも、是非お試しください！

当社システムサポートは、Google Cloudの導入・移行・運営支援を行っています。
特にデータ分析ではスペシャライゼーション（Google Cloud のパートナーに与えられる「専門分野の認定」）を取得しております。
導入事例もぜひご覧ください。
ソリューション：Google Cloudを活用した次世代データ分析基盤「ADDPLAT」を導入
Google Cloudに関するお問い合わせは以下よりお待ちしております。

関連記事

【Google Cloud】BIツール徹底比較！地図編

【Google Cloud】BIツール徹底比較！地図編

by Google Cloud研究開発チーム on 2023年05月16日

【Google Cloud】BigQueryのデータリネージ（Dataplex機能）を試してみた

【Google Cloud】BigQueryのデータリネージ（Dataplex機能）を試してみた

by Y.Y on 2023年04月20日

【Google Cloud】BigQuery MLの推論エンジン使ってみた！（Vision API,OCR,画像分析）

【Google Cloud】BigQuery MLの推論エンジン使ってみた！（Vision API,OCR,画像分析）

by Google Cloud研究開発チーム on 2023年08月18日

【Google Cloud】Looker Studio × Looker Studio Pro × Looker を徹底比較！機能・選び方を解説

【Google Cloud】Looker Studio × Looker Studio Pro × Looker を徹底比較！機能・選び方を解説

by Google Cloud研究開発チーム on 2023年09月05日

本記事はいかがでしたか？

Follow Google Cloud研究開発チーム:

株式会社システムサポート（STS）のGoogle Cloud研究開発チームです。 実際に技術検証した事例を中心に記事発信していきます。