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

1. Redshift 권한과 보안

사용자별 테이블 권한 설정

일반적으로 사용자별 테이블별 권한 설정은 하지 않음: 너무 복잡!
역할 혹은 그룹별로 스키마별 접근 권한을 주는 것이 일반적
- RBAC (Role Based Access Control)가 새로운 트렌드: 그룹보다 편리
- 여러 역할에 속한 사용자의 경우 각 역할의 권한을 모두 갖게 됨 (Inclusive)
개인정보와 관련된 테이블들이라면 별도 스키마 설정
- 극히 일부 사람만 속한 역할에 접근 권한을 줌
그룹 권한 설정 예시 (역할로 바꾸어도 됨)

스크린샷 2023-12-04 오후 4 19 55

ROLE -> GROUP 도 가능
analytics_authors 권한
- pii에 대해서는 아무런 설정이 없음 -> access 불가

GRANT ALL ON SCHEMA analytics TO ROLE analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA analytics TO ROLE analytics_authors; 
-- ALL TABLES 명령을 쓰기 위해서는 위 명령이 같이 실행되어야 함

GRANT ALL ON SCHEMA adhoc TO ROLE analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO ROLE analytics_authors;

GRANT USAGE ON SCHEMA raw_data TO ROLE analytics_authors;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO ROLE analytics_authors;   
-- SELECT 권한을 주기 위해서는 SCHEMA에 대한 USAGE 권한을 주어야 함

analytics_users 권한
- analytics 스키마는 읽기 권한만

GRANT USAGE ON SCHEMA analytics TO ROLE analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analytics_users; 

GRANT ALL ON SCHEMA adhoc TO ROLE analytics_users;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO ROLE analytics_users;

GRANT USAGE ON SCHEMA raw_data TO ROLE analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO ROLE analytics_users;   

pii_users 권한
- 다른 스키마에 대한 access 권한을 같이 해주어도 되고
- pii 스키마에 대한 권한만 설정을 하고, 사용자를 여러 role에 포함시켜도 됨

GRANT USAGE ON SCHEMA pii TO ROLE pii_users;
GRANT SELECT ON ALL TABLES IN SCHEMA pii TO ROLE pii_users;

컬럼 레벨 보안

테이블 내의 특정 컬럼들을 특정 사용자나 특정 그룹/역할에만 접근 가능하게 하는 것
보통 개인정보 등에 해당하는 컬럼을 권한이 없는 사용자들에게 감추는 목적으로 사용됨
- 사실 가장 좋은 방법은 아예 그런 컬럼을 별도 테이블로 구성하는 것
- 더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것!

레코드 레벨 보안

테이블 내의 특정 레코드들을 특정 사용자나 특정 그룹/역할에만 접근 가능하게 하는 것
특정 사용자/그룹의 특정 테이블 대상 SELECT, UPDATE, DELETE 작업에 추가 조건을 다는 형태로 동작
- 이를 RLS (Record Level Security) Policy라고 부릅
- CREATE RLS POLICY 명령을 사용하여 policy를 만들고, 이를 ATTACH RLS POLICY 명령을 사용해 특정 테이블에 추가
일반적으로 더 좋은 방법은 별도의 테이블로 관리하는 것
- 더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것!

2. Redshift 백업과 테이블 복구

백업

기본적으로 백업 방식은 마지막 백업으로부터 바뀐 것들만 저장하는 방식
- 이를 Snapshot이라고 부름
- 백업을 통해 과거로 돌아가 그 시점의 내용으로 특정 테이블을 복구하는 것이 가능 (Table Restore)
- 과거 시점의 내용으로 Redshift 클러스터를 새로 생성하는 것도 가능
자동 백업
- 기본은 하루이지만 최대 과거 35일까지의 변경을 백업하게 할 수 있음
  - 클러스터의 [Maintenance] 탭 - [Backup details] - [Edit] - 원하는 보관일수 선택
- 이 경우 백업은 같은 지역에 있는 S3에 이루어짐
- 다른 지역에 있는 S3에 하려면 Cross-regional snapshot copy를 설정해야 함
  - 보통 재난 시 데이터 복구에 유용함
