DBMS_CLOUDを利用したOCI BaseDBからAWS S3へCSVを書き出す方法

## はじめに

本文章では、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バージョンのドキュメントを[こちら](https://docs.oracle.com/search/?q=DBMS_CLOUD&category=database&product=en%2Fdatabase%2Foracle%2Foracle-database&lang=ja)で確認してください。

## 実施手順

- **前提条件**

  BaseDBにて、事前にDBMS_CLOUDをインストールする必要があります。
　セットアップ方法はこちらを参照してください。（要ログイン）
　[How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)](https://support.oracle.com/knowledge/Oracle%20Cloud/2748362_1.html)

1. **クレデンシャルの作成**

    データベース内に、AWSへアクセスするための資格証明を格納したオブジェクトを作成します。
    
    前提として、AWS側で、S3を参照可能な適切なポリシーをアタッチしたグループに属するIAMユーザを作成し、認証情報 ＞ アクセスキー から、アクセスキーの作成を行います。「アクセス・キーID」と、「シークレット・アクセス・キー」を使用します。
  
    ```sql
    BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
      credential_name => 'S3_CRED',       -- クレデンシャルの名前(任意の文字列)
      username => 'AKI*****************', -- AWS アクセス・キーID
      password => 'NAq*****************'  -- AWSシークレット・アクセス・キー
      );
    END;
    /
    ```
    <br>

2. **ディレクトリDATA_DUMP_DIRの場所を確認**

    一旦ディレクトリ・オブジェクトにデータを出力するので、デフォルトで用意されているDATA_DUMP_DIRの場所を確認します。

   ```sql
   SELECT directory_name, directory_path FROM DBA_DIRECTORIES WHERE directory_name='DATA_PUMP_DIR';
   ```
   <br>
  
3. **ディレクトリDATA_PUMP_DIRにCSVファイルを生成**

    この例では、ディレクトリDATA_PUMP_DIRにtest.csvという名前でCSVを出力します。TEST表からID列とNAME列を出力しています。
    SQL*Plusの制限により、最大の文字幅が32767文字となっているため超えないように注意します。
    
    ```sql
    $ 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接続］ボタンから確認することが可能です。
    <br>
4. **該当S3のバケットにを確認**

    出力先のバケットにオブジェクトがあるか確認します。同じ名前のファイルがあった場合に転送すると上書きされるので注意します。

    ```sql
    SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('S3_CRED', 'https://<バーケット名>.<リージョン名>.amazonaws.com/');
    
    no rows selected
    ```
    <br>
   
5. **CSVファイルをS3のバケットにアップロード**

    DBMS_CLOUD.PUT_OBJECTコマンドを使ってS3にCSVを転送します。

    ```sql
    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;
    /
    ```
    <br>
    
6. **再度該当S3のバーケットを確認**

    Step4と同じコマンドでファイルが転送されていることを確認します。

   
     ```sql
     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
     ```
     <br>

7. **[定期的に書き出す場合]CSVファイルをS3にアップロードするプロシージャを作成**

    ```sql
    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;
    /
   ```
   <br>


8. **[定期的に書き出す場合]Step7で作成したプロシージャをStep8に登録**

   DBMS_SCHEDULER.CREATE_JOBプロシージャでスケジューラーに登録します。

   ```sql
   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;
   /
   ```
   <br>

9. **[定期的に書き出す場合]Step8で登録したジョブの確認**

   以下のクエリで、スケジューラーに登録されているジョブが確認できます。

   ```sql
   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';
   ```