COPY 範例 - Amazon Redshift

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

COPY 範例

注意

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

從 DynamoDB 資料表載入 FAVORITEMOVIES

所以此AWS軟件開發工具包包含一個簡單的示例,DynamoDB 創建名為影片。(關於此範例,請參閱 DynamoDB 入門。) 下列範例將 DynamoDB 資料表中的資料載入 Amazon Redshift MOVES 資料表。亞 Amazon Redshift 資料表必須已存在於資料庫中。

copy favoritemovies from 'dynamodb://Movies' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' readratio 50;

從 Amazon S3 儲存體載入商品

下列範例從 Amazon S3 儲存貯體載入 LISTING。COPY 命令會載入 /data/listing/ 資料夾中的所有檔案。

copy listing from 's3://mybucket/data/listing/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

從 Amazon EMR 叢集載入 LISTING

下列範例從 Amazon EMR 叢集的 lzop 壓縮檔案中,將 Tab 字元分隔資料載入 SALES 資料表。COPY 會載入 myoutput/ 資料夾中開頭為 part- 的每個檔案。

copy sales from 'emr://j-SAMPLE2B500FC/myoutput/part-*' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '\t' lzop;

下列範例將 Amazon EMR 叢集上的 JSON 格式資料載入 SALES 資料表。COPY 會載入 myoutput/json/ 資料夾中的每個檔案。

copy sales from 'emr://j-SAMPLE2B500FC/myoutput/json/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' JSON 's3://mybucket/jsonpaths.txt';

使用資訊清單指定資料檔案

您可以使用資訊清單來確保 COPY 命令從 Amazon S3 載入所有必要檔案 (且只有必要檔案)。需要從不同儲存貯體載入多個檔案,或載入不共用相同字首的檔案時,您也可以使用資訊清單。

例如,假設您需要載入下列三個檔案:custdata1.txtcustdata2.txtcustdata3.txt。您可以使用下列命令指定字首,以載入 mybucket 中開頭為 custdata 的所有檔案:

copy category from 's3://mybucket/custdata' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

如果因為錯誤而只有兩個檔案存在,則 COPY 只會載入那兩個檔案,然後就順利完成,導致資料載入不完整。如果儲存貯體中有一個不需要的檔案剛好也使用相同的字首,例如名為 custdata.backup 的檔案,則 COPY 也會載入此檔案,導致載入不需要的資料。

若要確保載入所有必要檔案並防止載入不需要的檔案,您可以使用資訊清單檔案。資訊清單是 JSON 格式的文字檔案,其中列出要由 COPY 命令處理的檔案。例如,下列資訊清單會載入上述範例中的三個檔案。

{ "entries":[ { "url":"s3://mybucket/custdata.1", "mandatory":true }, { "url":"s3://mybucket/custdata.2", "mandatory":true }, { "url":"s3://mybucket/custdata.3", "mandatory":true } ] }

選用的 mandatory 旗標指出如果檔案不存在,則 COPY 是否應該終止。預設為 false。不考慮任何必要設定,只要找不到檔案,COPY 就會終止。在此範例中,如果找不到任何檔案,COPY 會傳回錯誤。如果您僅指定金鑰前綴 (例如 custdata.backup),則可能已挑選的不必要檔案會被忽略,因為這些檔案不在資訊清單上。

載入格式為 ORC 或 Parquet 的資料檔案時,需要 meta 欄位,如下列範例所示。

{ "entries":[ { "url":"s3://mybucket-alpha/orc/2013-10-04-custdata", "mandatory":true, "meta":{ "content_length":99 } }, { "url":"s3://mybucket-beta/orc/2013-10-05-custdata", "mandatory":true, "meta":{ "content_length":99 } } ] }

以下範例使用名為 cust.manifest 的資訊清單。

copy customer from 's3://mybucket/cust.manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' manifest;

您可以使用資訊清單從不同儲存貯體載入檔案,或載入不共用相同字首的檔案。下列範例示範的 JSON 會從名稱開頭為日期戳記的檔案載入資料。

