Redshift 고급 기능 -TIL230524

📚 KDT WEEK 8 DAY 3 TIL

• Redshift 권한과 보안
• Redshift 백업과 복구
• Redshift 관련 서비스
  • Redshift Spectrum + 실습
  • Athena
  • Redshift ML (SageMaker) + 실습
• Redshift 중지 및 제거 및 최적화

---

 

🟥 Redshift 권한과 보안

 

Redshift 사용자별 테이블 권한 설정

• 사용자별 테이블별 따로따로 권한을 지정하지는 않고, IAM 그룹과 역할을 활용한다.
• 최근엔 RBAC(Role Based Access Control)가 일반적이다.
• 여러 역할의 속한 사용자는 각 역할의 권한을 모두 가진다.
• GROUP 키워드를 ROLE로 바꾸어도 동일하게 동작한다.

다음은 Colab에서 간단하게 실습한 결과이다.

그룹을 먼저만들고, 그룹에 권한을 부여해주었다.

CREATE GROUP 그룹명 으로 그룹을 생성하고, GRANT 명령어로 권한을 부여한다.

GRANT ALL 은 읽기와 쓰기 권한을 부여하고, GRANT USAGE 는 SELECT 권한을 부여한다.

analytics_authors 그룹은 analytics, adhoc 스키마의 모든 테이블 읽기, 쓰기 권한과 raw_data의 모든 테이블 읽기 권한을 얻었다.

 

 

컬럼 레벨 보안(Column Level Security)

• 한 테이블 내에서 특정 컬럼에 대해 권한을 부여하는 것
• 보통 개인정보에 해당하는 컬럼을 감추기 위해 사용한다.
• 사실은 이러한 컬럼을 별도의 테이블로 구성하는 것이 베스트

 

레코드 레벨 보안 (Row Level Security)

• 한 테이블 내에서 특정 레코드에 대해 권한을 부여하는 것
• 역시 별도의 테이블을 구성하는 것이 더 좋다.

 

---

 

🟦 Redshift 백업과 복구

 

Redshift 데이터 백업 (고정비용)

• Snapshot : 마지막 백업으로부터 바뀐 것들만 저장한다.
• Table Restore : 스냅샷을 활용하여 특정 시점의 특정 테이블을 복구하는 것이 가능
• 자동 백업 : 같은 지역 S3에 최대 35일까지의 변경을 백업 가능
• 매뉴얼 백업 : 사용자가 직접 언제든 현재 지점의 백업을 만들어 무한히 유지 가능
• Cross-regional snapshot : 유사시를 대비하여 다른 지역의 S3 백업을 두는 것

 

Redshift Serverless 데이터 백업

• Snapshot 이전에 Recovery Points(복구 시점)가 존재해서, Recovery points를 snapshot으로 바꾸는 과정이 필요
• 가변 비용의 경우 컴퓨팅 자원과 스토리지가 고정적이지 않아 snapshot 바로 할당이 어려움
• 자동 백업 : Recovery Points가 생성되어 24시간동안 유지된다.
• 스냅샷을 활용한 다른 작업은 고정 비용 방식과 거의 동일하게 가능하다.

 

Redshift Serverless의 네임스페이스 > 데이터 백업 항목을 확인해보면,

스냅샷은 텅 비었고, Recovery Points(복구 시점)만 자동 생성된다.

오른쪽 상단의 스냅샷 생성 버튼을 통해 직접 스냅샷을 만들어야 사용할 수 있다.

Recovery Points(복구 시점) 항목에서 하나를 선택하고, '복구 시점에서 스냅샷 생성' 버튼으로 스냅샷을 생성할 수도 있다.

 

---

 

🟩 Redshift의 관련 기타 서비스

 

🎈 Redshift Spectrum

S3의 파일들을 마치 테이블처럼 SQL로 처리 가능한 서비스

Redshift spectrum

 

• S3 파일들을 외부 테이블로 처리하여 Redshift 테이블과 조인이 가능하도록 함
• 1TB를 스캔할 때마다 $5
• S3와 Redshift 클러스터가 같은 region에 있어야한다.

 

Spectrum 서비스를 사용할 때 알면 좋은 키워드

1. Fact 테이블 : 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블

ex) 매출 수익, 판매량, 이익 등 사용자의 행동이 기록된 테이블

 

2. Dimension 테이블 : (사용자가 구매한) 물품이나 고객에 대한 정보 테이블

Fact 테이블의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 한다.

보통 Dimension 테이블 크기가 작고, fact 테이블의 foreign key로 참조됨.

 

3. 외부 테이블(External Table)

• DB 엔진이 외부에 있는 데이터를 마치 내부 테이블(읽기 전용)처럼 사용
• CSV, JSON, XML과 같은 파일 형식 뿐만 아니라 원격 데이터베이스(ODBC 등)와 같은 다양한 데이터 소스도 가능
• 외부 테이블을 사용하여 데이터 처리 후 결과를 내부 데이터베이스에 적재할 수 있다.
• 하지만 외부 테이블은 보안 및 성능 문제에 대해 신중하게 고려해야 한다.

 

