VACUUM 명령은 다음의 기능을 수행한다.

기능

1. 정렬되지 않은 리전의 데이터 테이블 정렬

2. 삭제된 공간 회수

3. 인터리브 테이블 인덱스 재지정

세 가지 기능 모두다 성능과 많은 밀접한 관계가 있다. 정렬되지 않은 리전의 데이터 테이블 정렬은 SORT KEY를 통한 데이터 조회 최적화가 이루어 진다. Amazon Redshift는 삭제(갱신)된 데이터를 바로 지우지 않고 삭제된 행으로만 표시하고 그대로 두기 때문에 데이터를 실제로 지우고 줄어든 공간을 회수하는 작업도 VACUUM 명령을 통해 이루어진다.

구문

VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX ]

[ [ table_name ] [ TO threshold PERCENT ] ]

파라미터

• FULL, SORT ONLY, DELETE ONLY, REINDEX

VACUUM 명령이 수행하는 세 가지 기능을 선택적으로 수행할 수 있도록 파라미터가 제공된다. 파라미터마다 수행되는 기능을 정리하면 아래 표와같다.

• table_name: VACUUM 명령을 수행할 테이블 이름이다. 지정하지 않으면 데이터베이스의 모든 테이블에 대해 수행된다. 다만, REINDEX 파라미터를 사용할 때는 반드시 테이블 이름을 지정해야 한다.

• TO threshold PERCENT: 지정한 임계값 이상으로 테이블이 정렬되어 있다면 정렬 단계를 생략한다. 지정한 임계값 이상을 목표로 삭제된 공간을 회수한다. 기본값은 95

내 생각

1. 보통은 FULL로 돌려라. 시간이 너무 오래 걸리면, SORT ONLY, DELETE ONLY로 나눠 실행하면 좋을 듯

2. 인터리브 테이블의 경우, VACUUM REINDEX를 실행해야 한다. 다만, 엄청난 시간이 걸릴 수 있으므로 시간 제약을 고려해야 한다.

3. 테이블에 정렬되지 않은 큰 리전이 있는 경우, 전체 복사가 vacuum보다 훨씬 빠르다.

제약/주의사항

1. 클러스터당 한 번에 하나의 VACUUM 명령만 실행할 수 있다.

2. 실행중에 디스크 사용량이 (많이) 늘어날 수 있다.

3. 실행중에 쿼리 성능이 느려질 수 있다. 특히 COPY 같은 쓰기 작업은 피하자. 그리고 짧은 시간이지만 테이블이 단독 잠금에 걸릴 수 있다.

4. 뭔가 이상하다 싶으면 중단하자.

시스템 테이블

-- 정렬되지 않은 행 수 확인
select "table"
	, size
	, unsorted as unsorted_row_ratio
	, tbl_rows as total_rows
	, tbl_rows * unsorted * 0.01 as unsorted_rows
from svv_table_info

-- 삭제된 행 수 확인
select visible_rows, total_rows - visible_rows as deleted_rows
from
(
	select 
		(select sum(rows) from stv_tbl_perm where name = 'table_name') as total_rows
		, (select count(*) from table_name) as visible_rows 
) as rst

-- VACUUM 진행상황 확인
select * from svv_vacuum_progress

-- VACUUM 실행 이력
select * from svv_vacuum_summary

merge_increments bigint vacuum 작업의 병합 단계를 완료하는 데 필요한 병합 증분의 수.

>> merge_increments 열은 각각의 vacuum 작업을 위해 병합된 데이터 양을 표시

>> 연속적 vacuum 동안 병합 증분의 수가 테이블 크기 증가에 비례하여 증가하는 경우,

>> 기존의 정렬된 리전과 새로 정렬된 리전이 중첩되기 때문에 각각의 vacuum 작업이 다시 병합하는 이 테이블의 행이 증가하고 있음을 나타냄.

elapsed_time bigint Elapsed run time of the vacuum operation (in microseconds).

>> 실행 시간(마이크로 초)

row_delta bigint vacuum 전과 후의 테이블의 총 행 수의 차이.

>> 삭제된 행의 영향