매뉴얼 백업
- [Actions] - [Create snapshot]
- 언제든 원할 때 만드는 백업으로, 명시적으로 삭제할 때까지 유지됨
- 혹은 생성 시 보존 기한 지정 가능

복구

클러스터에서 [Resotre table] 메뉴 선택
복구 대상이 있는 백업 (Snapshot) 선택
원본 테이블 (Source table) 선택
어디로 복구될 것인지 타겟 테이블 선택

Redshift Serverless의 백업과 복구

고정비용 Redshift에 비해 제한적이고 복잡
Snapshot 이전에 Recovery Points 라는 것이 존재
- Recovery Point를 Snapshot으로 바꾼 다음, 여기서 테이블을 복구하거나 이것으로 새로운 Redshift 클러스터를 생성하는 것이 가능
Recovery Points는 과거 24시간에 대해서만 유지됨
고정비용은 정해진 스토리지가 있지만, serverless는 컴퓨팅 자원과 스토리지가 별개로 존재하고 사용한 만큼 값을 지불하는 형태
- 고정된 자원이 없기 때문에 Snapshot을 계속 잡을 수 없음

3. Redshift 관련 기타 서비스

Redshift Spectrum

Redshift의 확장 기능
S3 등에 있는 파일들을 테이블처럼 SQL로 처리 가능하게 해줌
- S3 파일들을 외부 테이블들(external table)로 처리하면서 Redshift 테이블과 조인 가능
- S3 외부 테이블들은 보통 Fact 테이블들이 되고, Redhsift 테이블들은 Dimension 테이블
- 1TB를 스캔할 때마다 $5 비용 발생
이를 사용하려면 Redshift 클러스터 필요 + S3와 Redshift 클러스터는 같은 region에 있어야 함

Athena

Apache Presto를 서비스화한 것
사실상 Redshift Spectrum과 비슷한 기능을 제공
S3에 있는 데이터들을 기반으로 SQL 쿼리 기능 제공
- 이 경우 S3을 데이터 레이크로 볼 수 있음

Redshift ML

SQL만 사용하여 머신러닝 모델을 훈련하고 사용할 수 있게 해주는 Redshift 기능
AWS SageMaker에 의해 지원됨
- SageMaker는 Auto Pilot이라 하는 최적화된 모델을 자동으로 생성해주는 기능 제공
이미 모델이 만들어져 있다면 이를 사용하는 것도 가능 (BYOM: Bring Your Own Model)

4. Redshift Spectrum으로 S3 외부 테이블 조작하기

S3에 매우 큰 데이터가 있는데 이를 Redshift로 로딩하기 버거운 경우

Fact 테이블과 Dimension 테이블

Fact 테이블
- 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
- 일반적으로 매출 수익, 판매량 또는 이익과 같은 사실 또는 측정 항목을 포함하며 비즈니스 결정에 사용
- 일반적으로 foreign key를 통해 여러 Dimension 테이블과 연결됨
- 보통 크기가 크고, 사용자의 행동 등을 포함함
Dimension 테이블
- Fact 테이블에 대한 상세 정보를 제공하는 테이블
- 고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공
- Fact 테이블의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 해줌
- 일반적으로 primary key를 가지며, fact 테이블의 foreign key에서 참조
- 보통 Fact 테이블에 비해 크기가 훨씬 작음
Fact 테이블은 굉장히 크기가 클 수 있고 이것을 DW로 다 로딩하는 것이 비용, 시간적인 측면에서 의미가 없음
값이 훨씬 싼 S3 등의 스토리지에 넣어놓고 필요할 때마다 로딩해서 처리하여 그것을 Redshift에 저장하는 것이 일반적
Dimension 테이블을 Redshift에 올려놓고 두 개를 join하는 것이 일반적

ex1
- Fact 테이블: user_session_channel (어떤 사용자가 어떤 채널에서 방문했는지)
- Dimension 테이블: user, channel 등
ex2
- Fact 테이블: order (사용자들의 상품 주문에 대한 정보)
- Dimension 테이블: product, user 등

Redshift Spectrum 유스 케이스

