[OCI]Object Storageバケット内にある全画像に対してOCI Visionを使用した画像分析を行い分析結果をDBに保存するプロシージャを作成してみた

# はじめに

Autonomous DatabaseからOCI AIサービスのひとつであるOCI VisionのAPIを呼び出して、指定したObject Storageバケット内にあるすべての画像ファイルの分析結果をDB内に保存するPL/SQLプロシージャを作成してみました。

# 1. 事前準備
今回は分析対象の画像として、こちらの3つのファイルを使用しました。

＜Sample1.jpg＞
![Sample1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/1b24c742-3531-1d47-42ac-45a3f42ee6d9.jpeg)
＜Sample2.jpg＞
![Sample2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/c2044f13-c465-9c14-1cd7-f331f435e69c.jpeg)
＜Sample3.jpg＞
![Sample3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/5dc29929-e81c-aab1-1512-90449c920716.jpeg)
imagesという名前のObject Storageバケットを作成し、そちらにこの3つのファイルをアップロードしました。
![スクリーンショット 2023-03-25 14.01.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/b7ed04e7-19e1-34f2-f449-783dea614fb2.png)

![スクリーンショット 2023-03-25 14.01.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/08321cfd-155d-e245-258a-38ffa2116045.png)

# 2. 分析結果保存用の表の作成

OCI Visionによる画像分析結果を保存するための表image_analyze_resultを作成します。
```
SQL> CREATE TABLE image_analyze_result (
  2     filename            VARCHAR2(100), -- ファイル名
  3     timestamp_analyzed  TIMESTAMP,     -- レコードが作成されたタイムスタンプ
  4     location            VARCHAR2(300), -- ファイルのURI
  5     analyze_result      CLOB           -- OCI Visionによる画像分析結果（JSON）
  6     CONSTRAINT ensure_json CHECK (analyze_result IS JSON)
  7  );

表が作成されました。

SQL>
```

# 3. PL/SQLプロシージャの作成
引数としてリージョン識別子、ネームスペース、バケット名を渡して実行すると、バケット内の全ファイルに対してOCI Visionを使用した画像分析を行い、その結果をimage_analyze_result表に保存するPL/SQLプロシージャanalyze_image_in_bucketを作成します。

```
SQL> CREATE OR REPLACE PROCEDURE analyze_image_in_bucket (
  2    region	  IN VARCHAR2, -- リージョン識別子
  3    namespace  IN VARCHAR2, -- ネームスペース
  4    bucketname IN VARCHAR2  -- バケット名
  5  )
  6  IS
  7    -- 変数の宣言
  8    bucket_uri	   VARCHAR2(200) := 'https://objectstorage.'||region||'.oraclecloud.com/n/'||namespace||'/b/'||bucketname||'/o/';
  9    api_uri		   VARCHAR2(100) := 'https://vision.aiservice.'||region||'.oci.oraclecloud.com/20220125/actions/analyzeImage';
 10    image_uri	   VARCHAR2(200);
 11    features 	   JSON_ARRAY_T  := JSON_ARRAY_T('[{"featureType":"IMAGE_CLASSIFICATION"}]');
 12    image		   JSON_OBJECT_T;
 13    request_body	   JSON_OBJECT_T;
 14    resp		       DBMS_CLOUD_TYPES.resp;
 15    analyze_result  CLOB;
 16    -- 指定したバケット内の各ファイルを処理するためのカーソルの宣言
 17    CURSOR image_cur IS
 18  	 SELECT object_name FROM
 19  	 DBMS_CLOUD.LIST_OBJECTS('OCI$RESOURCE_PRINCIPAL',bucket_uri);
 20    -- カーソルの結果セットの1行分を代入する変数
 21    image_row image_cur%ROWTYPE;
 22  
 23  BEGIN
 24    -- カーソルをオープン
 25    OPEN image_cur;
 26    -- ループ処理
 27    LOOP
 28  	 -- 結果セットを変数に代入
 29  	 FETCH image_cur INTO image_row;
 30  	 EXIT WHEN image_cur%NOTFOUND;
 31  	 -- 変数の初期化
 32  	 image	      := JSON_OBJECT_T('{}');
 33  	 request_body := JSON_OBJECT_T('{}');
 34  	 -- 分析対象の画像情報のセット
 35  	 image.put('source','OBJECT_STORAGE');
 36  	 image.put('namespaceName',namespace);
 37  	 image.put('bucketName',bucketname);
 38  	 image.put('objectName',image_row.object_name);
 39  	 -- リクエストボディのセット
 40  	 request_body.put('features', features);
 41  	 request_body.put('image', image);
 42  	 -- APIをコール
 43  	 resp := DBMS_CLOUD.SEND_REQUEST(
 44  		   -- 使用するクレデンシャルの指定
 45  		   credential_name => 'OCI$RESOURCE_PRINCIPAL',
 46  		   -- OCI Visionの画像解析APIのURIの指定
 47  		   uri		       => api_uri,
 48  		   -- APIコールで使用するメソッドの指定
 49  		   method	       => DBMS_CLOUD.METHOD_POST,
 50  		   -- APIコールのボディの指定
 51  		   body 	       => UTL_RAW.cast_to_raw(request_body.to_clob)
 52  		       );
 53  	 -- 画像解析結果のJSONを変数に代入
 54  	 analyze_result := DBMS_CLOUD.get_response_text(resp);
 55  	 -- 画像のURIを変数に代入
 56  	 image_uri := bucket_uri||image_row.object_name;
 57  	 -- image_analyze_result表にレコードをINSERT
 58  	 INSERT INTO image_analyze_result
 59  	   VALUES (image_row.object_name,systimestamp,image_uri,analyze_result);
 60    -- ループ終了
 61    END LOOP;
 62    -- カーソルを閉じる
 63    CLOSE image_cur;
 64    -- コミット
 65    COMMIT;
 66  END;
 67  /

プロシージャが作成されました。

SQL>
```

