はじめに
本文章では、Oracle Cloud Infrastructure(OCI) BaseDBから、DBMS_CLOUDパッケージを利用して、AWS Simple Storage Service(S3)へCSVを書き出す方法をご紹介します。最後にDBMS_SCHEDULERを使用して定期的に書き出す設定を行います。
利用条件
DBMS_CLOUDは、Oracle Database 19cの19.9以降およびOracle Database 21cの21.3以降でサポートされています。(On-Premise/Cloud両方に適用します)
注意事項
DBMS_CLOUDパッケージの機能は、Autonomous Database (Oracle Managed) と非Autonomous Database (Customer managed) の間で異なる場合があります。非Autonomous Databaseの場合は、ご使用のDatabaseバージョンのドキュメントをこちらで確認してください。
実施手順
-
前提条件
BaseDBにて、事前にDBMS_CLOUDをインストールする必要があります。
セットアップ方法はこちらを参照してください。（要ログイン）
How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)
-
クレデンシャルの作成
データベース内に、AWSへアクセスするための資格証明を格納したオブジェクトを作成します。
前提として、AWS側で、S3を参照可能な適切なポリシーをアタッチしたグループに属するIAMユーザを作成し、認証情報 ＞ アクセスキー から、アクセスキーの作成を行います。「アクセス・キーID」と、「シークレット・アクセス・キー」を使用します。
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'S3_CRED', -- クレデンシャルの名前(任意の文字列) username => 'AKI*****************', -- AWS アクセス・キーID password => 'NAq*****************' -- AWSシークレット・アクセス・キー ); END; /
-
ディレクトリDATA_DUMP_DIRの場所を確認
一旦ディレクトリ・オブジェクトにデータを出力するので、デフォルトで用意されているDATA_DUMP_DIRの場所を確認します。
SELECT directory_name, directory_path FROM DBA_DIRECTORIES WHERE directory_name='DATA_PUMP_DIR';
-
ディレクトリDATA_PUMP_DIRにCSVファイルを生成
この例では、ディレクトリDATA_PUMP_DIRにtest.csvという名前でCSVを出力します。TEST表からID列とNAME列を出力しています。
SQL*Plusの制限により、最大の文字幅が32767文字となっているため超えないように注意します。$ sqlplus -S <ユーザ名>/<パスワード>@<接続文字列> << SQL > <Step2で確認したディレクトリへのパス>/test.csv SET PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON FEEDBACK OFF -- SETコマンドはSQL*Plusの表示形式の指定 SELECT id || ',' || name FROM test; -- TEST表からID列・NAME列をカンマ区切りで出力 SQL- -SはSQL*Plusのサイレント・モードのオプションです。このオプションを使うと起動時や終了時のバージョン情報等が出力されません。
- 接続文字列はOCIコンソールの［PDB接続］ボタンから確認することが可能です。
-
該当S3のバケットにを確認
出力先のバケットにオブジェクトがあるか確認します。同じ名前のファイルがあった場合に転送すると上書きされるので注意します。
SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('S3_CRED', 'https://<バーケット名>.<リージョン名>.amazonaws.com/'); no rows selected
-
CSVファイルをS3のバケットにアップロード
DBMS_CLOUD.PUT_OBJECTコマンドを使ってS3にCSVを転送します。
BEGIN DBMS_CLOUD.PUT_OBJECT( credential_name => 'S3_CRED', -- Step1で作成したクレデンシャルの名前 object_uri => 'https://<バーケット名>.<リージョン名>.amazonaws.com/test.csv', directory_name => 'DATA_PUMP_DIR', file_name => 'test.csv' ); END; /
-
再度該当S3のバーケットを確認
Step4と同じコマンドでファイルが転送されていることを確認します。
SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('S3_CRED', 'https://<バケット名>.<リージョン名>.amazonaws.com/'); OBJECT_NAME BYTES CHECKSUM CREATED LAST_MODIFIED -------------------- ---------- --------------------------------------------- -------------------- -------------------------------------------------- test.csv 18 63ed4a9b12512fffec14bdad86d079d9 19-DEC-23 05.14.56.000000 AM +00:00
-
[定期的に書き出す場合]CSVファイルをS3にアップロードするプロシージャを作成
CREATE OR REPLACE PROCEDURE UPLOAD_TO_CLOUD AS credential_name_in VARCHAR2(100) := 'S3_CRED'; -- Step1で作成したクレデンシャルの名前 object_uri_in VARCHAR2(1000) := 'https://<バケット名>.<リージョン名>.amazonaws.com/test.csv'; directory_name_in VARCHAR2(100) := 'DATA_PUMP_DIR'; file_name_in VARCHAR2(100) := 'test.csv'; BEGIN DBMS_CLOUD.PUT_OBJECT( credential_name => credential_name_in, object_uri => object_uri_in, directory_name => directory_name_in, file_name => file_name_in ); END; /
-
[定期的に書き出す場合]Step7で作成したプロシージャをStep8に登録
DBMS_SCHEDULER.CREATE_JOBプロシージャでスケジューラーに登録します。
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'EXPORT_JOB', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN UPLOAD_TO_CLOUD END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=HOURLY; INTERVAL=1', enabled => TRUE ); END; /
-
[定期的に書き出す場合]Step8で登録したジョブの確認
以下のクエリで、スケジューラーに登録されているジョブが確認できます。
col owner for a20 col job_name for a40 col REPEAT_INTERVAL for a80 col JOB_ACTION for a60 col state for a10 select owner, job_name, repeat_interval, state, job_action from dba_scheduler_jobs where state != 'DISABLED';