[AWS Redshift] Redshift를 활용한 OLAP 구축 예시

문제 및 요구 사항

 

• 통계성 데이터를 보여주는 대시보드 기능이 추가되고, 데이터가 많아지면서 여러 문제가 생겼음
• 여러 테이블을 Join 해오기 때문에 속도가 매우 느림 (api 6초 정도 소요) 
• 통계쪽 데이터를 확인하기위해 계속 요청하면 서버에 전체적으로 영향이 감 
• 실시간 데이터와 배치성 데이터(1개월 전 데이터)를 함께 볼 수 있어야함 

 

 

시도 방안

 

• Aws Redshift 를 이용해보자. 

 

Redshift 란 ?

• MPP(Massive Parallel Processing) Database 임 
• OLTP 용으로 사용함 
• PK의 Uniqueness를 보장 하지 않음 

즉 결국 DB 인데, 우리가 일반적으로 사용하는 MySql, PostgresSql 과 같은 OLTP 성 DB가 아니라는 뜻 

 

 

Redshift 적용 구조 

 

타 기업들의 사용 예시를 살펴보고 우리 프로젝트에 적용해보면 좋을 것 같은 구조를 생각했다. 

 

1) RDS의 데이터를 하루에 한번 S3로 업로드 한다(이때 통계에 필요한 데이터 테이블의 데이터를 모아서 업로드함)

2) 업로드 된 S3 데이터를 Redshift로 적재한다 (이때 copy 명령어를 사용하는 것이 핵심)

3) 실시간으로 RDS 에서 발생한 데이터는 S3로 업로드 한다 (Redshift로 적재는 실시간으로X)

4) 이전 데이터는 Redshift에서 읽어오고, 실시간 데이터는 Redshift Spectrum을 이용하여 Redshift + S3 합쳐서 읽어온다

 

Redshift 사용 예시를 쳐보면 항상 구조에 S3가 있어서 저걸 어디다가 쓰는거지..? 하고 이해가 되지 않았다.

직접 사용해보면서 사용 이유를 알 수 있었다. (아래에 작성 하도록 하겠음) 

 

우선 저 구조를 보고 여러 의문점이 생길 것이다. 

 

Redshift Spectrum 란? 

• Redshift 테이블에 데이터를 로드하지 않고 S3 파일에서 정형 및 비정형 데이터를 쿼리하고 가져올 수 있는 기능
• 쿼리 속도가 빠름

 

실시간 데이터를 S3로 업로드하고,  AWS Glue를 사용한 이유 

실시간으로 RDS에 쌓이는 데이터를 얻어오는 방법은 정말 다양하다. 

• AWS DMS
  • RDS -> S3 로 실시간 데이터 복재(CDC 방법)
• AWS Glue
  • RDS -> Redshift 바로 Insert 가능 (AWS Crawler 를 사용) 허나 DB -> DB로는 성능, 안정성을 고려하여 바로 데이터를 넣는 방법은 지양해야함 
• AWS Lambda 함수 
  • 변경된 데이터를 실시간으로 S3에 떨어뜨릴 수 있음
• AWS Firehose

등 여러 방안이 있다. 

 

너무나 방법이 많았지만 내가 택한 방법은 실시간 데이터를 RDS -> S3로 떨어뜨리는 것이다.

S3에 떨어뜨린 데이터는 EXTERNAL schema로 만들어서 Redshift와 함께 사용이 가능하다. (Redshift spectrum 이용)

이게 가능한 이유가 Aws Glue를 이용해서이다. 

내가 딱히 수동으로 Aws Glue내 테이블을 생성해주지 않아도, Crawler를 이용하여 S3의 변경내역을 반영하도록 하면 EXTERNAL 테이블이 자동으로 생성 된다. 

 

 

실시간으로 S3 로 떨어뜨려 줄거면, 하루에 한번 S3로 떨어뜨리는 이유

• 실시간데이터는 S3에만 떨어뜨리고, 이를 Redshift Spectrum 을 이용하여 조회만 가능하다.
• Redshift에서 INSERT을 이용하여 실시간으로 데이터를 넣어줄 수 있지만, 성능이 느리고 OLTP 성으로 사용한다면 잘못사용하고 있는 것이다.
• 이로인해 하루에 한번 데이터를 모아서 S3로 떨어뜨린 이후, 해당 데이터를 COPY로 Redshift로 적재 시켜준다. 

 

 

---

 

 

