COPY 範例 - Amazon Redshift
從 DynamoDB 資料表載入 FAVORITEMOVIES從 Amazon S3 儲存貯體載入 LISTING從 Amazon EMR 叢集載入 LISTING使用資訊清單指定資料檔案從縱線分隔檔案 (預設分隔符號) 載入 LISTING使用 Parquet 格式的單欄資料載入 LISTING使用 ORC 格式的單欄資料載入 LISTING使用選項載入 EVENT從固定寬度資料檔案載入 VENUE從 CSV 檔案載入 CATEGORY載入 VENUE 時將明確值提供給 IDENTITY 欄從縱線分隔 GZIP 檔案載入 TIME載入時間戳記或日期戳記從含有預設值的檔案載入資料搭配 ESCAPE 選項來 COPY 資料從 JSON 複製的範例從 Avro 複製的範例準備要搭配 ESCAPE 選項來 COPY 的檔案載入 Shapefile具有 NOLOAD 選項的 COPY 命令

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

COPY 範例

注意

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

從 DynamoDB 資料表載入 FAVORITEMOVIES

AWS SDK 包括建立名為 Movies 之 DynamoDB 資料表的簡單範例。(關於此範例，請參閱 DynamoDB 入門。) 下列範例將 DynamoDB 資料表中的資料載入 Amazon Redshift MOVIES 資料表。Amazon Redshift 資料表必須已存在於資料庫中。

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

從 Amazon S3 儲存貯體載入 LISTING

以下範例從 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.txt、custdata2.txt 和 custdata3.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'
format as orc
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;

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

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

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

使用選項載入 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 檔案載入明確值。使用 EXLICIT_IDS 選項時，Amazon Redshift 不會檢查是否將重複的 IDENTITY 值載入到資料表中。

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 資料載入，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 ignorecase' 選項從 JSON 資料載入

若要使用 'auto ignorecase' 選項從 JSON 資料載入，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 ignorecase' 選項從 Avro 資料載入

若要使用 'auto ignorecase' 引數從 Avro 資料載入，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 資料表之前，如何準備資料來「逸出」換行字元。如果不準備資料來分隔換行字元，當您執行 COPY 命令時，Amazon Redshift 會傳回載入錯誤，因為換行字元通常做為記錄分隔符號。

例如，假設您想要將一個檔案或外部資料表中的一欄複製到 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 命令之後，您就可以使用 ESCAPE 參數，將 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) 的工具 (經常處理大量資料)，都提供選項來指定逸出和分隔符號字元。

將 Shapefile 載入 Amazon Redshift

下列範例示範如何使用 COPY 來載入 Esri Shapefile。如需有關載入 Shapefile 的詳細資訊，請參閱將 Shapefile 載入 Amazon Redshift。

載入 Shapefile

下列步驟說明如何使用 COPY 命令從 Amazon S3 擷取 OpenStreetMap 資料。此範例假設來自 Geofabrik 下載網站的挪威 Shapefile 存檔已上傳到您 AWS 區域中的私有 Amazon S3 儲存貯體。.shp、.shx 和 .dbf 檔案必須共用相同的 Amazon S3 字首和檔案名稱。

不使用簡化擷取資料

以下命令會建立資料表並擷取資料，這些資料無需任何簡化即可符合最大幾何大小。在您偏好的 GIS 軟體中開啟 gis_osm_natural_free_1.shp，並檢查此圖層中的欄。依預設，IDENTITY 或 GEOMETRY 欄都是第一個。當 GEOMETRY 欄是第一個，你可以建立資料表，如下所示。

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.shp Shapefile 並建立適當資料表，如下所示。

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

使用 SIMPLIFY AUTO max_tolerance 且公差低於自動計算的公差，可能會導致擷取錯誤。在這種情況下，請使用 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 欄顯示為 false。第二筆記錄在給定的公差範圍內載入。但是，最終大小大於使用自動計算的公差而不指定最大公差。

從壓縮的 Shapefile 載入

Amazon Redshift COPY 支援從壓縮的 Shapefile 中擷取資料。所有 Shapefile 元件必須具有相同的 Amazon S3 字首和相同的壓縮字尾。例如，假設您想要從上述範例中載入資料。在此情況下，gis_osm_water_a_free_1.shp.gz、gis_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;

具有 NOLOAD 選項的 COPY 命令

若要在實際載入資料之前先驗證資料檔案，請使用 NOLOAD 選項搭配 COPY 命令。Amazon Redshift 會剖析輸入檔案，並顯示發生的任何錯誤。下列範例使用 NOLOAD 選項，而且沒有列實際載入資料表中。

COPY public.zipcode1
FROM 's3://mybucket/mydata/zipcode.csv' 
DELIMITER ';' 
IGNOREHEADER 1 REGION 'us-east-1'
NOLOAD
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/myRedshiftRole';

Warnings:
Load into table 'zipcode1' completed, 0 record(s) loaded successfully.