[7주차] 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드 (2)

🙂 Redshift

✔ Redshift 특징

• AWS 지원 데이터 웨어하우스 (DW)
• 2 PB의 데이터까지 처리 가능: 최소 160GB로 시작해서 점진적으로 용량 증감 가능
• OLAP: 응답속도가 빠르지 않기 때문에 프로덕션 DB로 사용불가
• 컬럼 기반 스토리지
  • 레코드 별로 저장하는 것이 아니라 컬럼별로 저장
  • 컬럼별 압축이 가능하며 컬럼을 추가/삭제가 빠름
• 벌크 업데이트 지원: 레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift로 일괄 복사
• 고정 용량/비용 SQL 엔진. 가변 비용 옵션도 제공 (Redshift Serverless)
• 데이터 공유 기능(Datashare): 다른 AWS 계정과 특정 데이터 공유 가능. (Snowflake의 기능)
• 다른 DW처럼 PK Uniqueness를 보장하지 않음

🍦 SQL 기반 관계형 DB

• Postgresql 8.x와 SQL이 호환
  • Postgresql 8.x의 모든 기능 지원 X
  • text 타입 존재 X
• Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능
  • JDBC / ODBC
• SQL이 메인 언어
  • 데이터 모델링 (테이블 디자인)이 아주 중요.

🍦 스케일링 방식

• 용량이 부족해질 때마다 새로운 노드를 추가
• Scale Out (서버 수 증가) vs. Scale Up (서버 성능 증가)
• a.k.a. Resizing.
• Auto Scaling 사용 가능.
  +
• Snowflake나 BigQuery 방식 (가변비용 옵션) 과는 다름
• Redshift에도 가변비용 옵션 존재. -> Redshift Serverless

🍦 Redshift 최적화는 굉장히 복잡

Redshift가 여러 노드로 구성되면 분산 저장 + 노드 내 순서 지정

(Snowflake나 BigQuery는 생각할 필요 X)

 

🍦 Redshift 레코드 분배와 저장 방식

Redshift가 여러 노드로 구성되면 그 시점부터 테이블 최적화가 중요

• 한 테이블의 레코드를 어떻게 다수의 노드로 분배할 것이냐!

Diststyle, Distkey, Sortkey

• Diststyle: 레코드 분배가 어떻게 이뤄지는 지
  • Key / All / Even(default)
    

    

• Distkey: 레코드가 어떤 컬럼을 기준으로 배포되는 지
• Sortkey: 레코드가 한 노드 내에서 기준되는 컬럼
  • Timestamp가 default

Diststyle이 key일 때 컬럼 선택을 잘못하면?

• 레코드 분포에 Skew(왜도)가 발생 -> 분산처리 효율성 저하
• BigQuery나 Snowflake 에서는 시스템이 알아서 선택

Redshift의 레코드 분배와 저장 방식 예시

CREATE TABLE my_table (
    column1 INT,
    column2 VARCHAR(50),
    column3 TIMESTAMP,
    column4 DECIMAL(18,2)
) DISTSTYLE KEY DISTKEY(column1) SORTKEY(column3)
;

 

🍦 Redshift 벌크 업데이트 방식 - COPY SQL

 

---

✔ Redshift 초기 설정 (스키마, 그룹, 유저)

🍦 Redshift Schema

다른 기타 RDBMS와 동일

🍦 설정

1. 모든 Schema를 리스트: select * from pg_namespace;
   

   CREATE SCHEMA raw_data;
   CREATE SCHEMA analytics;
   CREATE SCHEMA adhoc;
   CREATE SCHEMA pii;​​

2. 모든 User를 리스트: select * from pg_user;
   

   CREATE USER [username] PASSWORD [password];

3. 그룹(Group) 생성/설정
   • 그룹 생성 - CREATE GROUP
   • 그룹에 사용자 추가 - ALTER GROUP [groupname] ADD USER [username]
   • 그룹에 스키마/테이블 접근 권한 설정
   • 모든 그룹 리스트: select * from pg_group;
     

     CREATE GROUP analytics_users;
     CREATE GROUP analytics_aythors;
     CREATE GROUP pii_users;
     
     ALTER GROUP analytics_users ADD USER [username];
     ALTER GROUP analytics_authors ADD USER [username];
     ALTER GROUP pii_users ADD USER [username];

   • 한 사용자는 다수의 그룹에 속할 수 있음.
   • 그룹의 문제는 계승이 안됨 - 너무 많은 그룹은 관리 부담
   • 테이블 별로 접근 권한 x - 스키마 별로 접근 권한 설정
4. 역할(Role) 생성/설정
   • 역할은 그룹과 달리 계승 구조 가능
   • 사용자 또는 다른 역할에 역할 부여 가능
   • 한 사용자는 다수의 역할에 소속 가능
   • 모든 역할 리스트: select * from SVV_ROLES;
     

     CREATE ROLE staff;
     CREATE ROLE manager;
     CREATE ROLE external;
     
     GRANT ROLE staff TO [username];
     GRANT ROLE staff TO ROLE manager;​

---

✔ Redshift COPY 명령으로 테이블에 레코드 적재

COPY 명령을 사용해 raw_data 스키마 하단 3개의 테이블에 레코드 적재하기

• 각 테이블을 CREATE TABLE 명령으로 raw_data 스키마 하단에 생성
  

  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
  );​

• Redshift의 COPY SQL을 사용해서 3개의 테이블 내용 적재
  • 입력 CSV 파일
    • user_session_channel.csv / session_timestamp.csv / session_transaction.csv
  • AWS 콘솔에서 S3 bucket 생성 후 업로드
    • s3://[bucket-name]/test_data/user_session_channel.csv
    • s3://[bucket-name]/test_data/session_timestamp.csv
    • s3://[bucket-name]/test_data/session_transaction.csv
• Redshift에 S3 접근 권한 설정
  
  • AWS IAM을 이용해 이에 해당하는 역할(Role)을 만들고 이를 Redshift에 부여
    • 먼저 Redshift가 S3를 접근할 수 있는 역할 생성 (IAM 웹콘솔)
    • 이 역할을 Redshift 클러스터에 지정 (Redshift 웹콘솔)
• COPY 명령을 사용해 앞서 CSV 파일을 테이블로 복사
  • S3로 로딩한 파일을 앞서 생성한 테이블로 벌크 업데이트
  • COPY SQL 사용
    • csv 파일이기에 delimiter는 콤마(,)로 지정
    • csv 파일에서 문자열이 따옴표로 둘러싸인 경우 제거하기 위해 removequotes 지정
    • csv 파일의 첫번째 라인(헤더)을 무시하기 위해 'IGNOREHEADER 1'을 지정
    • credentials에 앞서 Redshift에 지정한 Role을 사용. 이 때 해당 Role의 ARN을 읽어야 함
      

      COPY raw_data.user_session_channel
      FROM 's3://[bucket-name]/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​

    • COPY 명령 실행 중 에러 발생 시 stl_load_errors 테이블 내용 확인
      : SELECT * FROM stl_load_errors ORDER BY starttime DESC;

 

analytics 테스트 테이블 만들기

• analytics 스키마에 새로운 테이블 만들기
  • raw_data에 있는 테이블을 조인해서 새로 만들기 (ELT)
  • CTAS로 간단하게 조작 가능

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;