メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012-12-01)

UNLOAD の例

パイプ区切りファイルへの VENUE のアンロード (デフォルト区切り文字)

注記

次の例では読みやすくするため、改行しています。credentials-args 文字列には改行やスペースを含めないでください。

次の例は、VENUE テーブルをアンロードし、データを s3://mybucket/unload/ に書き込みます。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

デフォルトでは、UNLOAD はスライスごとに 1 つ以上のファイルを書き込みます。ノードごとに 2 つのスライスを装備した 2 ノードクラスターを想定すると、前の例では mybucket に以下のファイルが作成されます。

Copy to clipboard
unload/0000_part_00 unload/0001_part_00 unload/0002_part_00 unload/0003_part_00

出力ファイルの違いをわかりやすくするため、ロケーションにプレフィックスを含めることができます。次の例は、VENUE テーブルをアンロードし、データを s3://mybucket/venue_pipe_ に書き込みます。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

結果として、unload フォルダに以下の 4 つのファイルが生成されます。ここでも 4 つのスライスを想定しています。

Copy to clipboard
venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

VENUE の逐次アンロード

逐次アンロードを行うには、PARALLEL を OFF にします。これにより、UNLOAD が一度に書き込むファイルの数は 1 つになります (ファイルにつき最大で 6.2 GB まで)。

次の例は、VENUE テーブルをアンロードし、データを s3://mybucket/unload/ に逐次書き込みます。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/unload/venue_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off;

結果として、venue_serial_000 という名前のファイルが 1 つ生成されます。

アンロードするデータが 6.2 GB より大きい場合、UNLOAD は 6.2 GB のデータセグメントごとに新しいファイルを作成します。次の例は、LINEORDER テーブルをアンロードし、データを s3://mybucket/unload/ に逐次書き込みます。

Copy to clipboard
unload ('select * from lineorder') to 's3://mybucket/unload/lineorder_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off gzip;

結果として、以下の一連のファイルが生成されます。

Copy to clipboard
lineorder_serial_0000.gz lineorder_serial_0001.gz lineorder_serial_0002.gz lineorder_serial_0003.gz

出力ファイルの違いをわかりやすくするため、ロケーションにプレフィックスを含めることができます。次の例は、VENUE テーブルをアンロードし、データを s3://mybucket/venue_pipe_ に書き込みます。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/unload/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

結果として、unload フォルダに以下の 4 つのファイルが生成されます。ここでも 4 つのスライスを想定しています。

Copy to clipboard
venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

マニフェストファイルを使用した VENUE のアンロード

マニフェストファイルを作成するには、MANIFEST オプションを指定します。次の例では、VENUE テーブルをアンロードし、データファイルとともにマニフェストファイルを s3://mybucket/venue_pipe_ に書き込みます。

重要

MANIFEST オプションを指定してファイルをアンロードする場合、そのファイルをロードするときに COPY コマンドで MANIFEST オプションを使用する必要があります。同じプレフィックスを使用してファイルをロードし、MANIFEST オプションを指定しない場合、COPY ではマニフェストファイルがデータファイルであると推測され、操作が失敗します。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

結果は次の 5 ファイルです。

Copy to clipboard
s3://mybucket/venue_pipe_0000_part_00 s3://mybucket/venue_pipe_0001_part_00 s3://mybucket/venue_pipe_0002_part_00 s3://mybucket/venue_pipe_0003_part_00 s3://mybucket/venue_pipe_manifest

アンロードファイルからの VENUE のロード

一連のアンロードファイルからテーブルをロードするには、COPY コマンドを使用して単純にプロセスを逆順で実行します。次の例では、新しいテーブル LOADVENUE を作成し、前の例で作成したデータファイルからテーブルをロードします。

Copy to clipboard
create table loadvenue (like venue); copy loadvenue from 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

アンロードファイルを使用してマニフェストファイルを作成するときに MANIFEST オプションを使用した場合、COPY コマンドで MANIFEST オプションを指定することで、同じマニフェストファイルを使用してデータをロードできます。次の例では、マニフェストファイルを使用してデータをロードします。

Copy to clipboard
copy loadvenue from 's3://mybucket/venue_pipe_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

暗号化ファイルへの VENUE のアンロード

次の例では、VENUE テーブルを一連の暗号化ファイルにアンロードします。ENCRYPTED オプションでマニフェストファイルを指定する場合、マニフェストファイルは暗号化されません。詳細については、「暗号化されたデータファイルをアンロードする」を参照してください。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/venue_encrypt_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' manifest encrypted;

