COPY の例

注記

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

DynamoDB テーブルから FAVORITEMOVIES をロードする

AWS SDKs には、DynamoDBMovies という テーブルを作成する簡単な例が含まれています。(この例については、「 の開始方法DynamoDB」を参照してください)。 次の例では、Amazon Redshift テーブルから DynamoDBMOVIES テーブルにデータをロードします。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;

一時的認証情報を使用した LISTING のロード

次の例では、SESSION_TOKEN パラメータを使用して一時的セッション認証情報を指定します。

copy listing
from 's3://mybucket/data/listings_pipe.txt'
access_key_id '<access-key-id>'
secret_access_key '<secret-access-key'
session_token '<temporary-token>';

オプションを使用した 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 ignorecase' オプションを使用した JSON データからのロード
• ファイルを使用した JSON データからのロードJSONPaths
• ファイルを使用した JSON 配列からのロードJSONPaths

'auto' オプションを使用した JSON データからのロード

オプションを使用して JSON データからロードするには、JSON データが一連のオブジェクトで構成されている必要があります。'auto'キー名が列名と一致している必要がありますが、順序は関係ありません。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 データからのロード

オプションを使用して JSON データからロードするには、JSON データが一連のオブジェクトで構成されている必要があります。'auto ignorecase'キー名の大文字と小文字が列名と一致することは必要なく、順序は関係ありません。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';

ファイルを使用した JSON データからのロードJSONPaths

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

ファイルを使用した JSON 配列からのロードJSONPaths

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

トピック

• 'auto' オプションを使用した Avro データからのロード
• 'auto ignorecase' オプションを使用した Avro データからのロード
• ファイルを使用した Avro データからのロードJSONPaths

'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 データからのロード

引数を使用して Avro データからロードする場合、Avro スキーマのフィールド名の大文字と小文字が列名の大文字と小文字に一致する必要はありません。'auto ignorecase'引数を使用する場合、順序は関係ありません。'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';

ファイルを使用した Avro データからのロードJSONPaths

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

次の例では、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 コマンドを使用して OpenStreetMap から Amazon S3 データを取り込む方法を示しています。この例では、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_tlerance を使用すると、取り込みエラーが発生する可能性があります。この場合、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.