COPY 예
참고
여기에서 설명하는 예는 가독성을 위해 줄 바꿈이 포함되었습니다. 실제 credentials-args 문자열에서는 줄 바꿈이나 공백을 입력하지 마십시오.
주제
- DynamoDB 테이블에서 FAVORITEMOVIES 로드
- Amazon S3 버킷에서 LISTING 로드
- Amazon EMR 클러스터에서 LISTING 로드
- 매니페스트를 사용하여 데이터 파일 지정
- 파이프로 구분된 파일(기본 구분자)에서 LISTING 로드
- Parquet 형식의 열 기반 데이터를 사용한 LISTING 로드
- ORC 형식의 열 기반 데이터를 사용한 LISTING 로드
- 옵션을 사용한 EVENT 로드
- 고정 폭 데이터 파일에서 VENUE 로드
- CSV 파일에서 CATEGORY 로드
- IDENTITY 열의 명시적인 값을 사용한 VENUE 로드
- 파이프로 구분된 GZIP파일에서 TIME 로드
- 타임스탬프 또는 데이트스탬프 로드
- 파일에서 기본값을 사용한 데이터 로드
- ESCAPE 옵션을 사용한 데이터 COPY 작업
- JSON에서 복사 예제
- Avro에서 복사 예제
- ESCAPE 옵션과 함께 COPY에 사용할 파일 준비
- Amazon Redshift에 shapefile 로드
- NOLOAD 옵션을 사용한 CPPY 명령
- 멀티바이트 구분 기호와 ENCODING 옵션을 포함한 COPY 명령
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://amzn-s3-demo-bucket/data/listing/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
Amazon EMR 클러스터에서 LISTING 로드
다음은 Amazon EMR 클러스터의 lzop 압축 파일에서 탭으로 구분된 데이터를 SALES 테이블과 함께 로드하는 예입니다. COPY는 myoutput/ 폴더에서 part-로 시작하는 모든 파일을 로드합니다.
copy sales from 'emr://j-SAMPLE2B500FC/myoutput/part-*' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '\t' lzop;
다음은 Amazon EMR 클러스터에서 JSON 형식 데이터를 SALES 테이블과 함께 로드하는 예입니다. COPY는 myoutput/json/ 폴더에서 모든 파일을 로드합니다.
copy sales from 'emr://j-SAMPLE2B500FC/myoutput/json/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' JSON 's3://amzn-s3-demo-bucket/jsonpaths.txt';
매니페스트를 사용하여 데이터 파일 지정
매니페스트를 사용하여 COPY 명령이 Amazon S3에서 필요한 파일을 모두, 즉 필요한 파일만 로드할 수 있습니다. 그 밖에 동일한 접두사를 공유하지 않는 다른 버킷이나 파일에서 다수의 파일을 로드해야 할 때도 매니페스트를 사용할 수 있습니다.
예를 들어 custdata1.txt, custdata2.txt, custdata3.txt 등 파일 3개를 로드해야 하는 경우 다음 명령을 사용하여 접두사를 지정함으로써 amzn-s3-demo-bucket에서 custdata로 시작하는 파일을 모두 로드할 수 있습니다.
copy category from 's3://amzn-s3-demo-bucket/custdata' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
실수로 파일이 2개만 존재하는 경우에는 COPY가 해당 파일 2개만 로드하고 명령을 성공적으로 마치지만 결과적으로는 불완전 데이터 로드가 됩니다. 불필요하지만 동일한 접두사를 사용하는 파일(예: custdata.backup 파일)이 버킷에 있다면 COPY가 해당 파일까지 로드하므로 결국 원하지 않는 데이터가 로드됩니다.
필요한 파일은 모두 로드하고 원하지 않는 파일은 로드되지 않도록 하려면 매니페스트 파일을 사용합니다. 매니페스트란 COPY 명령에서 처리할 파일이 나열된 JSON 형식의 파일을 말합니다. 예를 들어 다음 매니페스트는 위의 예에서 파일 3개를 로드합니다.
{ "entries":[ { "url":"s3://amzn-s3-demo-bucket/custdata.1", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/custdata.2", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/custdata.3", "mandatory":true } ] }
옵션인 mandatory 플래그는 파일이 없을 때 COPY의 종료 여부를 나타냅니다. 기본값은 false입니다. 필수 설정에 상관없이 파일이 검색되지 않으면 COPY가 종료됩니다. 이 예에서 파일 중 하나라도 검색되지 않으면 COPY가 오류를 반환합니다. 키 접두사만 지정하여 선택될 수도 있는 불필요한 파일(custdata.backup 등)은 매니페스트에 없기 때문에 무시됩니다.
ORC 또는 Parquet 형식의 데이터 파일에서 불러올 경우 다음 예에 나와 있는 것처럼 meta 필드가 필요합니다.
{ "entries":[ { "url":"s3://amzn-s3-demo-bucket1/orc/2013-10-04-custdata", "mandatory":true, "meta":{ "content_length":99 } }, { "url":"s3://amzn-s3-demo-bucket2/orc/2013-10-05-custdata", "mandatory":true, "meta":{ "content_length":99 } } ] }
다음 예는 cust.manifest라는 매니페스트를 사용합니다.
copy customer from 's3://amzn-s3-demo-bucket/cust.manifest' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as orc manifest;
매니페스트를 이용하면 다른 버킷, 다른 리전이나 접두사가 다른 파일에서 파일을 불러올 수 있습니다. 다음은 이름이 데이트스탬프로 시작하는 파일의 데이터를 로드하는 JSON 예입니다.
{ "entries": [ {"url":"s3://amzn-s3-demo-bucket/2013-10-04-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-05-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-06-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-07-custdata.txt","mandatory":true} ] }
매니페스트는 버킷이 클러스터와 동일한 AWS 리전에 속하는 한 다른 버킷에 있는 파일을 나열할 수 있습니다.
{ "entries": [ {"url":"s3://amzn-s3-demo-bucket1/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/custdata2.txt","mandatory":false} ] }
파이프로 구분된 파일(기본 구분자)에서 LISTING 로드
다음은 별도의 옵션 지정 없이 입력 파일에 기본 구분자인 파이프 문자('|')가 포함된, 매우 단순한 예입니다.
copy listing from 's3://amzn-s3-demo-bucket/data/listings_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';
Parquet 형식의 열 기반 데이터를 사용한 LISTING 로드
다음은 Amazon S3에서 parquet이라는 데이터를 로드하는 예입니다.
copy listing from 's3://amzn-s3-demo-bucket/data/listings/parquet/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as parquet;
ORC 형식의 열 기반 데이터를 사용한 LISTING 로드
다음은 Amazon S3에서 orc라는 데이터를 로드하는 예입니다.
copy listing from 's3://amzn-s3-demo-bucket/data/listings/orc/' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as orc;
옵션을 사용한 EVENT 로드
다음은 파이프로 구분된 데이터를 EVENT 테이블에 로드하면서 아래 규칙을 적용하는 예입니다.
-
인용 부호 쌍을 사용하여 문자열을 묶는 경우 인용 부호가 삭제됩니다.
-
빈 문자열과 공백이 포함된 문자열 모두 NULL 값으로 로드됩니다.
-
오류가 6회 이상 반환되면 로드가 실패합니다.
-
타임스탬프 값은 지정된 형식을 따라야 합니다. 예를 들어 유효한 타임스탬프는
2008-09-26 05:43:12입니다.
copy event from 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/data/venue_fw.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' fixedwidth 'venueid:3,venuename:25,venuecity:12,venuestate:2,venueseats:6';
위의 예는 데이터 파일이 샘플 데이터와 동일한 형식이라는 가정을 전제로 합니다. 아래 샘플에서는 모든 열이 명세에서 기록한 것과 동일한 폭을 갖도록 공백이 자리 표시자의 역할을 합니다.
1 Toyota Park Bridgeview IL0 2 Columbus Crew Stadium Columbus OH0 3 RFK Stadium Washington DC0 4 CommunityAmerica BallparkKansas City KS0 5 Gillette Stadium Foxborough MA68756
CSV 파일에서 CATEGORY 로드
다음과 같이 표에 기재된 값을 CATEGORY에 로드한다고 가정하겠습니다.
| catid | catgroup | catname | catdesc |
|---|---|---|---|
| 12 | Shows | Musicals | Musical theatre |
| 13 | Shows | Plays | All "non-musical" theatre |
| 14 | Shows | Opera | All opera, light, and "rock" opera |
| 15 | Concerts | Classical | All symphony, concerto, and choir concerts |
다음 예에서는 필드 값을 쉼표로 구분하여 텍스트 파일의 내용을 보여 줍니다.
12,Shows,Musicals,Musical theatre 13,Shows,Plays,All "non-musical" theatre 14,Shows,Opera,All opera, light, and "rock" opera 15,Concerts,Classical,All symphony, concerto, and choir concerts
쉼표로 구분된 입력을 지정하는 DELIMITER 파라미터를 사용하여 파일을 로드하면 일부 입력 필드에 쉼표가 포함되어 있기 때문에 COPY 명령이 실패합니다. 이러한 문제는 CSV 파라미터를 사용하고 쉼표가 포함된 필드를 인용 부호로 묶으면 피할 수 있습니다. 인용 부호가 다른 인용 부호로 묶인 문자열 내에 들어가는 경우에는 인용 부호를 이중으로 사용하여 이스케이프 처리해야 합니다. 기본 인용 부호가 큰따옴표이므로 큰따옴표를 추가하여 큰따옴표를 각각 이스케이프 처리해야 합니다. 새 입력 파일의 모양은 다음과 같습니다.
12,Shows,Musicals,Musical theatre 13,Shows,Plays,"All ""non-musical"" theatre" 14,Shows,Opera,"All opera, light, and ""rock"" opera" 15,Concerts,Classical,"All symphony, concerto, and choir concerts"
파일 이름이 category_csv.txt인 경우 다음 COPY 명령을 사용하여 파일을 로드할 수 있습니다.
copy category from 's3://amzn-s3-demo-bucket/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%
다음은 QUOTE AS를 사용하여 category_csv.txt를 로드하는 COPY 명령입니다.
copy category from 's3://amzn-s3-demo-bucket/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 파일에서 명시적인 값을 로드합니다. Amazon Redshift는 EXLICIT_IDS 옵션을 사용할 때 테이블에 중복된 IDENTITY 값이 로드되었는지 확인하지 않습니다.
copy venue from 's3://amzn-s3-demo-bucket/data/venue.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' explicit_ids;
파이프로 구분된 GZIP파일에서 TIME 로드
다음은 파이프로 구분된 GZIP 파일에서 TIME 테이블을 로드하는 예입니다.
copy time from 's3://amzn-s3-demo-bucket/data/timerows.gz' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' gzip delimiter '|';
타임스탬프 또는 데이트스탬프 로드
다음은 형식이 지정된 타임스탬프로 데이터를 로드하는 예입니다.
참고
HH:MI:SS 형식의 TIMEFORMAT 역시 SS를 넘어 마이크로초의 정밀도까지 소수 초를 지원합니다. 이번 예에서 사용하는 time.txt 파일에는 2009-01-12
14:15:57.119568 행 하나가 포함되어 있습니다.
copy timestamp1 from 's3://amzn-s3-demo-bucket/data/time.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' timeformat 'YYYY-MM-DD HH:MI:SS';
이 파일의 구조는 다음과 같습니다.
select * from timestamp1; c1 ---------------------------- 2009-01-12 14:15:57.119568 (1 row)
파일에서 기본값을 사용한 데이터 로드
다음은 TICKIT 데이터베이스에서 변형된 VENUE 테이블을 사용하는 예입니다. 이때 VENUE_NEW 테이블을 아래와 같은 문으로 정의한다고 가정합니다.
create table venue_new( venueid smallint not null, venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000');
아래 예와 같이 venue_noseats.txt 데이터 파일에 VENUESEATS 열의 값이 포함되어 있지 않다고 가정합니다.
1|Toyota Park|Bridgeview|IL| 2|Columbus Crew Stadium|Columbus|OH| 3|RFK Stadium|Washington|DC| 4|CommunityAmerica Ballpark|Kansas City|KS| 5|Gillette Stadium|Foxborough|MA| 6|New York Giants Stadium|East Rutherford|NJ| 7|BMO Field|Toronto|ON| 8|The Home Depot Center|Carson|CA| 9|Dick's Sporting Goods Park|Commerce City|CO| 10|Pizza Hut Park|Frisco|TX|
다음은 데이터 파일에서 테이블을 성공적으로 로드한 후 누락된 열에 DEFAULT 값('1000')을 적용하는 COPY 문입니다.
copy venue_new(venueid, venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/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|
동일한 테이블 정의를 사용하는 경우 VENUENAME에 대한 DEFAULT 값이 지정되지 않았고 VENUENAME이 NOT NULL 열이므로 다음 COPY 문이 실패합니다.
copy venue(venueid, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';
이제 IDENTITY 열을 사용하는 다른 형태의 VENUE 테이블이 있다고 가정합니다.
create table venue_identity( venueid int identity(1,1), venuename varchar(100) not null, venuecity varchar(30), venuestate char(2), venueseats integer not null default '1000');
앞의 예와 마찬가지로 원본 파일에 VENUESEATS 열에 해당하는 값이 없다고 가정하면 다음 COPY 문에서 사전 정의한 IDENTITY 데이터 값을 자동 생성하지 않고 포함하는 테이블을 성공적으로 로드합니다.
copy venue(venueid, venuename, venuecity, venuestate) from 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/data/venue_pipe.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|';
ESCAPE 옵션을 사용한 데이터 COPY 작업
다음은 구분자 문자(여기에서는 파이프 문자)와 일치하는 문자의 로드 방법을 나타낸 예입니다. 먼저 입력 파일에서 로드할 파이프 문자(|)가 모두 백슬래시 문자(\)를 사용해 이스케이프 처리되어 있는지 확인합니다. 그런 다음 ESCAPE 파라미터를 사용해 파일을 로드합니다.
$ more redshiftinfo.txt 1|public\|event\|dwuser 2|public\|sales\|dwuser create table redshiftinfo(infoid int,tableinfo varchar(50)); copy redshiftinfo from 's3://amzn-s3-demo-bucket/data/redshiftinfo.txt' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' delimiter '|' escape; select * from redshiftinfo order by 1; infoid | tableinfo -------+-------------------- 1 | public|event|dwuser 2 | public|sales|dwuser (2 rows)
ESCAPE 파라미터를 사용하지 않으면 이 COPY 문은 Extra column(s)
found 오류와 함께 중단됩니다.
중요
COPY를 ESCAPE 파라미터와 함께 사용하여 데이터를 로드하는 경우 역수 출력 파일을 생성하도록 UNLOAD 명령과 함께 ESCAPE 파라미터도 지정해야 합니다. 마찬가지로 ESCAPE 파라미터를 사용하여 UNLOAD하는 경우 동일한 데이터를 COPY할 때 ESCAPE를 사용해야 합니다.
JSON에서 복사 예제
다음 예에서는 다음 데이터와 함께 CATEGORY 테이블을 로드합니다.
| CATID | CATGROUP | CATNAME | CATDESC |
|---|---|---|---|
| 1 | 스포츠 | MLB | 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 데이터에서 로드
JSON 데이터에서 'auto' 옵션을 사용해 로드하려면 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://amzn-s3-demo-bucket/category_object_auto.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 'auto';
'auto ignorecase' 옵션을 사용하여 JSON 데이터에서 로드
JSON 데이터에서 'auto ignorecase' 옵션을 사용해 로드하려면 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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_object_paths.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_array_data.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/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 데이터에서 로드
Avro 데이터에서 'auto' 인수를 사용하여 로드하려면 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://amzn-s3-demo-bucket/category_auto.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as avro 'auto';
'auto ignorecase' 옵션을 사용하여 Avro 데이터에서 로드
Avro 데이터에서 'auto ignorecase' 인수를 사용하여 로드하려는 경우 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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_object_paths.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format avro 's3://amzn-s3-demo-bucket/category_path.avropath ';
ESCAPE 옵션과 함께 COPY에 사용할 파일 준비
다음 예에서는 ESCAPE 파라미터와 함께 COPY 명령을 사용하여 데이터를 Amazon Redshift 테이블에 가져오기 전에 줄 바꿈 문자를 "이스케이프" 처리하도록 데이터를 준비하는 방법을 설명합니다. 줄 바꿈 문자를 구분할 데이터를 준비하지 않으면 줄 바꿈 문자가 대개 레코드 구분자로 사용되므로 COPY 명령을 실행할 때 Amazon Redshift에서 오류를 로드합니다.
예를 들어 외부 테이블에 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로 로드할 수 있도록에서 2열 테이블을 생성했습니다. 첫 번째 열인 c1은 nlTest2.txt 파일에서 XML 형식의 내용을 저장할 문자 열입니다. 두 번째 열인 c2에는 동일한 파일에서 로드되는 정수 값이 저장됩니다.
sed 명령을 실행한 후에는 ESCAPE 파라미터를 사용하여 nlTest2.txt 파일의 데이터를 Amazon Redshift 테이블로 정확하게 로드할 수 있습니다.
참고
COPY 명령에 ESCAPE 파라미터를 추가하면 백슬래시 문자(줄 바꿈 문자 포함)를 포함하여 다수의 특수 문자가 이스케이프 처리됩니다.
copy t2 from 's3://amzn-s3-demo-bucket/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(Extract, Transform, Load) 도구들도 대부분 이스케이프 및 구분자 문자를 지정하는 옵션이 있습니다.
Amazon Redshift에 shapefile 로드
다음 예에서는 COPY를 사용하여 Esri shapefile을 로드하는 방법을 보여줍니다. shapefile 로드에 대한 자세한 내용은 Amazon Redshift에 shapefile 로드 섹션을 참조하세요.
shapefile 로드
다음 단계에서는 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 shapefile을 검사하고 다음과 같이 적절한 테이블을 생성합니다.
CREATE TABLE norway_water ( wkb_geometry GEOMETRY, osm_id BIGINT, code INT, fclass VARCHAR, name VARCHAR);
COPY 명령이 실행되면 오류가 발생합니다.
COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; ERROR: Load into table 'norway_water' failed. Check 'stl_load_errors' system table for details.
STL_LOAD_ERRORS 쿼리 결과 지오메트리가 너무 큽니다.
SELECT line_number, btrim(colname), btrim(err_reason) FROM stl_load_errors WHERE query = pg_last_copy_id(); line_number | btrim | btrim -------------+--------------+----------------------------------------------------------------------- 1184705 | wkb_geometry | Geometry size: 1513736 is larger than maximum supported size: 1048447
이를 극복하기 위해SIMPLIFY AUTO 파라미터가 COPY 명령에 추가되어 지오메트리를 단순화합니다.
COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989196 record(s) loaded successfully.
단순화된 행과 지오메트리를 보려면 SVL_SPATIAL_SIMPLIFY를 쿼리합니다.
SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+---------------------- 20 | 1184704 | -1 | 1513736 | t | 1008808 | 1.276386653895e-05 20 | 1664115 | -1 | 1233456 | t | 1023584 | 6.11707814796635e-06
자동 계산된 것보다 낮은 허용치로 SIMPLIFY AUTO max_tolerance를 사용하면 수집 오류가 발생할 수 있습니다. 이 경우 MAXERROR를 사용하여 오류를 무시합니다.
COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp' FORMAT SHAPEFILE SIMPLIFY AUTO 1.1E-05 MAXERROR 2 CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_water' completed, 1989195 record(s) loaded successfully. INFO: Load into table 'norway_water' completed, 1 record(s) could not be loaded. Check 'stl_load_errors' system table for details.
SVL_SPATIAL_SIMPLIFY를 다시 쿼리하여 COPY가 로드하지 않은 레코드를 식별합니다.
SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id(); query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance -------+-------------+-------------------+--------------+------------+------------+----------------- 29 | 1184704 | 1.1e-05 | 1513736 | f | 0 | 0 29 | 1664115 | 1.1e-05 | 1233456 | t | 794432 | 1.1e-05
이 예에서는 첫 번째 레코드가 맞지 않아 simplified 열이 false로 표시됩니다. 두 번째 레코드가 지정된 허용치 내에서 로드되었습니다. 그러나 최종 크기는 최대 허용치를 지정하지 않고 자동 계산된 허용치를 사용하는 것보다 큽니다.
압축된 shapefile에서 로드
Amazon Redshift COPY는 압축된 shapefile에서 데이터 수집을 지원합니다. 모든 shapefile 구성 요소에는 동일한 Amazon S3 접두사와 동일한 압축 접미사가 있어야 합니다. 예를 들어 이전 예의 데이터를 로드하려고 한다고 가정합니다. 이 경우 파일 gis_osm_water_a_free_1.shp.gz, gis_osm_water_a_free_1.dbf.gz, 및 gis_osm_water_a_free_1.shx.gz는 동일한 Amazon S3 디렉터리를 공유해야 합니다. COPY 명령에는 GZIP 옵션이 필요하고 FROM 절은 다음과 같이 올바른 압축 파일을 지정해야 합니다.
COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/compressed/gis_osm_natural_free_1.shp.gz' FORMAT SHAPEFILE GZIP CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully.
다른 열 순서로 테이블에 데이터 로드
첫 번째 열이 GEOMETRY가 아닌 테이블이 있는 경우 열 매핑을 사용하여 열을 대상 테이블에 매핑할 수 있습니다. 예를 들어 osm_id가 첫 번째 열로 지정된 테이블을 생성합니다.
CREATE TABLE norway_natural_order ( osm_id BIGINT, wkb_geometry GEOMETRY, code INT, fclass VARCHAR, name VARCHAR);
그런 다음 열 매핑을 사용하여 shapefile을 수집합니다.
COPY norway_natural_order(wkb_geometry, osm_id, code, fclass, name) FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp' FORMAT SHAPEFILE CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName'; INFO: Load into table 'norway_natural_order' completed, 83891 record(s) loaded successfully.
지오그래피 열이 있는 테이블에 데이터 로드
GEOGRAPHY 열이 있는 테이블이 있는 경우 먼저 열GEOMETRY 로 수집한 다음 객체를 GEOGRAPHY 객체로 캐스팅합니다. 예를 들어, shapefile을 GEOMETRY 열에 복사한 후 테이블을 변경하여 GEOGRAPHY 데이터 유형의 열을 추가합니다.
ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;
그런 다음 지오메트리를 지오그래피로 변환합니다.
UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;
선택적으로 GEOMETRY 열을 삭제할 수 있습니다.
ALTER TABLE norway_natural DROP COLUMN wkb_geometry;
NOLOAD 옵션을 사용한 CPPY 명령
실제로 데이터를 로드하기 전에 데이터 파일을 확인하려면 COPY 명령과 함께 NOLOAD 옵션을 사용합니다. Amazon Redshift Redshift는 입력 파일을 구문 분석하여 발생한 오류를 표시합니다. 다음 예제에서는 NOLOAD 옵션을 사용하며 실제로 테이블에 행이 로드되지 않습니다.
COPY public.zipcode1 FROM 's3://amzn-s3-demo-bucket/mydata/zipcode.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'us-east-1' NOLOAD CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/myRedshiftRole';Warnings: Load into table 'zipcode1' completed, 0 record(s) loaded successfully.
멀티바이트 구분 기호와 ENCODING 옵션을 포함한 COPY 명령
다음 예제는 멀티바이트 데이터가 포함된 Amazon S3 파일에서 LATIN1을 로드합니다. COPY 명령은 구분 기호를 8진수 형식(\302\246\303\254)으로 지정하여 ISO-8859-1로 인코딩된 입력 파일의 필드를 구분합니다. UTF-8에서 동일한 구분 기호를 지정하려면 DELIMITER '¦ì'를 지정합니다.
COPY latin1 FROM 's3://amzn-s3-demo-bucket/multibyte/myfile' IAM_ROLE 'arn:aws:iam::123456789012:role/myRedshiftRole' DELIMITER '\302\246\303\254' ENCODING ISO88591