sortedrow_delta bigint vacuum 전과 후의 정렬된 테이블 행 수의 차이.

>> 정렬되지 않은 리전의 행 + 삭제된 행

block_delta integer vacuum 전과 후의 테이블 블록 수의 차이.

정렬 키에서 지정한 열의 순서로 데이터는 정렬된 상태로 테이블에 저장된다. 데이터를 정렬된 형태로 저장하면 읽어야할 데이터를 줄임으로써 쿼리

성능을 향상시킬 수 있다. 예를 들어, 고객번호를 정렬 키로 데이터를 저장한 상태에서, 고객번호 30에서 50 사이의 고객 정보를 조회하고자 하는 상황이라고 하자. 이때 데이터를 읽어가다 고객 번호가 50보다 큰 데이터가 나타나기 시작하면, 나머지 데이터를 다 읽지 않아도 읽기 작업을 멈춰도 된다. 이렇게 읽어야할 데이터를 줄여 성능 향상을 기대할 수 있게된다(실제론 이렇게 동작하지 않는다. 이해를 높기위한 설명으로 실제 동작 방식은 아래에서 정리한다).

일반적인 OLTP 워크로드를 처리하는 관계형 데이터베이스와 달리 Amazon Redshift는 인덱스의 개념이 없기 때문에, 정렬 키는 거의 유일하게 읽어야할 데이터를 줄여줄 수 있는 수단이다. 그래서 성능 관점에서 봤을 때 가장 중요한 몇 가지 요소 중 하나라고 생각한다. 잘만 쓰면 성능을 몇 배로 높일 수 있다. 특히 대용량 테이블에서는 그 효과는 더욱 커진다.

여기서는 정렬 키의 종류와 내부 구조에 대한 내용을 정리한다.

복합(Compound) 정렬 키

그림 출처: Redshift Sort Keys – Choosing Best Sort Style

복합 정렬 키는 엑셀에서의 데이터 정렬을 생각하면 된다. 정렬 키로 지정한 첫 번째 열로 먼저 정렬을 한다. 그리고 첫 번째 열 중 같은 값들은 두 번째 열로 다시 정렬하여 저장한다. Amazon Redshift는 열 단위로 데이터를 1MB 블록으로 저장하는데, 각 블록의 최소, 최대 값을 저장해 놓고 해당 블록을 읽을지 말지를 결정한다. 앞의 고객번호 예제를 이야기 하지면, 정확하게는 고객번호가 30에서 50사이의 블록만 읽으면 읽기 작업을 완료할 수 있다.

위 그림의 예제는 c_customer_id와 c_country_id 순서로 복합 정렬 키를 만들었을 때 블록을 그린것이다. 여기서 c_customer_id = 1인 데이터를 요청하면, 첫 번째 블록만 읽어서 사용자 요청에 응답할 수 있다. 나머지 데이터는 안 읽어도 되므로 처리 시간을 단축할 수 있는 것이다. 하지만 두 번째 정렬 키인 c_country_id로는 읽어야할 데이터를 줄일 수가 없다. 만약 c_country_id  = 1인 데이터를 요청하면, 4개의 블록을 모두 읽어야 되므로 성능적 이득은 없다.

이상 복합 정렬 키의 특징을 정리해보면 다음과 같다.

• WHERE 절에 정렬 키의 첫 번째 열로 데이터를 제한하는 부분이 있으면 읽을 데이터를 줄여준다

• 정렬 키의 첫 번째 열의 카디널리티 높으면 좋다.

• WHERE 절에 정렬 키의 첫 번째 열이 없다면 읽어야 할 데이터를 줄여주지 못 한다

• GROUP BY, ORDER BY 와 같이 정렬이 필요한 작업에 이점이 있다

인터리브(Interleaved) 정렬 키

그림 출처: Redshift Sort Keys – Choosing Best Sort Style

인터리브 정렬 키는 한마디로 뭐다라고 표현하기가 참 힘들다. 많은 문서에서 대부분 다음과 같이 설명한다. 먼저 살펴보자.