# 4. 作成したPL/SQLプロシージャの実行
リージョン、ネームスペース、バケット名を指定して、作成したプロシージャanalyze_image_in_bucketを実行します。

```
SQL> EXEC analyze_image_in_bucket('ap-tokyo-1','n**********w','images')

PL/SQLプロシージャが正常に完了しました。

SQL>
```
正常に実行できたので、image_analyze_result表の内容を確認してみます。
```
SQL> col filename for a30
SQL> SELECT * FROM image_analyze_result;

FILENAME
------------------------------
TIMESTAMP_ANALYZED
---------------------------------------------------------------------------
LOCATION
--------------------------------------------------------------------------------
ANALYZE_RESULT
--------------------------------------------------------------------------------
Sample1.jpg
23-03-25 05:09:06.408982
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample1.jpg
{
  "imageObjects" : null,
  "labels" : [ {
    "name" : "Snow",
    "confidence" : 0.992507
  }, {
    "name" : "Train",
    "confidence" : 0.9923946
  }, {
    "name" : "Roof",
    "confidence" : 0.9916843
  }, {
    "name" : "Road",
    "confidence" : 0.9895358
  }, {
    "name" : "Tree",
    "confidence" : 0.9878961
  } ],
  "ontologyClasses" : [ {
    "name" : "Roof",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Snow",
    "parentNames" : [ "Nature" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Road",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Tree",
    "parentNames" : [ "Plant" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Train",
    "parentNames" : [ "Land vehicle" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Nature",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Plant",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Land vehicle",
    "parentNames" : [ "Vehicle" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Vehicle",
    "parentNames" : [ "Transportation" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Transportation",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  } ],
  "imageText" : null,
  "detectedFaces" : null,
  "imageClassificationModelVersion" : "1.5.86",
  "objectDetectionModelVersion" : null,
  "textDetectionModelVersion" : null,
  "faceDetectionModelVersion" : null,
  "errors" : [ ]
}

FILENAME
------------------------------
TIMESTAMP_ANALYZED
---------------------------------------------------------------------------
LOCATION
--------------------------------------------------------------------------------
ANALYZE_RESULT
--------------------------------------------------------------------------------
Sample2.jpg
23-03-25 05:09:07.150694
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample2.jpg
{
  "imageObjects" : null,
  "labels" : [ {
    "name" : "Vegetation",
    "confidence" : 0.993203
  }, {
    "name" : "Roof",
    "confidence" : 0.99299645
  }, {
    "name" : "Twig",
    "confidence" : 0.9929301
  }, {
    "name" : "Snow",
    "confidence" : 0.9926892
  }, {
    "name" : "Road",
    "confidence" : 0.9925414
  } ],
  "ontologyClasses" : [ {
    "name" : "Roof",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Snow",
    "parentNames" : [ "Nature" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Twig",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Road",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Vegetation",
    "parentNames" : [ "Plant" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Nature",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Plant",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  } ],
  "imageText" : null,
  "detectedFaces" : null,
  "imageClassificationModelVersion" : "1.5.86",
  "objectDetectionModelVersion" : null,
  "textDetectionModelVersion" : null,
  "faceDetectionModelVersion" : null,
  "errors" : [ ]
}

FILENAME
------------------------------
TIMESTAMP_ANALYZED
---------------------------------------------------------------------------
LOCATION
--------------------------------------------------------------------------------
ANALYZE_RESULT
--------------------------------------------------------------------------------
Sample3.jpg
23-03-25 05:09:07.766818
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/nxxxxxxxxxxw/b/images/o/Sample3.jpg
{
  "imageObjects" : null,
  "labels" : [ {
    "name" : "Road",
    "confidence" : 0.99308455
  }, {
    "name" : "Street",
    "confidence" : 0.99289215
  }, {
    "name" : "Metal",
    "confidence" : 0.99259216
  }, {
    "name" : "Train",
    "confidence" : 0.99196345
  }, {
    "name" : "Track",
    "confidence" : 0.9919062
  } ],
  "ontologyClasses" : [ {
    "name" : "Road",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Street",
    "parentNames" : [ "Urban", "Road" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Metal",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Train",
    "parentNames" : [ "Land vehicle" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Track",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Urban",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  }, {
    "name" : "Land vehicle",
    "parentNames" : [ "Vehicle" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Vehicle",
    "parentNames" : [ "Transportation" ],
    "synonymNames" : [ ]
  }, {
    "name" : "Transportation",
    "parentNames" : [ ],
    "synonymNames" : [ ]
  } ],
  "imageText" : null,
  "detectedFaces" : null,
  "imageClassificationModelVersion" : "1.
5.86",
  "objectDetectionModelVersion" : null,
  "textDetectionModelVersion" : null,
  "faceDetectionModelVersion" : null,
  "errors" : [ ]
}


3行が選択されました。

SQL> 
```

