COPY の例 - Amazon Redshift
DynamoDB テーブルから FAVORITEMOVIES をロードするAmazon S3 バケットから LISTING をロードするAmazon EMR クラスターから LISTING をロードするマニフェストを使用し、データファイルを指定するパイプ区切りファイル（デフォルトの区切り記号）から LISTING をロードするParquet 形式の列指向データを使用した LISTING のロードオプションを使用した EVENT のロード固定幅のデータファイルから VENUE をロードするCSV ファイルから CATEGORY をロードするIDENTITY 列の明示的値を使用して VENUE をロードするパイプ区切りの GZIP ファイルから TIME をロードするタイムスタンプまたは日付スタンプのロードデフォルト値を使用してファイルのデータをロードするESCAPE データを使用したデータのコピーJSON からのコピーの例Avro の例からのコピーESCAPE オプションを指定する 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 をロードする

次の例は、タブ区切りデータを含んだ SALES テーブルを Amazon EMR クラスター上の LZOP 圧縮ファイルからロードします。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 からロードできます。異なるバケットの複数のファイル、または同じプレフィックスを共有しない複数のファイルをロードする必要がある場合も、マニフェストを使用できます。

たとえば、3 つのファイル custdata1.txt、custdata2.txt、custdata3.txt をロードする必要があるとします。次のコマンドを使用し、プレフィックスを指定することで、mybucket 内で custdata で始まるすべてのファイルをロードすることができます。

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

エラーのために 2 つのファイルしか存在しない場合、COPY はこれら 2 つのファイルのみロードして正常に終了しますが、データロードは未完了になります。バケット内に同じプレフィックスを使用する不要なファイル (custdata.backup などのファイル) がある場合、COPY はそのファイルもロードするため、不要なデータがロードされることになります。

必要なファイルがすべてロードされ、不要なデータがロードされないようにするには、マニフェストファイルを使用できます。マニフェストは、COPY コマンドで処理されるファイルをリストする、JSON 形式のテキストファイルです。例えば、次のマニフェストは前の例の 3 つのファイルをロードします。

{  
   "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。mandatory 設定と関係なく、どのファイルも見つからない場合、COPY は終了します。この例で、ファイルが見つからない場合、COPY はエラーを返します。custdata.backup など、キープレフィックスのみ指定した場合に選択された可能性がある不要なファイルは、マニフェストにないため、無視されます。

以下の例に示すように、Parquet または ORC 形式のデータファイルからロードする場合、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 のロード

次の例では、parquet と呼ばれる Amazon S3 のフォルダからデータをロードします。

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 ミュージカル劇
13 Shows Plays すべての「ミュージカル以外」の劇
14 Shows Opera すべてのオペラ、ライト、「ロック」オペラ
15 Concerts Classical すべてのシンフォニー、コンチェルト、合唱団のコンサート

次の例は、テキストファイルの内容をカンマで区切ったフィールド値とともに示しています。

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 パラメータを使用し、カンマを含むフィールドを引用符で囲みます。引用符で囲んだ文字列内に引用符がある場合、引用符を 2 つにしてエスケープする必要があります。デフォルトの引用符は二重引用符です。したがって、二重引用符を追加して各二重引用符をエスケープする必要があります。新しい入力ファイルは次のようになります。

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

IDENTITY 列の明示的値を使用して VENUE をロードする

次の例では、VENUE テーブルの作成時に少なくとも 1 列 (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 '|';

タイムスタンプまたは日付スタンプのロード

次の例では、書式設定したタイムスタンプ付きのデータをロードします。

注記

TIMEFORMAT HH:MI:SS では、SS を超える小数点以下の秒数もマイクロ秒レベルまでサポートします。この例で使用されるファイル time.txt には 2009-01-12 14:15:57.119568 という 1 行が含まれています。

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

次の例に示すように、VENUESEATS 列に値がない venue_noseats.txt データファイルを考えてみます。

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 データ値を自動生成する代わりに事前定義済みの 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 データを使用したデータのコピー

次の例は、区切り文字 (この場合、パイプ文字) に一致する文字をロードする方法を示しています。入力ファイルで、ロードするすべてのパイプ文字 (|) がバックスラッシュ文字 (\) でエスケープされていることを確認します。次に、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 パラメータを使用しないと、Extra column(s) found エラーが発生して、この COPY コマンドは失敗します。

重要

ESCAPE パラメータを指定した COPY を使用してデータをロードした場合、対応する出力ファイルを生成するには、UNLOAD コマンドにも ESCAPE パラメータを指定する必要があります。同様に、ESCAPE パラメータを使って UNLOAD を実行すると、同じデータを COPY する場合に ESCAPE を使用する必要があります。

JSON からのコピーの例

以下の例で、次のデータを含む CATEGORY テーブルをロードします。

CATID CATGROUP CATNAME CATDESC
1 スポーツ MLB Major League Baseball
2 スポーツ NHL National Hockey League
3 スポーツ NFL National Football League
4 スポーツ NBA National Basketball Association
5 Concerts Classical すべてのシンフォニー、コンチェルト、合唱団のコンサート

'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"
}

category_jsonpath.json という名前の次の JSONPaths ファイルで、ソースデータをテーブルの列にマッピングします。

{
    "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"]

category_array_jsonpath.json という名前の次の JSONPaths ファイルで、ソースデータをテーブルの列にマッピングします。

{
    "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 Major League Baseball
2 スポーツ NHL National Hockey League
3 スポーツ NFL National Football League
4 スポーツ NBA National Basketball Association
5 Concerts Classical すべてのシンフォニー、コンチェルト、合唱団のコンサート

'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"} 
     ]
}

