COPY 명령을 통한 데이터 적재 작업의 최적화 방안을 정리한다.

파일 분할

COPY 명령은 여러 파일에서 병렬로 데이터를 불러올 수 있기 때문에 파일을 분할하여 저장하면 좋다. AWS 공식 문서 데이터를 여러 파일로 분할의 내용을 요약하면 다음과 같다.

• 클러스터에 있는 조각 수의 배수가 되도록 파일을 분할해라

• 분할된 파일의 크기를 대략 비슷하게 만들어라

간단하게 dc2.large 인스턴스에서 파일 개수를 달리하면서 데이터 적재 속도를 비교해  보았는데, 그 결과는 아래와 같다. 참고로 dc2.large 인스턴스는 조각(Slice) 수가 2개이므로, 2의 배수로 파일을 분할하여 테스트를 진행했다.

테스트 환경

노드 유형: dc2.large

컴퓨팅 노드 수: 4

데이터 크기(원본): 1.6GB

아래 테스트 결과는 절대적이 아니며, 테스트 환경마다 달라질 수 있으므로 직접 테스트하여 최적의 속도를 보이는 파일 분할 수를 찾기를 권장한다.

정렬

여기서 하고자하는 이야기는 두 가지로 요약할 수 있다.

1. 먼저 COPY 명령을 이용해 읽을 데이터 원본을 미리 정렬해 놓는다.

2. 정렬 키(Sort key)를 이용한 정렬 작업이 최소화

먼저 COPY 명령으로 데이터가 어떻게 Redshift 테이블에 저장되는지 이해해야 한다.  Redshift 테이블은 정렬된 리전(Sorted region)과 정렬되지 않은 리전(Unsorted region)으로 나눠 데이터를 저장된다. 테이블에 데이터가 최초로 저장되거나 VACUUM 명령으로 테이블의 모든 데이터가 정렬된 상태를 유지할 경우 데이터는 모두 정렬된 리전에 저장되어 있다. 이 상태에서 COPY 명령으로 새로운 데이터가 테이블에 입력되면, 그 데이터는 VACUUM 명령이 실행될 때까지 정렬되지 않은 리전에 저장되어 있다. 정렬되지 않은 리전의 데이터가  읽기 성능을 하락시키는 원인이 되기도 한다.

그림 출처: 병합된 행의 볼륨 관리

위 그림에서 Unsorted Region 위쪽의 3행이 Sorted Region을 나타낸다. 대략적인 테이블의 데이터 저장 구조를 알았으니 하고자하는 두 가지 이야기를 진행할 수 있겠다.

데이터 원본을 미리 정렬

위 그림에서 COPY #1 예제를 보면 입력 데이터는 정렬되지 않았지만, COPY 명령 이후 테이블의 정렬되지 않은 리전에 저장된 모습을 보면 CUSTID로 정렬된 형태인 것을 알 수 있다. 이 이야기는 데이터는 리전은 분리되어 관리되지만, 각 리전의 데이터는 정렬된 형태라는 것이다. 즉, 새로 입력된 데이터도 기존 데이터(정렬된 리전)와 합치지지만 않을 뿐, 정렬된 형태로 저장된다. 따라서 COPY 명령에서 읽을 데이터 원본을 정렬 키로 미리 정렬해 놓으면 COPY 명령에서 정렬 작업을 안 하거나, 최소화 할 수 있을 것이다.

올바른 정렬 키 지정

앞 그림의 예제를 조금 바꿔보자.

입력 데이터

테이블 데이터

이 예제에서 확인할 수 있는 것은, 입력 데이터는 다 정렬되어 있고, 입력 데이터의 모든 정렬 키 값은 테이블에 먼저 입력된 값 보다 크다는 것이다. 쉽게 생각하면 새로 입력되는 데이터를 기존 데이터 뒤에 그냥 붙여 넣어도 문제 없이보인다. 실제로도 그렇게 동작할 수 있다. 이렇게 동작한다면 새로 입력되는 데이터도 정렬할 필요가 없으며, 새로 입력한 데이터를 기존 데이터와 합치기 위해서 정렬할 필요도 없을 것이다. 결국 COPY 명령을 수행하는 속도가 빨라진다.

AWS 문서에서도 다음을 만족하면 COPY 명령으로 입력된 데이터가 테이블의 정렬된 리전에 자동으로 추가된다고 한다.

• 테이블이 하나의 정렬 열에만 복합 정렬 키를 사용

• 정렬 열은 NOT NULL

• 테이블이 100% 정렬되어 있거나 비어 있음

• 모든 새 행은 삭제 대기 상태인 행을 포함하여 기존 행보다 정렬 순서가 더 높다. 이 인스턴스에서 Amazon Redshift는 정렬 키의 첫 8바이트를 사용하여 정렬 순서를 결정