S3에 대용량 Fact 테이블이 파일(들)로 존재
Redshift에 소규모 Dimension 테이블이 존재
Fact 테이블을 Redshift로 적재하지 않고 위 두 테이블을 조인하고 싶을 때 최적의 방안이 Redshift Spectrum!
- 이는 별도로 설정하거나 론치하는 것이 아니라 Redshift의 확장 기능으로 사용하고 그만큼 비용 부담

외부 테이블 (External Table)

데이터베이스 엔진이 외부에 저장된 데이터를 마치 내부 테이블처럼 사용하는 방법
- 보통 S3와 같은 클라우드 스토리지에 저장된 대량의 데이터를 DB 내부로 읽어오지 않고 임시 목적으로 사용하는 방식
SQL 명령어로 데이터베이스에 외부 테이블 생성 가능
- 데이터를 새로 만드는 것이 아니라 참조만 하게 됨
- 외부 테이블은 CSV, JSON, XML과 같은 파일 형식 뿐만 아니라, ODBC 또는 JDBC 드라이버를 통해 액세스하는 원격 DB와 같은 다양한 데이터 소스에 대해 사용 가능
외부 테이블을 사용하여 데이터 처리 후 결과를 DB에 적재하는 데 사용 가능
- 예를 들어, 외부 테이블로 로그 파일을 읽고 정제된 내용을 DB 테이블에 적재 가능
외부 테이블은 보안 및 성능 문제에 대해 신중한 고려가 필요
대부분의 빅데이터 시스템에서 사용됨

Redshift Spectrum 사용 방식

S3에 있는 파일들을 테이블처럼 SQL로 처리 가능
- S3 파일들을 외부 테이블로 처리하면서 Redshift 테이블과 join 가능
이를 사용하려면 Redshift 클러스터가 필요
- S3와 같은 region에 있어야 함
S3 데이터를 외부 테이블(External Table)로 정의해야 함

외부 테이블 용 스키마 설정

S3FullAccess 권한을 적용했던 역할에 AWSGlueConsoleFullAccess 권한 추가 필요
다음으로 아래 SQL을 실행하여 외부 테이블용 스키마 생성

CREATE EXTERNAL SCHEMA external_schema
FROM data catalog
DATABASE 'myspectrum_db'
IAM_ROLE '[IAM ARN]'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

AWS GLUE

AWS의 Serverless ETL 서비스로 아래의 기능 제공
데이터 카탈로그
- AWS Glue Data Catalog는 데이터 소스 및 대상의 메타데이터를 대상으로 검색 기능 제공
- 주로 S3나 다른 AWS 서비스 상의 데이터 소스를 대상으로 함
ETL 작업 생성: AWS Glue Studio
- 간단한 드래그 앤 드롭 인터페이스를 통해 ETL 작업 생성 가능
- 사용자는 데이터 소스 및 대상을 선택하고 데이터 변환 단계를 정의하는 스크립트 생성
작업 모니터링 및 로그
- AWS Glue 콘솔을 통해 ETL 작업의 실행 상태 및 로그 모니터링 가능
서버리스 실행
- 서버리스 아키텍처 사용 -> 사용자는 작업을 실행하는 데 필요한 인프라를 관리할 필요가 없음 (Auto Scaling)
Redshift, Athena를 쓸 때 꼭 써야하는 서비스이지만, 전체적으로 그렇게 많이 쓰이는 서비스는 아님

외부 Fact 테이블 정의

S3에 usc라는 폴더를 버킷 밑에 만들고, 그 폴더로 user_session_channel.csv 파일 복사
- 매우 큰 파일이라고 가정!
그 다음 아래 SQL 실행
- 이런 형태의 명령은 Hive/Presto/SparkSQL에서 사용됨

CREATE EXTERNAL TABLE external_schema.user_session_channel (
    userid integer,
    sessionid varchar(32),
    channel varchar(32)
)
row format delimited     -- 한 줄이 한 레코드에 해당
fields terminated by ',' -- csv파일 -> 콤마 기준
stored as textfile       -- 텍스트 파일로 저장되어 있음
location '[s3 URI]';     -- 파일 위치

내부 Dimension 테이블

user 테이블을 raw_data 스키마 밑에 생성

CREATE TABLE raw_data.user_property AS
SELECT
    userid,
    CASE WHEN CAST(random() * 2 as int) = 0 THEN 'male' ELSE 'female' END gender,
    (CAST(random() * 50 as int) + 18) age
