LoginSignup
17
24

More than 1 year has passed since last update.

@kempe(建平 五十嵐)

RDSとS3でファイルのやり取りを行う => 2022年11月からは EFS がサポートされました！

Last updated at Posted at 2017-08-21

データベースサーバ上にファイルを置いて、PL/SQLのUTL_FILE経由で読み書きするような処理があった場合、RDSに移行しようとすると、データベースサーバにファイルが置けないなあ・・・などという場合に、S3を間に置く方法があります。

2022/11/16更新！

RDS が Amazon Elastic File System (EFS) をサポートするようになりました！2019年にリリースされた S3 を使う方法からさらに進化して、EFSに対するOracle Directoryオブジェクトを直接定義してファイルをやり取りできるようになっています。この記事の手法ではなく、ぜひ EFS に対して直接アクセスするための PL/SQL ファンクションを使用して下さい！
Amazon EFS integration

PL/SQL
BEGIN
  rdsadmin.rdsadmin_util.create_directory_efs(
    p_directory_name => 'DATA_PUMP_DIR_EFS', 
    p_path_on_efs    => '/rdsefs-fs-1234567890abcdef0/datapump1');
END;
/

上記の例では DATA_PUMP_DIR_EFS というディレクトリオブジェクトが EFS に向けて作成されており、このディレクトリに対して読み書きすることができる、という仕組みになっています。

2019/02/28更新！

RDS が S3 integrationをサポートするようになりました！この記事の手法ではなく、以下のURLに示す、RDSで提供されるS3へのアクセスするためのPL/SQLファンクションを使用して下さい！
Amazon S3 統合

前提

EC2 <-file-> S3 <-file-> RDS上のファイル
というやり取りについて記載しています。
また、以下の情報は2017年8月時点のものです。

Oracle on Amazon RDSでの制限

前提として、Oracle on RDSでできることを整理しましょう。S3にアクセスするにはUTL_HTTPパッケージが必要です。ユーザーガイドの「utl_http、utl_tcp、utl_smtp の使用」にサポートされる旨が記載されています。
次に、UTL_FILEによるアクセスです。UTL_FILEを用いるにはディレクトリオブジェクトを扱える必要があります。こちらについてもユーザーガイドの「主要データストレージ領域で新しいディレクトリを作成する」にて、rdsadmin.rdsadmin_util.create_directoryプロシージャを使用して可能なことが記載されています。

使用するライブラリ

UTL_HTTPを使ったS3へのアクセスを全て自分で書くのは大変なので、alexandria-plsql-utilsのAMAZON_AWS_S3_PKGを用いることにします。

準備手順

EC2, S3のバケット、RDSをすべて新規で構成する手順を見ていきましょう。順序としては以下のようになります。

  1. RDSを作成する。
  2. S3にバケットを作成する。
  3. バケットに含まれるオブジェクトへのアクセス権を持つポリシーを作成する。
  4. 3で作成したポリシーを持つロールを付与したEC2を作成する。
  5. 3で作成したポリシーを持つユーザを作成する。
  6. RDSにディレクトリを作成し、S3へのアクセスに必要な権限と5で作成したユーザのアクセス情報を設定する。
  7. EC2とS3のやり取りを行ってみる。
  8. RDSとS3のやり取りを行ってみる。

1. RDSの作成

まず、いきなりRDSを作成するのではなく、先にRDSのメニューから「オプショングループ」を選択し、「apex」という名前でAPEX及びAPEX-DEVを含むオプショングループを作成して下さい。これは、AMAZON_AWS_S3_PKGが内部でデコード関連でAPEXのライブラリを使用しているためです(APEXを実際に起動する必要はありません)。以下のようになります。
スクリーンショット 2017-08-19 17.32.40.png

apexオプショングループを用いてRDSを作成して下さい。指定箇所はパラメータグループの下にあります。次のようになります。
スクリーンショット 2017-08-19 17.34.53.png
あとは通常のRDSの作成と同様です。RDSの作成については以下を参照して下さい。
RDSユーザーガイド-Oracle DB インスタンスを作成して Oracle DB インスタンス上のデータベースに接続する

