Cloud - Redshift & S3 connect

Redshift 소개

특징

• AWS에서 지원하는 데이터 웨어하우스 서비스
• Still OLAP : 응답 속도가 느려서 프로덕션 DB로 사용하기 힘들다.
• 컬럼 기반의 스토리지 : 컬럼별 압축이 가능하며, 컬럼의 추가와 삭제가 빠르다.
• 벌크 업데이트 지원 : S3와 연계해서 Copy 커맨드로 Redshfit에 복사가 가능하다.
• Datashare 지원
• PK uniqueness를 보장하지 않는다.
• SQL 기반이며 PostgreSQL 8.x을 호환한다.
• 두 대 이상의 노드로 구성되면 복잡해진다.
  • 분산 저장되어야 한다.
  • 노드에 데이터가 적절하게 분배되도록 사용자가 지정해주어야한다.
• 레코드 분배와 저장 방식
  • 두 대 이상의 노드로 구성되면 테이블 최적화가 중요하다.
    • Distkey : 레코드가 어떤 컬럼을 기준으로 배포되는지 나타낸다. → DIststyle이 key → 컬럼 선택이 중요하다. (Skew : 한쪽 쏠림 이 발생가능하다.)
    • Diststyle : 레코드 분배 방식을 결정 (all, even, key)
    • Sortkey : 레코드가 한 노드 안에서 어떤 컬럼을 기준으로 정렬되는지 나타낸다. (타임스탬프가 되는 경우가 많음)

• Redshift의 벌크 업데이트 방식
  1. 데이터 추출
  2. 파이프라인을 통해 S3에 업데이트
  3. S3 → Redshift로 복사

참고) VARCHAR의 경우 한중일 언어는 1CHAR가 3Byte이기 때문에 주의해야 한다.

• 고급 데이터 타입
  • GEOMETRY
  • HLLSKETCH
  • SUPER
  • JSON은 VARCHAR로 받아서 Parsing을 해야한다.

Serverless 설치

주의) 3달 or $300 비용 초과인 경우에 Free Trial이 만료됨!

참고) 비용 측면에서 free trial은 서울이 비싸기 때문에 오레곤으로 시작했다.

• Colab 연결
  • 엔드 포인트
    
  • Admin 계정 생성
    
  • 연결을 위해 endpoint, username, password 정보가 필요하다.
  • Public access 설정
    - 작업 그룹 구성에 진입
    - 네트워크 및 보안의 퍼블릭 액세스를 켜야한다.
    
    - VPC 보안 그룹의 세팅 변경이 필요하다.
    - 인바운드 규칙 변경
    - 규칙 추가를 진행 → 포트 넘버 5439 선택, 소스의 경우 모든 접근을 설정하기 위해 0.0.0.0으로 설정한다.

초기설정

• 스키마 : 폴더 컨셉의 구조 생성
  • 목적에 맞는 스키마를 생성한다.
  • 생성 : CREATE SCHEMA <schema_name>
  • 리스트하기 : SELECT * FROM pg_namespace;
  • public은 default 스키마다.
• 그룹
  • 한 사용자는 다수의 그룹에 속할 수 있다.
  • 그룹은 계승이 안되기 때문에 관리가 필요하다.
  • 그룹을 활용해 접근권한을 설정하는 방식을 많이 사용한다.
  • 그룹 생성 : CREATE GROUP
  • 그룹에 사용자 추가 : ALTER GROUP group_name ADD USER username
  • 그룹 리스트하기 : SELECT * FROM pg_group;
  • 접근 권한은 grant를 사용해서 부여한다.
• 역할 설정
  • 그룹과 다르게 계승 구조가 가능하다.
  • 사용자에게 부여될 수도 있고, 역할에 부여도 가능하다.
  • 한 사용자가 다수의 역할에 소속가능하다.
  • 역할 리스트 : SELECT * FROM SVV_ROLES;
  • 역할 생성 : CREATE ROLE role_name
  • 부여 : GRANT ROLE staff TO ROLE role_name 또는 TO username
• 유저 생성
  • 사용자 리스트하기 : SELECT * FROM pg_user;
  • 사용자 생성 : CREATE USER username password ‘…’;

---

Copy 명령으로 테이블에 레코드 적재하기

• raw_data 테이블 생성하기 : ETL을 통해 데이터 소스를 복사하는 형태로 이루어진다.
  • S3에서 Redshift에 접근하기 위해서는 접근 권한 생성이 필요
    • IAM → 역할 생성
      1. AWS service를 선택하고 하단의 Redshift를 선택한다.
      2. 다음으로 Redshift가 갖게 될 권한을 선택해야한다. → 이번에는 AmazonS3FullAccess를 선택하여 진행한다.
      3. 사용할 역할 이름을 입력하고 최종 생성한다.
    • Redshift → 네임스페이스 구성에 접근
      1. 보안 및 암호화에 IAM 역할관리를 선택
      2. IAM 역할 연결을 선택하고, 앞서 만든 IAM을 지정한다.
  • COPY 명령을 사용하여 raw_data 스키마 아래의 3개 테이블에 레코드를 적재합니다.
    • COPY SQL : https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html
    • aws_iam_role에는 해당 IAM의 ARN을 넣어야한다. → 실습
    • 에러 발생시 stl_load_erros를 확인해야한다.
      • SELECT * FROM stl_load_errors ORDER BY starttime DESC;
• analytics 테스트 테이블 생성하기
  • raw_data에 있는 테이블들을 조인해서 새로 만든다. or 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.usersession_channel B ON A.sessionid = B.sessionid
      GROUP BY 1
      ORDER BY 1 DESC;