Amazon Redshift에서 COPY 명령을 사용하는 동안 데이터 로드 오류를 해결하려면 어떻게 해야 합니까?

간략한 설명

STL_LOAD_ERRORS 테이블을 사용하여 플랫 파일 로드 중에 발생하는 데이터 로드 오류를 식별할 수 있습니다. STL_LOAD_ERRORS 테이블을 사용하면 데이터 로드 진행률을 추적하고 그 과정에서 모든 실패 또는 오류를 기록할 수 있습니다. 식별된 문제를 해결한 후 COPY 명령을 사용하는 동안 플랫 파일에서 데이터를 다시 로드합니다.

팁: COPY 명령을 사용하여 Parquet 형식으로 플랫 파일을 로드하는 경우 SVL_S3LOG 테이블을 사용할 수도 있습니다. SVL_S3LOG 테이블은 데이터 로드 오류를 식별하는 데 사용할 수 있습니다.

해결 방법

참고: 다음 단계는 도시 및 시설에 대한 예제 데이터 세트를 사용합니다.

1.    샘플 플랫 파일의 데이터를 확인하여 소스 데이터가 유효한지 확인합니다.

예:

7|BMO Field|Toronto|ON|0
16|TD Garden|Boston|MA|0
23|The Palace of Auburn Hills|Auburn Hills|MI|0
28|American Airlines Arena|Miami|FL|0
37|Staples Center|Los Angeles|CA|0
42|FedExForum|Memphis|TN|0
52|PNC Arena|Raleigh|NC  ,25   |0
59|Scotiabank Saddledome|Calgary|AB|0
66|SAP Center|San Jose|CA|0
73|Heinz Field|Pittsburgh|PA|65050

이 예제 demo.txt 파일에서는 파이프 문자로 구분된 5개의 필드가 사용됩니다. 자세한 내용은 파이프(기본 구분자)로 구분된 파일에서 LISTING 로드를 참조하세요.

2.    Amazon Redshift 콘솔을 엽니다.

3.    다음 DDL을 사용하여 샘플 테이블을 생성합니다.

CREATE TABLE VENUE1(
VENUEID SMALLINT,
VENUENAME VARCHAR(100),
VENUECITY VARCHAR(30),
VENUESTATE CHAR(2),
VENUESEATS INTEGER
) DISTSTYLE EVEN;

4.    STL_LOAD_ERRORS 테이블에서 관련 열을 미리 볼 보기를 작성합니다.

create view loadview as
(select distinct tbl, trim(name) as table_name, query, starttime,
trim(filename) as input, line_number, colname, err_code,
trim(err_reason) as reason
from stl_load_errors sl, stv_tbl_perm sp
where sl.tbl = sp.id);

이 보기는 데이터 로드 오류의 원인을 식별하는 데 도움이 될 수 있습니다.

5.    COPY 명령을 사용하여 데이터를 로드합니다.

copy Demo
from 's3://your_S3_bucket/venue/'
iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3'
delimiter '|' ;

참고: your_S3_bucket을 S3 버킷 이름으로 바꿉니다. 그런 다음, arn:aws:iam::123456789012:role/redshiftcopyfroms3를 AWS Identity and Access Management(IAM) 역할의 Amazon 리소스 이름(ARN)으로 바꿉니다. 이 IAM 역할은 S3 버킷에서 데이터에 액세스할 수 있어야 합니다. 자세한 내용은 파라미터를 참조하세요.

6.    로드 보기를 쿼리하여 테이블의 오류 로드 세부 정보를 표시하고 검토합니다.

testdb=# select * from loadview where table_name='venue1';
tbl | 265190
table_name | venue1
query | 5790
starttime | 2017-07-03 11:54:22.864584
input | s3://
your_S3_bucket/venue/venue_pipe0000_part_00
line_number | 7
colname | venuestate
err_code | 1204
reason | Char length exceeds DDL length

이 예제에서 예외가 발생한 원인은 venuestate 열에 추가해야 하는 길이 값입니다. (NC ,25 |) 값이 VENUESTATE CHAR(2) DDL에 지정된 길이보다 깁니다.

다음 두 가지 방법으로 이 예외를 해결할 수 있습니다.

• 데이터가 정의된 열 길이를 초과할 것으로 예상되는 경우 테이블 정의를 검토하고 업데이트하여 열 길이를 수정합니다.
  -또는-
• 데이터의 형식이 올바르지 않거나 변환되지 않은 경우 파일의 데이터를 수정하여 올바른 값을 사용합니다.

이 쿼리의 출력은 다음과 같은 중요한 정보를 포함합니다.

• 오류를 일으키는 파일.
• 오류를 일으킨 열
• 입력 파일 내의 라인 번호
• 예외 이유

7.    올바른 값을 사용하도록 로드 파일의 데이터를 수정합니다(길이는 정의된 열 길이에 맞춰 정렬되어야 함).

7|BMO Field|Toronto|ON|0
16|TD Garden|Boston|MA|0
23|The Palace of Auburn Hills|Auburn Hills|MI|0
28|American Airlines Arena|Miami|FL|0
37|Staples Center|Los Angeles|CA|0
42|FedExForum|Memphis|TN|0
52|PNC Arena|Raleigh|NC|0
59|Scotiabank Saddledome|Calgary|AB|0
66|SAP Center|San Jose|CA|0
73|Heinz Field|Pittsburgh|PA|65050

8.    데이터 로드를 다시 로드합니다.

testdb=# copy Demo
from 's3://your_S3_bucket/sales/'
iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3' delimiter '|' ;
INFO:  Load into table 'venue1' completed, 808 record(s) loaded successfully.

참고: STL_LOAD_ERRORS 테이블에는 제한된 양의 로그만 보관할 수 있습니다(일반적으로 약 4~5일). 또한 표준 사용자는 STL_LOAD_ERRORS 테이블을 쿼리할 때 자신의 데이터만 볼 수 있습니다. 모든 테이블 데이터를 보려면 슈퍼 사용자여야 합니다.

---

관련 정보

테이블 설계를 위한 Amazon Redshift 모범 사례

데이터 로딩을 위한 Amazon Redshift 모범 사례

문제 해결 데이터 로드를 위한 시스템 테이블

Amazon Redshift Advisor의 권장 사항 작업