인터리브 정렬 키는 정렬 키에서 각 열, 즉 열의 하위 집합에 똑같은 가중치를 부여합니다 -  Amazon Redshift 정렬 키 선택

인터리브 정렬 키를 정확하게 이해하기 위해서는 z order curve  혹은 z order index 이런 것들을 공부하면 보다 그 구조를 잘 알 수 있게된다. 자세한 내용은 추후에 정리해 보기로 하고, 여기서는 앞의 그림을 통해 인터리브 정렬 키는 어떻게 읽을 데이터를 줄여가는지 살펴보고, 어떤 상황에서 인터리브 정렬 키를 쓰면될지 정리해보자.

먼저 복합 정렬 키와 인터리브 정렬 키의 가장 큰 차이는 첫 번째 정렬 키 열이라도 동일한 값이 여러 데이터 블록에 나뉘어 저장된다는 것이다. 대신 두 번째 정렬 키 열로도 같은 가중치로 데이터 블록이 나뉘어 저장된다. 쉽게 이야기하면, 첫 번째 정렬 키의 일부분과 두 번째 정렬 키의 일부분 부분의 조합으로 데이터 블록을 구성한다. 그래서 정렬 키의 첫 번째 열이든, 두 번째 열이든 상관없이 읽어야할 데이터를 줄여줄 수 있다.

몇 가지 예제로 조건별 읽어야 할 데이터 블록의 위치를 살펴보자. 여기 예제에서도 동일하게 c_customer_id 열과 c_country_id 열의 순서로 인터리브 정렬 키를 정의했다고 가정한다.

WHERE c_customer_id = 3 AND c_country_id  = 104

WHERE c_customer_id = 3

WHERE c_country_id  = 104

이렇게 살펴본바로 인터리브 정렬 키의 가장 큰 특징은 정렬 키를 정의할 때 선언한 두 번째 열로도 읽어야할 데이터를 줄일 수 있다는 것이다.

특징

• 정렬 키를 이루는 열 순서에 상관없이 WHERE 절에 나타나면 잘 동작한다.

• GROUP BY, ORDER BY 이점이 없다(전체 블록을 다 읽어 정렬해봐야 한다).

• VACUUM 작업시 실행 시간이 (매우) 길어질 수 있다.

예제

-- 단일 열에 복합 정렬 키
create table customer
(
  c_custkey     	integer        not null sortkey,
  c_name        	varchar(25)    not null,
  c_address             varchar(25)    not null,
  c_city        	varchar(10)    not null,
  c_nation      	varchar(15)    not null,
  c_region      	varchar(12)    not null,
  c_phone       	varchar(15)    not null,
  c_mktsegment          varchar(10)    not null
)

-- 여러 열에 복합 정렬 키
create table customer_comp
(
  c_custkey     	integer        not null,
  c_name        	varchar(25)    not null,
  c_address             varchar(25)    not null,
  c_city        	varchar(10)    not null,
  c_nation      	varchar(15)    not null,
  c_region      	varchar(12)    not null,
  c_phone       	varchar(15)    not null,
  c_mktsegment          varchar(10)    not null
)
COMPOUND SORTKEY (c_custkey, c_name)

-- 여러 열에 인터리브 정렬 키
create table customer_inter
(
  c_custkey     	integer        not null,
  c_name        	varchar(25)    not null,
  c_address             varchar(25)    not null,
  c_city        	varchar(10)    not null,
  c_nation      	varchar(15)    not null,
  c_region      	varchar(12)    not null,
  c_phone       	varchar(15)    not null,
  c_mktsegment          varchar(10)    not null
)
INTERLEAVED SORTKEY (c_custkey, c_name, c_phone)

정렬 키 정보(시스템 테이블)

select * from SVV_TABLE_INFO where "table" in ('customer_inter','customer_comp', 'customer')
select * from PG_TABLE_DEF where tablename in ('customer_inter','customer_comp', 'customer')

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

analyze compression

analyze compression 명령은 기존 테이블의 최적의 압축 인코딩을 찾는데 사용한다. analyze compression 명령을 실행하면 각 열의 최적의 압축 인코딩 유형과 압축률을 표시해 준다. 하지만 copy 명령과 달리 직접 테이블에 압축 인코딩을 적용하지 않는다.

