RDS for SQL ServerからS3バケットにアクセスできるようになりました
こんにちは。データアナリティクス事業本部の松村です。
2ヶ月以上前の話ですが、RDS for SQL ServerからS3バケットにアクセスができるようになりましたのでご紹介します。この機能は公式ドキュメントではS3との統合という表現で呼ばれていますので、本記事でもそれに倣い「S3統合」と表記します。
概要
S3統合は、RDSでS3バケットを直接マウント、またはそれに類する行為を行うものではありません。RDSのローカルドライブを介してS3バケットからダウンロードまたはS3バケットへのアップロードができる、というものです。
使い道ですが、S3バケットからのダウンロードについては、これを利用してローカルドライブからのBULK INSERTができるようになります。従来でもEC2などの他のコンピュータから、bcpユーティリティを使ってデータの一括ロードができましたが、S3統合によりRDSとS3だけで完結できます。
S3バケットへのアップロードについては、RDS上でSQL Server Integration Services(SSIS)を実行していない限り出番はない、と思います。というのも、SSISの助けなくしてRDS上のデータやメタデータをそのローカルドライブに出力することはできないからです。普通のSQL Serverであれば、OPENROWSET関数でMicrosoft.ACE.OLEDBドライバを使ってローカルファイルをテーブルとして開いてそこにINSERT、とか、xp_cmdshellストアドプロシージャを使ってbcpやsqlcmdでエクスポート、とかいった強引な手段で何とかできますが、RDSではそのどちらも行うことができません。
ちなみにRDS for SQL Server上でSSISは追加料金なしで利用できますが、StandardもしくはEnterpriseエディションである必要があります。
今回は、BULK INSERTを実際にやってみます。SSISでの活用はまたの機会に。
準備
S3統合を使用するためには、IAM、およびSQL Server内部のログイン／ユーザーの権限設定が必要ですので、まずこれらを行います。
IAMの設定
IAMの設定については公式のユーザーガイドに記載があります。こちらにはS3統合の制限事項も記載されていますので、ひととおり目を通しておきましょう。
Amazon S3 で Amazon RDS を SQL Server DB インスタンス に統合
ここに書かれていない注意事項として、S3バケットとの窓口になるD:\S3フォルダはDBインスタンスストレージ上に存在する、という点が挙げられます。一時的にでもサイズの大きいファイルを置く必要があるのであれば、空き容量に注意しましょう。
IAMポリシーの作成
S3バケットにアクセスするためのIAMポリシーを作成します。必要なアクションは、S3バケットに対してダウンロード／アップロードのどちらが必要かによって異なります。今回はBULK INSERTの元データの取得が目的ですので、ダウンロードに必要なものだけを指定しておきます。
アクションによってリソースの対象が異なるので、マネジメントコンソールを使うならこんなふうに分けて定義します。
ListAllMyBucketsはリソースの指定がありません。
ListBucket、GetBucketACL、GetBucketLocationはバケットを指定します。
GetObjectはオブジェクトまで指定します。が、ここでは結局ワイルドカードでバケット配下のオブジェクト全てに対する許可を与えています。
IAMロールの作成
IAMロールを作成し、上記のIAMポリシーをアタッチします。
IAMロールのアタッチ
RDSのインスタンスに、作成したIAMロールをアタッチします。
SQL Serverのログイン／ユーザーの権限設定
ユーザーガイドには特に記載がありませんが、S3バケットからファイルをダウンロードするにも、BULK INSERTを実行するにも、SQL Serverのログインやユーザーに適切な権限が必要です。これらを設定します。
ストアドプロシージャの実行権限とテーブル値関数の選択権限
S3統合の機能は、すべてストアドプロシージャまたはテーブル値関数が実行インターフェースになっています。具体的にはmsdbデータベースのdboスキーマにある以下のオブジェクトが該当しますので、ユーザーにこれらに対する実行（EXECUTE）権限または選択（SELECT）権限を付与します。
それぞれが必要な場面、および使い方については、後述するBULK INSERまでの一連の操作の中で説明します。
| 名称 | 種類 | 説明 |
|---|---|---|
|
|
ストアドプロシージャ | S3バケットからファイルをダウンロードします。 |
|
|
ストアドプロシージャ | S3バケットにファイルをアップロードします。 |
|
|
ストアドプロシージャ |
|
|
|
ストアドプロシージャ |
|
|
|
ストアドプロシージャ | 実行中のストアドプロシージャをキャンセルします。 |
|
|
テーブル値関数 | 実行したストアドプロシージャの進捗状況を表示します。 |
|
|
テーブル値関数 |
|
権限付与はSQL Server Management StdioによるGUI操作で行うこともできますが、紙面の都合上SQLのみ紹介します。IAMの設定にはAWS CLIではなくマネジメントコンソールを使いましたので、ちょっとちぐはぐですが。
SQL Serverのログイン／ユーザーの権限設定に関する本節のSQLは、すべてRDSのマスターユーザーもしくは同等の権限を持ったユーザーで実行してください。
まずはストアドプロシージャが存在するmsdbデータベースに、アプリケーションから接続する際のログインに対応するユーザーを作成します。デフォルトスキーマはやはりストアドプロシージャが存在するdboにしておきます。
下記SQLのuser_nameとlogin_nameは、実際のユーザー名とログイン名に置き換えてください。
USE msdb; CREATE USER user_name FOR LOGIN login_name WITH DEFAULT_SCHEMA = dbo;
作成したユーザーにストアドプロシージャの実行（EXECUTE）権限、およびテーブル値関数の選択（SELECT）権限を付与します。
use msdb; GRANT EXECUTE ON dbo.rds_download_from_s3 TO user_name; GRANT EXECUTE ON dbo.rds_upload_to_s3 TO user_name; GRANT EXECUTE ON dbo.rds_gather_file_details TO user_name; GRANT EXECUTE ON dbo.rds_delete_from_filesystem TO user_name; GRANT EXECUTE ON dbo.rds_cancel_task TO user_name; GRANT SELECT ON dbo.rds_fn_list_file_details TO user_name; GRANT SELECT ON dbo.rds_fn_task_status TO user_name;
BULK INSERTの実行権限
BULK INSERTを実行するためには、ログインに「一括操作の管理」（ADMINISTER BULK OPERATIONS）権限が必要ですので、これを付与します。この権限はbulkadminサーバーロールに含まれるため、通常のSQL Serverではこのロールを付与することも多いですが、RDSでは付与することができません。
use master; GRANT ADMINISTER BULK OPERATIONS TO login_name;
S3統合機能によるBULK INSERT
では実際にS3統合機能を使ってBULK INSERTを実行してみましょう。
S3バケットからダウンロードする
S3バケットにBULK INSERT用のデータファイルが置いてあります。これをダウンロードします。
OrderDetails.csv中身はこのようになっています。ヘッダを除いて2,155件のデータがあります。
OrderID,ProductID,UnitPrice,Quantity,Discount 10248,11,14,12,0 10248,42,9.8,10,0 10248,72,34.8,5,0 10249,14,18.6,9,0 10249,51,42.4,40,0 ...
S3バケットからのダウンロードにはmsdb.dbo.rds_download_from_s3ストアドプロシージャを使います。これは以下3つのパラメータを指定します。
@s3_arn_of_file- ダウンロードするファイルのARNです。
@rds_file_path- RDS上の保存先です。必ず
D:\S3フォルダ配下でなくてはなりません。サブフォルダを含めた場合、ダウンロード時にそれがなければ生成されます。 @overwrite_file- RDS上に既に同名のファイルが存在するときに、上書きするかどうかを表すフラグです。上書きする場合は1、しない場合は0です。
EXEC msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::s3-bucket-name/mssql/OrderDetails.csv',
@rds_file_path='D:\S3\csv\OrderDetails.csv',
@overwrite_file=1;
実行結果はこうなります。
task_id task_type lifecycle created_at last_updated S3_object_arn overwrite_file task_progress task_info filepath ------- ---------------- --------- ----------------------- ----------------------- -------------------------------------------------- -------------- ------------- --------- -------------------------- 1 DOWNLOAD_FROM_S3 CREATED 2020-08-08 02:44:31.810 2020-08-08 02:44:31.810 arn:aws:s3:::s3-bucket-name/mssql/OrderDetails.csv 1 0 NULL D:\S3\csv\OrderDetails.csv Task created successfully. Task Id: 1
結果はすぐに返ってきますが、この時点では処理が実行キューに追加されただけで、ダウンロードは完了していません。実際のダウンロードはバックグラウンドで実行されるので、msdb.dbo.rds_fn_task_statusテーブル値関数で、処理の進捗状況をチェックする必要があります。この関数には2つパラメータが必要で、1つめには常にNULL、2つめには先のmsdb.dbo.rds_download_from_s3の戻り値のtask_idをセットします。
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,1);
実行結果はこうなります。lifecycleフィールドの値SUCCESSがダウンロードの正常終了を表しています。
task_id task_type database_name % complete duration(mins) lifecycle task_info last_updated created_at S3_object_arn overwrite_s3_backup_file KMS_master_key_arn filepath overwrite_file task_metadata ------- ---------------- -------------- ---------- -------------- --------- -------------------------------------------------------------------------------------------------- --------------------------- --------------------------- -------------------------------------------------- ------------------------ ------------------ -------------------------- -------------- ------------- 1 DOWNLOAD_FROM_S3 Not Applicable 100 2 SUCCESS [2020-08-08 02:45:22.560] Download started.\n[2020-08-08 02:46:22.237] Task finished successfully. 2020-08-08 02:46:22.2366667 2020-08-08 02:44:31.8100000 arn:aws:s3:::s3-bucket-name/mssql/OrderDetails.csv 0 NULL D:\S3\csv\OrderDetails.csv 1 NULL
D:\S3フォルダ配下のファイル一覧を取得する
D:\S3フォルダ配下に存在するファイルの一覧は、msdb.dbo.rds_gather_file_detailsストアドプロシージャとmsdb.dbo.rds_fn_list_file_detailsテーブル値関数を使用して取得します。
まずはmsdb.dbo.rds_gather_file_detailsでD:\S3フォルダ配下をスキャンします。パラメータはありません。
EXEC msdb.dbo.rds_gather_file_details;
このストアドプロシージャはファイルの一覧を返しません。msdb.dbo.rds_download_from_s3と同様に、スキャン処理を実行キューに追加するだけです。したがって、実行するとすぐにこのような結果が返ってきます。
task_id task_type lifecycle created_at ------- ------------------ --------- ----------------------- 2 LIST_FILES_ON_DISK CREATED 2020-08-08 02:51:15.570 Task created successfully. Task Id: 2 To get file details (path/size/last modified date), please run 'exec msdb.dbo.rds_fn_list_file_details(2)'
やはりmsdb.dbo.rds_fn_task_statusテーブル値関数で、処理の進捗状況をチェックする必要があります。
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,2);
lifecycleフィールドの値がSUCCESSならスキャンが完了しています。
task_id task_type database_name % complete duration(mins) lifecycle task_info last_updated created_at S3_object_arn overwrite_s3_backup_file KMS_master_key_arn filepath overwrite_file task_metadata ------- ------------------ -------------- ---------- -------------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- --------------------------- ------------- ------------------------ ------------------ -------- -------------- ------------- 2 LIST_FILES_ON_DISK Not Applicable 100 1 SUCCESS <FileList><File><FilePath>D:\S3\csv\</FilePath><SizeInBytes>0</SizeInBytes><LastModifiedUtc>2020-08-08 02:45:22.691</LastModifiedUtc><IsDirectory>true</IsDirectory></File><File><FilePath>D:\S3\csv\OrderDetails.csv</FilePath><SizeInBytes>42778</SizeInBytes> 2020-08-08 02:51:22.4500000 2020-08-08 02:51:15.5700000 NULL 0 NULL NULL 0 NULL
D:\S3フォルダ配下のスキャンが完了したら、msdb.dbo.rds_fn_list_file_detailsテーブル値関数を使用してファイルの一覧を取得します。パラメータとしてスキャン処理のtask_idを指定します。
SELECT * FROM msdb.dbo.rds_fn_list_file_details(2);
実行するとこのようなリストが取得できます。OrderDetails.csvがダウンロードできていることがわかります。
filepath size_in_bytes last_modified_utc is_directory -------------------------- ------------- --------------------------- ------------ D:\S3\csv\ 0 2020-08-08 02:45:22.6910000 true D:\S3\csv\OrderDetails.csv 42778 2020-08-08 02:45:22.6900000 false
BULK INSERTを実行する
それでは、ダウンロードしたデータファイルを使ってBULK INSERTしてみましょう。
BULK INSERT Northwind.dbo.[Order Details]
FROM 'D:\S3\csv\OrderDetails.csv'
WITH
(
CHECK_CONSTRAINTS,
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
FIRSTROW = 2
);
(2155 行処理されました)
BULK INSERTが成功しました！
D:\S3フォルダ配下のファイルを削除する
途中にも書きましたが、D:\S3フォルダ配下のファイルはDBインスタンスストレージを消費しますので、必要がなくなったら速やかに削除しておきましょう。ファイルの削除にはmsdb.dbo.rds_delete_from_filesystemストアドプロシージャを使用します。これは以下2つのパラメータを指定します。
@rds_file_path- 削除するファイルまたはフォルダのパスです。フォルダを指定した場合、配下のファイルやフォルダもすべて削除されます。フォルダを削除する場合は、末尾を
\で終わらせないと削除が失敗します。 @force_delete- フォルダの削除をするかどうかを表すフラグです。
@rds_file_pathにフォルダを指定したときには1を設定します。
今回はD:\S3\csvフォルダを丸ごと削除します。
EXEC msdb.dbo.rds_delete_from_filesystem
@rds_file_path='D:\S3\csv\',
@force_delete=1;
削除も実行キューに追加するだけです。実行するとすぐにこのような結果が返ってきます。
task_id task_type lifecycle created_at last_updated S3_object_arn overwrite_file task_progress task_info filepath ------- -------------------- --------- ----------------------- ----------------------- ------------- -------------- ------------- --------- ---------- 3 DELETE_FILES_ON_DISK CREATED 2020-08-08 03:23:12.570 2020-08-08 03:23:12.570 NULL 1 0 NULL D:\S3\csv\ Task created successfully. Task Id: 3
やはりmsdb.dbo.rds_fn_task_statusテーブル値関数で、処理の進捗状況をチェックする必要があります。
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,3);
lifecycleフィールドの値がSUCCESSなら削除が完了しています。
task_id task_type database_name % complete duration(mins) lifecycle task_info last_updated created_at S3_object_arn overwrite_s3_backup_file KMS_master_key_arn filepath overwrite_file task_metadata ------- -------------------- -------------- ---------- -------------- --------- ----------------------------------------------------------------------------------------------------- --------------------------- --------------------------- ------------- ------------------------ ------------------ ---------- -------------- ------------- 3 DELETE_FILES_ON_DISK Not Applicable 100 2 SUCCESS [2020-08-08 03:30:22.080] Delete Task started.\n[2020-08-08 03:31:22.243] Task finished successfully. 2020-08-08 03:31:22.2433333 2020-08-08 03:29:56.8933333 NULL 0 NULL D:\S3\csv\ 1 NULL
実際にファイルが削除されたことを確認します。まずmsdb.dbo.rds_gather_file_detailsストアドプロシージャを実行します。
EXEC msdb.dbo.rds_gather_file_details;
task_id task_type lifecycle created_at ------- ------------------ --------- ----------------------- 4 LIST_FILES_ON_DISK CREATED 2020-08-08 03:45:57.870 Task created successfully. Task Id: 4 To get file details (path/size/last modified date), please run 'exec msdb.dbo.rds_fn_list_file_details(4)'
msdb.dbo.rds_fn_task_statusストアドプロシージャでmsdb.dbo.rds_gather_file_detailsの完了を確認します。
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,4);
task_id task_type database_name % complete duration(mins) lifecycle task_info last_updated created_at S3_object_arn overwrite_s3_backup_file KMS_master_key_arn filepath overwrite_file task_metadata ------- ------------------ -------------- ---------- -------------- --------- ------------ --------------------------- --------------------------- ------------- ------------------------ ------------------ -------- -------------- ------------- 4 LIST_FILES_ON_DISK Not Applicable 100 1 SUCCESS <FileList /> 2020-08-08 03:46:22.1400000 2020-08-08 03:45:57.8700000 NULL 0 NULL NULL 0 NULL
ファイルのリストを表示します。
SELECT * FROM msdb.dbo.rds_fn_list_file_details(4);
ファイルが削除されていることを確認できました。
filepath size_in_bytes last_modified_utc is_directory -------- ------------- ----------------- ------------
【補遺】ここまでで使用しなかったストアドプロシージャ
S3統合機能のストアドプロシージャのうち、ここまでの操作で使用しなかったものを簡単に説明します。
rds_upload_to_s3
D:\S3フォルダ配下のファイルをS3バケットにアップロードするためには、msdb.dbo.rds_upload_to_s3ストアドプロシージャを使用します。これは以下3つのパラメータを指定します。
@s3_arn_of_file- アップロードするファイルが、このARNに従ってS3バケット上に作成されます。
@rds_file_path- アップロードするファイルのRDS上のパスです。必ず
D:\S3フォルダ配下でなくてはなりません。 @overwrite_file- S3バケット上に既に同じキーのファイルが存在するときに、上書きするかどうかを表すフラグです。上書きする場合は1、しない場合は0です。
このストアドプロシージャも非同期で実行されますので、msdb.dbo.rds_fn_task_statusテーブル値関数で進捗状況をチェックします。
rds_cancel_task
実行したS3統合のストアドプロシージャをキャンセルするためには、msdb.dbo.rds_cancel_taskストアドプロシージャを使用します。これは以下1つのパラメータを指定します。
@task_id- キャンセル対象のストアドプロシージャの
task_idです。
最後に
RDS for SQL ServerのS3統合機能と、それを用いたBULK INSERTを紹介しました。S3統合機能を使用しなくても、EC2など他のコンピュータからbcpユーティリティでファイルの一括ロードを行うことができますが、RDSとS3だけで完結させた方が管理負荷が少なくて済みます。また、bcpとは異なり、BULK INSERTはトランザクション内部で実行できるという利点もあります。
今までBULK INSERTを諦めていたり、bcpで代替したりしていた場合は、ぜひ検討してみてください。