Redshift의 기본 동작은 새로 입력된 데이터는 정렬되지 않은 리전에 저장되어 있다가, VACUUM  명령이 실행되면 그때 정렬된 리전의 데이터와 합쳐지기 된다.

외부에 저장된 데이터를 Redshift로 적재할 때 COPY 명령을 이용할 수 있다. COPY 명령은 데이터를 병렬로 적재하기 때문에 성능적인 이득과 자동으로 데이터 압축을 수행하는 등의 편리성을 제공하고 있다.

구문

COPY table_name [ column_list ]
FROM data_source
CREDENTIALS access_credentials
[options];

테이블 지정

COPY 명령으로 데이터가 적재될 대상 테이블 이름이다. 어디 테이블에 데이터를 입력할 것인지 지정한다. 괄호를 사용해 테이블의 열 목록을 지정할 수 있다. 테이블의 모든 열을 대상으로 데이터를 입력할 때는 생략해도 된다. 텍스트 파일로부터 데이터를 적재할 때는 텍스트 파일의 열 순서와 테이블의 열 순서가 동일하게 지정해야 한다.

예제

COPY customer;
COPY customer (c_custkey, c_name, c_address, c_city, c_nation, c_region, c_phone);

데이터 원본

COPY 명령으로 다음 데이터 저장소에 저장된 파일에 접근할 수 있다.

* 참고: 원격 호스트(SSH)는 연결 정보를 포함하는 매니페스트 파일을 S3에 저장하기 때문에 접두어를 S3로 표기했다.

S3, EMR, DynamoDB는 그 자체가 데이터 저장소라서 따로 설명할 필요는 없을 것 같다. SSH(Secure Shell)는 원격 호스트에 연결하여 표준 출력으로 출력되는 텍스트를 읽어와 테이블에 적재한다.

예제

COPY customer FROM 's3://awssampledbuswest2/ssbgz/customer'; 
COPY customer FROM 'emr://emrsampledbuswest2/ssbgz/customer/part-*'; 
COPY customer FROM 's3://mybucket/ssh_manifest';
COPY customer FROM 'dynamodb://CustomerList';

인증

SSH를 제외한 나머지 모든 데이터 저장소가 AWS에서 운영중인 서비스로 제한한 이유는 바로 인증 때문이라 생각한다. COPY 명령으로 지정한 데이터 원본 리소스에 접근하기 위한 인증 정보를 IAM 정보만 지정할 수 있다. 즉, SSH를 제외하면 AWS 외부 리소스는 접근할 수 없다.

역할 기반

역할 기반의 인증은 IAM(Identity and Access Managemen) 역할을 이용하는 것이다. IAM 역할은 쉽게 설명하면 Amazon Redshift가 접근할 수 있는 AWS 리소스 목록표라 생각하면 된다. 만약 S3에 데이터를 저장하고 이를 COPY 명령을 통해 불러오는 상황이라면, 접근할 수 있는 리소스에 S3를 선택만 하면 손쉽게 IAM 역할을 만들 수 있다. 이렇게 만들어진 IAM 역할의 이름(Amazon 리소스 이름(ARN))을 COPY 명령에 지정하여 사용한다. IAM 역할은 민감한 인증정보가 평문으로 노출되지 않아 권장되는 방법이다. IAM 역할을 만드는 방법은 IAM 역할 만들기를 참고하자.

예제

COPY customer 
FROM 's3://awssampledbuswest2/ssbgz/customer' 
IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>';

키 기반

키 기반 인증은 IAM 사용자의 액세스 키를 이용한다. 접근이 필요한 AWS 리소스에 권한을 가진 IAM 사용자를 만들고, 해당 IAM 사용자의 "보안 자격 증명"을 확인해 보면 액세스 키 ID(Access Key ID)와 보안 액세스 키(Secret Aaccess Key)를 확인할 수 있다. 이 정보를 COPY 명령에 함께 적어주면 된다. 키 기반의 인증은 Python과 같은 프로그램을 통해 AWS 리소스에 접근할 때 주로 쓰는 방법인데, 아무래도 민감한 인증 정보가 직접 노출되기 때문에 앞에서 나온 역할 기반의 인증을 사용하는 것을 Amazon에서는 강력하게 권장하고 있다. 아래 두 예제를 실행한 결과를 동일하다.

예제

COPY customer 
FROM 's3://...' 
ACCESS_KEY_ID '<access-key-id>'
SECRET_ACCESS_KEY '<secret-access-key>';

COPY customer 
FROM 's3://...' 
CREDENTIALS 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>';

데이터 포맷