analyze compression 명령 예제

-- 테이블의 모든 열에 대해 조사
analyze compression customer  

-- 테이블의 일부 열에 대해 조사
analyze compression customer  (c_city,c_nation,c_region)

-- 테이블의 일부 열에 대해 조사
analyze compression customer comprows 100

앞에서 만든 raw 인코딩의 customer 테이블에 analyze compression 명령을 수행한 결과는 다음과 같다.

Encoding 열은 압축률이 가장 좋은 최적의 압축 인코딩 유형이고, Est_reduction_pct값은 압축률을 뜻한다. 압축률이 75(%)이라면 원본의 크기가 100이라면 압축 후 25로 줄어든다는 이야기다.

analyze compression 명령의 결과대로 압축 인코딩을 적용하기 위해서는 테이블을 새로 만들어야 한다. 다음은 analyze compression 명령의 결과대로 압축 인코딩을 지정한 CREATE TABLE 문이다.

create table customer_new
(
c_custkey		integer not null sortkey encode raw,
c_name			varchar(25) not null encode zstd,
c_address		varchar(25) null encode zstd,
c_city			varchar(10) null encode bytedict,
c_nation		varchar(15) null encode bytedict,
c_region		varchar(12) null encode bytedict,
c_phone			varchar(15) null encode zstd,
c_mktsegment		varchar(10) not null encode bytedict
);

Amazon Redshift Column Encoding Utility

Amazon에서 만든 유틸리티를 소개하고자 한다. Amazon Redshift Column Encoding Utility는 analyze compression 명령의 결과에서 얻은 최적의 인코딩 유형대로 새 테이블을 만들어주는 SQL 스크립트를 만들어준다. 뿐만 아니라, 기존 테이블의 sortkey, distkey, primary key, foreign key 등도 모두 동일하게 만들 수 있도록 스크립트가 만들어 진다.

파이썬으로 작성된 이 유틸리티의 기본 사용법은 다음과 같다.

python analyze-schema-compression.py --db db_name --db-user username --db-pwd password --db-host redshift_address

이렇게 실행하면 데이터베이스 안에 존재하는 모든 테이블에 대해 조사와 스크립트를 생성이 이루어진다. 다음은 앞에서 만든 customer 테이블에 대해 유틸리티를 실행한 결과이다.

> python analyze-schema-compression.py --db db_name --db-user user_name --db-pwd password --db-host host --analyze-table customer

-- [36712] [36712] Running set statement_timeout = '1200000'

-- [36712] Success.

-- [36712] [36712] Running set application_name to 'ColumnEncodingUtility-v.9.3.4'

-- [36712] Success.

-- [36712] [36712] Running set search_path to '$user', public, public;

-- [36712] Success.

-- [36712] Connected to tendw-test.c9ucj9e4yn8e.ap-northeast-2.redshift.amazonaws.com:5439:tendw as redsa

-- [36712] Analyzing Table 'customer' for Columnar Encoding Optimisations with 1 Threads...

-- [36712] Analyzing 1 table(s) which contain allocated data blocks

-- [36712] Table public.customer contains 7 unoptimised columns

-- [36712] Analyzing Table 'public.customer'

-- [36712] Column Encoding will be modified for public.customer

-- [36712] No encoding modifications run for public.customer

begin;

lock table public."customer";

create table public."customer_$mig"(

"c_custkey" integer  NOT NULL encode RAW

,"c_name" varchar(25)  NOT NULL encode zstd

,"c_address" varchar(25)   encode zstd

,"c_city" varchar(10)   encode bytedict

,"c_nation" varchar(15)   encode bytedict

,"c_region" varchar(12)   encode bytedict

,"c_phone" varchar(15)   encode zstd

,"c_mktsegment" varchar(10)  NOT NULL encode bytedict

)

SORTKEY(c_custkey)

;

alter table public."customer_$mig" owner to redsa;

insert into public."customer_$mig"  select * from public."customer" order by "c_custkey";