2. S3バケットの作成

特に特筆すべきことはありません。グローバルで一意になる名前でS3にバケットを作成しましょう。
S3入門ガイド-バケットの作成

3. ポリシーの作成

IAMから2で作成したバケットに含まれるオブジェクトへのアクセス権限を持つポリシーを作成します。
IAM -> ポリシーで「ポリシーの作成」を押したら、「独自のポリシーの作成」を選びましょう。
スクリーンショット 2017-08-19 17.46.37.png
ポリシー名には「allow-rds-s3-policy」などとし、ポリシードキュメントには以下のJSONを記述します。

S3-rds-policy
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::<手順2で作成したS3バケットの名前>"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::<手順2で作成したS3バケットの名前>/*"
            ]
        }
    ]
}

このポリシーは2で作成したS3バケット、及びオブジェクトに対する権限を付与します。これをEC2、及びRDSのPL/SQLアクセス時に有効にすれば、EC2 <-> S3 <-> RDS上のPL/SQLでファイルをやり取りできます。

4. EC2インスタンスの作成

3の手順で作成したポリシーを付与したEC2用のIAMロールを作成します。IAMサービスから
ロール -> 新しいロールの作成 -> EC2ロールタイプ と選択し、
スクリーンショット 2017-08-20 0.33.30.png

3の手順で作成したポリシーを付与して
スクリーンショット 2017-08-20 0.34.37.png

名前をつければ完了です。
スクリーンショット 2017-08-20 0.37.17.png

あとはこのロールを指定してEC2インスタンスを作成します。
スクリーンショット 2017-08-20 0.39.29.png
1の手順で作成したRDSに1521ポートで接続可能なサブネットに作成して下さい。
EC2インスタンスの作成については以下も参照して下さい。
インスタンスの作成

5. PL/SQL用IAMユーザの作成

PL/SQLにIAMロールは付与できないので3の手順で作成したポリシーを付与したユーザをPL/SQL用に作成します。IAMサービスから
ユーザー -> ユーザーの追加
を選択し、ユーザー名を入力して「プログラムによるアクセス」を有効にします。
スクリーンショット 2017-08-20 0.45.29.png
アクセス権限の設定画面では、「既存のポリシーを直接アタッチ」を選択し、3の手順で作成したポリシーをチェックします。
スクリーンショット 2017-08-20 0.48.06.png
作成が完了した際に得られる「アクセスキー ID」と「シークレットアクセスキー」をPL/SQL側で使用することになります。

6. RDS上での設定

RDS上ではディレクトリの作成と、アクセス権限の設定を行います。
設定を行う前に、4の手順で作成したEC2インスタンスにログインし、必要なツールを入れます。
###SQLPlus
OTNのInstant Client Downloads for Linux x86-64から、basic及びsqlplusの2つのパッケージをブラウザ経由でダウンロードし、EC2インスタンスに転送してインストールして下さい(オラクル社のSSOログインが要求されますので、ブラウザで実施する必要があります)。
以下のように12.2のrpmをインストールした場合には、OCIライブラリやSQLPlusのバイナリは/usr/lib/oracle/12.2/client64にインストールされています。

SQL*Plusのインストール
$ sudo rpm -i oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -i oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
$ ls /usr/lib/oracle/12.2/client64/bin/
adrci  genezi  sqlplus
$ ls /usr/lib/oracle/12.2/client64/lib/
glogin.sql             libmql1.so       libocijdbc12.so   libsqlplusic.so
libclntsh.so.12.1      libnnz12.so      libons.so         ojdbc8.jar
libclntshcore.so.12.1  libocci.so.12.1  liboramysql12.so  xstreams.jar
libipc1.so             libociei.so      libsqlplus.so
$

