2023年8月18日 【Google Cloud】BigQueryで図書画像のデータを分析してみた! BigQuery 検索する Popular tags 生成AI(Generative AI) Looker Studio BigQuery AlloyDB Google Workspace 事例紹介 Cloud SQL Category Google Cloud Author Google Cloud研究開発チーム SHARE 目次 前提知識と全体アーキテクト 検証方法 まとめ Content こんにちは。Google Cloud研究開発チームです。 Google Cloud Next'22において『データウェアハウスであるBigQueryを使用して、非構造化データに対し直接クエリができるようになった』旨の発表がありました。 今回は、実際にBigQueryのクエリを使用して図書画像のデータを分析してみました。 前提知識と全体アーキテクト 前提知識 まずはじめに、データの種類について確認をしておきたいと思います。 データは大きく分けて 構造化データ・半構造化データ・非構造化データ の3種類に分けられます。 今回扱う画像データは、そのうちの 非構造化データ に属します。 構造化データ 半構造化データ 非構造化データ 「列」と「行」で管理できるため、そのままで集計、比較、解析、分析が可能。 リレーショナルデータベース(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で検証を進めました。 画像ファイルのアップロード 外部接続の作成 外部接続への権限付与 オブジェクト テーブルの作成 リモート関数の作成 リモート関数で画像データを分析! STEP0.前提条件 検証方法の記載は、以下の設定を前提としています。 本ブログを参考に実装される際は、必要に応じて読み替えてください。 以降の作業は、いずれもオーナーの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学習用データセット STEP1.画像ファイルのアップロード まずはじめにCloud Storageに検証用のバケットを新規作成し、事前にダウンロードしておいた画像ファイル一式をアップロードします。 今回の検証で作成したバケットは、以下のような階層構造となっています。 gs://[バケット名]/ ----------> バケット └─tosho_all_linejson/ └─img/ └─tosho_1940_rikei/ └─*.jpg ----------> 複数の画像ファイル バケットの詳細を開くと、アップロードした画像データを確認することができます。 STEP2.外部接続の作成 外部接続を使用することで、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という名称で外部接続が作成されていることがわかります。 STEP3.外部接続への権限付与 外部接続が使用するサービス アカウントに対し、プロジェクトレベルで必要な権限を付与します。 ◆ 付与対象のサービス アカウント IDの確認 まず、付与対象のサービス アカウント IDを確認します。 Cloud ConsoleでBigQueryを開き、『STEP2.外部接続の作成』で作成した外部接続を選択することにより、確認が可能です。 このサービス アカウントに対して、権限の付与を行います。 ◆ 付与する権限の種類 今回の検証では、外部接続を使用するために以下3つの権限が必要です。 確認したサービス アカウントに対して、これらの権限を追加します。 Cloud Functions 起動元 Cloud Run 起動元 Storage オブジェクト閲覧者 ◆ 権限の編集と確認 Cloud ConsoleのIAMと管理で、権限の編集と確認を行うことが可能です。 STEP4.オブジェクト テーブルの作成 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という名称のテーブルが作成されていることがわかります。 STEP5.リモート関数の作成 オブジェクト テーブルの非構造化データを分析するために、リモート関数を使用します。 今回は、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:『STEP3.外部接続の作成』で作成した外部接続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 ) STEP6.リモート関数で画像データを分析! 作成したリモート関数を呼び出して、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テーブルが作成されました。 プレビューで、画像分析の結果セットが保存されていることを確認できました。 ◆ 検索クエリ例 (1)OCRの実行結果の全文から、文字列「雪」が含まれるデータを検索してみます。 SELECT text FROM `[PROJECT_ID].tosho.1940_rikei_meta` where text like "%雪%" 画像内のテキストを、データとして取得することができました。 検索された2つの画像を確認してみると、確かに「雪」の文字を見つけることができました! 1872253_R0000111.jpg ※以下は元画像の一部抜粋です。 1073085_R0000003.jpg ※以下は元画像の一部抜粋です。 (2)エンティティ分析結果から、エンティティの代表的な名前・顕著性スコア・エンティティ タイプを検索してみます。 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に関するお問い合わせは以下よりお待ちしております。 Google Cloud導入についてのお問い合わせはこちら 関連コンテンツ 【Google Cloud】BIツール徹底比較!地図編 by Google Cloud研究開発チームon 2023年5月16日 【Google Cloud】BigQueryのデータリネージ(Dataplex機能)を試してみた by Y.Yon 2023年4月20日 【Google Cloud】BigQuery MLの推論エンジン使ってみた!(Vision API,OCR,画像分析) by Google Cloud研究開発チームon 2023年8月18日 【Google Cloud】Looker Studio × Looker Studio Pro × Looker を徹底比較!機能・選び方を解説 by Google Cloud研究開発チームon 2023年9月5日 頂きましたご意見につきましては、今後のより良い商品開発・サービス改善に活かしていきたいと考えております。 分かりやすかった あまり分からなかった 面白かった Vision APIやってみたい NaturalLanguage APIやってみたい Author Google Cloud研究開発チーム 株式会社システムサポート(STS)のGoogle Cloud研究開発チームです。 実際に技術検証した事例を中心に記事発信していきます。 BigQuery 2023年8月18日 【Google Cloud】BigQueryで図書画像のデータを分析してみた! Category Google Cloud 前の記事を読む 【Google Cloud】BigQuery MLの推論エンジン使ってみた!(Vision API,OCR,画像分析) 次の記事を読む 【Google Cloud】BIツール徹底比較!テーブル編 Recommendation オススメ記事 2023年9月5日 Google Cloud 【Google Cloud】Looker Studio × Looker Studio Pro × Looker を徹底比較!機能・選び方を解説 2023年8月24日 Google Cloud 【Google Cloud】Migrate for Anthos and GKEでVMを移行してみた(1:概要編) 2022年10月10日 Google Cloud 【Google Cloud】AlloyDB と Cloud SQL を徹底比較してみた!!(第1回:AlloyDB の概要、性能検証編) BigQuery ML ワークショップ開催のお知らせ 生成AI導入支援パッケージ Discovery AI導入支援パッケージ Google Cloud ホワイトペーパー 新着記事 2024年9月30日 Google Cloud 【Google Cloud】サーバレスでマネージドなサービス「Cloud Run」でアプリケーションを走らせよう! 2024年9月27日 技術開発 モブレビューを導入して分かったメリットとデメリットについて 2024年9月26日 Google Cloud 自然言語でデータを可視化できるLookerのExplore Assistantを試してみた HOME Google Cloud 【Google Cloud】BigQueryで図書画像のデータを分析してみた! ご意見・ご相談・料金のお見積もりなど、お気軽にお問い合わせください。 お問い合わせはこちら HOME Categories お知らせ イベント・セミナー Google Cloud Google Workspace モバイル インフラ 技術開発 ブログ 4koma Tags 生成AI(Generative AI) Looker Studio BigQuery AlloyDB Google Workspace 事例紹介 Cloud SQL STSエンジニアリングマガジン 「サイタル」