analyze public."customer_$mig";

alter table public."customer" rename to "customer_20180802_jqZ9ADTiEZ_$old";

alter table public."customer_$mig" rename to "customer";

commit;

-- [36712] Performed modification of 1 tables

-- [36712] Processing Complete

테이블(열)에 데이터 압축을 적용하는 방법은 두 가지가 있다. 테이블에 처음으로 데이터를 로드할 때 COPY 명령을 이용해 자동으로 인코딩 형식을 지정하도록 하는 방법과 CREATE TABLE 문으로 직접 인코딩 형식을 지정하는 방법이 있다. 여기서 권장되는 방법은 COPY 명령을 이용하는 것이다.

COPY 명령

COPY 명령으로 빈 테이블에 데이터가 적재될 때 자동으로 최적의 인코딩 형식이 적용된다. 이와 관련된 옵션은 다음과 같은 것들이 있다.

• COMPUPDATE [ON | OFF]

압축 인코딩을 자동으로 적용할지 여부를 결정한다.  COMPUPDATE 옵션을 생략했을 때는 테이블에 데이터가 없는 상태에서 테이블에 인코딩이 지정되지 않은 경우에 한해 자동으로 압축이 적용된다. COMPUPDATE 옵션을 ON으로 지정하면, 빈 테이블일 경우에 한해 이미 인코딩 유형이 지정되어 있어도 이를 무시하고 자동으로 최적의 인코딩 형식이 적용된다.

• COMPROWS numrows

최적의 압축 인코딩을 결정하기 위해 조사할 데이터의 행 수를 지정한다. 원리는 비교적 간단하다. 여기서 지정한 행 수 만큼을 불러와 가능한 모든 인코딩 형식으로 압축해 보고, 가장 압축률이 좋은 인코딩 형식을 선택하게 된다.

행 수를 지정하지 않으면 조각(Slice) 마다 100,000개의 행이 적용된다. 행 수를 지정하면, 지정한 행 수를 총 조각수 만큼 나눈 숫자만큼 각 조각이 이용한다. 예를 들어, 클러스터에 총 4개의 조각이 있을 때, 행 수를 1,000,000으로 지정하면 각 조각마다 250,000개의 행을 이용한다.AWS Redshift 클러스터 유형별 조각수는 Amazon Redshift 클러스터 - 노드 유형에서 확인할 수 있다.

이제 실제 테이블을 만들어 COPY 명령을 이용한 예제를 살펴보자. 먼저 명시적으로 Raw 인코딩으로 테이블을 하나 생성하고, 별도로 인코딩 유형을 지정하지 않아 기본 인코딩 유형이 지정되도록 테이블을 하나 만들어 보자.

-- 명시적으로 압축하지 않도록 Raw 인코딩을 지정
create table customer
(
c_custkey		integer not null sortkey encode raw,
c_name			varchar(25) not null encode raw,
c_address		varchar(25) null encode raw,
c_city			varchar(10) null encode raw,
c_nation		varchar(15) null encode raw,
c_region		varchar(12) null encode raw,
c_phone			varchar(15) null encode raw,
c_mktsegment		varchar(10) not null encode raw
);

-- 테이블을 생성할 때 압축 인코딩을 지정하지 않아 기본 인코딩 유형이 선택된다.
create table customer_compressed
(
c_custkey		integer not null sortkey,
c_name			varchar(25) not null,
c_address		varchar(25) null,
c_city			varchar(10) null,
c_nation		varchar(15) null,
c_region		varchar(12) null,
c_phone			varchar(15) null,
c_mktsegment		varchar(10) not null
);

이제 앞에서 만든 두 테이블의 열들의 압축 인코딩을 확인하기 위해 pg_table_def  시스템 카달로그 테이블을 쿼리한다. 쿼리 결과에서 encoding 열의 값을 확인하면 압축 인코딩 유형을 알 수 있다. 결과를 보면 명시적으로 Raw 인코딩을 지정한 열들은 encoding 열 값이 none 으로 나타나 압축을 하지 않고 있음을 알 수 있다. customer_compressed 테이블의 경우 기본 인코딩 유형이 선택 되었음을 확인할 수 있다.