이제 설계한 구조대로 한번 사용해보도록 하겠다. 테스트 목록은 다음과 같다.

 

테스트 목록

 

• (배치성) RDS -> S3로 데이터 업로드 
• (배치성) 해당 데이터 S3 -> Redshift로 적재
• (실시간) RDS -> S3 업로드 자동화
• (실시간) Redshift spectrum을 통해 S3 + Redshift 데이터 읽어오기 

 

우선 S3 버킷을 생성해야한다.

 

AWS S3 버킷 생성 

하루에 한번 RDS의 데이터를 업로드 할 버킷이다. 

 

버킷 만드는 방법은 아래를 참고하자

https://mingg123.tistory.com/202

[AWS] S3 버킷 만들기

 

생성한 버킷에 txt 파일 업로드

 

원래는 RDS의 하루치 통계 데이터를 S3로 업로드 해야하지만, 임의 예시 데이터를 만든 버킷에 업로드 하도록 하겠음 

 

sales_tab.txt

10.74MB

 

용량은 약 11 MB 정도 된다. 

 

 

Redshift Serverlsess 생성 

기술 도입 전 프로토타입으로 테스트 할 거라, Serverless 로 만들었다. 

 

대시보드에서 [쿼리 데이터] 를 클릭하면 Redshift query editor v2를 이용해서 Redshift에 쿼리를 날려 확인할 수 있다.

 

초기엔 Run 버튼이 비활성화 일텐데, Redshift 를 만들었다면 DB를 선택하면 Run 버튼이 활성화 된다.

 

자 그렇다면 테이블을 만들고 S3 -> Redshift로 copy해서 데이터를 넣어보자. 

 

 

테이블 생성

 

create table sales(
    salesid integer not null,
    listid integer not null distkey,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp);

select * from sales;

해당 쿼리를 실행하면 아래와 같이 뜰 것이다. 

 

 

 

select * from sales;

 

텅빈 테이블을 확인할 수 있다. 

 

 

S3-> Redshift 데이터 Load

 

copy sales from 's3://{버킷이름}/sales_tab.txt'
credentials 'aws_iam_role={너의 iam role ana}' 
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'ap-northeast-2';

 

172456 행을 copy하는데 1.6초 밖에 걸리지 않았다. 굿 

 

 

select * from sales;

데이터를 확인해보면 알맞게 들어간 것을 알 수 있다. 

 

그렇다면 배치성 데이터들은 하루에 한번 RDS -> S3로 업로드하고, S3 -> Redshift로 copy 명령어를 이용하여 적재하면 된다.

이후 Redshift에 쿼리를 날려서 데이터 분석에서 사용하면 된다. 

 

이런식으로 차트도 볼 수 있는데 사용하진 않을 듯 하다. 

 

 

 

실시간 데이터 Redshift Spectrum 을 이용하여 읽어오기 

과거 데이터는 이전 방법을 통해 Redshift에서 읽어오면 되지만, 실시간 데이터를 어떤 식으로 관리해야할지가 가장 큰 난제였다. 

 

실시간 트랜잭션이 발생할 때마다 Redshift로 INSERT 해주는 방법도 있지만,

Redshift에서 INSERT는 성능이 떨어지기 때문에 실시간으로 데이터를 쌓진 않았다. 

 

이를 해결하기위해 사용한 방법이 Redshift Spectrum 이다. 

 

1. 실시간으로 트랜잭션이 발생하면 json 형식으로(형식은 너 마음대로 편한대로 해라) S3에 업로드 한다. 

2. Aws Glue > Cralwer를 이용하여 S3에 변경사항이 있을 경우, AWS Glue의 EXTERNAL 테이블을 업데이트 한다

3. 해당 EXTERNAL 테이블 + Redshift를 이용하여 실시간 데이터를 조회한다. 

 

사실 직접 사용해보기 전까진 이게 무슨 소리인가 싶었다. 

 

실시간 데이터 S3 업로드

 

코드 예시 

 

 

 

 

AWS Glue >  Crawlers 사용

 

크롤러를 만들어서 S3의 변경 사항을 Redshift의 External 테이블에 반영한다. 

 

S3 path에 S3://{버킷이름}/경로를 지정한다. 

난 sales_realtime으로 지정했다. (이후 redshift의 external 테이블 명도 sales_realtime 이 된다) 

 

Role 을 등록해주어야한다. 없다면 AWS IAM 가서 만들자.

AWS Glue 관련 권한을 추가해주면 된다.

 