指定したバケットimages内にあるファイル（Sample1.jpg、Sample2.jpg、Samlple3.jpg）のOCI Visionによる分析結果が、image_analyze_result表に保存されていることが確認できました。

# 5. 画像分析の結果をもとにした画像データの検索
OCI Visionによる画像分析の結果がDBに保存できたので、image_analyze_result表から特定のラベルが付けられた画像を抽出してみます。

SQL/JSON条件JSON_TEXTCONTAINSを使用するためにimage_analyze_result表のanalyze_result列にJSON検索索引を作成します。

```
SQL> CREATE INDEX idx ON image_analyze_result(analyze_result)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

索引が作成されました。

SQL>
```

JSON_TEXTCONTAINS条件を使用して、「Train」のラベルが付けられている画像の情報を抽出してみます。
```
SQL> col location for a90
SQL> SELECT location FROM image_analyze_result
  2  WHERE JSON_TEXTCONTAINS(analyze_result, '$.labels.name', 'Train');

LOCATION
------------------------------------------------------------------------------------------
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample1.jpg
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample3.jpg

2行が選択されました。

SQL>
```
画像に電車が含まれているSample1.jpgとSample3.jpgが抽出できました。

# 6. Object Storage内の画像ファイルを参照するためのビューの作成

Object Storageバケット内の画像ファイルを参照するためのビューimage_analyze_result_vを作成します。
location列のURIにあるObject Storageバケット内の画像ファイルの内容が、image列にBLOB形式で返されます。
```
SQL> CREATE OR REPLACE VIEW image_analyze_result_v
  2  AS
  3  SELECT
  4    filename,
  5    timestamp_analyzed,
  6    location,
  7    analyze_result,
  8    DBMS_CLOUD.GET_OBJECT('OCI$RESOURCE_PRINCIPAL',location) image
  9  FROM image_analyze_result;

ビューが作成されました。

SQL>
```

# 7. 作成したビューの出力結果の確認

Database ActionsのSQLから、以下のSQLを実行して、mage_analyze_result_vビューの出力を確認します。
```
SELECT filename, location, image FROM image_analyze_result_v
WHERE JSON_TEXTCONTAINS(analyze_result, '$.labels.name', 'Train');
```
![スクリーンショット 2023-03-28 10.25.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/d85409f8-941c-be75-4fad-00d3e33d6644.png)
IMAGE列の(BLOB)の部分をクリックすると目のアイコンが表示されるので、そこをクリックします。
![スクリーンショット 2023-03-28 10.27.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/7a525af0-f5e2-7601-f4bb-0fd03ac30ed8.png)
IMAGE列の詳細が表示されるので、「Image」をクリックします。
![スクリーンショット 2023-03-28 10.29.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/6e3ceb2c-2f7e-8e2d-5441-0dd5f7dc2eea.png)
Object Storageバケット内にある画像ファイルSample1.jpgが表示されました。
![スクリーンショット 2023-03-28 10.31.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/d35204b8-d14f-1dfa-2b1f-ecb5b7d684b6.png)



# 参考資料
・[Vision API:AnalyzeImage](https://docs.oracle.com/en-us/iaas/api/#/en/vision/20220125/AnalyzeImageResult/AnalyzeImage)
・[JSON_TEXTCONTAINS条件](https://docs.oracle.com/cd/F19136_01/sqlrf/SQL-JSON-Conditions.html#GUID-DEF7941B-1267-44E7-8514-5CD448503179)