はじめに

DBMS_CLOUDパッケージを使用して、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. 資格証明を使用した方法
  - こちらの方法では、資格証明が変更したら資格証明の保存作業の再実行が必要になります。
- b. リソース・プリンシパルを使用した方法
  - こちらの方法では、資格証明の作成が必要ありません。

テスト用のデータ

事前に、次のデータのある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に保存
- 今回は、こちらの手順で作成した認証トークンを使用します
- DBMS_CLOUD.CREATE_CREDENTIALプロシージャについてはこちら

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

DBA_CREDENTIALSビューで作成した資格証明を確認します。

select OWNER, CREDENTIAL_NAME, USERNAME, ENABLED from DBA_CREDENTIALS;

表示例

   OWNER    CREDENTIAL_NAME                                       USERNAME    ENABLED 
________ __________________ ________________________________________________ __________ 
ADMIN    BUCKET_TOKEN_EXT       oracleidentitycloudservice/xxx@xxx.com    TRUE

2. 外部表の作成

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

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についてはこちら
- エラーが出た場合にデータ検証のログを確認する方法はこちら

exec DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ('CHANNELS_EXT');

外部表を確認

select object_name from user_objects where object_name='CHANNELS_EXT';

出力例

    OBJECT_NAME 
_______________ 
CHANNELS_EXT

desc CHANNELS_EXT

出力例

________________ ________ _______________ 
CHANNEL_ID                NUMBER          
CHANNEL_DESC              VARCHAR2(20)    
CHANNEL_CLASS             VARCHAR2(20)

3. 作成した外部表に問い合わせ

SELECT count(*) FROM channels_ext;

出力例

   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からテスト表の削除

drop table channels_ext purge;

select object_name from user_objects where　object_name='CHANNELS_EXT';

ADB内の既存の資格証明を削除
- DBMS_CLOUD.DROP_CREDENTIALプロシージャについてはこちら

EXEC DBMS_CLOUD.DROP_CREDENTIAL ('BUCKET_TOKEN_EXT');

SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN';

b. リソース・プリンシパルを使用した方法

1. `動的グループ`の作成

1-1. `動的グループ`の`ルール`の作成

OCIコンソール → アイデンティティとセキュリティ → 動的グループをクリック

動的グループを作成

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

作成できました

1-2. `動的グループ`の`ポリシー`の作成

OCIコンソール → アイデンティティとセキュリティ → ポリシー → ポリシーの作成
今回は、特定コンパートメント内のObject Storageとバケットを管理するポリシーを作成しています。
- 名前：任意の名前
- 説明：任意の説明
- ポリシー・ユース・ケース：ストレージ管理
- 共通ポリシー・テンプレート：オブジェクト・ストレージ管理者がバケットおよびオブジェクトを管理できるようにします
- グループ or d動的グループ：動的グループ（先程作成したものを指定）
- 場所：今回は、自分のコンパートメントを指定しています

作成されました

補足
- リソース・プリンシパル・トークンは2時間キャッシュされる。
- ポリシーまたは動的グループを変更した場合、変更の影響を確認するには、2時間待つ必要がある

2. `リソース・プリンシパル`の有効化

ADBインスタンスにADMINユーザーでログイン
リソース・プリンシパルを有効化
- DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPALプロシージャについてはこちら
- ADMINユーザ以外のDBユーザーでリソース・プリンシパルへのアクセス権限を付与したい場合はこちらを参照

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

リソース・プリンシパルが有効になったか確認

SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN';

出力例

   OWNER           CREDENTIAL_NAME 
________ _________________________ 
ADMIN    OCI$RESOURCE_PRINCIPAL

3. 外部表の作成

テスト表を作成

CREATE TABLE CHANNELS
   (channel_id NUMBER,
    channel_desc VARCHAR2(20),
    channel_class VARCHAR2(20)
   );

作成した表を確認

select object_name from user_objects where object_name='CHANNELS';

出力例

   OBJECT_NAME 
______________ 
CHANNELS

desc CHANNELS

出力例

            Name    Null?            Type 
________________ ________ _______________ 
CHANNEL_ID                NUMBER          
CHANNEL_DESC              VARCHAR2(20)    
CHANNEL_CLASS             VARCHAR2(20)

DBMS_CLOUD.COPY_DATAプロシージャを使用し、テスト表上にデータをロード。
- DBMS_CLOUD.COPY_DATAプロシージャについてはこちら

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. 作成した外部表に問い合わせ

select * from CHANNELS;

出力例

  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からテスト表の削除

drop table CHANNELS purge;

select object_name from user_objects where object_name='CHANNELS';

出力例

no rows selected

リソース・プリンシパルの無効化

EXEC DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL();

SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN';

出力例

no rows selected

その他参考