4. AWS Glue

• AWS의 Serverless ETL 서비스
• AWS Glue Data Catalog는 데이터 소스 및 대상의 메타데이터 검색 기능을 제공
• 주로 S3나 다른 AWS 서비스 상의 데이터 소스를 대상으로 하므로 Spectrum이나 Athena에 필요한 서비스이다.
• 따라서 관련 서비스를 이용할 때 Glue 권한이 있는 역할이 필요하다. > Spectrum 실습할 때 넣어줄 예정

 

 

⏩ Redshift Spectrum 실습해보기

 

1. AWS Glue 권한 추가

 

기존에 S3FullAccess를 가지고 있던 역할에 Glue 권한도 추가해주었다.

AWSGlueConsoleFullAccess를 역할에 넣어준다.

 

 

2. S3 버킷에 외부 테이블로 사용할 파일 추가

 

usc 폴더를 만들어 해당 csv 파일을 넣어주었다.

 

3. Colab 실습

 

외부 스키마 생성

-- AWSGlueConsoleFullAccess
CREATE EXTERNAL SCHEMA external_schema
from data catalog
database 'myspectrum_db' -- myspectrum_db에 만든 외부 스키마 저장
iam_role 'arn:aws:iam::*****:role/redshift.read.s3' -- IAM ARL 넣기
create external database if not exists; -- 해당 DB가 없으면 만듦

myspectrum_db를 생성하여 해당 DB에 external_schema를 하나 만들었다.

중요한 점은, Glue 권한을 포함한 역할을 지정해주어야 한다.

 

Dimension 용도로 사용할 유저 정보 테이블 만들기

-- Dimension 용도의 테이블 하나 제작, 유저 정보 테이블
-- 유저의 성별과 나이(18~50)를 랜덤으로 부여하고 user_property 테이블로 저장한다.
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
);

 

외부 테이블을 만들어 양식에 맞게 데이터 할당

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://sunho-test-bucket/usc/'; -- 파일 경로

 

외부 테이블과 JOIN 결과

정상적으로 JOIN이 된 모습

 

 

---

 

 

🎈 Athena

• AWS의 Presto 서비스로, Redshift Spectrum과 비슷한 기능을 제공
• S3에 있는 데이터들을 기반으로 SQL 쿼리기능을 제공
• Redshift를 사용하지 않는다면 Athena를 사용하면 된다.

 

 

🎈 Redshift ML

• SQL만 사용해서 머신러닝 모델을 훈련가능하도록 하는 서비스
• AWS SageMaker(최적화 모델 자동 생성)에 의해 지원된다.
• BYOM(Bring Your Own Model)로 이미 있는 모델 사용도 가능

 

Redshift ML에서 알면 좋은 키워드

 

1. 머신러닝

• 트레이닝 셋 : 학습에 사용되는 데이터
• 레이블(Y) : 예측해야 하는 값
• Featuer(X) : 레이블을 예측하는 데 사용하는 힌트들

 

2. Amazon SageMaker

 

머신러닝 모델 개발을 처음부터 끝까지 해결하는 AWS 서비스(MLOps 프레임웍)

 

다음의 과정을 모두 지원해준다.

• 트레이닝 셋 준비
• 모델 훈련
• 모델 검증
• 모델 배포 및 관리

• SageMaker Studio라는 웹 기반 환경 제공
• Python Notebook에 SageMaker 모듈 넣어 훈련도 가능하다.

 

3. AutoPilot

SageMaker에서 제공되는 AutoML(데이터 주면 알아서 모델 만듦) 기능

 

⏩ Redshift ML 실습해보기

 

1. S3에 학습에 사용할 CSV 파일 업로드

S3 > sunho-test-bucket > redshift_ml > train.csv

 

2. SageMaker 권한을 위한 IAM Role 만들기

위 이미지처럼 IAM 역할을 하나 만들어준다.

Redshift가 SageMaker에 Access하고, SageMaker도 Redshift에 Access하므로 양쪽으로 관계를 설정해야한다.

 

이 설정은 해당 역할의 신뢰 관계 > 신뢰 정책 편집 에서 바꿀 수 있다.

왼쪽 상단의 신뢰 정책 편집 버튼을 클릭한다.

 

오른쪽 탭의 보안 주체 추가 버튼을 눌러 다음과 같이 편집해준다.

보안 주체를 추가하고 정책 업데이트 버튼을 눌러 저장한다.

 

3. Redshift 네임스페이스에 해당 역할 추가

Redshift 원하는 네임스페이스 > 보안 및 암호화 > IAM 역할 관리

 

만들었던 Redshift-ML 역할이 추가된 모습

 

4. S3의 train.csv를 Redshift 테이블에 COPY

테이블을 새로 먼저 하나 만든 뒤, COPY 커맨드를 사용한다.

-- 속성은 해당 csv 파일에 맞게 지정
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://sunho-test-bucket/redshift_ml/train.csv'
credentials 'aws_iam_role=arn:aws:iam::*****:role/redshift.read.s3' -- IAM ARN 입력
delimiter ',' dateformat 'auto' timeformat 'auto' IGNOREHEADER 1 -- 의미는 저번 글 참고(For CSV)
removequotes;

 

