【Autonomous Database（ADB）】Object Storageのデータを問い合わせる（外部データの問い合わせ）

# はじめに
[DBMS_CLOUD](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-package.html#GUID-CE359BEA-51EA-4DE2-88DB-F21A9FC10721)パッケージを使用して、ADBからObject Storageなどの外部データを問い合せることができます。

* サポートされているクラウド・オブジェクト・ストレージ・サービス（マニュアルより）
> Oracle Cloud Infrastructure Object Storage
Azure Blob Storage
Amazon S3
Amazon S3互換: Oracle Cloud Infrastructure Object Storage、Google Cloud StorageおよびWasabi Hot Cloud Storageを含む。

 * 方法は2つ
    * a. [資格証明](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/query-external.html#GUID-ABF95242-3E04-42FF-9361-52707D14E833) を使用した方法
        * こちらの方法では、資格証明が変更したら資格証明の保存作業の再実行が必要になります。
    * b. [リソース・プリンシパル](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/resource-principal.html#GUID-E283804C-F266-4DFB-A9CF-B098A21E496A) を使用した方法
        * こちらの方法では、資格証明の作成が必要ありません。

# テスト用のデータ
事前に、次のデータのある```channels.txt```ファイルを作成し、Object Storageにアップロードしておく。
```
1,Direct Sales,Direct
2,Tele Sales,Direct
3,Catalog,Indirect
4,Internet,Indirect
5,Partners,Others
```

# 手順
## a. 資格証明を使用した方法
### 1. クラウド・サービスの資格証明をADBに保存
* 各クラウド・サービスごとの資格証明
    * 今回はOCIのパターンを試します

| クラウド・サービス | オブジェクト・ストレージ・サービス |username |password|
|:-----------|:------------|:------------|:------------|
| OCI |Object Storage| OCIユーザー名 |OCI認証トークン |
|AWS| S3|AWSアクセス・キーID | AWS秘密アクセス・キー |
| Azure|Blob Storage| Azureストレージ・アカウント名 | Azureストレージ・アカウント・アクセス・キー |

* ADBにADMINユーザでログイン
* ```DBMS_CLOUD.CREATE_CREDENTIAL```プロシージャを使用し資格証明をADBに保存
    * 今回は、[こちら](https://qiita.com/billtench/items/b74d9aa34c9fe71dfd09)の手順で作成した認証トークンを使用します
    * DBMS_CLOUD.CREATE_CREDENTIALプロシージャについては[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-742FC365-AA09-48A8-922C-1987795CF36A)

```sql
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'BUCKET_TOKEN_EXT', 
    username => '<username>',
    password => '<password>' );
END;
/
```

* [DBA_CREDENTIALS](https://docs.oracle.com/cd/F19136_01/refrn/ALL_CREDENTIALS.html#GUID-69F7F819-1490-4260-A314-047B20BCBFB2)ビューで作成した資格証明を確認します。
```sql
select OWNER, CREDENTIAL_NAME, USERNAME, ENABLED from DBA_CREDENTIALS;
```
表示例
```sql
   OWNER    CREDENTIAL_NAME                                       USERNAME    ENABLED 
________ __________________ ________________________________________________ __________ 
ADMIN    BUCKET_TOKEN_EXT       oracleidentitycloudservice/xxx@xxx.com    TRUE
```

### 2. 外部表の作成
```DBMS_CLOUD.CREATE_EXTERNAL_TABLE```プロシージャを使用し、ソース表上に外部表を作成します。

```sql
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE
   (
    table_name      =>'CHANNELS_EXT',
    credential_name =>'BUCKET_TOKEN_EXT',
    file_uri_list   =>'https://objectstorage.ap-osaka-1.oraclecloud.com/n/<ネームスペース名>/b/
kadw-bucket/o/channels.txt',
    format          => json_object('delimiter' value ','),
    column_list     => 'CHANNEL_ID NUMBER,
                        CHANNEL_DESC VARCHAR2(20), 
                        CHANNEL_CLASS VARCHAR2(20)'
   );
END;
/
```

* 外部表のソース・ファイルを検証
    * DBMS_CLOUD.VALIDATE_EXTERNAL_TABLEについては[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-604173CA-1428-486D-99F2-54BE7F8E3B75)
    * エラーが出た場合にデータ検証のログを確認する方法は[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/validate-view-load-logs.html#GUID-218CCE9B-4FD4-4141-B9C8-12FC1C15BF19)
```sql
exec DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ('CHANNELS_EXT');
```

外部表を確認
```sql
select object_name from user_objects where object_name='CHANNELS_EXT';
```
出力例
```sql
    OBJECT_NAME 
_______________ 
CHANNELS_EXT    
```
```sql
desc CHANNELS_EXT
```
出力例
```sql            Name    Null?            Type 
________________ ________ _______________ 
CHANNEL_ID                NUMBER          
CHANNEL_DESC              VARCHAR2(20)    
CHANNEL_CLASS             VARCHAR2(20)   
```

### 3. 作成した外部表に問い合わせ
```sql
SELECT count(*) FROM channels_ext;
```
出力例
```sql
   CHANNEL_ID    CHANNEL_DESC    CHANNEL_CLASS 
_____________ _______________ ________________ 
            1 Direct Sales    Direct           
            2 Tele Sales      Direct           
            3 Catalog         Indirect         
            4 Internet        Indirect         
            5 Partners        Others    
```

### 4. 環境削除
* ADBからテスト表の削除
```sql
drop table channels_ext purge;
```
```sql
select object_name from user_objects where　object_name='CHANNELS_EXT';
```

* ADB内の既存の資格証明を削除
    * DBMS_CLOUD.DROP_CREDENTIALプロシージャについては[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-7373D043-C3AE-4D86-87BE-4F7ACC8B3EB5)

```sql
EXEC DBMS_CLOUD.DROP_CREDENTIAL ('BUCKET_TOKEN_EXT');
```
```sql
SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN'; 
```

# b. リソース・プリンシパルを使用した方法
### 1. ```動的グループ```の作成
#### 1-1. ```動的グループ```の```ルール```の作成
OCIコンソール → ```アイデンティティとセキュリティ``` → ```動的グループ```をクリック
![o1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/f06f1817-8ed1-6641-e842-b76ff1afa422.png)

動的グループを作成
![o2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/3c221bb9-2c79-2a07-bea0-d56331ead0d5.png)

* 今回は次のルールを指定しています
    * resource.compartment.id = 'コンパートメントのOCID'
    * instance.compartment.id = 'コンパートメントのOCID' 

![o7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/ca049f61-5dbb-a2c2-0b94-3d082a9bfbcd.png)

作成できました

![06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/5a37512c-530e-1a40-9b6e-50c32eb6d2fc.png)



#### 1-2.  ```動的グループ```の```ポリシー```の作成
* OCIコンソール → ```アイデンティティとセキュリティ``` → ```ポリシー``` → ```ポリシーの作成```
* 今回は、特定コンパートメント内のObject Storageとバケットを管理するポリシーを作成しています。
    * 名前：任意の名前
    * 説明：任意の説明
    * ポリシー・ユース・ケース：ストレージ管理
    * 共通ポリシー・テンプレート：オブジェクト・ストレージ管理者がバケットおよびオブジェクトを管理できるようにします
    * グループ or d動的グループ：動的グループ（先程作成したものを指定）
    * 場所：今回は、自分のコンパートメントを指定しています
 
![o4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/fccd5ac7-4cec-348e-3a25-42a9e47a76a0.png)
作成されました
![o5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/c380f2de-5afe-b088-ab76-940ac04e06b7.png)
* 補足
    * リソース・プリンシパル・トークンは2時間キャッシュされる。
    * ポリシーまたは動的グループを変更した場合、変更の影響を確認するには、2時間待つ必要がある


### 2. ```リソース・プリンシパル```の有効化 
* ADBインスタンスにADMINユーザーでログイン
* リソース・プリンシパルを有効化
    * DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPALプロシージャについては[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-admin.html#GUID-4DC6A536-DC78-43FE-B173-CED1F9FB45A0)
    * ADMINユーザ以外のDBユーザーでリソース・プリンシパルへのアクセス権限を付与したい場合は[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/resource-principal-enable.html#GUID-D4FEBDB0-B9CD-45B2-8DBE-A83EFE1DD8A2)を参照
```sql
EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
```

リソース・プリンシパルが有効になったか確認
```sql
SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN'; 
```
出力例
```sql
   OWNER           CREDENTIAL_NAME 
________ _________________________ 
ADMIN    OCI$RESOURCE_PRINCIPAL    
```

### 3. 外部表の作成
テスト表を作成
```sql
CREATE TABLE CHANNELS
   (channel_id NUMBER,
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20)
   );
```
作成した表を確認
```sql
select object_name from user_objects where object_name='CHANNELS';
```
出力例
```sql
   OBJECT_NAME 
______________ 
CHANNELS     
```
```sql
desc CHANNELS
```
出力例
```sql
            Name    Null?            Type 
________________ ________ _______________ 
CHANNEL_ID                NUMBER          
CHANNEL_DESC              VARCHAR2(20)    
CHANNEL_CLASS             VARCHAR2(20)   
```

* ```DBMS_CLOUD.COPY_DATA```プロシージャを使用し、テスト表上にデータをロード。
    * DBMS_CLOUD.COPY_DATAプロシージャについては[こちら](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-9428EA51-5DDD-43C2-B1F5-CD348C156122)

```sql
BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    credential_name =>'OCI$RESOURCE_PRINCIPAL',
    file_uri_list =>'https://objectstorage.ap-osaka-1.oraclecloud.com/n/<ネームスペース名>/b/kadw-bucket/o/channels.txt',
    format => json_object('delimiter' value ',')
 );
END;
/
```

### 4. 作成した外部表に問い合わせ
```sql
select * from CHANNELS;
```
出力例
```sql
  CHANNEL_ID    CHANNEL_DESC    CHANNEL_CLASS 
_____________ _______________ ________________ 
            1 Direct Sales    Direct           
            2 Tele Sales      Direct           
            3 Catalog         Indirect         
            4 Internet        Indirect         
            5 Partners        Others    
```

### 5. 環境削除
* ADBからテスト表の削除
```sql
drop table CHANNELS purge;
```
```sql
select object_name from user_objects where object_name='CHANNELS';
```
出力例
```sql
no rows selected
```
* リソース・プリンシパルの無効化
```sql
EXEC DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL();
```
```sql
SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN'; 
```
出力例
```sql
no rows selected
```

# その他参考
* [AWS S3からOracle DBにデータを導入する](https://qiita.com/liu-wei/items/2ed58265519531d09e97)
* [Azure BlobからOracle DBにデータを導入する](https://qiita.com/liu-wei/items/17b3b49735b458e3412d)