[Week8 AWS 데이터웨어하우스] TIL 33일차 Redshift 고급

Ⅰ. 권한과 보안

사용자와 테이블은 유한하게 늘어날 수 있어 각각을 기준으로 접근권한을 지정하려면 무수히 많은 조합이 생긴다.

따라서, 그룹이나 역할별로 스키마 접근 권한을 부여한다.

 

1. 그룹별 접근 권한 부여

admin : 모든 스키마에 접근할 수 있는 관리자 그룹

pii_users : 개인정보를 읽을 수 있는 그룹

analytics_authors : 데이터웨어하우스의 데이터를 수정, 읽을 수 있는 그룹

analytics_users : 데이터웨어하우스의 데이터를 읽을 수 있는 그룹

 

- 모든 권한을 부여하는 경우(r/w) : All

GRANT ALL ON SCHEMA 스키마명1 TO GROUP 그룹명1;
GRANT ALL ON ALL TABLES IN SCHEMA 스키마명1 TO GROUP 그룹명1;

 

- 특정 권한을 부여하는 경우(r) : Usage-select

GRANT USAGE ON SCHEMA 스키마명1 TO GROUP 그룹명1;
GRANT SELECT ON ALL TABLES IN SCHEMA 스키마명1 TO GROUP 그룹명1;

 

2. 역할별 접근 권한 부여(IAM Role)

역할의 경우 그룹과 달리 계승이 가능하기 때문에 새로운 역할을 만들 때 편리하다.(요즘에는 역할별 접근 권한을 활발히 이용하는 추세이다.)

특정 역할을 생성해서 공통된 접근권한을 부여하고, 이를 각 개인/그룹에 부여한다.

 

3. 공통

한 사람이 여러 역할에 속할경우 역할과 관련된 모든 권한을 갖는다.

 

아래 두가지 방법으로도 권한을 부여할 수 있는데, 피치 못할 사정으로 정보가 노출될 수도 있으므로 별도 테이블로 관리하거나, 사용하지 않는 경우 아예 데이터 웨어하우스에 업로드하지 않는 것이 좋다.

1) 컬럼 레벨 보안(Column Level Security)

특정 컬럼 기준으로 사용자/그룹에게 접근권한을 부여한다.

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

특정 컬럼 기준으로 사용자/그룹에게 접근권한을 부여한다.

CREATE RLS POLICY 
ATTACH RLS POLICY

 

 

Ⅱ. 백업과 테이블 복구

옵션	고정비용	가변비용(Serverless)
	이전 Snapshot을 기준으로 바뀐 내용들만 새로운 Snapshot으로 저장 (바뀐 것만 저장하기 때문에 용량이 덜 든다) 고정된 자원으로 snapshot용 스토리지가 바로 할당된다. 기본 하루가 저장되고 최대 35일까지 무료료 저장할 수 있다.	바뀐 내용만 저장하지 않고 모든 기록을 저장 → Recovery Point 생성 복구 시점은 30분마다 자동으로 생성, 24시간 동안 보관 → Snapshot 생성
실행	Redshift>Maintenance>Back up Details>Edit automated shapshot period 선택	Redshift>namespace>복구시점>Recovery Point선택>복구시점에서 스냅샷 생성
	다른 지역의 S3로 백업할 수도 있는데, 데이터가 있는 지역 재난시 복구에 유리하다.
실행	Redshift>Actions>Configure Cross-region snapshot Redshift>namespace>데이터백업>Cross region backup>예

 

 

Ⅲ. AWS Spectrum - AWS 사용 시 권장

추가 비용을 내고 데이터레이크(S3)의 정보를 처리할 수 있는 기능으로 Redshift와 같은 지역의 S3에 대해서, S3 파일들(외부저장)을 내부 테이블(Factor table)로 간주하고 SQL 처리할 수 있다.(조인연산도 가능)

간주하기 때문에 테이블을 새로 생성하지 않고 참조(-보통 읽기 전용)한다.(csv, json, xml, 관계형 DB)