5. ML 모델 생성하기

CREATE MODEL 명령어를 사용하여 ML 모델을 생성한다.

참고로 데이터의 purpose 필드 값이 Train 이면 학습용 데이터로 사용하겠다는 의미로 저장했었다.

모델을 생성하면서 이 모델을 사용할 수 있는 함수도 같이 생성하게 된다.

 

CREATE MODEL orange_telecom_customers_model
FROM (
 SELECT
 state, account_length, …, customer_service_calls, churn -- 해당하는 속성들 모두 입력
 FROM raw_data.orange_telecom_customers
 WHERE purpose = 'Train'
)
TARGET churn -- 예측할 값 필드
FUNCTION ml_fn_orange_telecom_customers -- 나중에 함수로 사용할 때 사용할 함수명
IAM_ROLE 'arn:aws:iam::*****:role/Redshift-ML' -- IAM ARN(아까 만든 SageMaker ML 역할)
SETTINGS ( -- 버킷 지정
 S3_BUCKET 'sunho-test-bucket'
);

 

이후 모델이 만들어지는데, 데이터의 크기에 따라 다르겠지만 시간이 꽤 오래 소요된다고 한다.

약 2700개의 데이터를 학습하는데 3~4시간 정도 소요됬으며, 비용은 약 $21가 청구된다고 한다.

나는 비용이 청구되는게 부담스러워서 직접 실행하진 않았다..

 

6. 만들어진 모델 사용해보기

모델을 생성하며 만들었던 함수를 이용해서 예측값을 띄어보자.

 

SELECT churn, -- 정답이 될 것이다.
 ml_fn_orange_telecom_customers(
 state, account_length, area_code, international_plan, voice_mail_plan,
 number_vmail_messages, total_day_minutes, total_day_calls,
 total_day_charge, total_eve_minutes, total_eve_calls, total_eve_charge,
 total_night_minutes, total_night_calls, total_night_charge,
 total_intl_minutes, total_intl_calls, total_intl_charge,
 customer_service_calls
 ) AS "prediction" -- ml_fn_orange_telecom_customers 함수를 통해 예측값이 나온다.
FROM raw_data.orange_telecom_customers
WHERE purpose = 'Test'; -- 이번엔 Test용 데이터를 사용한다.

 

7. 만들어진 모델 제거하기!

이대로 모델을 내버려두면 용량도 차지하고 비용이 청구될 수도 있다고 한다.

다음의 과정으로 남은 잔재들을 꼭 청소해주자.

 

Drop Model '모델명'  명령어를 사용하여 모델을 삭제한다.

SageMaker 대시보드에서 Models, Endpoints에 해당하는 값을 모두 삭제한다.

 

모델을 더 이상 사용하지 않는다면 꼭 청소하는 것을 잊지 않도록 명심하자.

 

 

---

 

🟧 Redshift 중지 / 제거 및 최적화하기

 

Redshift(고정비용)의 경우

• 대시보드에서 Pause와 Resume을 적절히 사용하여 조절
• Redshift를 삭제하고 S3에 스냅샷을 저장해두었다가 나중에 새로운 Redshift cluster로 만들 수 있음
• Redshift 고정 비용 서비스는 주기적으로 버전 업그레이드를 위해 중단된다. (Maintenance window)

 

Redshift Serverless의 경우

• Serverless의 경우 고정비용처럼 중지할 수 없다.
• 제거해야하는 경우 다음의 과정을 따라야 한다.
  1. 먼저 모든 Workgroup들을 모두 삭제해야함
  2. 다음으로 모든 Namespace 삭제

 

Redshift 최적화하기

Redshift에서는 데이터가 삽입, 업데이트, 삭제될 때 데이터들이 불규칙하게 분산되어 저장된다.

이때 VACUUM 명령어를 통해 데이터를 정리하고, 남은 디스크 공간 회수 및 해제를 처리해준다.

 

VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX | RECLUSTER ] [table_name] [TO threshold PERCENT]

• FULL - 테이블 정렬 + 삭제된 공간 회수
• SORT ONLY - 테이블 정렬
• DELETE ONLY - 삭제된 공간 회수
• REINDEX - 인터리브 테이블 인덱스 재지정 + 테이블 정렬 + 삭제된 공간 회수

• TO threshold PERCENT : 지정한 임계값 이상으로 테이블이 정렬되어 있다면 정렬 단계를 생략한다.

 

다음은 사용 예시다.

VACUUM 사용 예시 - 출처) AWS document

 

VACUUM은 주기적으로 실행시켜주어 Redshift를 최적화해주면 좋지만, 리소스를 많이 잡아먹는 작업이다.

VACUUM 커맨드를 쓸 때에는 일이 없는 시간대에 실행해주면 좋다고 한다.

 

VACCUM 참고 문서 : https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_VACUUM_command.html

VACUUM - Amazon Redshift

 

---