FROM (
    SELECT DISTINCT userid
    FROM raw_data.user_session_channel    
)

Fact + Dimension 테이블 조인

SELECT gender, COUNT(1)
FROM external_schema.user_session_channel usc
JOIN raw_data.user_property up ON usc.userid = up.userid
GROUP BY 1;

5. Redshift ML 사용하기

머신러닝 모델

머신러닝의 최종 산물
학습된 패턴(훈련셋)에 따라 예측을 해주는 블랙박스
- 선택한 머신러닝 학습 알고리즘에 따라 내부가 달라짐
- 디버깅은 쉽지 않으며 왜 동작하는지 이유를 설명하기도 쉽지 않음
- 훈련셋의 품질이 머신러닝 모델의 품질을 결정
입력 데이터를 주면 그를 기반으로 예측 (지도학습)
- 비지도학습, 강화학습도 있음
머신러닝 모델 트레이닝/빌딩
- 머신러닝 모델을 만드는 것
- 입력은 훈련셋

AWS SageMaker

머신러닝 모델 개발을 처음부터 끝까지 해결해주는 AWS 서비스
- MLOps 프레임워크
크게 4가지 기능 제공
- 훈련 셋 준비
- 모델 훈련
- 모델 검증
- 모델 배포와 관리
  - API 엔드포인트, 배치 서빙, …
다양한 머신러닝 프레임워크 지원
- Tensorflow/Keras, PyTorch, MXNet, …
- 자체 SageMAker 모듈로 머신러닝 모델 훈련 가능
SageMaker Studio라는 웹 기반 환경 제공 (노트북)
다양한 개발 방식 지원
- 기본적으로 Python Notebook (SageMaker 모듈)을 통해 모델 훈련
  - Scala / JAVA SDK도 제공
- AutoPilot이라는 코딩 불필요 모델 훈련 기능 제공
  - 이 경우에도 코드를 만들어줌
다른 클라우드 업체들도 비슷한 프레임워크 제공

AutoPilot

SageMaker에서 제공되는 AutoML 기능
- AutoML: 모델 빌딩을 위한 훈련용 데이터셋을 제공하면 자동으로 모델을 만들어주는 기능
훈련용 데이터셋을 입력으로 다음을 자동으로 수행
- EDA를 수행하고 이를 파이썬 노트북으로 만들어줌
- 다수의 머신러닝 알고리즘과 하이퍼 파라미터의 조합에 대해 아래 작업을 수행
  - 머신러닝 모델 생성, 훈련, 테스트, 테스트 결과 기록
- 선택 옵션에 따라 모델 테스트까지 다 수행하기도 하지만, 코드를 만드는 단계(노트북)로 마무리도 가능
  - 즉 모델 개발 속도 단축이 가능
최종적으로 사용자가 모델을 선택 후 API로 만드는 것도 가능
- 여기에 로그를 설정할 수 있음 (전체 로깅이나 샘플 로깅 설정 가능)

전체적인 절차

1) 캐글 Orange Telecom Customer Churn 데이터셋 이용
2) 데이터 준비: csv 파일을 S3 버킷 아래 폴더로 업로드
3) 위의 데이터를 raw_data.orange_telecom_customers로 로딩 (COPY)
4) SageMaker 사용 권한을 Redshift cluster에 지정 (IAM Role 생성)
5) CREATE MODEL 명령 사용 - 모델을 생성하고 모델 사용시 호출할 SQL 함수도 생성 - 이때 SageMaker와 관련한 비용이 발생함 유의!
6) Model SQL 함수를 사용해서 테이블 상의 레코드들을 대상으로 예측 수행
7) 사용이 다 끝난 후 SageMaker와 관련한 리소스 제거

데이터

21개 컬럼과 3,333개 레코드를 갖는 csv 파일
20% test셋 (Purpose 컬럼 값이 “Test”), 80%는 “Train”
예측할 컬럼: Churn

S3 버킷에 파일 업로드

S3 웹콘솔로 이동
S3 버킷 아래 redshift_ml 폴더 생성
csv 파일 업로드

Redshift로 로딩

CREATE TABLE raw_data.orange_telecom_customers (
    state varchar, 
    account_length integer,
    area_code integer,
    international_plan varchar,
    ...
    customer_service_calls integer,
    churn varchar,
    purpose varchar
);

COPY raw_data.orange_telecom_customers
FROM '[S3 URI]'
CREDENTIALS 'aws_iam_role=[ROLE ARN]'
delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1
removequotes;

SageMaker 사용 권한 부여

IAM Role 생성
AmazonS3FullAccess, AmazonSageMakerFullAccess 정책 부여하여 생성
Edit trust policy
- Edit statement에서 AssumeRole 선택
- Add principal에서
  - Principal type: AWS services
  - ARN: redshift.amazonaws.com
- Update policy
이 Role의 ARN 문자열 복사
이 역할을 Redshift cluster에 지정

CREATE MODEL 명령으로 ML 모델 생성

CREATE MODEL orange_telecom_customers_model
FROM (
    SELECT
        state, acount_length, ..., customer_service_calls, churn
    FROM raw_data.orange_telecom_customers
    WHERE purpose = 'Train'
)
TARGET churn
FUNCTION ml_fn_orange_telecom_customers
IAM_ROLE '[ROLE ARN]'
SETTINGS (
    S3_BUCKET '[bucket명]'
);

SHOW MODEL [모델명] 명령어로 모델이 준비되었는지 체크
- SageMaker가 백그라운드에서 가장 동작을 잘하는 알고리즘과 파라미터 결정
3-4시간 정도 소요됨

스크린샷 2023-12-04 오후 8 43 58

Model SQL 함수로 Test 레코드 예측 수행

앞서 만들어진 ml_fn_orange_telecom_customers 호출 결과와 churn 필드와 비교

SELECT churn,
    mo_fn_orange_telecom_customers(
        state, account_length, area_code, ..., customer_service_calls
    ) AS "prediction"
FROM raw_data.orange_telecom_customers
WHERE purpose = "Test";

Model 제거 및 청소

Drop Model 실행
SageMaker 웹 콘솔에서 남은 잔재들이 있는지 확인

6. Redshift 중지/제거

고정비용 옵션만!
Redshift 고정비용 서비스는 주기적으로 버전 업그레이드를 위해 중단됨
- Mainteneance window라고 부름
- Serverless에는 존재하지 않음

테이블 청소와 최적화 : `VACUUM`

테이블 데이터 정렬
- Redshift 테이블에 데이터가 삽입, 업데이트 또는 삭제될 때 데이터가 불규칙하게 분산되어 저장될 수 있는데,
- VACUUM 명령어는 데이터를 정렬하여 남아있는 행을 모야 쿼리 실행 시 검색해야 할 블록 수를 줄임
디스크 공간 해제
- 테이블에서 행이 삭제되면 디스크 공간이 그 즉시 해제되지 않음
- VACUUM 명령어는 더 이상 필요하지 않은 행을 제거하고 사용한 디스크 공간을 해제
삭제된 행에서 공간 회수
- 테이블에서 행이 삭제되면 VACUUM 명령 실행 전까지 이 공간은 회수되지 않음
테이블 통계 업데이트
- VACUUM은 테이블 통계를 업데이트하여 Query Planner가 쿼리 최적화 지원
큰 테이블에 대한 VACUUM 명령은 리소스를 많이 잡아먹음
- 바쁘지 않을 때 실행해주는 것이 좋음!

고정비용 Redshift 클러스터 중지 / 재실행

당분간 필요없다면
- Redshift 콘솔에서 해당 Redshift 클러스터를 선택하고 상단 메뉴에서 STOP 선택
- 이 경우 클러스터의 스토리지 비용만 부담
- SQL 실행은 불가능
다시 필요해지면
- 같은 메뉴에서 RESUME 선택
정말 필요없다면
- 같은 메뉴에서 DELETE 선택
- 이때 데이터베이스 내용 백업을 S3로 할 지 여부 선택 가능
- 이 S3 백업으로부터 Redshift 클러스터를 나중에 새로 론치 가능

가변비용 Redshift Serverless 삭제

먼저 모든 Workgroup 삭제
이후 Namespace 삭제