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

UNLOAD の例

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

注記

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

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

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

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

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

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

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

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

Copy
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
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
unload ('select * from lineorder') to 's3://mybucket/unload/lineorder_serial_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' parallel off gzip;

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

Copy
lineorder_serial_0000.gz lineorder_serial_0001.gz lineorder_serial_0002.gz lineorder_serial_0003.gz

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

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

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

Copy
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
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

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

Copy
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
create table loadvenue (like venue); copy loadvenue from 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

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

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

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

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

Copy
unload ('select * from venue') to 's3://mybucket/venue_encrypt_kms' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' kms_key_id '1234abcd-12ab-34cd-56ef-1234567890ab' manifest encrypted;
次の例では、マスター対称キーを使用して VENUE テーブルを一連の暗号化ファイルにアンロードします。
Copy
unload ('select * from venue') to 's3://mybucket/venue_encrypt_cmk' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' master_symmetric_key 'EXAMPLEMASTERKEYtkbjk/OpCwtYSx/M4/t7DMCDIK722' encrypted;

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

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

Copy
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
unload ('select venueid, venuename, venueseats from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t';

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

Copy
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
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
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
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
unload ('select * from venue') to 's3://mybucket/venue_tab_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter as '\t' gzip;

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

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

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

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

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

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

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

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

Copy
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
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
update venue set venuestate = NULL where venuecity = 'Cleveland';

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

Copy
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
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
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
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
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
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
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
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
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
unload ('select * from venue') to 's3://mybucket/venue_pipe_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest allowoverwrite;