COPY の例
注記
次の例では読みやすくするため、改行しています。credentials-args 文字列には改行やスペースを含めないでください。
トピック
- DynamoDB テーブルから FAVORITEMOVIES をロードする
- Amazon S3 バケットから LISTING をロードする
- Amazon EMR クラスターから LISTING をロードする
- マニフェストを使用し、データファイルを指定する
- パイプ区切りファイル (デフォルトの区切り記号) から LISTING をロードする
- Parquet 形式の列指向データを使用した LISTING のロード
- 一時的認証情報を使用した LISTING のロード
- オプションを使用した EVENT のロード
- 固定幅のデータファイルから VENUE をロードする
- CSV ファイルから CATEGORY をロードする
- IDENTITY 列の明示的値を使用して VENUE をロードする
- パイプ区切りの GZIP ファイルから TIME をロードする
- タイムスタンプまたは日付スタンプのロード
- デフォルト値を使用してファイルのデータをロードする
- ESCAPE データを使用したデータのコピー
- JSON からのコピーの例
- Avro の例からのコピー
- ESCAPE オプションを指定する COPY 用のファイルの準備
DynamoDB テーブルから FAVORITEMOVIES をロードする
AWS SDK には、Movies という DynamoDB テーブルを作成する簡単な例が含まれています。(この例については、DynamoDB の使用開始を参照)。 次の例では、DynamoDB テーブルから Amazon RedshiftMOVIES テーブルにデータをロードします。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} ] }
バケットがクラスターと同じリージョンにある限り、マニフェストは異なるバケットにあるファイルをリストできます。
{ "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 | 表示 | ミュージカル | ミュージカル劇 |
| 13 | 表示 | Plays | すべての「ミュージカル以外」の劇 |
| 14 | 表示 | 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 パラメータを使用し、カンマを含むフィールドを引用符で囲みます。引用符で囲んだ文字列内に引用符がある場合、引用符を 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 | 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';
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 | 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';
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) ツールは、エスケープ文字と区切り文字を指定するオプションを備えています。