-- 테이블의 인코딩 유형을 확인한다.
select tablename, "column", type, encoding 
from pg_table_def 
where tablename in('customer','customer_compressed');

앞에서 만든 두 테이블에 COPY 명령을 이용해 데이터를 적재하여 보자. customer 테이블은 compupdate off 옵션을 이용해 자동 압축이 일어나지 않아 압축되지 않은 형태로 데이터를 저장한다. 반면에 customer_compressed 테이블은 compupdate on 옵션을 이용해 최적의 압축 인코딩을 찾도록 했다.

-- 각 테이블에 데이터를 로드한다. 아래 데이터는 AWS 계정이 있으면 누구나 접근할 수 있다.
copy customer from 's3://awssampledbuswest2/ssbgz/customer' 
credentials 'aws_access_key_id=*************;aws_secret_access_key=*************'
gzip
compupdate off  -- 압축 인코딩을 자동으로 수행하지 않는다.
region 'us-west-2';

copy customer_compressed from 's3://awssampledbuswest2/ssbgz/customer' 
credentials 'aws_access_key_id=*************;aws_secret_access_key=*************'
gzip
compupdate on  -- 압축 인코딩을 자동으로 수행한다.
region 'us-west-2';

데이터 적재가 완료되고 압축 인코딩의 변화를 살펴보기 위해 다시한번 pg_table_def  시스템 카달로그 테이블을 쿼리한다. 결과를 보면 customer_compressed 테이블의 압축 인코딩 유형이 처음과 바뀐것을 알 수 있다.

select tablename, "column", type, encoding 
from pg_table_def 
where tablename in('customer','customer_compressed');

마지막으로 두 테이블과 두 테이블의 각 열들의 크기(MB)를 비교해 보자.

-- 테이블의 크기를 비교해 보자.
select "table", size
from svv_table_info
where "table" in('customer','customer_compressed');

-- 열 크기를 비교해 보자.
select p.name, b.col, count(*)
from stv_blocklist b
	join stv_tbl_perm p on b.tbl = p.id and b.slice = p.slice
where p.name in ('customer', 'customer_compressed')
	and b.col < 8
group by p.name, b.col
order by 1,2

테이블은 대략 절반 정도로 줄어 들었으며, 4번째 열(col=3)인 c_city 열은 원본이 46MB에서 6MB로 줄어 원본대비 13%로 줄어들었다.

인코딩 유형 지정

CREATE TABLE 문으로 테이블을 생성하거나 ALTER TABLE 문으로 새로운 열을 추가할 때 인코딩 유형을 지정할 수 있다. 다만 이미 데이터가 있는 테이블이려면 인코딩 유형을 변경할 수 없다. 다음은 cusomter 테이블을 만들고, 나이(age) 열을 추가할 때 인코딩 유형을 지정하는 예제이다.

create table customer 
(
c_custkey integer not null encode delta,
c_name varchar(25) not null encode zstd,
c_address varchar(25) null encode zstd,
c_city varchar(10) null encode bytedict,
c_nation varchar(15) null encode bytedict,
c_region varchar(12) null encode bytedict,
c_phone varchar(15) null encode zstd,
c_mktsegment varchar(10) not null encode bytedict
);

alter table customer
add column c_age int null encode mostly8;

데이터 압축은 저장 공간과 읽어야 할 데이터를 줄여준다. 따라서 특별한 이유가 없다면 데이터를 압축하도록 구성하는 것이 일반적이다.  Amazon Redshift에서는 열 단위로 압축 유형(인코딩)을 지정하기 때문에 열에 포함된 데이터의 특성에 맞게 압축 유형을 지정해야 한다. 그러기 위해 Amazon Redshift의 압축 유형을 먼저 정리해 본다.

Raw

데이터를 압축하지 않고 원본 그대로 저장한다. 정렬 키(SORTKEY) 및 BOOLEAN, REAL, DOUBLE PRECISION 데이터 형식의 열에서 압축 인코딩 유형을 지정하지 않으면 기본적으로 Raw 인코딩이 선택된다.

