COPY の例
次の例では読みやすくするため、改行しています。credentials-args 文字列には改行やスペースを含めないでください。
トピック
- DynamoDB テーブルから FAVORITEMOVIES をロードする
- Amazon S3 バケットから LISTING をロードする
- Amazon EMR クラスターから LISTING をロードする
- マニフェストを使用し、データファイルを指定する
- パイプ区切りファイル(デフォルトの区切り記号)から LISTING をロードする
- Parquet 形式の列指向データを使用した LISTING のロード
- オプションを使用した EVENT のロード
- 固定幅のデータファイルから VENUE をロードする
- CSV ファイルから CATEGORY をロードする
- IDENTITY 列の明示的値を使用して VENUE をロードする
- パイプ区切りの GZIP ファイルから TIME をロードする
- タイムスタンプまたは日付スタンプのロード
- デフォルト値を使用してファイルのデータをロードする
- ESCAPE データを使用したデータのコピー
- JSON からのコピーの例
- Avro の例からのコピー
- ESCAPE オプションを指定する COPY 用のファイルの準備
- シェープファイルを Amazon Redshift にロードする
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 のダウンロードサイト.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;