はじめに
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. リソース・プリンシパル を使用した方法
- こちらの方法では、資格証明の作成が必要ありません。
- a. 資格証明 を使用した方法
テスト用のデータ
事前に、次のデータのある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に保存
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;
/
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ユーザーでログイン
- リソース・プリンシパルを有効化
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