[TIL_2023.11.28] 데이터 인프라, 고급 SQL, BI 대시보드 (2) : Redshift 특징, 설치, 초기 설정, COPY

7주차 화요일, 32일차 Today I Learned

 

 

데이터 인프라, 고급 SQL, BI 대시보드 (2) 
: Redshift 특징, 설치, 초기 설정, COPY

 

 

---

 

 

✏️ 학습 내용

1. Redshift 특징

AWS에서 지원하는 데이터 웨어하우스 서비스 Redshift는 2PB 데이터까지 처리 가능하다. 레코드 별로 저장하는 것이 아니라 컬럼별로 저장하기 때문에 컬럼별 압축이 가능하며 컬럼 추가 및 삭제가 빠르다는 장점이 있다.

 

모든 데이터 웨어하우스의 특징이기도 하지만, 벌크 업데이트를 지원하여 SQL의 INSERT INTO 대신 모든 레코드를 일괄 복사하는 COPY 커맨드를 이용할 수 있다. 레코드가 들어있는 파일을 S3로 복사한 후에 사용할 수 있는데, 이 방법은 아래에서 다시 살펴보도록 하겠다.

 

원래는 고정 용량/비용 SQL엔진인데 최근에는 가변 비용 옵션인 Redshift Serverless도 등장했다. 이 가변 비용 옵션을 설치하는 실습을 할 것이다. 처음 사용 시에 3개월 무료이거나 $300 까지 제공해주는데, 필요없어진 경우에는 반드시 셧다운을 해주어야 한다.

 

또한 Snowflake처럼 다른 AWS 계정과 특정 데이터를 공유할 수 있는 기능도 제공하며, 다른 데이터 웨어하우스처럼 PK uniqueness를 보장하지 않는다.

 

• SQL 기반 관계형 데이터베이스
• 고정 용량/비용 SQL 엔진 (가변 비용 옵션 : Redshift Serverless)
• OLAP
• 2PB 데이터까지 처리 가능
• 컬럼 기반 스토리지
• 데이터 공유 기능
• 벌크 업데이트 지원 (COPY)
• PK 유니크 보장하지 않음
• Postgresql 8.x와 SQL이 호환됨

 

Redshift의 스케일링 방식 : 

용량이 부족해질 때마다 새로운 노드를 추가하는 방식으로 스케일링을 한다. Scale Out 방식과 Scale UP 방식이 있는데, 이를 Resizing이라고 부르며 Auto Scaling 옵션을 설정하면 자동으로 이뤄진다.

• Scale Out은 수평 스케일링을 의미하며, 시스템의 성능을 향상시키기 위해 더 많은 노드를 추가하는 것으로 대용량 데이터에 대한 분석 및 쿼리 처리가 필요한 경우, 데이터베이스의 확장성이 중요한 경우 선택
• Scale Up은 수직 스케일링을 의미하며, 단일 서버 또는 노드의 성능을 향상시키는 것으로, 단일 쿼리나 특정 작업에 대한 높은 성능이 필요하며, 상대적으로 높은 비용을 감수할 수 있는 경우 선택

 

 

Redshift 최적화 : 

Redshift는 최적화하기가 굉장히 복잡하다는 단점이 존재한다. 두 대 이상의 노드로 구성되면 한 테이블의 레코드들이 분산되어 저장되어야 하고, 한 노드 내에서는 순서를 정해주어야 한다. 이렇게 두 대 이상의 노드로 구성되면 그 시점부터는 한 테이블의 레코드들을 어떻게 다수의 노드로 분배할 것인지에 대한 테이블 최적화가 중요해진다.

 

Redshift의 레코드 분배와 저장 방식을 알기 위해서 Distkey, Diststyle, Sortkey 3가지 키워드를 알아야 한다. Diststyle은 레코드 분배가 어떻게 이뤄지는지 결정하고, Distkey는 diststyle이 key인 경우 레코드가 어떤 컬럼을 기준으로 배포되는지 나타내며, Sortkey는 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지 나타낸다. 보통 타임스탬프 필드를 기준으로 한다.

• Distkey : all (모든 노드에 복제),even (default, 균등분배 방식), key
• Diststyle : key로 지정한 컬럼을 기준으로 배포
• Sortkey : 지정한 컬럼을 기준으로 정렬

Diststyle이 key인 경우, 컬럼 선택이 잘못되면 레코드 분포에 Skew가 발생하여 분산 처리의 효율성이 사라진다. (빅쿼리나 스노우플레이크에서는 시스템이 알아서 선택하기 때문에 이런 속성을 개발자가 지정할 필요가 없다는 차이가 있다.) 

 

 

 

2. Redshift Serverless Trial 설치하기

가변 비용 옵션인 서버리스 버전을 설치하도록 하겠다.

