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

# はじめに

本記事は[Google Cloud Next '22](https://cloud.google.com/blog/ja/topics/google-cloud-next/google-cloud-next22-wrap-up)で発表されたBigQueryにおける非構造化データの分析に関連して、[オブジェクト テーブルの概要](https://cloud.google.com/bigquery/docs/object-table-introduction?hl=ja)をもとにオブジェクトテーブルの内容と実際に操作した感想をまとめた記事です。また[Unstructured Data Analysis with BigQuery ML](https://adswerve.com/blog/unstructured-data-analysis-with-bigquery-ml/)も参考にしています。なお、本記事で記載するコマンドについては[Cloud Shell](https://cloud.google.com/shell?hl=ja)での操作です。また、本記事では`<>`付きの名前が登場しますが、それらについては適宜自分の環境に合わせて読み替えてください。

# 前提知識
## 非構造化データとは

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

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

## 事前準備

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

### バケットの準備

はじめに非構造化データを保存するためのバケットを[Clous Storage](https://console.cloud.google.com/storage?hl=ja)に作成します。以下のコマンドでバケットを作成することができます。

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

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

続いて、オブジェクトテーブルに設定するための非構造化データをダウンロードします。本記事では[Stanford Dogs Dataset](http://vision.stanford.edu/aditya86/ImageNetDogs/main.html)から `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](https://console.cloud.google.com/marketplace/product/google/bigqueryconnection.googleapis.com)を有効にした後、以下のコマンドを実行して外部接続を作成します。

```
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2998227/e3961763-7206-8d7f-42da-e1316744031f.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](https://console.cloud.google.com/apis/api/vision.googleapis.com)を有効にする必要があります。

```
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2998227/0253583f-d54a-44f6-f244-cc0a14f19b06.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上のリソースについては削除してください。**