외부테이블이 클 경우 성능 이슈가 발생할 수 있고 보안 문제에 유의해야 한다.

Redshift 로딩하는 오버헤드 없이 처리할 수 있다.

데이터 레이크는 평균적으로 데이터의 크키가 크고, 상대적으로 데이터웨어하우스(Dimension table)는 평균적으로 데이터 크기가 작다.

 

cf) Factor table과 Dimesion table

Factor table : 분석의 대상이 되는 양적 정보를 포함하는 중앙 데이블(매출 수익, 판매량 등)

Dimension table : Factor 테이블에 대한 상세 정보를 제공하는 테이블(사용자 정보, 물품 정보 등)

보통 Factor table의 크기 >> Dimension table 크기이다 따라서 보통 Dimension 테이블은 Redshift에 적재하고 Factor table은 비용이 저렴한 S3에 저장한 뒤, 일부 가공하여 Redshift에 적재한다.

 

데이터레이크와 데이터웨어하우스에 적재하는 데이터 정보

	데이터레이크(S3)	데이터 웨어하우스(Redshift)
테이블 종류	Dimension table	Factor table
예시1	user_session_channel order	user, channel product, user

 

사용

데이터레이크 JOIN 데이터웨어하우스

데이터레이크의 파일을 가공하여 데이터레이크에 저장

데이터레이크의 파일을 가공하여 데이터 웨어하우스에 저장

 

실습

user_session_channel을 외부테이블로 만든 뒤에 Redshift와 조인 연산을 수행해 보자.

 

1. S3 버킷 생성

2. Redshift 서버 생성

3. Role 생성

IAM>역할>역할생성

IAM> 역할> 권한추가

- Amazon S3 Full Access

- AWSGlueConsoleFullAccess

4. Redshift에 Role 부여

(이전의 포스팅과 동일)

---

5. 외부 테이블용 스키마 생성

S3의 external 폴더에 user_session_channel.csv 파일 업로드한다.

%%sql
-- AWSGlueConsoleFullAccess
CREATE EXTERNAL SCHEMA external_schema
from data catalog
database 'myspectrum_db'
iam_role 'IAMROLE arn 주소'
create external database if not exists;

 

6. 외부 테이블 정의

SQL 문장을 이용해 외부 테이블을 정의한다.

%%sql
CREATE EXTERNAL TABLE external_schema.user_session_channel (
   userid integer ,
   sessionid varchar(32),
   channel varchar(32)
)
row format delimited
fields terminated by ','
stored as textfile
location 'S3>external 폴더 URI';

 

- location 폴더 내의 모든 파일들 각각을 테이블처럼 로딩(단, 파일 간의 포맷이 같아야 한다.)

이 과정에서 계속 오류가 발생했는데,

1. S3 버킷의 퍼블릭 액세스 차단을 해지

2. AWS Glue 직접 연결 후 삭제한 뒤 다시 실행

두 가지 방법을 실행한 뒤에 다시 코드를 실행하니 정상적으로 수행되었다.

 

7. 외부테이블과 내부테이블을 조인

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

 

 

Ⅳ. Athena(Apache presto 서비스화) - 다른 클라우드 사용 시 권장

Spectrum과 유사한 기능으로 데이터레이크(S3) 정보를 처리할 수 있다.

단) 테이블로 간주하지는 않는다.

 

 

Ⅴ. Redshift ML

AWS Sagemaker(최적화된 모델을 자동 생성하는 기능)에 의해 지원되는 기능으로 머신러닝 훈련 데이터를 로딩하면 최적의 Supervised machine 알고리즘을 만들어준다.

 

Sagemaker

머신러닝 모델 개발을 처음부터 끝가지 해결해 주는 서비스

Tensorflow, Keras, Pytorch 등의 프레임워크 지원

 

제공하는 기능

- Traning Set 준비

- 모델 훈련

- 모델 검증

- 모델 배포와 관리

 

SageMaker Studio

웹 기반의 환경 제공

 

Autopilot

코딩을 하지 않고 모델을 만들어주는 기능 제공(AutoML).

