LoginSignup
1
0

More than 1 year has passed since last update.

@kosuke21

オブジェクトテーブルで非構造化データを扱う方法 (BigQuery)

Last updated at Posted at 2023-03-07

はじめに

本記事はGoogle Cloud Next '22で発表されたBigQueryにおける非構造化データの分析に関連して、オブジェクト テーブルの概要をもとにオブジェクトテーブルの内容と実際に操作した感想をまとめた記事です。またUnstructured Data Analysis with BigQuery MLも参考にしています。なお、本記事で記載するコマンドについてはCloud Shellでの操作です。また、本記事では<>付きの名前が登場しますが、それらについては適宜自分の環境に合わせて読み替えてください。

前提知識

非構造化データとは

非構造化データとは画像・動画・音声などの構造定義されていないデータで、我々の日常にある多くのデータが非構造化データです。しかしながら、これらの非構造化データは構造定義を持たないため、定義に従い整形されている構造化データよりも扱いにくいというデメリットがあります。そのため、現実社会では非構造化データを活用しきれず、今後はこれらの非構造化データに埋もれている重要な情報をビジネス戦略に活かすことが大切だと言われています。BigQueryではオブジェクトテーブルでGoogle Storageに保存されている非構造化データを分析できます。

オブジェクトテーブルのスキーマ

前述したオブジェクトテーブルではGoogle Cloudに保存されているオブジェクトのメタデータを保持し、オブジェクトテーブルの各行はオブジェクトそのものを、列はオブジェクトのメタデータ (カスタムメタデータを含む)を示します。スキーマは以下のテーブルのとおりです。

