UNLOAD 範例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

UNLOAD 範例

將 VENUE 卸載至縱線分隔檔案 (預設分隔符號)

注意

這些範例包含換行以方便閱讀。請勿在 credentials-args 字串中包含換行或空格。

下列範例會卸載 VENUE 資料表並將資料寫入 s3://mybucket/unload/

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

根據預設,UNLOAD 會在每個分割中寫入一個或多個檔案。假設有一個雙節點叢集,且每個節點有兩個分割,則上方範例會在 mybucket 中建立這些檔案:

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

為了能更清楚區分輸出檔案,您可以在位置中包含字首。下列範例會卸載 VENUE 資料表並將資料寫入 s3://mybucket/unload/venue_pipe_

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

結果會是 unload 資料夾中的這四個檔案,同樣假設有四個分割。

venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

將 LINEITEM 資料表卸載至已分割的 Parquet 檔案

以下範例會使用以 l_shipdate 資料行分割的 Parquet 格式卸載 LINEITEM 資料表。

unload ('select * from lineitem') to 's3://mybucket/lineitem/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' PARQUET PARTITION BY (l_shipdate);

假設有四個分割,則結果產生的 Parquet 檔案會以動態方式分成不同的資料夾。

s3://mybucket/lineitem/l_shipdate=1992-01-02/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet s3://mybucket/lineitem/l_shipdate=1992-01-03/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet s3://mybucket/lineitem/l_shipdate=1992-01-04/0000_part_00.parquet 0001_part_00.parquet 0002_part_00.parquet 0003_part_00.parquet ...
注意

在某些情況下,UNLOAD 命令會使用 INCLUDE 選項,如下列 SQL 陳述式中所示。

unload ('select * from lineitem') to 's3://mybucket/lineitem/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' PARQUET PARTITION BY (l_shipdate) INCLUDE;

在這些情況下,l_shipdate 資料欄也在 Parquet 檔案的資料中。否則,l_shipdate 資料欄資料不在 Parquet 檔案中。

將 VENUE 表卸載到 JSON 文件

下列範例會卸載 VENUE 資料表並以 JSON 格式寫入資料庫資料至s3://mybucket/unload/

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

以下是 VENUE 資料表中的範例資料列。

venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+----------- 1 | Pinewood Racetrack | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0

卸載到 JSON 後,文件的格式類似於以下內容。

{"venueid":1,"venuename":"Pinewood Racetrack","venuecity":"Akron","venuestate":"OH","venueseats":0} {"venueid":2,"venuename":"Columbus \"Crew\" Stadium ","venuecity":"Columbus","venuestate":"OH","venueseats":0} {"venueid":4,"venuename":"Community, Ballpark, Arena","venuecity":"Kansas City","venuestate":"KS","venueseats":0}

將 VENUE 卸載至 CSV 檔案

下列範例會卸載 VENUE 資料表,並以 CSV 格式寫入資料至 s3://mybucket/unload/

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

假設 VENUE 資料表包含下列資料列。

venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+----------- 1 | Pinewood Racetrack | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0

卸載檔案看起來類似下列。

1,Pinewood Racetrack,Akron,OH,0 2,"Columbus ""Crew"" Stadium",Columbus,OH,0 4,"Community, Ballpark, Arena",Kansas City,KS,0

使用分隔符號將 VENUE 卸載到 CSV 檔案

下列範例會卸載 VENUE 資料表,並使用縱線字元 (|) 做為分隔符號,寫入 CSV 格式的資料。卸載的檔案會寫入到 s3://mybucket/unload/。此範例中的 VENUE 表格在第一列 (Pinewood Race|track) 的值中包含縱線字元。它這樣做是為了表明結果中的值用雙引號括住。雙引號會逸出雙引號,而整個欄位會用雙引號括住。

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

假設 VENUE 資料表包含下列資料列。

venueid | venuename | venuecity | venuestate | venueseats --------+----------------------------+-----------------+------------+------------- 1 | Pinewood Race|track | Akron | OH | 0 2 | Columbus "Crew" Stadium | Columbus | OH | 0 4 | Community, Ballpark, Arena | Kansas City | KS | 0

卸載檔案看起來類似下列。