暗号化ファイルからの VENUE のロード

ENCRYPT オプションを指定した UNLOAD を使って作成した一連のファイルからテーブルをロードするには、ENCRYPTED オプションを指定した COPY コマンドを使ってプロセスを逆にし、UNLOAD コマンドで使用したものと同じマスター対称キーを指定します。次の例では、前の例で作成した暗号化されたデータファイルから LOADVENUE テーブルをロードします。

Copy to clipboard
create table loadvenue (like venue); copy loadvenue from 's3://mybucket/venue_encrypt_manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' manifest encrypted;

タブ区切りファイルへの VENUE データのアンロード

Copy to clipboard
unload ('select venueid, venuename, venueseats from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t';

出力データファイルは次のようになります。

Copy to clipboard
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 ...

一時的認証情報を使った VENUE のアンロード

一時的セキュリティ認証情報を使い、データにアクセスするユーザーを制限できます。一時的セキュリティ認証情報はセキュリティを強化します。使用期限が短く、期限が切れた後は再利用できないためです。このような一時的セキュリティ認証情報を与えられたユーザーは、その認証情報の有効期限の間だけリソースにアクセスできます。詳細については、COPY コマンドの「使用に関する注意事項」の「一時的セキュリティ認証情報」を参照してください。

次の例では、一時認証情報を使って、LISTING テーブルをアンロードします。

Copy to clipboard
unload ('select venueid, venuename, venueseats from venue') to 's3://mybucket/venue_tab' credentials 'aws_access_key_id=<temporary-access-key-id>;aws_secret_access_key=<temporary-secret-access-key>;token=<temporary-token>' delimiter as '\t';

重要

一時的セキュリティ認証情報は、UNLOAD ステートメントの期間全体で有効にする必要があります。一時的セキュリティ認証情報の期限がロードプロセス中に切れた場合、UNLOAD は失敗し、処理はロールバックされます。たとえば、一時的セキュリティ認証情報の期限が 15 分後に切れるときに UNLOAD に 1 時間かかる場合、UNLOAD は完了前に失敗します。

固定幅のデータファイルへの VENUE のアンロード

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/venue_fw_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth as 'venueid:3,venuename:39,venuecity:16,venuestate:2,venueseats:6';

出力データファイルは次のようになります。

Copy to clipboard
1 Toyota Park Bridgeview IL0 2 Columbus Crew Stadium Columbus OH0 3 RFK Stadium Washington DC0 4 CommunityAmerica BallparkKansas City KS0 5 Gillette Stadium Foxborough MA68756 ...

一連のタブ区切り GZIP 圧縮ファイルへの VENUE のアンロード

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t' gzip;

区切り文字を含むデータのアンロード

この例では、ADDQUOTES オプションを使って、実際の一部のデータフィールドにカンマが含まれているカンマ区切りデータをアンロードします。

最初に引用符を含むテーブルを作成します。

Copy to clipboard
create table location (id int, location char(64)); insert into location values (1,'Phoenix, AZ'),(2,'San Diego, CA'),(3,'Chicago, IL');
次に ADDQUOTES オプションを使って、データをアンロードします。
Copy to clipboard
unload ('select id, location from location') to 's3://mybucket/location_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter ',' addquotes;

アンロードされたデータファイルは次のようになります。

Copy to clipboard
1,"Phoenix, AZ" 2,"San Diego, CA" 3,"Chicago, IL" ...

結合クエリの結果のアンロード

次の例では、ウィンドウ関数を含む結合クエリの結果をアンロードします。

Copy to clipboard
unload ('select venuecity, venuestate, caldate, pricepaid, sum(pricepaid) over(partition by venuecity, venuestate order by caldate rows between 3 preceding and 3 following) as winsum from sales join date on sales.dateid=date.dateid join event on event.eventid=sales.eventid join venue on event.venueid=venue.venueid order by 1,2') to 's3://mybucket/tickit/winsum' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

出力ファイルは次のようになります。

Copy to clipboard
Atlanta|GA|2008-01-04|363.00|1362.00 Atlanta|GA|2008-01-05|233.00|2030.00 Atlanta|GA|2008-01-06|310.00|3135.00 Atlanta|GA|2008-01-08|166.00|8338.00 Atlanta|GA|2008-01-11|268.00|7630.00 ...

NULL AS を使用したアンロード

UNLOAD では、デフォルトで空の文字列として Null 値を出力します。以下の例では、NULL AS を使用して Null のテキスト文字列を置換する方法を示します。

これらの例では、VENUE テーブルにいくつかの Null 値を追加します。

Copy to clipboard
update venue set venuestate = NULL where venuecity = 'Cleveland';

VENUESTATE が Null である VENUE から選択して、列に Null が格納されていることを確認します。

Copy to clipboard
select * from venue where venuestate is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 72 | Cleveland Browns Stadium | Cleveland | | 73200 (3 rows)

次に、VENUE テーブルに対して NULL AS オプションを指定して UNLOAD を実行し、Null 値をキャラクタ文字列 'fred' で置き換えます。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred';

次のアンロードファイルの例は、Null 値が fred で置き換えられたことを示しています。この場合、VENUESEATS の値の一部も Null であったため、fred で置き換えられたことが分かります。VENUESEATS のデータ型が整数であっても、UNLOAD によってアンロードファイルの値がテキストに変換され、さらに COPY によって再び整数に戻されます。固定幅ファイルにアンロードする場合、NULL AS の文字列をフィールド幅以下にする必要があります。

Copy to clipboard
248|Charles Playhouse|Boston|MA|0 251|Paris Hotel|Las Vegas|NV|fred 258|Tropicana Hotel|Las Vegas|NV|fred 300|Kennedy Center Opera House|Washington|DC|0 306|Lyric Opera House|Baltimore|MD|0 308|Metropolitan Opera|New York City|NY|0 5|Gillette Stadium|Foxborough|MA|5 22|Quicken Loans Arena|Cleveland|fred|0 101|Progressive Field|Cleveland|fred|43345 ...

アンロードファイルからテーブルをロードするには、COPY コマンドで同じ NULL AS オプションを指定します。

注記

NOT NULL として定義された列に Null のロードを試みた場合、COPY コマンドは失敗します。

Copy to clipboard
create table loadvenuenulls (like venue); copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' null as 'fred';

列に単なる空の文字列ではなく Null が含まれていることを確認するには、LOADVENUENULLS から選択して Null でフィルタリングします。

Copy to clipboard
select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 72 | Cleveland Browns Stadium | Cleveland | | 73200 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 251 | Paris Hotel | Las Vegas | NV | ...

Null を含むテーブルに対してデフォルトの NULL AS の動作を使用して UNLOAD を実行し、次にデフォルトの NULL AS の動作を使用して COPY を実行してデータをテーブルに戻すことができます。ただし、ターゲットテーブルの数値以外のフィールドには、Null ではなく空の文字列が格納されます。デフォルトでは、UNLOAD によって Null が空の文字列 (空白またはゼロ長) に変換されます。COPY によって、数値列では空の文字列が Null に変換されますが、数値以外の列には空の文字列が挿入されます。次の例では、デフォルトの NULL AS 動作を使用して UNLOAD の後で COPY を実行する方法を示します。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

この場合、Null でフィルタリングすると、VENUESEATS に Null が含まれていた行のみが表示されます。テーブル (VENUE) で VENUESTATE に Null が含まれていた場合、ターゲットテーブル (LOADVENUENULLS) の VENUESTATE には空の文字列が格納されます。

Copy to clipboard
select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 251 | Paris Hotel | Las Vegas | NV | ...

数値以外の列に空の文字列を Null としてロードするには、EMPTYASNULL オプションまたは BLANKSASNULL オプションを含めます。両方を組み合わせて使用しても問題ありません。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' allowoverwrite; truncate loadvenuenulls; copy loadvenuenulls from 's3://mybucket/nulls/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' EMPTYASNULL;

列に単なる空白または空の文字列ではなく Null が含まれていることを確認するには、LOADVENUENULLS から選択して Null でフィルタリングします。

Copy to clipboard
select * from loadvenuenulls where venuestate is null or venueseats is null; venueid | venuename | venuecity | venuestate | venueseats ---------+--------------------------+-----------+------------+------------ 72 | Cleveland Browns Stadium | Cleveland | | 73200 253 | Mirage Hotel | Las Vegas | NV | 255 | Venetian Hotel | Las Vegas | NV | 22 | Quicken Loans Arena | Cleveland | | 0 101 | Progressive Field | Cleveland | | 43345 251 | Paris Hotel | Las Vegas | NV | ...

ALLOWOVERWRITE の例

デフォルトでは、UNLOAD は送り先バケットの既存のファイルを上書きしません。例えば、送り先バケット内のファイルを変更せずに、同じ UNLOAD ステートメントを 2 回実行すると、2 回目の UNLOAD は失敗します。マニフェストファイルを含めて既存のファイルを上書きするには、ALLOWOVERWRITE オプションを指定します。

Copy to clipboard
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest allowoverwrite;