1) EDA 수행 : 각 필드 특성 파악. drop, scaling(정규화)

2) 여러 머신러닝 알고리즘과 하이퍼 파라미터의 조합에 대해 훈련-테스트 수행한 뒤 최적의 모델을 선택

3) 이후 API로 만드는 것도 가능

 

실습 : 간단한 ML 모델 생성해서 적용하기

1. 캐글 데이터 다운로드

2. S3 버킷에 데이터 업로드

3. Redshift에 COPY 명령어를 사용하여 벌크 업데이트

테이블 생성(create table)

벌크 업데이트(copy)

4. Sagemaker에게 Redshift 사용권한 부여

- 새로운 역할 생성 : Redshift-ML

IAM> 역할> 역할생성

AWS service 선택 + SageMaker-Excution 선택

IAM Sagemaker 역할생성

 

정책

AmazonSageMakerFullAccess(sagemaker 선택 시 기본으로 적용)

역할> redshift-ML> 권한> 권한추가

AmazonS3 FullAccess(새로 추가해야 함)

 

그런데 현재 상황의 경우 Redshift, Sagemaker 서로 권한을 가지고 있어야 한다.

역할> redshift-ML> 신뢰관계> 신뢰정책편집> 보안주체 추가를 다음과 같이 한다.

Redshift-ML 보안 주체 추가

 

5. Redshift에 Sagemaker 권한 부여

Redshift> Namespace> 보안 및 암호화> IAM 역할관리> IAM 역할 연결

Redshift-ML 선택한다.

 

6. 머신러닝 모델 생성(80% 데이터 사용)

- create model(train 데이터의 80% 사용)

CREATE MODEL SQL로조회할수있는모델이름
FROM (
  SELECT 
      x로 원하는 컬럼목록(,로 구분)
  FROM 스키마.테이블명
  WHERE purpose = 'Train'
)
TARGET y로 원하는 컬럼
FUNCTION 함수이름
IAM_ROLE 'Redshift-ML arn 붙여넣기'
SETTINGS (
  S3_BUCKET '저장할 s3버킷이름'
);

S3버킷 하위에 redshift_ml폴더가 생성되며 모델 관련 모든 로그들이 저장된다.

 

모델빌드확인

SHOW MODEL SQL로조회할수있는모델이름;

 

7. 테스트 수행

(train 테이터의 나머지 20% 사용)

SELECT 예측대상,
       ml_fn_orange_telecom_customers(
          x로 원하는 컬럼명
       ) AS "prediction"
FROM 스키마.테이블명
WHERE purpose = 'Test';

 

8. Sagemaker 관련 리소스 제거

DROP MODEL SQL로호출할수있는모델이름;

+ AWS 웹에 접속해서 Sagemaker관련 파일 삭제

 

 

Ⅵ. Redshift 중지/종료하기

1. 고정비용

Maintenance window가 주기적으로 버전 업그레이드를 위해 중단

중지

Redshift 클러스터를 선택> Pause → 다시 실행 시(Resume)

삭제

Redshift 클러스터를 선택> Delete

해당 클러스터를 S3 snapshot으로 저장할 건지 여부를 결정해야 함(이후에 복구 가능)

 

2. 가변비용(serverless)

관련된 Workgroup 삭제

관련된 Namespace 삭제

 

3. 공통) Vacuum : 테이블 청소화 최적화

DB 테이블의 삭제나 수정이 빈번한 경우 최적화해주는 기능. 리소스를 많이 잡아먹기 때문에 바쁘지 않을 때 수행해야 한다!

- 테이블 데이터 정렬

데이터를 정렬하여 분산되어 있는 행을 모아 쿼리 실행 시 검색해야 할 블록수를 줄이는 작업 수행

- 디스크 공간 해제/회수

삭제된 행의 디스크 공간은 즉시 해제되지 않으므로 공간 회수 및 직접적으로 해제

- 테이블 통계 업데이트

테이블 통계를 업데이트하여 Query Planner의 쿼리 최적화를 지원