1|"Pinewood Race|track"|Akron|OH|0 2|"Columbus ""Crew"" Stadium"|Columbus|OH|0 4|Community, Ballpark, Arena|Kansas City|KS|0

使用資訊清單檔案卸載 VENUE

若要建立資訊清單檔案,請包括 MANIFEST 選項。下列範例會卸載 VENUE 資料表,並將資訊清單檔案與資料檔案一起寫入 s3://mybucket/venue_pipe_:

重要

若您使用 MANIFEST 選項卸載檔案,則應在載入檔案時使用 MANIFEST 選項搭配 COPY 命令。若您使用相同的字首載入檔案且未指定 MANIFEST 選項,則 COPY 會失敗,因為它會假設資訊清單檔案是資料檔案。

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

結果會是這五個檔案:

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

以下示範資訊清單檔案的內容。

{ "entries": [ {"url":"s3://mybucket/tickit/venue_0000_part_00"}, {"url":"s3://mybucket/tickit/venue_0001_part_00"}, {"url":"s3://mybucket/tickit/venue_0002_part_00"}, {"url":"s3://mybucket/tickit/venue_0003_part_00"} ] }

使用 MANIFEST VERBOSE 卸載 VENUE

指定 MANIFEST VERBOSE 選項時,資訊清單檔案會包含下列區段:

  • entries 區段會列出每個檔案的 Amazon S3 路徑、檔案大小和資料列計數。

  • schema 區段會列出每個資料欄的資料欄名稱、資料類型和維度。

  • meta 區段會顯示所有檔案的檔案大小總計和資料列計數。

下列範例會使用 MANIFEST VERBOSE 選項來卸載 VENUE 資料表。

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

以下示範資訊清單檔案的內容。

{ "entries": [ {"url":"s3://mybucket/venue_pipe_0000_part_00", "meta": { "content_length": 32295, "record_count": 10 }}, {"url":"s3://mybucket/venue_pipe_0001_part_00", "meta": { "content_length": 32771, "record_count": 20 }}, {"url":"s3://mybucket/venue_pipe_0002_part_00", "meta": { "content_length": 32302, "record_count": 10 }}, {"url":"s3://mybucket/venue_pipe_0003_part_00", "meta": { "content_length": 31810, "record_count": 15 }} ], "schema": { "elements": [ {"name": "venueid", "type": { "base": "integer" }}, {"name": "venuename", "type": { "base": "character varying", 25 }}, {"name": "venuecity", "type": { "base": "character varying", 25 }}, {"name": "venuestate", "type": { "base": "character varying", 25 }}, {"name": "venueseats", "type": { "base": "character varying", 25 }} ] }, "meta": { "content_length": 129178, "record_count": 55 }, "author": { "name": "Amazon Redshift", "version": "1.0.0" } }

使用標題卸載 VENUE

以下範例會使用標題列卸載 VENUE。

unload ('select * from venue where venueseats > 75000') to 's3://mybucket/unload/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' header parallel off;

以下示範資訊具有標題列的輸出檔案的內容。

venueid|venuename|venuecity|venuestate|venueseats 6|New York Giants Stadium|East Rutherford|NJ|80242 78|INVESCO Field|Denver|CO|76125 83|FedExField|Landover|MD|91704 79|Arrowhead Stadium|Kansas City|MO|79451

將 VENUE 卸載至更小的檔案

根據預設,檔案大小的上限為 6.2 GB。如果卸載資料大於 6.2 GB,則 UNLOAD 會為每個 6.2 GB 資料區段建立一個新檔案。若要建立更小的檔案,請包括 MAXFILESIZE 參數。假設先前範例中的資料大小為 20 GB,則下列 UNLOAD 命令會建立 20 個檔案,每個檔案的大小為 1 GB。

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

依序卸載 VENUE

若要依序卸載,請指定 PARALLEL OFF。如此 UNLOAD 就會一次寫入一個檔案,直到每個檔案達到 6.2 GB 上限為止。

下列範例會卸載 VENUE 資料表並依序將資料寫入 s3://mybucket/unload/

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

結果會產生一個名為 venue_serial_000 的檔案。

如果卸載資料大於 6.2 GB,則 UNLOAD 會為每個 6.2 GB 資料區段建立一個新檔案。下列範例會卸載 LINEORDER 資料表並依序將資料寫入 s3://mybucket/unload/

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