{ "entries": [ {"url":"s3://mybucket/2013-10-04-custdata.txt","mandatory":true}, {"url":"s3://mybucket/2013-10-05-custdata.txt”,"mandatory":true}, {"url":"s3://mybucket/2013-10-06-custdata.txt”,"mandatory":true}, {"url":"s3://mybucket/2013-10-07-custdata.txt”,"mandatory":true} ] }

資訊清單可以列出不同儲存貯體中的檔案,前提是儲存貯體位於相同的AWS區域作為集羣。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata1.txt","mandatory":false}, {"url":"s3://mybucket-beta/custdata1.txt","mandatory":false}, {"url":"s3://mybucket-beta/custdata2.txt","mandatory":false} ] }

從縱線分隔檔案 (預設分隔符號) 載入 LISTING

下列範例是非常簡單的案例,其中沒有指定任何選項,且輸入檔案包含預設分隔符號,即縱線字元 ('|')。

copy listing from 's3://mybucket/data/listings_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

使用 Parquet 格式的單欄資料載入 LISTING

下列範例從 Amazon S3 上名為 parquet 的資料夾載入資料。

copy listing from 's3://mybucket/data/listings/parquet/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as parquet;

使用選項載入 EVENT

下列範例將縱線分隔資料載入 EVENT 資料表,並套用下列規則:

  • 如果使用成對的引號來括住任何字元字串,則會移除引號。

  • 都會將空字串和含有空白的字串載入為 NULL 值。

  • 如果傳回 5 個以上的錯誤,載入會失敗。

  • 時間戳記值必須符合指定的格式;例如,有效時間戳記為 2008-09-26 05:43:12

copy event from 's3://mybucket/data/allevents_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' removequotes emptyasnull blanksasnull maxerror 5 delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS';

從固定寬度資料檔案載入 VENUE

copy venue from 's3://mybucket/data/venue_fw.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth 'venueid:3,venuename:25,venuecity:12,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

從 CSV 檔案載入 CATEGORY

假設您想要將下表所示的值載入 CATEGORY。

catid catgroup catname catdesc
12 Shows Musicals Musical theatre
13 Shows Plays All "non-musical" theatre
14 Shows Opera All opera, light, and "rock" opera
15 Concerts Classical All symphony, concerto, and choir concerts

下列範例顯示文字檔案的內容,欄位值以逗號分隔。

12,Shows,Musicals,Musical theatre 13,Shows,Plays,All "non-musical" theatre 14,Shows,Opera,All opera, light, and "rock" opera 15,Concerts,Classical,All symphony, concerto, and choir concerts

如果您載入此檔案時使用 DELIMITER 參數來指定逗號分隔輸入,COPY 命令會失敗,因為有些輸入欄位包含逗號。您可以使用 CSV 參數,並以引號字元括住含有逗號的欄位,即可避免此問題。如果引號括住的字串內出現引號引號括住的引號字元,則需要多加一個引號字元才能將其逸出。預設引號字元是雙引號,所以您需要多加一個雙引號來逸出每一個雙引號。新的輸入檔案如下所示。

12,Shows,Musicals,Musical theatre 13,Shows,Plays,"All ""non-musical"" theatre" 14,Shows,Opera,"All opera, light, and ""rock"" opera" 15,Concerts,Classical,"All symphony, concerto, and choir concerts"

假設檔案名稱為 category_csv.txt,您可以使用下列 COPY 命令來載入檔案:

copy category from 's3://mybucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv;

或者,若要避免需要逸出輸入中的雙引號,您可以使用 QUOTE AS 參數來指定不同的引號字元。例如,下方版本的category_csv.txt使用 '%'作為引號字符。

12,Shows,Musicals,Musical theatre 13,Shows,Plays,%All "non-musical" theatre% 14,Shows,Opera,%All opera, light, and "rock" opera% 15,Concerts,Classical,%All symphony, concerto, and choir concerts%

下列 COPY 命令使用 QUOTE AS 來載入 category_csv.txt

copy category from 's3://mybucket/data/category_csv.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' csv quote as '%';

載入 VENUE 時將明確值提供給 IDENTITY 欄

下列範例假設建立 VENUE 資料表時,至少有一欄 (例如 venueid 欄) 指定為 IDENTITY 欄。此命令覆寫 IDENTITY 預設行為,亦即自動產生 IDENTITY 欄的值,而改以從 venue.txt 檔案載入明確值。

copy venue from 's3://mybucket/data/venue.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' explicit_ids;

從縱線分隔 GZIP 檔案載入 TIME

下列範例從縱線分隔 GZIP 檔案載入 TIME 資料表:

copy time from 's3://mybucket/data/timerows.gz' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' gzip delimiter '|';

載入時間戳記或日期戳記

下列範例載入含有格式化時間戳記的資料。

注意

HH:MI:SS 的 TIMEFORMAT 也可以支援超過 SS 的小數秒,精細程度可達到微秒。此範例中使用的檔案 time.txt 包含一列,即 2009-01-12 14:15:57.119568

copy timestamp1 from 's3://mybucket/data/time.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' timeformat 'YYYY-MM-DD HH:MI:SS';

此複製的結果如下所示:

select * from timestamp1; c1 ---------------------------- 2009-01-12 14:15:57.119568 (1 row)

從含有預設值的檔案載入資料

下列範例使用一個從 TICKIT 資料庫中的 VENUE 資料表變化而來的版本。假設 VENUE_NEW 資料表是使用下列陳述式來定義:

create table venue_new( venueid smallint not null, venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000');

假設 venue_noseats.txt 資料檔案的 VENUESEATS 欄沒有值,如下列範例所示:

1|Toyota Park|Bridgeview|IL| 2|Columbus Crew Stadium|Columbus|OH| 3|RFK Stadium|Washington|DC| 4|CommunityAmerica Ballpark|Kansas City|KS| 5|Gillette Stadium|Foxborough|MA| 6|New York Giants Stadium|East Rutherford|NJ| 7|BMO Field|Toronto|ON| 8|The Home Depot Center|Carson|CA| 9|Dick's Sporting Goods Park|Commerce City|CO| 10|Pizza Hut Park|Frisco|TX|

下列 COPY 陳述式可成功從檔案載入資料表,並將 DEFAULT 值 ('1000') 套用至省略的欄:

copy venue_new(venueid, venuename, venuecity, venuestate) from 's3://mybucket/data/venue_noseats.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

現在檢視已載入的資料表:

select * from venue_new order by venueid; venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 1000 2 | Columbus Crew Stadium | Columbus | OH | 1000 3 | RFK Stadium | Washington | DC | 1000 4 | CommunityAmerica Ballpark | Kansas City | KS | 1000 5 | Gillette Stadium | Foxborough | MA | 1000 6 | New York Giants Stadium | East Rutherford | NJ | 1000 7 | BMO Field | Toronto | ON | 1000 8 | The Home Depot Center | Carson | CA | 1000 9 | Dick's Sporting Goods Park | Commerce City | CO | 1000 10 | Pizza Hut Park | Frisco | TX | 1000 (10 rows)

在下列範例中,除了假設檔案不含 VENUESEATS 資料,也假設不含 VENUENAME 資料:

1||Bridgeview|IL| 2||Columbus|OH| 3||Washington|DC| 4||Kansas City|KS| 5||Foxborough|MA| 6||East Rutherford|NJ| 7||Toronto|ON| 8||Carson|CA| 9||Commerce City|CO| 10||Frisco|TX|

使用同樣的資料表定義,下列 COPY 陳述式會失敗,因為未指定 VENUENAME 的 DEFAULT 值,且 VENUENAME 是 NOT NULL 欄:

copy venue(venueid, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

現在假設一個使用 IDENTITY 欄的變化版 VENUE 資料表:

create table venue_identity( venueid int identity(1,1), venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000');

如同上述範例,假設 VENUESEATS 欄在來源檔案中沒有對應的值。下列 COPY 陳述式可成功載入資料表,包括預先定義的 IDENTITY 資料值,而不是自動產生那些值:

copy venue(venueid, venuename, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids;

此陳述式會失敗,因為不含 IDENTITY 欄 (欄清單中缺少 VENUEID),卻包含 EXPLICIT_IDS 參數:

copy venue(venuename, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' explicit_ids;

此陳述式會失敗,因為不含 EXPLICIT_IDS 參數:

copy venue(venueid, venuename, venuecity, venuestate) from 's3://mybucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';

搭配 ESCAPE 選項來 COPY 資料

下列範例示範如何載入符合分隔符號字元 (在此例子中是縱線字元) 的字元。在輸入檔案中,請確定您要載入的所有縱線字元 (|) 都以反斜線字元 (\) 逸出。載入以 ESCAPE 參數載入檔案。

$ more redshiftinfo.txt 1|public\|event\|dwuser 2|public\|sales\|dwuser create table redshiftinfo(infoid int,tableinfo varchar(50)); copy redshiftinfo from 's3://mybucket/data/redshiftinfo.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' escape; select * from redshiftinfo order by 1; infoid | tableinfo -------+-------------------- 1 | public|event|dwuser 2 | public|sales|dwuser (2 rows)

如果不指定 ESCAPE 參數,此 COPY 命令會失敗並傳回 Extra column(s) found 錯誤。

重要

如果您使用 COPY 搭配 ESCAPE 參數來載入資料,則在 UNLOAD 命令中也必須指定 ESCAPE 參數,以產生對等的輸出檔案。同樣地,如果使用 ESCAPE 參數來 UNLOAD,則在 COPY 相同的資料時需要使用 ESCAPE。

從 JSON 複製的範例

在下列範例中,您會將下列資料載入 CATEGORY 資料表。

CATID CATGROUP CATNAME CATDESC
1 運動 MLB 美國職棒大聯盟
2 運動 NHL National Hockey League
3 運動 NFL National Football League
4 運動 NBA National Basketball Association
5 Concerts Classical All symphony, concerto, and choir concerts

使用 'auto' 選項從 JSON 資料載入

若要使用'auto'選項時,JSON 資料必須包含一組物件。金鑰名稱必須符合欄名稱,但順序並不重要。以下顯示一個名為 category_object_auto.json 之檔案的內容。

{ "catdesc": "Major League Baseball", "catid": 1, "catgroup": "Sports", "catname": "MLB" } { "catgroup": "Sports", "catid": 2, "catname": "NHL", "catdesc": "National Hockey League" }{ "catid": 3, "catname": "NFL", "catgroup": "Sports", "catdesc": "National Football League" } { "bogus": "Bogus Sports LLC", "catid": 4, "catgroup": "Sports", "catname": "NBA", "catdesc": "National Basketball Association" } { "catid": 5, "catgroup": "Shows", "catname": "Musicals", "catdesc": "All symphony, concerto, and choir concerts" }

若要從上述範例的 JSON 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_object_auto.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto';

使用 'auto 忽略' 選項從 JSON 資料載入

若要使用'auto ignorecase'選項時,JSON 資料必須包含一組物件。金鑰名稱的大小寫不必符,而順序並不重要。以下顯示一個名為 category_object_auto-ignorecase.json 之檔案的內容。

{ "CatDesc": "Major League Baseball", "CatID": 1, "CatGroup": "Sports", "CatName": "MLB" } { "CatGroup": "Sports", "CatID": 2, "CatName": "NHL", "CatDesc": "National Hockey League" }{ "CatID": 3, "CatName": "NFL", "CatGroup": "Sports", "CatDesc": "National Football League" } { "bogus": "Bogus Sports LLC", "CatID": 4, "CatGroup": "Sports", "CatName": "NBA", "CatDesc": "National Basketball Association" } { "CatID": 5, "CatGroup": "Shows", "CatName": "Musicals", "CatDesc": "All symphony, concerto, and choir concerts" }

若要從上述範例的 JSON 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_object_auto ignorecase.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto ignorecase';

使用 JSONPaths 檔案從 JSON 資料載入

如果 JSON 資料物件沒有直接對應至欄名稱,您可以使用 JSONPaths 檔案將 JSON 元素映射至欄。JSON 來源資料中的順序並不重要,但 JSONPaths 檔案表達式的順序必須符合欄順序。假設您有下列資料檔案,名稱為 category_object_paths.json

{ "one": 1, "two": "Sports", "three": "MLB", "four": "Major League Baseball" } { "three": "NHL", "four": "National Hockey League", "one": 2, "two": "Sports" } { "two": "Sports", "three": "NFL", "one": 3, "four": "National Football League" } { "one": 4, "two": "Sports", "three": "NBA", "four": "National Basketball Association" } { "one": 6, "two": "Shows", "three": "Musicals", "four": "All symphony, concerto, and choir concerts" }

下列 JSONPaths 檔案 (名為 category_jsonpath.json) 將來源資料映射至資料表欄。

{ "jsonpaths": [ "$['one']", "$['two']", "$['three']", "$['four']" ] }

若要從上述範例的 JSON 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_object_paths.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://mybucket/category_jsonpath.json';

使用 JSONPaths 檔案從 JSON 陣列載入

若要從包含一組陣列的 JSON 資料載入,您必須使用 JSONPaths 檔案將陣列元素映射至欄。假設您有下列資料檔案,名稱為 category_array_data.json

[1,"Sports","MLB","Major League Baseball"] [2,"Sports","NHL","National Hockey League"] [3,"Sports","NFL","National Football League"] [4,"Sports","NBA","National Basketball Association"] [5,"Concerts","Classical","All symphony, concerto, and choir concerts"]

下列 JSONPaths 檔案 (名為 category_array_jsonpath.json) 將來源資料映射至資料表欄。

{ "jsonpaths": [ "$[0]", "$[1]", "$[2]", "$[3]" ] }

若要從上述範例的 JSON 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_array_data.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://mybucket/category_array_jsonpath.json';

從 Avro 複製的範例

在下列範例中,您會將下列資料載入 CATEGORY 資料表。

CATID CATGROUP CATNAME CATDESC
1 運動 MLB 美國職棒大聯盟
2 運動 NHL National Hockey League
3 運動 NFL National Football League
4 運動 NBA National Basketball Association
5 Concerts Classical All symphony, concerto, and choir concerts

使用 'auto' 選項從 Avro 資料載入

若要使用 'auto' 引數從 Avro 資料載入,Avro 結構描述中的欄位名稱必須符合欄名稱。當您使用'auto'參數,順序並不重要。以下顯示一個名為 category_auto.avro 之檔案的結構描述。

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "catid", "type": "int"},
        {"name": "catdesc", "type": "string"},
        {"name": "catname", "type": "string"},
        {"name": "catgroup", "type": "string"},
}

Avro 檔案中的資料是二進位格式,無法直接閱讀。以下顯示 category_auto.avro 檔案中之資料的 JSON 表示法。

{
   "catid": 1,
   "catdesc": "Major League Baseball",
   "catname": "MLB",
   "catgroup": "Sports"
}
{
   "catid": 2,
   "catdesc": "National Hockey League",
   "catname": "NHL",
   "catgroup": "Sports"
}
{
   "catid": 3,
   "catdesc": "National Basketball Association",
   "catname": "NBA",
   "catgroup": "Sports"
}
{
   "catid": 4,
   "catdesc": "All symphony, concerto, and choir concerts",
   "catname": "Classical",
   "catgroup": "Concerts"
}

若要從上述範例的 Avro 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_auto.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto';

使用 'auto 忽略' 選項從 Avro 資料載入

若要使用'auto ignorecase'引數時,Avro 結構描述中的欄位名稱的大小寫不必符合欄名稱的大小寫。當您使用'auto ignorecase'參數,順序並不重要。以下顯示一個名為 category_auto-ignorecase.avro 之檔案的結構描述。

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "CatID", "type": "int"},
        {"name": "CatDesc", "type": "string"},
        {"name": "CatName", "type": "string"},
        {"name": "CatGroup", "type": "string"},
}

Avro 檔案中的資料是二進位格式,無法直接閱讀。以下顯示 category_auto-ignorecase.avro 檔案中之資料的 JSON 表示法。

{
   "CatID": 1,
   "CatDesc": "Major League Baseball",
   "CatName": "MLB",
   "CatGroup": "Sports"
}
{
   "CatID": 2,
   "CatDesc": "National Hockey League",
   "CatName": "NHL",
   "CatGroup": "Sports"
}
{
   "CatID": 3,
   "CatDesc": "National Basketball Association",
   "CatName": "NBA",
   "CatGroup": "Sports"
}
{
   "CatID": 4,
   "CatDesc": "All symphony, concerto, and choir concerts",
   "CatName": "Classical",
   "CatGroup": "Concerts"
}

若要從上述範例的 Avro 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_auto-ignorecase.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto ignorecase';

使用 JSONPaths 檔案從 Avro 資料載入

如果 Avro 結構描述中的欄位名稱沒有直接對應至欄名稱,您可以使用 JSONPaths 檔案將結構描述元素映射至欄。JSONPaths 檔案表達式的順序必須符合欄順序。

假設您有一個名為 category_paths.avro 的資料檔案,其中包含的資料與上述範例相同,但使用下列結構描述。

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "id", "type": "int"},
        {"name": "desc", "type": "string"},
        {"name": "name", "type": "string"},
        {"name": "group", "type": "string"},
        {"name": "region", "type": "string"} 
     ]
}

下列 JSONPaths 檔案 (名為 category_path.avropath) 將來源資料映射至資料表欄。

{
    "jsonpaths": [
        "$['id']",
        "$['group']",
        "$['name']",
        "$['desc']"
    ]
}

若要從上述範例的 Avro 資料檔案載入,請運行下列 COPY 命令。

copy category from 's3://mybucket/category_object_paths.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format avro 's3://mybucket/category_path.avropath ';

準備要搭配 ESCAPE 選項來 COPY 的檔案

下列示例描述在使用 COPY 命令和 ESCAPE 參數,將資料導入 Amazon Redshift 資料表之前,如何準備資料來「逸出」換行字元。如果不準備資料來分隔換行字元,Amazon Redshift 會在您執行 COPY 命令時返回載入錯誤,因為換行字元通常作為記錄分隔符號。

例如,假設您想要將一個檔案或外部資料表中的某一欄複製到 Amazon Redshift 資料表。如果此檔案或欄包含 XML 格式的內容或類似資料,您必須確定內容中的所有換行字元 (\n) 都以反斜線字元 (\) 逸出。

含有內嵌的換行字元的檔案或資料表提供相當簡單的比對模式。每一個內嵌的換行字元很可能都接在 > 字元後面,且之間可能有幾個空格字元 (' ' 或 Tab 字元),如下列範例所示 (在名為 nlTest1.txt 的文字檔案中)。

$ cat nlTest1.txt <xml start> <newline characters provide> <line breaks at the end of each> <line in content> </xml>|1000 <xml> </xml>|2000

在下列範例中,您可以執行文字處理公用程式來預先處理來源檔案,並在需要的地方插入逸出字元。(|字元主要做為分隔符號來分隔欄資料複製到 Amazon Redshift 資料表。)

$ sed -e ':a;N;$!ba;s/>[[:space:]]*\n/>\\\n/g' nlTest1.txt > nlTest2.txt

同樣地,您可以使用 Perl 來執行類似的操作:

cat nlTest1.txt | perl -p -e 's/>\s*\n/>\\\n/g' > nlTest2.txt

為了適應從nlTest2.txt文件添加到 Amazon Redshift 中,我們在 Amazon Redshift 中創建了一個兩列表格。第一欄 c1 是字元欄,將存放來自 nlTest2.txt 檔案的 XML 格式內容。第二欄 c2 存放從同一個檔案載入的整數值。

運行sed命令,您就可以從nlTest2.txt文件添加到 Amazon Redshift 表中。

注意

在 COPY 命令中包含 ESCAPE 參數時,可逸出一些含有反斜線字元的特殊字元 (包括換行字元)。

copy t2 from 's3://mybucket/data/nlTest2.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' escape delimiter as '|'; select * from t2 order by 2; c1 | c2 -------------+------ <xml start> <newline characters provide> <line breaks at the end of each> <line in content> </xml> | 1000 <xml> </xml> | 2000 (2 rows)

您可以用類似的方法來準備從外部資料庫匯出的資料檔案。例如,對於 Oracle 資料庫,您可以在您要複製到 Amazon Redshift 的資料表中,對每個受影響的欄使用 REPLACE 函數。

SELECT c1, REPLACE(c2, \n',\\n' ) as c2 from my_table_with_xml

此外,許多資料庫匯出及擷取、轉換、載入 (ETL) 的工具 (經常處理大量資料),都提供選項來指定逸出和分隔符號字元。

將形狀文件加載到 Amazon Redshift

下列範例示範如何使用 COPY 來載入 Esri shapefile。如需有關載入 shapefile 的詳細資訊,請參將形狀文件加載到 Amazon Redshift

載入 Shapefile

下列步驟示範如何在 OpenStreetMap 來自 Amazon S3 的資料。此範例假設挪威地福布裏克的下載網站已上 Amazon S3 至AWS區域。所以此.shp.shx,以及.dbf文件必須共享相同的 Amazon S3 前綴和文件名。

無需簡化即可獲取資料

以下命令創建可以適合最大幾何大小的表和接收數據,而無需進行任何簡化。開啟gis_osm_natural_free_1.shp並檢查此圖層中的列。默認情況下,「身份」列或「幾何」列為首位。當幾何列為第一個時,可以創建表,如下所示。

CREATE TABLE norway_natural ( wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR);

或者,如果第一個 IDENTITY 列,則可以創建表,如下所示。

CREATE TABLE norway_natural_with_id ( fid INT IDENTITY(1,1), wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR);

現在,您可以使用 COPY 接收數據。

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully

或者,您可以按如下所示獲取數據。

COPY norway_natural_with_id FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural_with_id' completed, 83891 record(s) loaded successfully.

簡化規則採集資料

以下命令創建一個表,並嘗試在沒有任何簡化的情況下接收不能適合最大幾何大小的數據。檢查gis_osm_water_a_free_1.shpshapefile 並創建相應的表格,如下所示。

CREATE TABLE norway_water ( wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR);

當 COPY 命令運行時,會導致錯誤。

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; ERROR: Load into table 'norway_water' failed. Check 'stl_load_errors' system table for details.

查詢STL_LOAD_ERRORS顯示幾何體太大。

SELECT line_number, btrim(colname), btrim(err_reason) FROM stl_load_errors WHERE query = pg_last_copy_id(); line_number | btrim | btrim -------------+--------------+----------------------------------------------------------------------- 1184705 | wkb_geometry | Geometry size: 1513736 is larger than maximum supported size: 1048447

為瞭解決這個問題,SIMPLIFY AUTO參數添加到 COPY 命令中以簡化幾何。

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989196 record(s) loaded successfully.

要查看已簡化的行和幾何,請查詢SVL_SPATIAL_SIMPLIFY

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+---------------------- 20 | 1184704 | -1 | 1513736 | t | 1008808 | 1.276386653895e-05 20 | 1664115 | -1 | 1233456 | t | 1023584 | 6.11707814796635e-06

使用簡化自動最大容差而容差低於自動計算的容差可能會導致攝入錯誤。在這種情況下,使用 MAXERROR 忽略錯誤。

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO 1.1E-05 MAXERROR 2 CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989195 record(s) loaded successfully. INFO: Load into table 'norway_water' completed, 1 record(s) could not be loaded. Check 'stl_load_errors' system table for details.

查詢SVL_SPATIAL_SIMPLIFY來識別 COPY 無法加載的記錄。

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+----------------- 29 | 1184704 | 1.1e-05 | 1513736 | f | 0 | 0 29 | 1664115 | 1.1e-05 | 1233456 | t | 794432 | 1.1e-05

在此示例中,第一條記錄無法適應,因此simplified列顯示為假。第二條記錄在給定容差範圍內加載。但是,最終尺寸大於在不指定最大公差的情況下使用自動計算的公差。

從壓縮的形狀文件加載

Amazon Redshift 拷貝支持從壓縮形狀文件攝取數據。所有 shapefile 組件必須具有相同的 Amazon S3 前綴和相同的壓縮後綴。例如,假設您希望從上一例子載入資料。在此案例中,檔案gis_osm_water_a_free_1.shp.gzgis_osm_water_a_free_1.dbf.gz,以及gis_osm_water_a_free_1.shx.gz必須共享相同的 Amazon S3 目錄。COPY 命令需要 GZIP 選項,FROM 子句必須指定正確的壓縮文件,如下所示。

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/compressed/gis_osm_natural_free_1.shp.gz' FORMAT SHAPEFILE GZIP CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully.

將數據加載到具有不同列順序的表

如果您有沒有GEOMETRY作為第一列,則可以使用列映射將列映射到目標表。例如,創建一個具有osm_id指定為第一列。

CREATE TABLE norway_natural_order ( osm_id BIGINT, wkb_geometry GEOMETRY, code INT, fclass VARCHAR, name VARCHAR);

然後使用列映射攝取 shapefile。

COPY norway_natural_order(wkb_geometry, osm_id, code, fclass, name) FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural_order' completed, 83891 record(s) loaded successfully.

將數據加載到具有地理位置列的表

如果您的資料表具有GEOGRAPHY列中,您首先接收到GEOMETRY列,然後將對象轉換為GEOGRAPHY物件金。例如,在將 shapefile 複製到GEOMETRY列中,更改表以添加GEOGRAPHY資料類型。

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

然後將幾何轉換為地理位置。

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

或者,您可以刪除GEOMETRY列出來。

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;