[Oracle Cloud]OCI Data Catalogを使ったAutonomous DatabaseからObject Storageファイルの検索 (2022/01/26)

#はじめに
OCI Data Catalogは、データの専門家がデータを発見し、データガバナンスをサポートするのに役立つメタデータ管理サービスです。

OCI Data Catalogについては以下を参照ください。
- [Oracle Cloud Infrastructure Data Catalog](https://www.oracle.com/jp/big-data/data-catalog/)
- [データカタログとは？なぜそれが必要なのか？](https://www.oracle.com/jp/big-data/what-is-a-data-catalog/)
- [OCI Data Catalog Overview 2022年1月版](https://speakerdeck.com/oracle4engineer/oci-data-catalog-overview-2022nian-1yue-ban)

Data Catalogによって収集された論理エンティティ（Object Storageのオブジェクト）をAutonomous Databaseに同期することで外部表としてアクセスすることが可能になります。
この機能によって、外部データソースのスキーマ（カラムとデータ型）を確認し、外部表を手動で作成することなく、すぐにデータを照会することが可能です。
同期処理はジョブとして実行され、基礎となるデータの変更に対してAutonomous Databaseを最新の状態に保ち、数百から数千のテーブルを自動的に維持するため管理コストが削減できます。

# 構成ステップ

1. OCI Data Catalogの準備
    - Data Catalogの作成
    1. ファイル名パターンの作成
    1. Object Storageに対するデータ・アセットの作成
1. アイデンティティの準備
    - 動的グループの作成
    - ポリシーの作成
1. OCI Data CatalogでObject Storage バケットから収集 
1. Autonomous Databaseでの操作
    - Resource Principalの有効化
    1. Data Catalogへの接続
    1. Data Catalogとの同期の実行
    1. 外部表へアクセス

# OCI Data Catalogの準備
## データ・カタログの作成
アナリティクスとAI > データ・カタログ > データ・カタログ から データ・カタログの作成をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/b563da76-638b-5b91-dc4e-411b78eb3c9e.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/4b90c80c-c973-170a-cd18-0e497bc870a2.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/97c84d21-c26c-1401-50ca-84e70e072a30.png)

## ファイル名パターンの作成
データ・カタログのクイック・アクションから ファイル名パターンの管理 から ファイル名パターンを作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/3fed23bd-387b-b96c-26d9-b2639d762ac1.png)

式に以下を指定

```
{bucketName:[A-Za-z0-9\.\-_]+}/{logicalEntity:[^\.\/]+}.*$
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/a5207375-6eb5-1bf1-928e-bec015f78665.png)


## Object Storageに対するデータ・アセットの作成
データ・カタログのクイック・アクションから データ・アセットの作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/efdf8a38-8da0-d067-fe05-8c0a985e97cd.png)


- タイプ：Oracle Object Storage
- URL：https://swiftobjectstorage.<region>.oraclecloud.com （Object Storageのリージョンを指定）
- ネームスペース：当該テナントのObject Storageネームスペース

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/951f7249-2c37-807a-c549-226addb7b08f.png)

## 接続の追加

- タイプ：Resource Principal
- OCIリージョン：Object Storageのリージョンを指定
- コンパートメントOCID：Object StorageバケットがあるコンパートメントのOCID

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/40bb8563-ac3f-ff64-a99a-9a3c340f2b90.png)


テスト接続をし確認

## ファイル名パターンの割当て

作成したObject Storageのデータアセットに対して事前に作成した「ファイル名パターン」を割当て

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/16410418-8246-2b68-73b2-4559293f3282.png)


# アイデンティティの準備
## 動的グループの作成
アイデンティティとセキュリティ > 動的グループ から 動的グループの作成をクリック
リソースプリンシパルに使用する動的グループを作成
一致ルールにデータカタログのOCIDとAutonomous DatabaseのOCIDを指定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/e73bff56-6880-aaff-2eb8-c74a9fe907a4.png)


## ポリシーの作成
作成した動的グループに対して、Object StorageとData Catalogの操作を許可

ポリシーの例

```
allow dynamic-group datacatalog-group to manage object-family in tenancy
allow dynamic-group datacatalog-group to manage data-catalog-family in tenancy
```

# OCI Data CatalogでObject Storage バケットから収集 
データカタログでOracle Object Storageのデータ・アセットを選択し、収集をクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/6b379154-e53f-f734-fdde-5013ba9d5675.png)

接続の確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/76b59f28-3961-6fca-c32c-846d9bcfeaa3.png)


収集対象のデータ・エンティティ（バケット）の選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/d686789b-45c2-179e-2a9a-4119db124877.png)


「今すぐジョブを実行」を選択し「ジョブの作成」

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/12de56ca-16da-39c1-8fe4-7bb47b80123d.png)


収集結果の確認
Object Storageのデータ・アセットから割当てたファイル名パターンの詳細を表示

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/aeb8cea1-fb71-06aa-a0a0-b44866f35341.png)

論理データ・エンティティが表示されることを確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/9f8b0407-7b89-fda7-c6a7-2af5315f796b.png)


# Autonomous Databaseでの操作

## Resource Principalの有効化
ADMINユーザでAutonomous Databaseに接続しSQLを実行

```
SQL> EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => '<ADB_USER名>’);
-- ( ADMIN ユーザへは EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(); でも可 )

PL/SQL procedure successfully completed.

-- 確認
SQL> SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL’;

OWNER CREDENTIAL_NAME        
----- ---------------------- 
ADMIN OCI$RESOURCE_PRINCIPAL 

-- Object Storage へのアクセス確認 (バケット内のファイルが表示されることを確認）
SQL> select * from dbms_cloud.list_objects('OCI$RESOURCE_PRINCIPAL', '&uri_private’);
-- ( &uri_private には 環境に合わせて https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket名>/o/  を指定 )

OBJECT_NAME        BYTES CHECKSUM                 CREATED                  LAST_MODIFIED            
------------------ ----- ------------------------ ------------------------ ------------------------ 
salesdata4APEX.csv 58811 BCBp2thUrEXS5jiaAa1Aa1== 2022-01-24T00:00:00.000Z 2022-01-24T00:00:00.000Z 
```

## Data Catalogへの接続
ADMINユーザでAutonomous Databaseに接続しSQLを実行

```
-- credentials のセット
SQL> exec dbms_dcat.set_data_catalog_credential(credential_name => 'OCI$RESOURCE_PRINCIPAL');

PL/SQL procedure successfully completed.

SQL> exec dbms_dcat.set_object_store_credential(credential_name => 'OCI$RESOURCE_PRINCIPAL');

PL/SQL procedure successfully completed.


-- Data Catalogへの接続
SQL> begin
   dbms_dcat.set_data_catalog_conn (
     region => '&dcat_region',
     catalog_id => '&dcat_ocid');
end;
/

-- dcat_region : Data Catalogのリージョン ap-tokyo-1 など
-- dcat_ocid ： Data Catalogの OCID

PL/SQL procedure successfully completed.

-- connectionの確認
SQL> select * from all_dcat_connections;

COMPARTMENT_ID           INSTANCE_ID                            REGION     ENDPOINT CREATED     NAME        LAST_UPDATED        LAE  DATA_CATALOG_CREDENTIAL      OBJECT_STORE_CREDENTIAL      
------------------------ -------------------------------------- ---------- -------- ----------- ----------- ------------------- ---  ---------------------------- ---------------------------- 
ocid1.tenancy.oc1..aaaaa ocid1.datacatalog.oc1.ap-tokyo-1.aaaaa ap-tokyo-1  2022-01-24T00:00:00 DataCatalog 2022-01-24T00:00:00  21  ADMIN.OCI$RESOURCE_PRINCIPAL ADMIN.OCI$RESOURCE_PRINCIPAL 
```

## Data Catalogとの同期の実行
ADMINユーザでAutonomous Databaseに接続しSQLを実行

```
-- Data Catalog と Autonomous Databaseの同期 ( すべてのアセットを対象にする例 ）

SQL> begin
 dbms_dcat.run_sync('{"asset_list":["*"]}');
end;
/

PL/SQL procedure successfully completed.

-- ログ表名の確認 
SQL> select type, start_time, status, logfile_table from user_load_operations order by start_time;

TYPE      START_TIME               STATUS    LOGFILE_TABLE      
--------- ------------------------ --------- ------------------ 
DCAT_SYNC 2022-01-24T01:00:00.000Z COMPLETED "DBMS_DCAT$21_LOG" 

-- 該当する時間の Logfile_Table にログが出力される
SQL> select * from dbms_dcat$21_log;

-- <省略>

-- 同期により作成された外部表の確認
SQL> select * from dcat_entities;

ASSET_KEY                            ENTITY_KEY                           FOLDER_KEY                           ORACLE_TABLE_NAME ORACLE_SCHEMA_NAME        ENTITY_ORACLE_DB_SCHEMA ASSET_ORACLE_DB_SCHEMA FOLDER_ORACLE_DB_SCHEMA 
------------------------------------ ------------------------------------ ------------------------------------ ----------------- ------------------------- ----------------------- ---------------------- ----------------------- 
65298a31-1211-4ab2-a0a4-754b71850aee 73be4374-714e-4d86-8c9f-524790828356 f8556d46-684b-451c-890c-31d38f8ab8ba SALESDATA         DCAT$SCHEMA_BUCKET                                schema                                       

SQL> select * from dcat_attributes;

ASSET_KEY                            ENTITY_KEY                           ATTRIBUTE_KEY                        ORACLE_COLUMN_NAME 
------------------------------------ ------------------------------------ ------------------------------------ ------------------ 
65298a31-1211-4ab2-a0a4-754b71850aee be813751-47ad-48f9-9bb8-ea5e6ae2a501 88d99602-0d56-40a8-b03b-2b179a4e588b "店舗名"             
65298a31-1211-4ab2-a0a4-754b71850aee be813751-47ad-48f9-9bb8-ea5e6ae2a501 9cb626ae-6375-4275-a07a-9888239d5b64 "店舗住所"             
65298a31-1211-4ab2-a0a4-754b71850aee be813751-47ad-48f9-9bb8-ea5e6ae2a501 53e253ed-e512-4f43-affd-c3737c6ea14e "商品"               
65298a31-1211-4ab2-a0a4-754b71850aee be813751-47ad-48f9-9bb8-ea5e6ae2a501 f71caa79-2d7e-46d4-acc3-1fec162ded27 "売上"               
65298a31-1211-4ab2-a0a4-754b71850aee be813751-47ad-48f9-9bb8-ea5e6ae2a501 3d46df25-edb2-4644-a7b5-48dd68d1089e "粗利額" 
```

## 外部表へアクセス

```
SQL> select * from DCAT$SCHEMA_BUCKET.SALESDATA;

店舗名     店舗住所                 商品     売上       粗利額
-------- ------------------------ ------- ---------- ---------- 
札幌       札幌市中央区南3条西3      A       150395940  4598240.96
```

# 補足
外部表のスキーマ名はObject Storageのデータ・アセットから「DBMS_DCAT」の編集でパラメータを指定可能

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/3d30564e-9a02-d147-28a0-34e4a2c7d9ac.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/ac06ecce-221c-f06f-6a40-50ab4ce0a2ca.png)

Data Catalogへの接続の削除には unset_data_catalog_connプロシージャを実行します。

```
SQL> exec dbms_dcat.unset_data_catalog_conn;

PL/SQL procedure successfully completed.
```


# おわりに
OCI Data Catalogサービスを使ってObject Storageのオブジェクトに対してAutonomous Databaseの外部表を簡単に作成でき、データの活用がよりしやすくなりました。

## 参考情報

- [データ・カタログ・チュートリアル](https://docs.oracle.com/ja-jp/iaas/data-catalog/tutorials/tutorial-home.htm)
- [Query External Data with Data Catalog](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/query-external-data-catalog.html#GUID-480FAF23-453D-4B15-BF92-8435805EB8A5)
- [DBMS_DCAT Package](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-dbms-dcat.html#GUID-4D927F21-E856-437B-B42F-727A2C02BE8D)
- [Data Catalog Views](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/ref-views.html#GUID-D88972AD-ACAE-48E4-9CE2-CB6410434E86)
- [[Oracle Cloud] Resource Principalを使用して、Autonomous DBからObject Storageにアクセスする](https://qiita.com/West_Tail/items/0539b7682afdebb140b4)