Edited at

RDSとS3でファイルのやり取りを行う

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


2019/02/28更新!

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

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/oracle-s3-integration.html


前提

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インスタンスにログインし、必要なツールを入れます。


SQL*Plus

OTNのInstant Client Downloads for Linux x86-64から、basic及びsqlplusの2つのパッケージをブラウザ経由でダウンロードし、EC2インスタンスに転送してインストールして下さい(オラクル社のSSOログインが要求されますので、ブラウザで実施する必要があります)。

以下のように12.2のrpmをインストールした場合には、OCIライブラリやSQL*Plusのバイナリは/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;
/


テスト

$ 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ドキュメントが返される)の判定を厳密にしたりなどが必要となるでしょう。