Amazon Redshift DB開発者ガイド – データのアンロード処理

Amazon Redshift DB開発者ガイド

#データベース

#Amazon Redshift

#AWS

しんや

2013.08.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データベーステーブルからAmazon S3の一連のファイルにデータをアンロードする為に、SELECT文とUNLOADコマンドを使用する事が出来ます。それをロードする為に使われたデータフォーマット形式の如何に関わらず、区切り文字形式若しくは固定幅形式のいずれかでテキストデータをアンロードする事が出来ます。また、圧縮されたGZIPファイルを作成するかどうかも指定出来ます。

一時的なセキュリティ証明書を使用して、Amazon S3バケットへのアクセスユーザを制限する事も出来ます。

重要：
Amazon Redshiftでは、出力ファイルを書き込むAmazon S3バケットは
クラスタと同じリージョン内に作成する必要があります。

Amazon S3へのアンロード処理

Amazon RedshiftのSELECT文の結果は、ノードスライス1つあたり1つ若しくはそれ以上ファイルのセット全体で並列データのリロード処理を簡素化する為に分割されます。

Amazon Redshiftがサポートする他のSELECT文内部のLIMIT文を使用しているSELECT文文を除いて、UNLOADコマンド内部では、どんなSELECT文でも使用出来ます。例えば、特定の列を含む、または復数のテーブルを結合するWHERE句を使用しているSELECT文を使用出来たりします。もしクエリに引用符が含まれている場合(例えばリテラル値を囲む、等)、クエリテキスト(\')でそれらをエスケープする必要があります。詳細については、SELECTコマンドをご参照ください。

LIMIT句を使用する方法の詳細については、UNLOADコマンドの使用上の注意を参照してください。

unload ('select * from venue')   
to 's3://mybucket/tickit/venue_' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>';

一時的なセキュリティ証明書を使用して、データへアクセスするユーザーを制限する事が出来ます。証明書の寿命は短いですし、証明書が期限切れとなった後は再利用は出来ませんので、一時的なセキュリティ資格は強化されたセキュリティを提供する事となります。証明書の有効期限が切れるまでの間だけ、これらの一時的なセキュリティ認証情報を持つユーザーはリソースにアクセスする事が出来ます。詳細については、Temporary Security Credentialsをご参照ください。一時的なセキュリティ認証情報を使用してデータをアンロードするには、以下の構文を使用します。

unload ('select * from venue')   
to 's3://mybucket/tickit/venue_'
credentials 'aws_access_key_id=<temporary-access-key-id>;aws_secret_access_key=<temporary-secret-access-key>;token=<temporary-token>' 
[<options>];

重要：
一時的なセキュリティ認証情報は、UNLOAD文実行の全期間に渡って有効である必要があります。
一時的なセキュリティ認証情報は、ロード処理中に有効期限が切れる場合、アンロード処理は失敗し、
トランザクションはロールバックされます。
例えば、一時的なセキュリティ認証情報の有効期限が15分、アンロード処理の処理時間が1時間掛かる場合、
アンロード処理は完了する前に時間切れで失敗するでしょう。

データのUNLOAD処理完了後、ファイル出力先に指定したAmazon S3バケットに移動し、正常にアンロードされた事を確認します。そこでは数字のゼロから始まる、スライス毎に1つの番号が割り振られたファイルを見ることになるでしょう。例えば以下のように。

venue_0000_part_00
venue_0001_part_00
venue_0002_part_00
venue_0003_part_00

UNLOAD処理完了後にAmazon S3のリスト操作を呼び出す事により、Amazon S3に書き込まれたファイルのリストを取得する事が出来ます。Amazon S3のリスト操作は最終的には一貫性を保ちますが、(S3リスト操作を)呼び出すタイミングによってはリストは不完全な状態で表示されるかもしれません。もし直ぐにちゃんとしたリスト情報が欲しいと言う場合はSTL_UNLOAD_LOGを照会します。

以下のクエリは、クエリID2320にてUNLOAD処理によって作成されたファイルのパス名を返しています。

select query, substring(path,0,40) as path
from stl_unload_log
where query=2320
order by path;

結果は以下のようになります。

 query |             path
-------+--------------------------------------
  2320 | s3://my-bucket/venue0000_part_00
  2320 | s3://my-bucket/venue0001_part_00
  2320 | s3://my-bucket/venue0002_part_00
  2320 | s3://my-bucket/venue0003_part_00
(4 rows)

もしデータ量が非常に大きい場合、Amazon Redshiftはスライス毎に復数のファイルに分割を行うかも知れません。例えば以下のように。

0000_part_00
0000_part_01
0000_part_02
0001_part_00
0001_part_01
0001_part_02
...

以下のUNLOADコマンドでは、SELECT文の中で引用符で囲まれた文字を含んでいます。その為、引用符の部分がエスケープ(=\'OH\' ')されています。

unload ('select venuename, venuecity from venue where venuestate=\'OH\' ')
to 's3://mybucket/tickit/venue/ '
credentials
'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>';

S3のパス文字列に接頭辞を含める場合は、UNLOAD処理を行う際にファイル名に対してその接頭辞を使用します。

unload ('select venuename, venuecity from venue where venuestate=\'OH\' ')
to 's3://mybucket/tickit/venue/OH_'
credentials
'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>';

結果には接頭辞(OH)が含まれるようになります。

OH_0000_part_00
OH_0001_part_00
OH_0002_part_00
OH_0003_part_00

デフォルトでは、UNLOAD処理は対象バケット内の既存ファイルを上書きするよりも失敗しなくなります。既存ファイルを上書きする際は、ALLOWOVERWRITEオプションを使用します。

unload ('select * from venue') to 's3://mybucket/venue_pipe_' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
allowoverwrite;

暗号化ファイルのアンロード処理

Amazon S3では、UNLOADコマンドの暗号化オプションを使用して暗号化データファイルを作成する事が出来ます。UNLOADは、Amazon S3クライアント側で使用するものと同じエンベロープ暗号化プロセスを使用しています。この処理の後、暗号化されたファイルを読み込む為に暗号化オプションを指定して、COPYコマンドを使用する事が出来ます。

プロセスは、以下のように動作します。

あなたがプライベート暗号化キー、またはマスター対称キーとして使用する、base64でエンコードされた256ビットAWSキーを作成します。
マスター対称キーとENCRYPTEDオプションを含めてUNLOADコマンドを発行します。
UNLOADはワンタイム利用対称鍵(エンベロープ対称鍵と呼ばれます)とデータを暗号化の際に使用する初期化ベクトル(IV:Initialization Vector)を生成します。
UNLOADはマスター対称キーを使用してエンベロープ対称鍵を暗号化します。
UNLOADはAmazon S3上の暗号化されたデータファイル及び、オブジェクトメタデータとして暗号化されたエンベロープキーとIVを格納します。暗号化されたエンベロープキーはオブジェクトメタデータのx-amz-meta-x-amz-keyとして、IVはオブジェクトメタデータのx-amz-meta-x-amz-ivとして格納されています。

エンベロープの暗号化プロセスの詳細については、記事：Client-Side Data Encryption with the AWS SDK for Java and Amazon S3をご参照ください。

暗号化データファイルをアンロードする為に、マスターキー値をアンロード資格認証情報に追加し、ENCRYPTEDオプションを含めます。

unload ('select venuename, venuecity from venue')
to 's3://mybucket/encrypted/venue_' credentials
'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>;master_symmetric_key=<master_key>' encrypted;

GZIP圧縮され暗号化されたデータファイルをアンロードするには、ENCRYPTEDオプションと併せてGZIPオプションを含めます。

unload ('select venuename, venuecity from venue')
to 's3://mybucket/encrypted/venue_' credentials
'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>;master_symmetric_key=<master_key>' encrypted gzip;

暗号化されたデータファイルをロードするためには、暗号化オプションを利用した時に使っていたものと同じマスターキー値を認証情報に含めます。

copy venue from 's3://mybucket/encrypted/venue_' credentials
'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>;master_symmetric_key=<master_key>' encrypted;

区切り文字 or 固定幅フォーマットデータのアンロード処理

Redshiftでは、区切り文字形式又は固定幅フォーマットでデータをアンロードする事が出来ます。デフォルトの出力はパイプ文字(|)となっています。

以下の例では、区切り文字としてカンマを使用しています。

unload ('select * from venue')
to 's3://mybucket/tickit/venue/comma' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
delimiter ',';

結果は以下のようになります。

20,Air Canada Centre,Toronto,ON,0
60,Rexall Place,Edmonton,AB,0
100,U.S. Cellular Field,Chicago,IL,40615
200,Al Hirschfeld Theatre,New York City,NY,0
240,San Jose Repertory Theatre,San Jose,CA,0
300,Kennedy Center Opera House,Washington,DC,0
...

同じ結果をタブ文字区切りでアンロードするには、以下のコマンドを発行します。

unload ('select * from venue') 
to 's3://mybucket/tickit/venue/tab' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
delimiter as '\t';

また、FIXEDWIDTH仕様を用いる事も出来ます。この仕様では、各テーブルの列と列の幅(文字数)の為の識別子で構成されています。UNLOADコマンドはデータを切り捨てるということはせず、失敗します。なので、その列で想定し得る最大文字数の桁数を指定するようにします。固定幅のデータをアンロードすると、結果出力が区切り文字を含まない事を除けば、アンロード区切りデータと同じように動作します。例えば以下のように、

unload ('select * from venue')
to 's3://mybucket/tickit/venue/fw' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
fixedwidth '0:3,1:100,2:30,3:2,4:6';

固定幅区切りの結果は以下のようになります。

20 Air Canada Centre         Toronto      ON0
60 Rexall Place              Edmonton     AB0
100U.S. Cellular Field       Chicago      IL40615
200Al Hirschfeld Theatre     New York CityNY0
240San Jose Repertory TheatreSan Jose     CA0
300Kennedy Center Opera HouseWashington   DC0

FIXEDWIDTHの詳細仕様については、COPYコマンドをご参照ください。

アンロード処理済データのリロード

アンロード操作の結果をリロードする為に、COPYコマンドを使用する事が出来ます。以下の例は、VENUEテーブルがアンロードされ、切り捨てられ、リロードされているシンプルなケースを示しています。

unload  ('select * from venue order by venueid')
to 's3://mybucket/tickit/venue/reload_' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' 
delimiter '|';

truncate venue;

copy venue from 's3://mybucket/tickit/venue/reload_' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
delimiter '|';

リロード後、VENUEテーブルは以下のようになります。

select * from venue order by venueid limit 5;

 venueid |         venuename         |  venuecity  | venuestate | venueseats
---------+---------------------------+-------------+------------+-----------
       1 | Toyota Park               | Bridgeview  | IL         |          0
       2 | Columbus Crew Stadium     | Columbus    | OH         |          0
       3 | RFK Stadium               | Washington  | DC         |          0
       4 | CommunityAmerica Ballpark | Kansas City | KS         |          0
       5 | Gillette Stadium          | Foxborough  | MA         |      68756
(5 rows)