以下のように.bash_profileを設定しておきましょう。これでいつでもRDSにログインできます。

~/.bash_profile
...
ORACLIENT=/usr/lib/oracle/12.2/client64
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLIENT/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLIENT/lib

alias sql="sqlplus '<DBユーザー>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<DB名>.<エンドポイント>.ap-northeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=<DBのSID>)))'"

alexandria-plsql-utils

Gitでクローンし、AMAZON_AWS_S3_PKGをインストールします。

alexandria-plsql-utilsのインストール
$ sudo yum install git
...
完了しました!
$ git clone https://github.com/mortenbra/alexandria-plsql-utils.git
Cloning into 'alexandria-plsql-utils'...
remote: Counting objects: 447, done.
remote: Total 447 (delta 0), reused 0 (delta 0), pack-reused 447
Receiving objects: 100% (447/447), 382.00 KiB | 0 bytes/s, done.
Resolving deltas: 100% (184/184), done.
Checking connectivity... done.
$ cd alexandria-plsql-utils/
$ ls
README.md  alexandria-logo.jpg  demos  doc  extras  ora  setup
$ ls setup/
$ sql      #前節で設定したエイリアスでRDSへ接続
...
SQL> @install_core
...
SQL> show errors
No errors
SQL> @install_inet
...
SQL> show errors
No errors
SQL> @install_amazon
...
SQL> show errors
No errors
SQL> exit
$

ACLの設定

明示的にACLを設定しない限りUTL_HTTPによるアウトバウンドのアクセスはOracleにより全て拒否されます。次のようにDBMS_NETWORK_ACL_ADMINパッケージを用いて自ユーザから手順2で作成したs3バケットに対してのみアクセスを許可します。

create_acl.sql
declare
   l_myuser varchar(32);
begin
   select user into l_myuser from dual;
   dbms_network_acl_admin.create_acl(
     acl         => 's3',
     description => 's3 acl',
     principal   => l_myuser,
     is_grant    => true,
     privilege   => 'connect'
   );
   dbms_network_acl_admin.add_privilege(
     acl         => 's3',
     principal   => l_myuser,
     is_grant    => true,
     privilege   => 'resolve'
   );
   dbms_network_acl_admin.assign_acl(
     acl         => 's3',
     host        => '<手順2で作成したバケット名>.s3.amazonaws.com'
   );
end;
/

ディレクトリの作成

RDS側の格納先であるメインデータストレージ領域上のディレクトリを作成します。以下では2つ作成しています。テーブル名などと同じく、Oracleのデータベース・オブジェクト名となるので引用符で囲まなければ大文字となります。
データベース・オブジェクト名および修飾子

create_directory.sql
begin
  rdsadmin.rdsadmin_util.create_directory('EC2');
  rdsadmin.rdsadmin_util.create_directory('S3');
end;
/

#アクセス
##EC2からS3へのアクセス
テスト用ファイルとしてAWSのEC2オファーファイルを使い、EC2とS3のオファーファイルをそれぞれ異なるディレクトリに配置しておきましょう。

テスト用ファイルのダウンロード
$ mkdir ~/files
$ cd ~/files
$ mkdir ec2 s3
$ wget -O ec2/ec2-price.csv https://pricing.us-east-1.amazonaws.com/offers/v1.0/aws/AmazonEC2/current/index.csv
...
ec2/ec2-price.csv   100%[===================>]  92.92M  29.9MB/s    in 3.1s    
...
$ wget -O s3/s3-price.csv https://pricing.us-east-1.amazonaws.com/offers/v1.0/aws/AmazonS3/current/index.csv
...
s3/s3-price.csv     100%[===================>] 870.02K  --.-KB/s    in 0.05s   
...
$

###EC2からS3へのコピー
EC2ではAWS CLIが使え、手順4でS3への権限をロールで与えているので、以下のコマンドを打てば完了です。

