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

構造化データ	半構造化データ	非構造化データ
「列」と「行」で管理できるため、そのままで集計、比較、解析、分析が可能。 リレーショナルデータベース（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. リモート関数で画像データを分析！

検証方法の記載は、以下の設定を前提としています。
本ブログを参考に実装される際は、必要に応じて読み替えてください。
以降の作業は、いずれもオーナーのIAM権限を持つユーザーで実行しています。

PROJECT_ID=$(gcloud config get-value project)

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

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

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

今回の検証で作成したバケットは、以下のような階層構造となっています。

　gs://[バケット名]/　         ----------> バケット
　└─tosho_all_linejson/
　　└─img/
　　　└─tosho_1940_rikei/
　　　　└─*.jpg　　　　　　　　----------> 複数の画像ファイル

バケットの詳細を開くと、アップロードした画像データを確認することができます。

外部接続を使用することで、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という名称で外部接続が作成されていることがわかります。

外部接続が使用するサービス アカウントに対し、プロジェクトレベルで必要な権限を付与します。

◆ 付与対象のサービス アカウント IDの確認

まず、付与対象のサービス アカウント IDを確認します。
Cloud ConsoleでBigQueryを開き、『STEP２．外部接続の作成』で作成した外部接続を選択することにより、確認が可能です。
このサービス アカウントに対して、権限の付与を行います。

◆ 付与する権限の種類

今回の検証では、外部接続を使用するために以下３つの権限が必要です。
確認したサービス アカウントに対して、これらの権限を追加します。

• Cloud Functions 起動元
• Cloud Run 起動元
• Storage オブジェクト閲覧者

◆ 権限の編集と確認

Cloud ConsoleのIAMと管理で、権限の編集と確認を行うことが可能です。

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という名称のテーブルが作成されていることがわかります。

オブジェクト テーブルの非構造化データを分析するために、リモート関数を使用します。
今回は、Pythonを使用して以下の結果を返す関数を実装し、Cloud Functionsをデプロイします。

• Vision APIを用いてOCRを実行した結果 
• Natural Language APIを用いてエンティティ分析した結果

その後、BigQueryのクエリから呼び出せるようにリモート関数を構成します。
参考：Google Cloud – リモート関数を使用してオブジェクト テーブルを分析する

参考：Google Cloud – リモート関数の操作

◆ Pythonコードの作成

前述の関数を、Pythonを使用して実装します。
今回は、以下のような関数を持つmain.pyを作成しました。

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 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
)

作成したリモート関数を呼び出して、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テーブルが作成されました。

プレビューで、画像分析の結果セットが保存されていることを確認できました。

◆ 検索クエリ例

（１）OCRの実行結果の全文から、文字列「雪」が含まれるデータを検索してみます。

SELECT text FROM `[PROJECT_ID].tosho.1940_rikei_meta` where text like "%雪%"

画像内のテキストを、データとして取得することができました。

検索された２つの画像を確認してみると、確かに「雪」の文字を見つけることができました！

• 1872253_R0000111.jpg　※以下は元画像の一部抜粋です。

• 1073085_R0000003.jpg　※以下は元画像の一部抜粋です。

（２）エンティティ分析結果から、エンティティの代表的な名前・顕著性スコア・エンティティ タイプを検索してみます。

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 MLでも行うことができます。
BigQuery MLは定額制プランが必要ですが、リモート関数を使用する必要がなく、よりかんたんにBigQueryで非構造化データの分析が可能です。
BigQuery リモート関数は、オンデマンド分析料金で使用することができます。
みなさまも、是非お試しください！

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

Google Cloudに関するお問い合わせは以下よりお待ちしております。