Last active
February 28, 2019 00:27
Star
You must be signed in to star a gist
RDSとS3でファイルのやり取りを行う ref: https://qiita.com/kempe/items/1a4b0c479f1ac38a3557
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
... | |
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>)))'" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
begin | |
rdsadmin.rdsadmin_util.create_directory('EC2'); | |
rdsadmin.rdsadmin_util.create_directory('S3'); | |
end; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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バケットの名前>/*" | |
] | |
} | |
] | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 | |
... | |
$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SQL> @copy_s3_to_local_test | |
PL/SQL procedure successfully completed. | |
SQL> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SQL> @copy_s3_to_local | |
Procedure created. | |
No errors. | |
SQL> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment