[TIL] 8주차_Day32: 데이터 웨어하우스 관리, 고급 SQL, BI 대시보드 (2)

💡Today I Learned

• 데이터 웨어하우스, SQL, BI 대시보드에 대한 두 번째 수업을 진행했습니다.
• Redshift (Trial) 설치, Redshift 초기 설정 (스키마, 그룹, 유저), Redshift COPY 명령으로 테이블에 레코드 적재하기 관련 실습 진행

---

1. Redshift의 특징 

- Overall

: AWS에서 지원하는 데이터 웨어하우스 서비스

: OLAP(Analytical) → 응답속도가 빠르지 x, 프로덕션 DB로는 사용불가

: 컬럼 기반 스토리지 → 레코드(row)가 아닌 컬럼(속성)별로 저장함, 컬럼별 압축이 가능, 컬럼 추가/삭제가 빠름

: 벌크 업데이트 → 레코드가 들어있는 파일을 S3(AWS 스토리지)로 복사 후 COPY 커맨드로 Redshift에 일괄 복사

: 고정 용량, 고정 비용 SQL 엔진

: 데이터 공유 기능 (Datashare) → 다른 AWS 계정과 특정 데이터 공유 가능

: (다른 데이터 웨어하우스처럼) 기본키 유일성을 보장하지 않음 *) 프로덕션 DB는 보장함

 

- Redshift == SQL 기반 관계형 DB

: PostgreSQL과 호환됨 (모든 기능을 지원하지는 x..)

: Postgresql을 지원하는 툴이나 라이브러리로 액세스 가능 (JDBC/ODBC)

: SQL이 메인 언어 → 데이터 모델링 (테이블 스키마 등.. 디자인) 이 중요함

 

*) dc2.large 옵션 (가장 낮은 사양) 으로 데모 실습 진행

 

- Redshift의 스케일링 방식

: 용량이 부족해질 때마다 새로운 노드 추가 

1) Scale Out: 새로운 노드, 서버 추가 → "Resizing" (Auto Scaling 옵션 설정 시 자동으로 이뤄짐)

2) Scale Up: 사양 증가

→ 고정비용에 해당됨

 

*) Redshift Serverless

: 가변비용 옵션임 (Pay as You Go)

: 데모 실습 시 사용

 

- Redshift 최적화 (in 고정비용 옵션)

: Redshift가 두 대 이상의 노드로 구성될 경우 한 테이블의 레코드 → 분산 저장 돼야함

: 한 노드 내에서 순서가 정해져야 함

 

*) Skewed Data (비대칭 데이터) 문제

: 테이블의 데이터를 분산 저장할 때 한 노드에 엄청나게 많은 데이터가 몰려서 저장될 수도 있음

: 분산 저장하는 의미가 없음.. 한 노드만 전체 처리 속도가 증가

→ BigQuery, Snowflake는 엔진이 알아서 최적화 해줌

 

- Redshift의 레코드 분배/저장 방식

Diststyle : 레코드 분배가 어떻게 이뤄지는지 결정 (opt: all, even, key, default="even")

• all: 모든 노드에 레코드가 각각 저장 (ex: 300개 레코드 3개의 노드 → 3개의 노드에 모두 300개 레코드를 copy)
• even: Round Robin 형태(돌아가며 사이좋게)로 노드에 레코드들을 분산 저장 (ex: 300개 레코드 3개의 노드 → 100개씩 copy)
• key: 특정 컬럼의 값을 기준으로 레코드들이 분산 저장 (ex: primary key 기준으로 레코드들을 다수의 노드로 분배) → 이 key로 join이나 group by 시 연산 속도에 이점이 있음

Distkey : 레코드가 어떤 컬럼을 기준으로 분배되는지 나타냄 (Diststyle의 opt="key"인 경우)

Sortkey : 레코드가 한 노드 내에서 어떤 컬럼을 기준으로 정렬되는지 나타냄 (보통 타임스탬프 필드)

 

*) Diststyle이 key인 경우 컬럼 선택이 잘못될 경우

: 레코드 분포에 Skew 발생 → 분산 처리 효율성이 사라짐

: 이 때 BigQuery, Snowflake는 엔진이 알아서 최적화 해준다는 의미

 

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

→ column1 값을 기준으로 분배, 같은 노드(=슬라이스) 안에서는 column3을 기준으로 정렬

 

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

1. 소스로부터 데이터 추출

2. S3에 업로드 (보통 Parquet 포맷의 파일)

3. COPY SQL로 S3 → REDSHIFT 테이블로 한 번에 복사

 

*) 원래 SQL의 INSERT INTO 명령어

: 레코드 by 레코드로 테이블에 적재 → 로딩 시간이 오래 걸림

: 따라서 binary 파일(Parquet)로 압축 → 클라우드 스토리지(S3)에 업로드 → COPY 명령어로 벌크 업데이트

 

- Redshift의 데이터 타입

*) 한,중,일어: 3byte = 1 char (UTF-8 기준)

*) 영어: 1byte = 1 char

: 이외에도 Postgresql과 char type에서 약간의 차이가 있음

 

(extra): GEOMETRY, GEOGRAPHY, HLLSKETCH, SUPER

 

*) Redshift에서는 JSON 타입이 따로 x → JSON으로 된 필드는 CHAR 타입으로 받은 후 .json() 함수로 파싱해서 써야 함

 

2. Redshift Serverless 설치하기 (실습)

*) trial 기간 종료 전 꼭 해지하기 ! (~23.02.26)

*) Create cluster = 고정비용 → Free trial 지원 x

*) Serverless = 가변비용 → 300$ Free trial

 

- Redshift와 구글 Colab 연결하기