• CREATE TABLE 키워드: raw

• 적용 가능한 데이터 형식: 모든 데이터 형식

Byte Dictionary

256개의 사전을 만들어, 원래 값을 사전(딕셔너리)의 순번(인덱스)으로 대체하여 저장한다. 사전의 순번은 1바이트를 차지한다. 따라서 반복적으로 나타나는 값이 적을 때 유리하다. 예를 들어, 다음과 같이 시도를 나타내는 열이 있을 때 압축전후 크기를 비교해 보자.

데이터를 압축하지 않았을 때는 원본 크기 항목의 총 합인 135 바이트를 차지한다. 하지만 Byte Dictionary 압축을 하게되면 데이터의 크기가 사전의 크기 + (1바이트 x 행수)로 줄어든다. 위 예제에서 사전은 서울특별시(15), 경기도(9), 전라북도(12), 제주도(9)로 구성되어 크기가 45 바이트이다. 그리고 원래 데이터는 모두 사전의 순번으로 대체되므로 1바이트만 차지하게 된다. 따라서 최종 압축된 크기는 45 + (1 x 12) 이렇게 계산을 하여 57 바이트가 된다.

만약 단어의 수가 256개를 초과할 경우, 초과되는 값은 압축되지 않은 형태로 저장된다.

• CREATE TABLE 키워드: bytedict

• 적용 가능한 데이터 형식: BOOLEAN을 제외한 모든 형식

Delta

Delta 인코딩은 인접한 값의 차이만 저장하는 식으로 저장 공간을 줄인다. 큰 숫자인 1,000,000,000과 1,000,000,001이 있다고 할 때, 이 두 값을 압축하지 않고 저장하면 각각 4바이트가 필요하다.

여기서 첫 번째 값 1,000,000,000을 기준(플래그)이라고 할 때, 두 번째 값은 기준과의 차이(델타)인 1만 증시키면 얻을 수 있다. 그래서 두 번째 값은 큰 값 대신에 기준과 차이인 1만 저장하게 되고, 그 크기는 1바이트로 줄일 수 있게 된다. 그래서 Delta 인코딩은 날짜/시간 매우 유용하다고 한다. 절대 날짜/시간 값이 아닌, 이전과의 날짜/시간의 차이만 저장하면 되기 때문이다.

세부적으로는 Delta 인코딩은 차이의 크기를 1바이트로 저장하고, Delta32k  인코딩은 차이의 크기를 2바이트로 저장하는 것으로 구분할 수 있다.

AWS 공식 문서의 예제를 살펴보는 것도 이해에 도움이 된다.

• CREATE TABLE 키워드: delta, delta32k

• 적용 가능한 데이터 형식: SMALLINT, INTEGER, BIGINT, NUMERIC, DATE, TIMESTAMP

LZO

LZO 인코딩은 LZO 압축 알고리즘을 사용하여 문자열 형식인 CHAR 및 VARCHAR 열에서 유용하다. LZO는 정렬 키로 지정되는 열과 BOOLEAN, REAL 또는 DOUBLE PRECISION 데이터 형식으로 정의되는 열을 제외하고 기본 인코딩이다.

LZO 압축 알고리즘에 대한 추가 정보는 여기서 확인할 수 있다.

• CREATE TABLE 키워드: lzo

• 적용 가능한 데이터 형식: REAL, DOUBLE PRECISION, BOOLEAN을 제외한 모든 형식

Mostly

Mostly 인코딩은 정수형 데이터 형식에서 대부분의 값이 주어진 최대 크기보다 작은 값이 저장될때 유용하다. 만약 INT 형식의 열에 가끔 10억 단위의 큰 값이 들어오지만, 대부분 1~10 사이의 값이 저장되는 경우가 여기에 해당된다.

Mostly 인코딩은 범위에 따라 Mostly8, Mostly16, Mostly32로 나뉜다. 주어진 범위를 벗어나는 값은 원본 그대로 저장하고 범위 안의 값이라면 8비트(1바이트), 16비트(2바이트), 32비트(바이트) 중 하나의 크기로 저장된다.