1. AWS 계정 접속 및 Redshift Serverless가 Free Trial인지 확인하기
2. Redshift 접속 후 [Try Redfshift Serverless free trial] > default setting으로 Save configuratio
3. Serverless Dashboard에서 Redshift와 Google colab을 연결하기 위한 사전 준비
   1) Redshift Cluster의 Endpoint가 무엇인지 알아보기
   - Workgroup > default > Endpoint COPY
   2) 엑세스할 수 있는 Account 세팅하기
   - 방법 1) Admin 접속 : Namespace > default > [Change admin password] > name, pw 입력 및 확인 > [save]
   - 방법 2) IAM으로 별도 Account 생성하여 접속
4. 접속을 위한 설정 변경
   1) Publicly accessible 변경 : Workgroup > [Edit] > Publicly accessible 체크
   2) 외부 유입되도록 포트 세팅 : Workgroup > VPC security group > [Edit inbound rules] > [Add rule] > 입력
5. Google colab 실습

%load_ext sql

!pip install ipython-sql==0.4.1
!pip install SQLAlchemy==1.4.49

%sql postgresql://ID:PW@hostname:5439/dev

 

 

 

3. Redshift 스키마, 유저, 그룹, 역할 초기 설정

# 스키마(Schema) 설정
CREATE SCHEMA raw_data; 
CREATE SCHEMA analytics; 
CREATE SCHEMA adhoc; 
CREATE SCHEMA pii;

# 모든 스키마를 리스트하기
select * from pg_namespace;

 

# 사용자(User) 생성
CREATE USER keeyong PASSWORD '...';

# 모든 사용자를 리스트하기
select * from pg_user;

 

## 그룹(Group) 생성/설정
CREATE GROUP analytics_users; 
CREATE GROUP analytics_authors; 
CREATE GROUP pii_users;
ALTER GROUP analytics_authors ADD USER keeyong; 
ALTER GROUP analytics_users ADD USER keeyong; 
ALTER GROUP pii_users ADD USER keeyong;

# 그룹 생성
CREATE GROUP

# 그룹에 사용자 추가
ALTER GROUP 그룹이름 ADD USER 사용자이름

# 그룹에 스키마/테이블 접근 권한 설정
GRANT

# 모든 그룹을 리스트하기
select * from pg_group;

 

# 역할(Role) 생성/설정
CREATE ROLE staff; 
CREATE ROLE manager; 
CREATE ROLE external;
GRANT ROLE staff TO keeyong; 
GRANT ROLE staff TO ROLE manager;

# 모든 역할을 리스트하기
select * from SVV_ROLES;

 

그룹과 역할은 기능이 비슷하지만, 그룹은 계승이 안 되며 역할은 계승이 가능하다는 차이가 있다.

 

 

 

4. Redshift COPY - 테이블에 레코드 적재하기

Redshift에서 벌크 업데이트를 하는 방식은 COPY SQL을 이용하는 것이다. 소스로부터 데이터 추출 -> 변환 -> 적재하여 S3에 업로드하고, Copy SQL로 S3에서 Redshift 테이블로 한 번에 복사하면 된다.

 

1. raw_data 테스트 테이블 만들기

# 1) 테이블 생성
CREATE TABLE raw_data.user_session_channel ( 
    userid integer ,
    sessionid varchar(32) primary key,
    channel varchar(32)
);

CREATE TABLE raw_data.session_timestamp ( 
    sessionid varchar(32) primary key,
    ts timestamp
);

CREATE TABLE raw_data.session_transaction ( 
    sessionid varchar(32) primary key,
    refunded boolean,
    amount int
);

 

2) S3 버킷 생성과 파일 업로드

AWS 콘솔에서 S3 버켓을 만들고 거기에 다운로드 받은 CSV 파일 업로드하기 (S3 URI copy)

 

2. Redshift에 S3 접근 권한 설정

AWS IAM (Identity and Access Management) 이용하여 이에 해당하는 역할을 만들고 Redshift에 부여해야 한다.

 

3. 역할 (권한)을 Redshift 클러스터에 지정하기

 

4. COPY 명령으로 CSV 파일들 테이블로 복사하기

COPY raw_data.user_session_channel
FROM 's3://keeyong-test-bucket/test_data/user_session_channel.csv' 
	credentials 'aws_iam_role=arn:aws:iam:xxxxxxx:role/redshift.read.s3' 
    delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 removequotes;

 

+. Analytics 테스트 테이블 만들기

CREATE TABLE analytics.mau_summary AS SELECT
TO_CHAR(A.ts, 'YYYY-MM') AS month,
COUNT(DISTINCT B.userid) AS mau
FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid GROUP BY 1
ORDER BY 1 DESC;

 

 

반응형

 

 

---

 

☁️ 소감

그룹, 역할 등의 내용은 SQLd 시험 공부를 할 때 들은 적 있었던 부분이라서 크게 어렵지는 않았다. 다만 조금 헷갈릴 수는 있으니 다시 살펴볼 필요는 있을 것 같다. 지금은 Redshift를 이용한 실습을 했는데, 이 외에도 무료로 테스트해보기 좋은 데이터 웨어하우스는 없을까? Redshift에서 어떻게 데이터를 활용하는지, 어떤 형태로 데이터가 적재되어 있는지가 궁금해졌다. (호기심은 좋은 징조다)