フィールド名 型 モード 説明
uri STRING NULLABLE オブジェクトのURI (gs://bucket_name/[folder_name/]object_name形式)
generation INTEGER NULLABLE オブジェクトの世代 (オブジェクトのバージョンを識別)
content_type STRING NULLABLE オブジェクトデータのContent-Type (Content-Typeなしで格納されたオブジェクトはapplication/octet-stream)
size INTEGER NULLABLE データのContent-Length （バイト単位）
me5_hash STRING NULLABLE データのMD5ハッシュ (base64でエンコード)
updated TIMESTAMP NULLABLE オブジェクトのメタデータが最後に変更された時刻
metadata RECORD REPEATED オブジェクトのカスタムメタデータ (各メタデータはmetadataフィールドの子 (metadata.)nameフィールドと (metadata.)valueフィールドのKey-Valueペア)
(metadata.)name STRING NULLABLE メタデータのエントリキー
(metadata.)value STRING NULLABLE メタデータのエントリバリュー

オブジェクトテーブルで非構造化データを扱う主な用途

オブジェクトテーブルでは、BigQueryの他のテーブルと同様にメタデータに関するクエリを投げられるため、主に以下の用途でメタデータを利用します。

  1. メタデータの分析
  2. BQMLによる画像オブジェクトの推定
  3. リモート関数を使用した非構造化データの分析

オブジェクトテーブルによる非構造化データの操作

事前準備

最初にオブジェクトテーブルを作成するための事前準備を行います。

バケットの準備

はじめに非構造化データを保存するためのバケットをClous Storageに作成します。以下のコマンドでバケットを作成することができます。

gsutil mb -c regional -l <LOCATION> -b on gs://<BUCEKT_NAME>

データセットのダウンロード

続いて、オブジェクトテーブルに設定するための非構造化データをダウンロードします。本記事ではStanford Dogs Datasetから border collieおよびpugの画像の一部を使用します。なお、以下のコマンド（ここのコマンドはローカルのターミナルで実行）でダウンロードすることもできます。

wget http://vision.stanford.edu/aditya86/ImageNetDogs/images.tar -P <DOWNLOAD_PATH>

データセットのアップロード

ダウンロードしたborder collieおよびpugの画像を上で作成したバケットにアップロードするために作成したバケットにborder collieおよびpugの画像をアップロードします。アップロード先のフォルダ構成は以下のとおりです。

<BUCKET_NAME>
├── border_collie
│   ├── <COLLIE_1>.jpg
│   ├── <COLLIE_2>.jpg
│   ├──　　：
│   └── <COLLIE_10>.jpg
└── pug
 　　  ├── <PUB_1>.jpg
　　　　  ├── <PUG_2>.jpg
　　　　  ├──　　：
　　　　  └── <PUG_10>.jpg

外部接続の作成

オブジェクトテーブルでGCS上に保存されている非構造化データを取り扱うためには、GCSとオブジェクトテーブルを繋ぐための外部接続を作成する必要があります。そのため、BigQuery Connection APIを有効にした後、以下のコマンドを実行して外部接続を作成します。

bq mk --connection --location=<LOCATION> --project_id=<PROJECT_ID> --connection_type=CLOUD_RESOURCE <CONNECTION_NAME>

上のコマンド実行後、コネクションが作成されたことを確認します。

bq show --location=<LOCATION> --project_id=<PROJECT_ID> --connection <CONNECTION_NAME>

以下実行結果

                  name                    friendlyName   description      Last modified           type        hasCredential                 properties 
 --------------------------------------- -------------- ------------- --------------------- ---------------- --------------- -----------------------------------------
  <NUMBER>.<LOCATION>.<CONNECTION_NAME>                                <MODIFIED_DATETIME>   CLOUD_RESOURCE     <BOOLEAN>     {"serviceAccountId": <SERVICE_ACCOUNT>}

オブジェクトテーブルの作成とクエリ実行

ここではCloud Storageにある画像ファイルを利用してオブジェクトテーブルを作成し、簡単なクエリを実行します。

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

はじめに、上で作成した外部接続を使用してBiqQueryにオブジェクトテーブルを作成します。 以下のDDLをBiqQueryからクエリ実行します。なお、データセットが存在しない場合は、適宜データセットを作成してください。

CREATE OR REPLACE EXTERNAL TABLE
  `<DATASET_NAME>.<OBJECT_TABLE_NAME>`
WITH CONNECTION 
  `<LOCATION>.<CONNECTION_NAME>` 
OPTIONS(
  object_metadata="SIMPLE",
  uris=["gs://<BUCKET_NAME>/*"]
);

作成したテーブルの確認

上で作成したオブジェクトテーブルの内容を確認します。 ここではオブジェクトのURIと作成した日時を取得します。以下のSQLの正規表現でpugディレクトリ配下の画像を指定し、必要な情報を収集します。

WHERE句でLIKEを使用した曖昧検索

SELECT
  uri, updated
FROM
  `<DATASET_NAME>.<OBJECT_TABLE_NAME>`
WHERE
  uri LIKE 'gs:/<BUCKET_NAME>/pug/%'
;

WHERE句でREGEXP_CONTAINSを使用した場合

SELECT
  uri, updated
FROM
  `<DATASET_NAME>.<OBJECT_TABLE_NAME>`
WHERE REGEXP_CONTAINS(
  uri,
  r'^gs:\/\/<BUCKET_NAME>\/pug\/.+\.jpg$'
);

以下のエラーが発生する場合は指定したバケットへのアクセス権限がサービスアカウントに付与されていない可能性が高いです。

Access Denied: BigQuery BigQuery: Calling Match with file "gs://<BUCKET_NAME>/**": cloud.bigstore.ResponseCode.ErrorCode::ACCESS_DENIED (省略)

そのため、外部接続のプロパティに設定されているサービスアカウントに指定したバケットへのアクセス権限が付与されているか確認する必要があります。 以下のコマンドを実行して、bindings のmembersに該当のサービスアカウントが設定され、また roleが roles/storage.objectViewerであることを確認します。

gsutil iam get gs://<BUCKET_NAME>

該当のサービスアカウントが指定したバケットへのアクセス権限を持っていない場合は以下のコマンドを実行してアクセス権限を付与します。

gsutil iam ch serviceAccount:<SERVICE_ACCOUNT>:roles/storage.objectViewer  gs://<BUCKET_NAME>

疑似列 (data) の確認

オブジェクトテーブルにはdataという疑似列が存在します。 この列はファイルコンテンツをRaw bytesで示します。 なお、オブジェクトテーブルのスキーマにはこの擬似列は表示されません。

以下のコマンドで擬似列を確認できます。

SELECT
  data
FROM
  `<DATASET_NAME>.<OBJECT_TABLE_NAME>`
WHERE
  uri = 'YOUR_URI'
;

リモート関数による非構造化データの分析

ここではBigQueryリモート関数からオブジェクトテーブルを操作することで非構造化データを分析します。

Cloud Functionsの生成

はじめに、リモート関数でオブジェクトテーブルを操作するためのCloud Fucntionsを作成します。Cloud Fucntionsを実行するためにCloud Build API Cloud Functions API Cloud Logging APIなどのAPIを有効にする必要がありますが、コンソールからCloud Functionsを作成すると以下のようなAPIを有効にするポップアップが表示されるため、事前にAPIを有効にする必要はありません。

apis.png

ここでは第2世代のCloud Functionsを作成しています。なお、ランタイムをPyhton3.11、エントリポイントにlabel_detectionを指定しています。（エントリポイント：Cloud Functions (main.py) で実行したい関数）

main.py と requirements.txtに以下を設定する必要があります。なお、以下のmain.pyではCloud Vision APIを使用して、画像内の物体検出を行います。

main.py
import urllib.request

import flask
import functions_framework
from google.cloud import vision

@functions_framework.http
def label_detection(request: flask.Request) -> flask.Response:
    try:
        client = vision.ImageAnnotatorClient()
        calls = request.get_json()['calls']
        replies = []
        for call in calls:
            content = urllib.request.urlopen(call[0]).read()
            results = client.label_detection({'content': content})
            replies.append(vision.AnnotateImageResponse.to_dict(results))
        return flask.make_response(flask.jsonify({'replies': replies}))
    except Exception as e:
        return flask.make_response(flask.jsonify({'errorMessage': str(e)}), 400)
requirements.txt
Flask==2.2.2
functions-framework==3.3.0
google-cloud-vision==3.2.0

ここ作成したしCloud FunctionsのトリガーURLを下のBigQueryリモート関数の作成で使用します。（作成したCloud Functionsページのトリガータブから該当のURLを確認でます。）

BigQueryリモート関数の作成

上でCloud Functionsをデプロイしたら、続いてリモート関数を作成します。以下のクエリをBigQueryで実行します。以下のクエリで、作成したCloud FunctionsをBigQueryから実行できるようになります。

CREATE OR REPLACE FUNCTION 
  `<DATASET_NAME>.<FUNCTION_TABLE_NAME>` (signed_url_ STRING) 
RETURNS 
  JSON REMOTE
WITH CONNECTION
  `<LOCATION>.<CONNECTION_NAME>`
OPTIONS(
  endpoint = '<TRIGGER_URL>',
  max_batching_rows = 1
);

リモート関数の呼び出し

最後に上で作成したBigQueryリモート関数を実行します。クエリは以下のとおりです。なお、本記事で作成したCloud FunctionsではCloud Vision APIを使用しているため事前にCloud Vison APIを有効にする必要があります。

SELECT 
  uri, <DATASET_NAME>.<FUNCTION_TABLE_NAME>(signed_url)
FROM
  EXTERNAL_OBJECT_TRANSFORM(
    TABLE `<DATASET_NAME>.<OBJECT_TABLE_NAME>`,
    ["SIGNED_URL"]
  )
WHERE REGEXP_CONTAINS(
  uri,
  r'<REGEX>'
)
LIMIT 5;

上の実行結果については、リモート関数で使用したuriとCloud Functionsの実行結果f0_が表示されます。以下の画像については次の実行結果が得られました。

border_collie.jpeg

{
  "uri": "gs://<BUCKET_NAME>/border_collie/<COLLIE_1>.jpg",
  "f0_": "{\"face_annotations\":[],\"label_annotations\":[{\"confidence\":0.0,\"description\":\"Dog\",\"locale\":\"\",\"locations\":[],\"mid\":\"/m/0bt9lr\",\"properties\":[],\"score\":0.961963,\"topicality\":0.961963},(省略)
}

"description": "Dog" "score": "0.961963"が得られたことがわかります。

なお、以下のようなエラーが出る場合には、Cloud Functions及びCloud Runに対するinvokerロールをサービスアカウントに付与する必要があります。

Access Denied: BigQuery BigQuery: Received response code 403 from endpoint <TRIGGER_URL>; Make sure the service account associated with the connection <PROJECT_ID>.<LOCATION>.<DATASET_NAME> is granted the cloudfunctions.functions.invoke permission (e.g., via the Cloud Functions Invoker role) on your Cloud Function endpoint, or the run.routes.invoke permission (e.g., via the Cloud Run Invoker role) on your Cloud Run endpoint. Please allow 60 seconds for the permission change to propagate before retrying.

ロールを付与するためのコマンドは以下のとおりです。

gcloud projects add-iam-policy-binding <PROJECT_ID> --member serviceAccount:<SERVICE_ACCOUNT> --role roles/<cloudfunctions.invoker or run.invoker>

まとめ

今回、リモート関数とCloud Functionsを使用して、オブジェクトテーブルを操作しましたが、BigQueryを普段から触れている方にとってはシームレスにメタデータ分析ができるところが一番のメリットだと感じました。しかしながら、私がBigQueryを触る機会がほとんどなく、今回BigQueryの操作に少々手間取ったところもあり、オブジェクトテーブルに大きなメリットを感じることはできませんでした。但し、Cloud Storageにある非構造化データのメタ分析ができることについては嬉しいと感じるところもあります。

例えば、日々特定のバケットに画像がアップロードされ、その画像に対してBQMLを定期実行するクエリがあったとします。そして、実行結果の評価指標が突然低下したときに、オブジェクトテーブルからアップロードされた日時ごとに予測結果の評価調査ができます。また、今回のようにフォルダごとに画像をカテゴリ分けしていれば、精度が低下している画像のカテゴリもメタ分析できるようになります。

以上の内容については、BigQueryのみ実現できる機能ではないですが、やはりBigQueryを普段触っている人にとって、非構造化データのメタ分析ができることは嬉しいことだと感じます。

※以後使用しないGCP上のリソースについては削除してください。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0