1. Redshift cluster의 엔드포인트(host 이름, 포트 넘버, db 이름)

: 서버리스 대시보드 > 네임스페이스 / 작업 그룹 > 작업 그룹 선택 > 엔드포인트 복붙

 

2. access할 수 있는 admin account 세팅하기

: 서버리스 대시보드 > 네임스페이스 / 작업 그룹 > 네임스페이스 선택 > admin user name, 패스워드 설정

 

*) Colab에서 username, password, endpoint 이용해 연결 시 public access 설정

: 작업 그룹 선택 > 네트워크 / 보안 > 편집 > 퍼블릭 액세스 가능(v) > 저장

 

*) VPC 보안 그룹 내 인바운드 규칙 설정 → 같은 VPC 안의 서버/서비스들만 액세스 할 수 있도록

: 작업 그룹 선택 > VPC 보안 그룹 > 인바운드 규칙 > 편집 > 5439 포트로 들어오는 모든 사용자(0.0.0.0/0) 트래픽을 허가하는 규칙 추가

 

3. Redshift cluster 초기 설정

- 스키마

: 관계형 DB의 테이블 관리를 카테고리 별로 하도록 → 2중 구조

: 폴더의 컨셉 = 스키마

 

: DEV 아래에 4개의 스키마

ex) DEV

         ㄴ raw_data (ETL을 통해 외부 소스의 데이터가 테이블 형태로 저장될 스키마)

         ㄴ analytics (ELT를 통해 기존의 테이블을 조인해 만든 새로운 테이블이 저장될 스키마_CTAS)

         ㄴ adhoc (개발/테스트 용도로 만든 테이블이 저장될 스키마)

         ㄴ pii (서비스 사용자의 개인정보 저장될 스키마)

 

: CREATE SCHEMA 문 이용 → admin 권한이 필요

: 모든 스키마 리스트 확인

select * from pg_namespace;

 

 

*) 스키마 이름 없이 만든 테이블은 (default 스키마인) "public" 스키마 아래에 들어감

 

- 유저

: CREATE USER 문 이용

CREATE USER [사용자 이름] PASSWORD ['패스워드']

 

 

: 모든 유저 리스트 확인

select * from pg_user;

 

- 그룹

: 한 사용자는 다수의 그룹에 속할 수 있음

: 테이블 - 사용자 별로 액세스 권한을 지정 (불편..) → 스키마 - 그룹 별로 엑세스 권한 설정하기

: 스키마 = 테이블들의 묶음 / 그룹 = 사용자들의 묶음

: CREATE GROUP 문 이용

: ALTER GROUP 문 이용 (변경)

: 모든 그룹 리스트 확인

select * from pg_group;

 

 

*) 그룹은 계승이 안됨 (부분집합 개념이 x) → 그룹 마다의 권한을 다 하나씩 지정해줘야.. (복잡 -)

 

- 역할

: (그룹과 달리) 계승 구조 만들 수 있음

: 역할 → 사용자 or 다른 역할에 부여

: 한 사용자는 다수의 역할에 소속 가능

 

: CREATE ROLE 문 이용

GRANT ROLE [역할 이름] TO [사용자 이름];
GRANT ROLE [역할 이름] TO ROLE [계승 받을 새로운 역할 이름];

 

: 모든 역할 리스트 확인

select * from SVV_ROLES;

 

 

4. Redshift COPY 명령으로 벌크 업데이트

- 테이블에 레코드 적재하는 과정

1. raw_data 스키마 아래에 3개의 table 생성 (CREATE TABLE)

2. 각 테이블의 입력이 되는 .csv 파일(*.csv 파일 1개 : table 1개)을 S3의 특정 폴더 아래에 복사 (S3 버킷 먼저 생성)

3. S3에서 해당 테이블로 COPY 시 Redshift가 S3로의 접근 권한을 가져야 함 (AWS IAM을 이용해 S3 접근 권한이 있는 역할 생성, 해당 역할을 Redshift 클러스터에 지정)

 

 

- AWS의 IAM Role

(웹 콘솔에서 진행)

 

1. Redshift 서비스 → S3로의 역할 생성

: IAM 역할(IAM Role) 생성 > AWS 서비스 > 사용 사례 = Redshift Customizable > S3FullAccess 정책(policy) 추가 > 'redshift.read.s3'라는 역할 > 생성

 

2. Redshift 클러스터에 만든 역할을 지정

: Redshift 콘솔 > 클러스터의 Default Namespace > 보안 및 암호화 > IAM 역할 관리 > IAM 역할 연결 > 'redshift.read.s3' 연결

 

- COPY 명령으로 .csv 파일을 테이블로 복사

*) .csv 파일 → delimeter은 콤마(,)

*) removequotes  → 문자열 "" 제거

*) IGNOREHEADER 1 → .csv 파일 첫 번째 라인 = 헤더 무시

*) credentials → 만들어놓은 Role의 ARN 복붙

 

*) 에러 발생 시 stl_load_errors 테이블의 내용 확인

select * from stl_load_errors ORDER BY starttime DESC;

: 시작 시간 기준 내림차순 (최근 것부터 확인)

 

- S3에 .csv 파일 업로드

: bucket 생성 > 폴더 생성 > .csv 파일 (예제 파일 4개) 업로드

: S3 URI 사용

 

- Redshift에 S3FullAccess 포함된 IAM 역할 연결

: 생성한 IAM Role의 ARN 사용

 

---

 

💡Furthermore

• Redshift의 character 단위는 "byte" (한중일../영 차이 o → Use case에서 어떤 의미를 가지는지..?)
• 더 다양한 COPY SQL의 문법 → aws docs에서 확인 가능