結果會是下方的一系列檔案。

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

為了能更清楚區分輸出檔案,您可以在位置中包含字首。下列範例會卸載 VENUE 資料表並將資料寫入 s3://mybucket/venue_pipe_

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

結果會是 unload 資料夾中的這四個檔案,同樣假設有四個分割。

venue_pipe_0000_part_00 venue_pipe_0001_part_00 venue_pipe_0002_part_00 venue_pipe_0003_part_00

從卸載檔案載入 VENUE

若要從一組卸載檔案載入資料表,只要使用 COPY 命令將程序反向即可。下列範例會建立 LOADVENUE 這個新資料表,並從先前範例中建立的資料檔案載入資料表。

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

若您使用 MANIFEST 選項在卸載檔案中建立資訊清單檔案,則您可以使用相同的資訊清單檔案載入資料。您可以使用 COPY 命令搭配 MANIFEST 選項來執行此動作。下列範例會使用資訊清單檔案載入資料。

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

將 VENUE 卸載至加密檔案

下列範例會使用 VENUE 資料表卸載至一組加密檔案。AWS KMS金鑰。若您使用 ENCRYPTED 選項指定資訊清單檔案,則資訊清單檔案也會加密。如需詳細資訊,請參閱 卸載加密的資料檔案

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 資料表卸載至一組加密檔案。

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

若要從使用 UNLOAD 搭配 ENCRYPT 選項建立的一組檔案中載入資料表,請使用 COPY 命令執行反向程序。使用該命令,使用 ENCRYPTED 選項並指定用於 UNLOAD 命令的相同根對稱鍵。下列範例會從先前範例中建立的加密資料檔案載入 LOADVENUE 資料表。

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 資料卸載至 Tab 分隔檔案

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

輸出資料檔案看起來像這樣:

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 卸載至固定寬度資料檔案

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';

輸出資料檔案看起來會像下面這樣。

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 ...

將 VENUE 卸載至一組 Tab 分隔的 GZIP 壓縮檔案

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

卸載包含分隔符號的資料

此範例會使用 ADDQUOTES 選項卸載逗號分隔的資料,其中部分實際資料欄位包含逗號。

首先,請建立包含引號的資料表。

create table location (id int, location char(64)); insert into location values (1,'Phoenix, AZ'),(2,'San Diego, CA'),(3,'Chicago, IL');

接著使用 ADDQUOTES 選項卸載資料。

unload ('select id, location from location') to 's3://mybucket/location_' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter ',' addquotes;

卸載的資料檔案看起來像這樣:

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

卸載聯結查詢的結果

以下範例會卸載包含視窗函數之聯結查詢的結果。

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';

輸出檔案看起來像這樣:

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 值。

update venue set venuestate = NULL where venuecity = 'Cleveland';

從其中 VENUESTATE 是 null 的 VENUE 選取,以確認資料欄包含 NULL。

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)

現在使用 NULL AS 選項對 VENUE 資料表執行 UNLOAD,將 null 值取代為字元字串 'fred'。

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 字串不得大於欄位寬度。

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 選項。

注意

如果嘗試將 null 載入己定義為 NOT NULL 的欄,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。

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 AS 行為 UNLOAD 包含 null 的資料表,然後使用預設的 NULL AS 行為將資料 COPY 回資料表中;不過,目標資料表中任何非數值欄位都會包含空字串,而非 null。根據預設,UNLOAD 會將 null 轉換成空字串 (空格或零長度)。COPY 會針對數值資料欄將空字串轉換成 NULL,但是會將空字串插入非數值資料欄。下列範例說明如何使用預設的 NULL AS 行為來執行 UNLOAD,後面接著 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 的資料列。VENUESTATE 在資料表 (VENUE) 中包含 null,而 VENUESTATE 在目標資料表 (LOADVENUENULLS) 中則包含空字串。

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 選項。也可以兩者都使用。

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,而不只是空格或空字串,請從 LOADVENULLS 選取並篩選出 null。

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 陳述式兩次,而未修改目的地儲存貯體中的檔案,則第二個 UNLOAD 會失敗。為覆寫現有檔案 (包括資訊清單檔案),指定 ALLOWOVERWRITE 選項。

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