■■■Oracle Cloudのウェビナーシリーズは→こちら■■■
Q.「Autonomous Database から AWS S3 のデータは見えますか？」
A.「はい、見えます」
というやりとりが何度かあったので、書いてみました。
AWS S3のバケット上に置いたファイルを、Oracle Cloud (OCI) 上の、Autonomous Databaseから参照してみます。
※[はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）]
(https://qiita.com/mikika/items/5323651cb5826a5b495e)
で書いた、Oracle Cloud（OCI）のObject Storageにあるデータを参照するときのやり方と、手順は同じで、DBMS_CLOUD.CREATE_CREDENTIAL でAWSのアクセス・キーIDと、シークレット・アクセス・キーを指定します。製品ドキュメントでは、dbms_cloud.create_creadential の説明が該当しますが、Amazon S3資格証明に関する記述と同時に、Azure Blob Storageへも同じことが可能な旨、記載があります。
※今回はCSVファイルをAutonomous Databaseへロードしましたが、ロード時に使用したdbms_cloud.copy_data は、ParquetやAvro形式にも対応しています。
ParquetおよびAvroのDBMS_CLOUDパッケージ形式オプション
準備
Autonomous Database を作成直後に存在する adminは管理者ユーザなので、別のユーザを作成して、作業します。
Autonomous Database上のユーザの作成
※作成するユーザ名は任意です。ここではSTAGEというデータベースユーザで。
-- adminユーザで実行。データベースユーザを作成し、権限付与
CREATE USER STAGE IDENTIFIED BY Tiger123456##; -- ユーザ名とパスワードを指定
GRANT DWROLE to STAGE;
GRANT UNLIMITED TABLESPACE to STAGE;
以降、STAGEユーザで実行していきます。
SQL Developer Webを使って実行したい場合は、SQL Developer Web へ、admin以外のユーザで接続する にある追加の作業を、今作成したユーザ名 STAGE に合わせて実施してください。
AWS S3 にあるファイルを参照する
AWS上の「aws-xx-test」というバケットに、予め customer1.csv とcustomer2.csv の 2つのファイルを置いたので、それを参照してみます。※バケット名は架空のものです
customer1.csv の URLを確認しておきます。今回は以下。
https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/customer1.csv
AWSのアクセス・キーIDと、シークレット・アクセス・キーを用意
AWS側で、S3を参照可能な適切なポリシー（今回はAmasonS3FullAccessで試しています）をアタッチしたグループに属するIAMユーザを作成し、認証情報 ＞ アクセスキー から、アクセスキーの作成を行います。
「アクセス・キーID」と、「シークレット・アクセス・キー」を後で使用します。
Autonomous Database -> AWS S3 のバケットにアクセスできるようクレデンシャルを登録
以下のSQLで、S3のバケットにアクセスするためのクレデンシャル（資格証明）を、Autonomous Databaseに格納します。
AWSのS3に相当する、OCI上のObject Storageにアクセスする場合と、構文は同じで、
- username には、AWSのアクセス・キーID
- password には、AWSのシークレット・アクセス・キー
をそれぞれ指定します。
※ここでつけたクレデンシャルの名前(例では AWSUSER_CRED)を、後で使用します。
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'AWSUSER_CRED', -- クレデンシャルの名前(任意の文字列)
username => 'AKI*****************', -- AWS アクセス・キーID
password => 'NAq***************************************' -- AWSシークレット・アクセス・キー
);
END;
/
dbms_cloud.list_objectsでバケット内のオブジェクトを表示
DBMS_CLOUD.LIST_OBJECTSは、指定したバケット内のオブジェクトを表示します。(URIはAWS上のバケットを指定しています)
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('AWSUSER_CRED', 'https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/');
今回はこのバケットに customer1.csv と customer2.csv の2ファイルを置いていますが、以下のように2ファイルが存在することが確認できました。
OBJECT_NAME BYTES CHECKSUM CREATED LAST_MODIFIED
------------- ----- -------------------------------- ------- --------------------
customer1.csv 3860 7dd71ac56ed4043b10fbf0c7bf9310c6 2020-04-18T08:44:19Z
customer2.csv 3860 5cf36f112206a8c55d6aa9c11aa5c7e4 2020-04-18T08:43:49Z
2行が選択されました。
ということで、冒頭の問いの「見えますか？」は「はい、見えます」となります。
ちょっと脱線 (SQL Developer Web のエラー)
SQL Developer Web を使って、上のSQLを実行時に、ORA-00907: missing right parenthesis （右カッコがありません）が出ることがあります。
文全体をマウスで選択してから（下図のように青く反転）、実行（緑の▶）を押下すると、エラー無く実行できます。
AWS S3 にあるCSVファイルからロードする
無事にファイルが参照できたので、Autonomous Databaseへのロードもやってみます。
##今回使用するファイルをAWS S3上に配置する
順番が前後しますが、ファイルを2つ用意し(customer1.csv, customer2.csv)、AWS S3 のバケットにアップロードします。
1行目がヘッダ、2行目以降にデータがカンマ区切りで書かれたCSVファイルを使います。
※ジェネレータツール：なんちゃって個人情報 を使用して生成・加工した、架空のデータです。
- 以下の内容(全31行)をテキストエディタにコピーし、customer1.csv としてローカルのPC上に保存。この後の手順の都合で、保存時に 文字コードを「UTF-8」、にするとスムーズです。
- customer1.csv を customer2.csv としてコピーし、C100 → C200 に全て置換する。文字コードは同じく「UTF-8」で。
※UTF8以外の場合(例:SJIS)、dbms_cloud.copy_data()時に文字コードの指定が必要になります。
(例：SJISの場合、characterset:JA16SJISTILDE など)
顧客ID,名前,ふりがな,メールアドレス,性別,生年月日,婚姻,都道府県,電話番号,職業
C10001,百瀬 丈史,ももせ たけし,momose_takeshi@example.com,男,1938/09/25,既婚,石川県,000-4101-2921,営業
C10002,津田 まみ,つだ まみ,tsuda_mami@example.com,女,1938/09/27,既婚,岡山県,000-2148-4441,建築
C10003,小寺 優,こでら ゆう,kodera_yuu@example.com,女,1938/10/8,既婚,京都府,000-5128-4436,事務
C10004,横川 遥,よこかわ はるか,yokokawa_haruka@example.com,女,1938/11/15,既婚,東京都,000-8588-7054,技能工
C10005,荻原 陽子,はぎわら ようこ,hagiwara_youko@example.com,女,1938/12/18,既婚,神奈川県,000-4449-6819,自営業
C10006,大橋 陽子,おおはし ようこ,oohashi_youko@example.com,女,1939/01/31,既婚,高知県,000-9016-2328,営業
C10007,菅沼 なつみ,すがぬま なつみ,suganuma_natsumi@example.com,女,1939/02/26,既婚,山形県,000-4527-6536,営業
C10008,外山 砂羽,そとやま さわ,sotoyama_sawa@example.com,女,1939/04/07,既婚,熊本県,000-9216-7220,登録なし
C10009,藤原 千佳子,ふじわら ちかこ,fujiwara_chikako@example.com,女,1939/05/04,既婚,東京都,000-3076-4029,登録なし
C10010,北村 広司,きたむら こうじ,kitamura_kouji@example.com,男,1939/05/20,既婚,広島県,000-6004-3842,自営業
C10011,武井 瑠璃亜,たけい るりあ,takei_ruria@example.com,女,1939/05/29,既婚,佐賀県,000-8683-3579,自営業
C10012,白川 美佳,しらかわ みか,shirakawa_mika@example.com,女,1939/06/06,既婚,宮城県,000-2328-4569,自営業
C10013,岩谷 ヒロ,いわたに ひろ,iwatani_hiro@example.com,男,1939/07/15,既婚,神奈川県,000-9885-4766,建築
C10014,佐川 雅彦,さがわ まさひこ,sagawa_masahiko@example.com,男,1939/08/21,既婚,兵庫県,000-2687-7932,営業
C10015,上条 聖陽,かみじょう まさあき,kamijou_masaaki@example.com,男,1939/09/19,既婚,富山県,000-1432-7322,教育
C10016,小松 まひる,こまつ まひる,komatsu_mahiru@example.com,女,1939/10/28,既婚,福岡県,000-8134-3716,サービス業
C10017,豊田 倫子,とよた のりこ,toyota_noriko@example.com,女,1939/12/14,既婚,広島県,000-2424-5328,事務
C10018,山野 ひとみ,やまの ひとみ,yamano_hitomi@example.com,女,1940/03/13,既婚,山梨県,000-4565-7877,クリエイター
C10019,及川 健,おいかわ けん,oikawa_ken@example.com,男,1940/03/14,既婚,埼玉県,000-1283-2386,営業
C10020,安藤 敦,あんどう あつし,anndou_atsushi@example.com,男,1940/04/28,既婚,山形県,000-7495-8133,技能工
C10021,竹中 徹,たけなか とおる,takenaka_tooru@example.com,男,1980/07/01,既婚,岐阜県,000-1554-3673,営業
C10022,湊 浩介,みなと こうすけ,minato_kousuke@example.com,男,1940/09/15,既婚,兵庫県,000-0371-0676,サービス業
C10023,緒形 夏空,おがた そら,ogata_sora@example.com,女,1980/09/16,既婚,滋賀県,000-7475-5465,教育
C10024,小泉 一恵,こいずみ かづえ,koizumi_kadue@example.com,女,1940/11/07,既婚,茨城県,000-8759-9978,士業
C10025,竹田 友香,たけだ ともか,takeda_tomoka@example.com,女,1980/12/23,既婚,秋田県,000-0724-9546,エンジニア
C10026,前田 鉄二,まえだ てつじ,maeda_tetsuji@example.com,男,1981/01/06,既婚,富山県,000-4564-5797,事務
C10027,安倍 美紀,あべ みき,abe_miki@example.com,女,1941/02/17,既婚,東京都,000-5651-8006,サービス業
C10028,松山 進,まつやま すすむ,matsuyama_susumu@example.com,男,1981/02/19,既婚,大分県,000-8141-9928,技能工
C10029,橘 佳乃,たちばな よしの,tachibana_yoshino@example.com,女,1941/02/24,既婚,静岡県,000-9000-1834,営業
C10030,関口 美帆,せきぐち みほ,sekiguchi_miho@example.com,女,1981/05/06,既婚,兵庫県,000-8345-2548,自営業
##ロード先の表となるCUSTOMERS表を作成
CSVファイルの内容を元に、以下のSQLでCUSTOMERS表を作成。
CREATE TABLE CUSTOMER (
CUSTOMER_ID VARCHAR2(26),
NAME VARCHAR2(128),
FURIGANA VARCHAR2(128),
EMAIL VARCHAR2(128),
GENDAR VARCHAR2(26),
BIRTHDAY DATE,
MARITAL_STATUS VARCHAR2(26),
PREFECTURE VARCHAR2(26),
MOBILE_NO VARCHAR2(26),
OCCUPATION VARCHAR2(26)
);
DBMS_CLOUD.COPY_DATAを使ってデータロード①
DBMS_CLOUD.COPY_DATAを使ってCSVファイルの内容をAutonomous Database上の既存のテーブルにコピーします。CSVファイルのデータをそのままコピーしたいときに使用します。
ロード先のテーブル名(CUSTOMER)、クレデンシャル名(AWSUSER_CRED)、CSVファイルのURLパス、フォーマット(CSVであること等)をそれぞれ指定。まずは特定の1つのファイルを指定しています。
https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/customer1.csv
※file_uri_list => は、CSVアップロード時に確認したご自身の環境のURIに置き換えてください。
begin
dbms_cloud.copy_data(
table_name => 'CUSTOMER',
credential_name => 'AWSUSER_CRED',
file_uri_list => 'https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/customer1.csv',
format => json_object('type' value 'csv','ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY/MM/DD', 'skipheaders' value '1')
);
end;
/
成功したら以下の表示です。
PL/SQL procedure successfully completed.
無事にロード出来ていることを確認します。
select * from customer;
select count(1) from customer; -- 30行ロードできているはず
DBMS_CLOUD.COPY_DATAを使ってデータロード②
今度は、URIで customer1.csv 部分を customer*.csv に変更し、複数のCSVファイルを指定します。
https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/customer*.csv
※実行前に、CUSTOMER表をTRUNCATEして空にしてから実行しています。
-- customer表をtruncate (先にロードしたレコードを全て削除）
truncate table customer;
-- customer*.csv で、複数ファイルを指定
begin
dbms_cloud.copy_data(
table_name => 'CUSTOMER',
credential_name => 'AWSUSER_CRED',
file_uri_list => 'https://aws-xx-test.s3-ap-northeast-1.amazonaws.com/customer*.csv',
format => json_object('type' value 'csv','ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY/MM/DD', 'skipheaders' value '1')
);
end;
/
ロードに成功したら、今度は、60行のデータが格納されているはずです。
select * from customer where name like '百瀬%';
で検索すると、C10001, C20001 の2行がHitします。2つのCSVともロードできていることがわかります。
今回は簡単のため dbms_cloud.copy_data で、CSVファイルをそのままロードしました。(OCI上のObject Storage上にファイルを置いた場合と比較して)ネットワーク的に遠いところにデータがある状況なのであまり複雑なことはしないほうがよいかなと思いますが、ほかにも、外部表を使って簡単な変換をかけながらロードする方法もあります。詳細はこちらに。
#参考