category_path.avropath という名前の次の JSONPaths ファイルで、ソースデータをテーブルの列にマッピングします。

{
    "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 用のファイルの準備

次の例では、ESCAPE パラメータを指定した COPY コマンドでデータを Amazon Redshift テーブルにインポートする前に、データを準備して改行文字を "エスケープする" 方法について説明します。データを準備して改行文字を区切らないと、Amazon Redshift は COPY コマンドの実行時にロードエラーを返します。通常、改行文字はレコード区切り文字として使用されるためです。

例えば、ファイルまたは外部テーブルの列を Amazon Redshift テーブルにコピーするとします。そのファイルまたは列に XML 形式のコンテンツまたは同様なデータが含まれている場合、コンテンツの一部である改行文字 (\n) はすべてバックスラッシュ文字 (\) でエスケープする必要があります。

埋め込み改行文字を含むファイルまたはテーブルは、比較的簡単に一致パターンを提供します。テキストファイル > の次の例に示すように、ほとんどの場合、それぞれの埋め込み改行文字は ' ' 文字の後に続きます。場合によっては、その間に空白文字 (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 に 2 列のテーブルを作成しました。最初の列 c1 は文字の列です。この列は nlTest2.txt ファイルからの XML 形式のコンテンツを保持します。2 番目の列 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) ツールは、エスケープ文字と区切り文字を指定するオプションを備えています。

シェープファイルを Amazon Redshift にロードする

次の例は、COPY を使用して Esri シェープファイルをロードする方法を示しています。シェープファイルのロードについての詳細は、「シェープファイルを Amazon Redshift にロードする」を参照してください。

シェープファイルのロード

以下のステップは、COPY コマンドを使用して Amazon S3 から OpenStreetMap データを取り込む方法を示しています。この例では、ノルウェーのGeofabrik のダウンロードサイトから得たシェープファイルアーカイブが、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 シェープファイルを調べて、次のように適切なテーブルを作成します。

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 が表示されています。2 番目のレコードは、指定された許容値内でロードされました。ただし、最終的なサイズは、最大許容値を指定せずに自動的に計算された許容値を使用するよりも大きくなります。

圧縮シェープファイルからのロード

Amazon Redshift COPY は、圧縮されたシェープファイルからのデータの取り込みをサポートしています。すべてのシェープファイルコンポーネントには、同じ 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);

次に、列マッピングを使用してシェープファイルを取り込みます。

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.

Geogry 列を含めたデータのテーブルへのロード

GEOGRAPHY 列を含むテーブルがある場合は、まず、この列を GEOMETRY 列に取り込んだ上で、オブジェクトを GEOGRAPHY オブジェクトにキャストします。例えば、シェープファイルを 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;