그 다음 DB (redshift)를 선택해주면 된다. 

Redshift Serverless로 만들었다면 dev가 뜰 것임. 

 

생성이 완료되었다면 Run crawler를 눌려보자. 

 

 

S3 내 json 업로드 

 

자 이제 S3 버킷 /sales_realtime 내 데이터를 업로드 해보도록 하겠음 

 

sales_test.json 

{"salesid":40006,
"listid":1,
"sellerid":36861,
"buyerid":21191,
"eventid":7872,
"dateid":1875,
"qtysold":6,
"pricepaid":728.00,
"commission":109.20,
"saletime":"2/18/2008 02:36:48"
}

 

 

 

 

AWS Glue

AWS Glue에 가서 확인하면 Table이 생성되었음.

Json 형식으로 넣어주면 알아서 파싱을 하더라. 

 

 

 

Redshift query editor v2

 

이제 sales_realtime에 들어간 데이터를 확인해보자. 

SELECT * from EXTERNAL_SCHEMA.sales_realtime

(테스트 여러번해서 데이터가 좀 많음)

 

나는 데이터를 S3에 업로드했는데 Redshift의 External 테이블로 이용 가능한 것임 

 

Redshift Spectrum

 

S3 데이터(EXTERNAL_SCHEMA.sales_realtime)과, Redshift 내 데이터를 읽어와보자. 

이걸 이용하여 실시간 데이터와 배치성 데이터를 Redshift에 쿼리를 쏨으로써 얻어올 수 있다.

SELECT SUM(qtysold) as total_qty_sold 
FROM (
    SELECT qtysold, dateid
    FROM sales 
    UNION ALL
    SELECT qtysold, dateid
    FROM EXTERNAL_SCHEMA.sales_realtime
) combined_sales
JOIN date ON combined_sales.dateid = date.dateid
WHERE caldate = '2008-02-18';

 

실시간 데이터는 계속 S3에 올리고 →

Glue 크롤러가 돌면서 External 테이블에 데이터트를 업데이트 해주면 ->

External 테이블 + 기존 Redshift 테이블을 합쳐서 사용 가능한 것임 

 

 

 

 

정리

 

• 사실 Redshift가 생소하기도 했고, 데이터 엔지니어링 영역이라서 어떤 구조를 잡아야 좋을지 고민을 많이 했다. 
• aws redshift 영상이나 타 기업들이 적용한 구조도 많이 참고하면서 배울수 있었다. 
• 아직 성능 테스트, 비용 쪽도 확인을 해보아야 하지만, 이번 경험을 통해 큰 구조를 고민해보는게 재밌었다.

 

 

발생했던 트러블 슈팅

진행하면서 발생했던 기타 이슈들을 정리해 두고자 한다. 

 

 

Redshift -> S3 Unload

 

S3에서 Redshift로 업로드가 가능하듯이, 반대로 Redshift 에서 S3로도 Unload 가 가능하다. 

테스트해보려고 아래 쿼리를 수행해보았는데 권한 에러가 발생했다.

-- UNLOAD ('SELECT * FROM sales')
-- TO 's3://{버킷이름}/test'
-- IAM_ROLE '{iam role ana}'
-- PARALLEL off
-- CSV;
-- region 'ap-northeast-2';

 

해결법)

Iam role에 S3FullAccess 권한을 주었다. 

 

 

txt 데이터를 import 하기

 

Unload된 데이터를 새로운 테이블로 Import 하려하는데 잘 되지 않았다. txt -> csv로 변경해야하나 여러 시도를 해본 끝에..

 

해결법) 

Dbeaver 이용

데이터 형식이 다 다르겠지만, 나는 헤더도 없는 상태라 create table 을 이용하여 테이블을 만들어 준 이후에, 헤더 위치 none으로 import를 성공했다. 

 

sales_tab.txt

10.74MB

중간중간 이상한 데이터들 id가 없다거나.. 그런 데이터는 전부 지웠음 (테스트 용 데이터라)

 

 

 

 

 

txt 데이터를 RDS import 이후 id가 증가하지 않는 현상 

 

unload된 데이터를 빈 RDS에 Import 이후, 새로운 트랜잭션이 발생하여 저장시에 id가 자동으로 증가해야하는데 되지 않았다.

 

해결법)

CREATE SEQUENCE sales_salesid_seq START 1;
ALTER SEQUENCE sales_salesid_seq RESTART WITH 172457;

CREATE SEQUENCE {테이블명}_{컬럼명}_seq