COPY 명령을 사용할 때 데이터 포맷에 대한 정보를 지정하지 않으면 기본적으로 파이프 문자(|) 구분자로된 UTF-8 텍스트 형식으로 간주한다. 이를 변경하기 위해서는 적절한 데이터 포맷 정보를 지정해야 한다.

• CSV [ QUOTE [AS] 'quote_character' ]

텍스트 파일을 CSV 형식으로 저장한 경우 CSV 옵션을 사용한다. 추가로 QUOTE 옵션을 이용해 Quote Character를 지정하여 열 구분자가 값으로 나타나 텍스트 파일의 구조가 이상해지는 것을 방지할 수 있다.

COPY customer FROM 's3://...' CSV
COPY customer FROM 's3://...' CSV QUOTE AS '"' 

• DELIMITER

텍스트 파일의 열 구분자 문자를 지정한다.

COPY customer FROM 's3://...' DELIMITER '##'
COPY customer FROM 's3://...' DELIMITER '|'

• FIXEDWIDTH

고정 너비를 지정한다. 방법은 '컬럼이름(순번):길이, 컬럼이름(순번):길이, ...' 와 같이  고정 너비 스펙을 지정한다.

COPY customer FROM 's3://...' FIXEDWIDTH 'col1:10,col2:15,col3:3'
COPY customer FROM 's3://...' FIXEDWIDTH '0:10,1:15,2:3'

• AVRO

Avro 형식의 파일을 불러온다. 추가 옵션으로 Avro 파일의 열과 대상 테이블의 열 간의 매핑 방법을 지정할 수 있다. 'auto'는 Avro 파일과 대상 테이블간의 열 이름이 일치할 경우 사용한다. 일치하지 않는 경우라면, JSONPaths 파일을 이용해 열을 매핑해야 한다.

COPY customer FROM 's3://mybucket/customer.avro' AVRO 'auto'
COPY customer FROM 's3://mybucket/customer.avro' AVRO 's3://mybucket/customer_jsonpath.avropath'

• JSON

JSON 형식의 파일을 불러온다. Avro 파일과 비슷하게 'auto' 옵션을 사용하면 열 매핑이 자동으로 이루어 진다. 또한 JSONPaths 표현식 이용해 JSON 파일과 대상 테이블간의 열 매핑도 할 수 있다.

COPY customer FROM 's3://mybucket/customer.json' JSON 'auto'
COPY customer FROM 's3://mybucket/customer.json' JSON 's3://mybucket/customer_jsonpath.json'

• ORC / PARQUET

열 기반의 데이터 형식인 ORC 및 PARQUET 파일을 불러온다.

COPY customer FROM 's3://mybucket/customer/parquet' PARQUET
COPY customer FROM 's3://mybucket/customer/orc' ORC

옵션

필자가 주로 사용하는 기타 옵션을 소개한다. 더 많은 옵션은 데이터 변환 파라미터의 내용을 참고하자.

• 압축파일

gzip, lzop, bzip2 형식으로 압축된 파일은 압축을 풀지 않아도 바로 COPY 명령을 이용해 데이터를 적재할 수 있다.

COPY customer FROM 's3://...' CSV GZIP

• 자동 압축 인코딩

COPY 명령으로 데이터를 적재할 때 최적의 압축 입코딩을 자동으로 적용할 수 있다.

자세한 내용은 데이터 압축2 - 압축 수행을 참고하자.

COPY customer FROM 's3://...' CSV COMPUPDATE ON

• 통계 업데이트

데이터 잭재 후 통계 정보를 갱신한다.

COPY customer FROM 's3://...' CSV STATUPDATE ON

• 시간날짜 형식

입력 파일의 날찌/시간 형식을 지정할 수 있다. 더 자세한 형식 유형은 DATEFORMAT 및 TIMEFORMAT 문자열 혹은 DATEFORMAT 및 TIMEFORMAT 옵션의 자동 인식 사용을 참고하자.

COPY customer FROM 's3://...' CSV dateformat 'YYYY-MM-DD'
COPY customer FROM 's3://...' CSV timeformat 'YYYY-MM-DD HH:MI:SS.SSS'
COPY customer FROM 's3://...' CSV timeformat 'YYYY-MM-DDTHH:MI:SS'
COPY customer FROM 's3://...' CSV timeformat 'auto'

• 행 건너뛰기

파일에 열 이름과 같이 데이터가 아닌 부분이 포함되어 있다면 이를 무시할 수 있다.

COPY customer FROM 's3://...' CSV IGNOREHEADER 1

• 적재 테스트

데이터를 실제 적재하지 않고 파일의 유효성을 확인한다. 작업전 문제 확인 여부를 확인할 때 사용하면 좋다.

COPY customer FROM 's3://...' CSV NOLOAD