예를 들어, INT 형식에 Mostly8 인코딩을 지정한 경우에서 12는 1바이트, 3000은 4바이트(Mostly8 인코딩의 범위를 벗어나기 때문에) 를 차지한다.

Mostly8, Mostly16, Mostly32 구체적 범위는 아래와 같다.

• CREATE TABLE 키워드: mostly8, mostly16, mostly32

• 적용 가능한 데이터 형식:

• Mostly8: SMALLINT, INTEGER, BIGINT, NUMERIC

• Mostly16: INTEGER, BIGINT, NUMERIC

• Mostly32: BIGINT, NUMERIC

Runlength

Runlength 인코딩은 연속적으로 나타나는 값을 그 값과 발생 횟수로 구성된 토큰 하나로 대체한다. 예를 들어, Blue, Blue, Blue, Blue, Red, Red, Green, Green, Green 이렇게 값이 연속적으로 나타난다면, 이 값은 (4, Blue), (2, Red), (3, Green) 이런 토큰으로 대체하여 공간을 절약할 수 있다. 여기서 (4, Blue) 토큰은 Blue가 4번 연속적으로 나타났음을 말한다.

• CREATE TABLE 키워드: runlength

• 적용 가능한 데이터 형식: 모든 데이터 형식

Text255 및 Text32k

Text255 및 Text32k 인코딩은 Byte Dictionary 인코딩과 유사하게 단어를 사전에 등록하고 원래 단어를 사전의 순번으로 대체하여 공간을 줄여준다. Byte Dictionary와 다른점은 열에 있는 값 전체를 하나로 사전에 등록하는 것이 아니라, 각 단어를 개별로 사전에 등록한다. Text255 인코딩은 디스크 블록마다 먼저 나타나는 단어 245개를 사전에 등록한다. Text32k 인코딩은 사전의 크기가 약 32k에 도달할 때까지 단어를 등록한다. 사전에 없는 나머지 단어는 원본 그대로 저장한다.

• CREATE TABLE 키워드: text255, text32k

• 적용 가능한 데이터 형식: VARCHAR

Zstandard

Zstandard 인코딩은 페이스북에서 Zip을 대체할 목적으로 공개한 Zstandard 압축  알고리즘이다. 개인적인 경험으로는 텍스트 형식 데이터의 경우 lzo 보다 압축률이 좋아 보였다. 알고리즘에 대한 자세한 내용은 ZDNet의 “페이스북, ZIP 대체할 압축기술 소스코드 공개” 기사를 참고하자.

• CREATE TABLE 키워드: zstd

• 적용 가능한 데이터 형식: 모든 데이터 형식

Power BI에서 Amazon Redshift에 연결하기 위해서는 AWS 보안그룹에서 Power BI쪽 서버들의 아이피 주소를 등록해주어야 한다.

이 작업은 다음 과정을 통해 진행한다.

1. Power BI 웹 사이트에서 우측 상단의 메뉴에서 [도움말 및 지원] - [Power BI 정보]를 차례로 누른다.

2. Power BI 정보 창이 나타나면, 데이터 저장 위치를 확인한다. 여기서 데이터 저장 위치가 현재 사용중인 Power BI가 서비스 되고 있는 애저(Azure) 리전을 뜻한다. 아래 화면의 저장 위치 동남 아시아(싱가포르)의 리전은 asiasoutheast 이다.

3. 다음 사이트에서 애저 데이터센터 아이피 대역대(Microsoft Azure Datacenter IP Ranges)를 정리해 놓은 XML 파일을 다운로드 받는다.

https://www.microsoft.com/en-us/download/details.aspx?id=41653

4. 앞에서 다운로드 받은 XML 파일을 열어 Power BI가 서비스되고 있는 리전의 아이피 대역을 찾는다. 그리고 필요한 아이피 주소를 AWS 보안그룹에 추가해 즌다.

참고로 애저 리전은 다음 주소에서 확인할 수 있다.

https://azure.microsoft.com/en-us/global-infrastructure/regions/