S3へのupload
$ cd ~/files
$ aws s3 cp ec2/ec2-price.csv s3://<手順2で作成したバケット名>/ec2/ec2-price.csv
upload: ec2/ec2-price.csv to s3://<手順2で作成したバケット名>/ec2/ec2-price.csv
$ aws s3 cp s3/s3-price.csv s3://<手順2で作成したバケット名>/s3/s3-price.csv
upload: s3/s3-price.csv to s3://<手順2で作成したバケット名>/s3/s3-price.csv
$

S3からEC2へのコピー

以下のコマンドを打てば完了です。

S3からのdownload
$ cd ~/files
$ rm -R */*.csv #ファイルを消しておきます
$ aws s3 cp s3://<手順2で作成したバケット名>/ec2/ec2-price.csv ec2/ec2-price.csv
download: s3://<手順2で作成したバケット名>/ec2/ec2-price.csv to ec2/ec2-price.csv
$ aws s3 cp s3://<手順2で作成したバケット名>/s3/s3-price.csv s3/s3-price.csv
download: s3://<手順2で作成したバケット名>/s3/s3-price.csv to s3/s3-price.csv       
$ ls -R
.:
ec2  s3

./ec2:
ec2-price.csv

./s3:
s3-price.csv
$

RDSからS3へのアクセス

AMAZON_AWS_S3_PKGはBLOBとS3オブジェクトをインタフェースします。
認証とやり取りのためのBLOBとして1つテーブルを用意しておきます。

rds_s3_config.sql
create table rds_s3_config (
  key varchar2(32) primary key,
  value varchar2(128),
  tmpblob blob
);
insert into rds_s3_config (key, value) values ('aws_id', '<手順5で得たアクセスキーID>');
insert into rds_s3_config (key, value) values ('aws_key', '<手順5で得たシークレットアクセスキー>');
insert into rds_s3_config (key, value) values ('aws_s3_bucket', '<手順2で作成したS3バケット名>');
insert into rds_s3_config (key, tmpblob) values ('temporary_blob', empty_blob());
commit;
実行結果
SQL> @rds_s3_config

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.

SQL>

S3からRDSへのコピー

S3からオブジェクトをBLOBで取り出し、BLOBをファイルに書き込みます。次のようなプロシージャを作成しておきます。

copy_s3_to_local.sql
create or replace procedure copy_s3_to_local(
  p_s3_bucket varchar2,
  p_s3_key varchar2,
  p_local_dir varchar2,
  p_local_file varchar2
) is
  l_aws_id      varchar2(128);
  l_aws_key     varchar2(128);

  l_blob        blob;
  l_length      integer;
  l_index       integer := 1;
  l_bytecount   integer;
  l_tempraw     raw(32767);
  l_file        utl_file.file_type;
  l_dir         varchar2(128);
begin
  select value into l_aws_id from rds_s3_config where key = 'aws_id';
  select value into l_aws_key from rds_s3_config where key = 'aws_key';
  amazon_aws_auth_pkg.init(l_aws_id, l_aws_key);

  l_blob := amazon_aws_s3_pkg.get_object(p_s3_bucket, p_s3_key);
  -- エラーレスポンスかどうかを粗く判定
  if utl_raw.cast_to_varchar2(dbms_lob.substr(l_blob,256,1)) like '%<Error>%' then
    raise NO_DATA_FOUND;
  end if;

  l_length := dbms_lob.getlength(l_blob);
  l_file  := utl_file.fopen(p_local_dir, p_local_file, 'wb', 32767);
  
  while l_index <= l_length
  loop
      l_bytecount  := 32767;
      DBMS_LOB.read(l_blob, l_bytecount, l_index, l_tempraw);
      utl_file.put_raw(l_file, l_tempraw);
      l_index      := l_index + l_bytecount;
  end loop;
  utl_file.fflush(l_file);
  utl_file.fclose(l_file);
end;
/
show errors
実行結果
SQL> @copy_s3_to_local

Procedure created.

No errors.
SQL>

テストしてみましょう。

copy_s3_to_local_test.sql
set serveroutput on
begin
  copy_s3_to_local('<手順2で作成したS3バケット名>', 'ec2/ec2-price.csv', 'EC2', 'ec2-price.csv');
  copy_s3_to_local('<手順2で作成したS3バケット名>', 's3/s3-price.csv', 'S3', 's3-price.csv');
end;
/
テスト：S3からRDSへのダウンロード
SQL> @copy_s3_to_local_test

PL/SQL procedure successfully completed.

SQL>

RDSからS3へのコピー

ファイルからテーブル上のBLOBに書き込み、S3にアップロードします。次のようなプロシージャを作成しておきます。

copy_local_to_s3.sql
create or replace procedure copy_local_to_s3(
  p_local_dir varchar2,
  p_local_file varchar2,
  p_s3_bucket varchar2,
  p_s3_key varchar2
) is
  l_aws_id      varchar2(128);
  l_aws_key     varchar2(128);

  l_blob        blob;
  l_handle      bfile;
  l_dir         varchar2(128);
  l_doffset     pls_integer := 1;
  l_soffset     pls_integer := 1;
begin
  select value into l_aws_id from rds_s3_config where key = 'aws_id';
  select value into l_aws_key from rds_s3_config where key = 'aws_key';
  amazon_aws_auth_pkg.init(l_aws_id, l_aws_key);

  select tmpblob into l_blob from rds_s3_config where key = 'temporary_blob' for update;
  l_handle := bfilename(p_local_dir, p_local_file);
  dbms_lob.fileopen(l_handle, dbms_lob.file_readonly);
  dbms_lob.loadblobfromfile(l_blob, l_handle, dbms_lob.getlength(l_handle), l_doffset, l_soffset);
  -- このサンプルはContent-TypeをCSVに固定
  amazon_aws_s3_pkg.new_object(p_s3_bucket, p_s3_key, l_blob, 'text/csv');
  dbms_lob.fileclose(l_handle);
  rollback;
end;
/
show errors

テストしてみましょう。

copy_local_to_s3_test.sql
set serveroutput on
begin
  copy_local_to_s3('EC2', 'ec2-price.csv', '<手順2で作成したS3バケット名>', 'ec2/ec2-price.csv');
  copy_local_to_s3('S3', 's3-price.csv', '<手順2で作成したS3バケット名>', 's3/s3-price.csv');
end;
/
テスト:RDSからS3へのアップロード
$ aws s3 rm s3://<手順2で作成したS3バケット名>/ec2/ec2-price.csv #ファイルを削除
delete: s3://<手順2で作成したS3バケット名>/ec2/ec2-price.csv
$ aws s3 rm s3://<手順2で作成したS3バケット名>/s3/s3-price.csv   #ファイルを削除
delete: s3://<手順2で作成したS3バケット名>/s3/s3-price.csv
$ aws s3 ls s3://<手順2で作成したS3バケット名>/ec2/ #空であることを確認
$ aws s3 ls s3://<手順2で作成したS3バケット名>/s3/  #空であることを確認
$ sql
... 
SQL> @copy_local_to_s3_test

PL/SQL procedure successfully completed.

SQL> exit
...
$ aws s3 ls s3://<手順2で作成したS3バケット名>/ec2/ #アップロードされたことを確認
2017-08-21 13:44:18   97438744 ec2-price.csv
$ aws s3 ls s3://<手順2で作成したS3バケット名>/s3/  #アップロードされたことを確認
2017-08-21 13:44:20     890903 s3-price.csv
$

まとめ

以上、EC2とS3のファイルのやり取り、そしてS3とRDSのファイルのやり取りについて見てきました。
より本格的に処理するには、特に紹介したPL/SQLプロシージャにおいて、S3へのアップロードのContent-Typeを適切に選択したり、エラーレスポンス(XMLドキュメントが返される)の判定を厳密にしたりなどが必要となるでしょう。

17
24
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
17
24