[데이터 엔지니어링 데브코스 2기] TIL-8주차-파트04 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드(3)

1. Redshift 권한과 보안

1-1. 사용자별 테이블 권한 설정

• 일반적으로 사용자별 테이블별 권한 설정을 하지는 않음. (너무 복잡함.)
• 역할(Role) 혹은 그룹(Group) 별로 스키마 별 접근 권한을 주는 것이 일반적임.
  • 최근 PBAC(Role Based Access Control)가 새로운 트렌드로 사용됨. (그룹은 계승이 안 되기에, 계승이 되는 Role을 많이 사용함.)
  • 여러 역할에 속한 사용자의 경우, 각 역할의 권한을 모두 갖게 됨(Inclusive).
• 개인 정보와 관련한 테이블이라면 별도의 스키마로 설정해 주어야 함.
  • 극히 일부 사람들만 속한 역할에 접근 권한을 부여함.
• GROUP이라는 키워드 대신 ROLE로 바꾸어서 실행해도 동작함.

---

1-2. 사용자 그룹 권한 설정

• 각 사용자 그룹의, 각 테이블에 대한 각각의 권한을 설정.

• analytics_authors의 각 테이블에 대한 권한 설정.

-- analytics 스키마 안에 있는 모든 테이블에 대해서 모든 권한 부여.
GRANT ALL ON SCHEMA analytics TO GROUP analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_authors;

-- adhoc 스키마 안에 있는 모든 테이블에 대해서 모든 권한 부여.
GRANT ALL ON SCHEMA adhoc TO GROUP analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_authors;

-- raw_data 스키마 안에 있는 모든 테이블에 대해서 SELECT 권한만 부여.
GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_authors;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_authors;

• analytics_users의 각 테이블에 대한 권한 설정.

-- analytics 스키마 안에 있는 모든 테이블에 대해서 SELECT 권한만 부여.
GRANT USAGE ON SCHEMA analytics TO GROUP analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_users;

-- adhoc 스키마 안에 있는 모든 테이블에 대해서 모든 권한 부여.
GRANT ALL ON SCHEMA adhoc TO GROUP analytics_users;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_users;

-- raw_data 스키마 안에 있는 모든 테이블에 대해서 SELECT 권한만 부여.
GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_users;

• pii_users의 각 테이블에 대한 권한 설정.

-- pii 스키마 안에 있는 모든 테이블에 대해서 SELECT 권한만 부여.
GRANT USAGE ON SCHEMA pii TO GROUP pii_users;
GRANT SELECT ON ALL TABLES IN SCHEMA pii TO GROUP pii_users;

그룹으로 권한을 설정하니 계승이 되지 않아 불편한 점이 있다. 이 경우, 그룹 대신 ROLE을 사용하면 간편하게 바꿀 수 있다.

---

1-3. 칼럼 레벨 보안 (Column Level Security)

• 테이블 내의 특정 칼럼에 대해서 특정 사용자 혹은 그룹/역할에만 그 접근 권한을 부여하는 것.
• 보통 개인정보 등에 해당되는 칼럼을 권한이 없는 사용자들에게 감추는 목적으로 사용됨.
• 하지만, 이 방법은 좋은 방법이 아님.
• 제일 좋은 방법은, 칼럼들에 대한 정보를 별도의 테이블에 저장해서 해당 테이블에 대해 권한을 부여하는 것.

---

1-4. 레코드 레벨 보안 (Row Level Security)

• 테이블 내의 특정 레코드에 대해서 특정 사용자 혹은 그룹/역할에만 그 접근 권한을 부여하는 것.
• SELECT, UPDATE, DELETE 작업에 따라 권한을 다르게 부여 가능.
  • 이를 RLS(Record Level Security) Policy라고 부름.
  • CREATE RLS POLICY 명령어로 Policy 생성, ATTACH RLS POLICY 명령어로 추가.
• 이 역시 추천하는 방법이 아님.
• 필요없는 정보는 로딩하지 않는 것이 best.

---

2. Redshift 백업과 테이블 복구

2-1. Redshift의 데이터 백업 방식

• 기본적인 백업 방식은, 마지막 백업으로부터 바뀐 것들만 저장하는 방식.
  • 이를 Snapshot이라고 부름.
  • 백업을 통해 과거의 특정 테이블을 복구하는 것이 가능함. (Table Restore)
  • 과거 시점의 내용을 기준으로 Redshift 클러스터를 새로 생성하는 것도 가능함.
• 자동 백업:
  • 기본은 하루 단위의 백업이나, 최대 35일까지의 변경을 백업할 수도 있음.
  • 같은 Region의 S3에 저장됨.
  • 서로 리젼이 다른 경우, Cross-regional snapshot copy를 설정해야 함. (재난에 대한 대비용.)
• 매뉴얼 백업:
  • 백업을 원할 때 만드는 백업으로 명시적으로 삭제 될 때까지 유지됨.

---

2-2. Redshift Serverless의 데이터 백업 방식

• 고정비용에 비하면 좀더 복잡함.
• Recovery Points 생성 -> Snapshot으로 변경.
• Recovery Points(Snapshot)은 과거 24시간에 대해서만 유지됨.

---

3. Redshift 관련 기타 서비스 소개

• Redshift Spectrum : S3 등에 있는 파일들을 테이블처럼 사용 가능하게 해줌.
• Redshift Serverless
• Athena : Apache Presto를 서비스화한 것.
• Redshift ML

3-1. Redshift Spectrum

• Redshift의 확장 기능.
• S3에 있는 파일들을 마치 테이블처럼 SQL로 처리 가능.
  • S3 파일들을 external table로 처리하여 Redshift 테이블과 조인 가능.
  • Fact 테이블 : S3 외부 테이블.
  • Dimension 테이블 : Redshift 테이블.
  • 1TB를 스캔할 때마다 $5 비용.
• 이를 사용하기 위해서 Redshift 클러스터가 필요.
• S3와 Redshift는 같은 리젼에 있어야 함.

---

3-2. Athena

• AWS의 Presto 서비스로, 사실상 Redshift Spectrum과 비슷한 기능을 제공.
• S3에 있는 데이터들을 기반으로 SQL 쿼리 기능 제공.
  • 이때 S3를 데이터 레이크로 볼 수 있음.

---

3-3. Redshift ML

• SQL만 사용하여 ML 모델을 훈련하고 사용할 수 있게 해주는 기능.
• AWS SageMaker에 의해 지원되는 서비스.
  • SageMaker는 Auto Pilot이라 하여 최적화된 모델을 자동 생성해 주는 기능을 제공함.
• 이미 만들어진 모델에 대해서도 사용 가능. (BYOM : Bring Your Own Model)

---

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

• S3에 있는 데이터를 정제해서 데이터 웨어하우스(Redshift)에 저장하고 싶은 경우 사용.

4-1. Fact 테이블과 Dimension 테이블

• Fact 테이블 : 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블.
  • 일반적으로 매출 수익, 판매량 또는 이익과 같은 사실 또는 측정 항목을 포함하며, 비즈니스 결정에 사용됨.
  • Fact 테이블은 일반적으로 외래 키를 통해 여러 Dimension 테이블과 연결됨.
  • 보통 Fact 테이블의 크기가 Dimension 테이블보다 훨씬 더 큼.
• Dimension 테이블 : Fact 테이블에 대한 상세 정보를 제공하는 테이블.
  • 고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공.
  • Fact 테이블의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 해줌.
  • Dimension 테이블은 일반적으로 primary key를 가지며, fact 테이블의 foreign key에서 참조함.
  • 보통 Dimension 테이블의 크기는 Fact 테이블보다 훨씬 더 작음.

예시1)

• Fact 테이블 : 앞서 사용했던 user_session_channel.
• Dimension 테이블 : 사용자나 채널에 대한 정보.
  • user
  • channel

예시2)

• Fact 테이블 : Order 테이블. 사용자들의 상품 주문에 대한 정보가 들어간 테이블.
• Dimension 테이블 :
  • Product 테이블. 상품 정보 포함.
  • User 테이블. 사용자 정보 포함.

---

4-2. Redshift Spectrum 사용 케이스

• S3에 대용량 Fact 테이블 존재.
• Redshift에 소규모 Dimension 테이블 존재.
• Fact 테이블을 Redshift에 적재하지 않고 위의 두 테이블을 조인하고 싶다면,
  • Redshift Spectrum 사용.

---

4-3. 외부 테이블(External Table)

• 데이터베이스 엔진이 외부에 저장된 데이터를 마치 내부 테이블처럼 사용하는 방법.
• SQL 명령어로 데이터베이스에 외부 테이블 생성 가능. (데이터를 새로 생성X. 그저 참조.)
• 외부 테이블은 보안 및 성능 문제와 관련하여 신중한 고려가 필요함.

---

4-4. Redshift Spectrum 사용 방식

• S3에 있는 파일들을 external table로 처리한 후, Redshift 테이블과 조인.
• 이를 사용하려면 (같은 리젼의) Redshift 클러스터가 필요.

1. 먼저 앞서 만든 redshift.read.s3 ROLE에 AWSGlueConsoleFullAccess 권한 지정.
2. 아래 SQL을 실행하여 외부 테이블용 스키마 생성.

CREATE EXTERNAL SCHEMA external_schema
FROM data catalog
DATABASE 'myspectrum_db'
iam_role 'arn:aws:iam::xxx:role/redshift.read.s3'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

AWS Glue란, AWS Serverless의 ETL 서비스로 아래의 기능을 제공함.

• 데이터 카탈로그 : 데이터 소스 및 대상의 메타데이터를 대상으로 검색 기능을 제공함.
• ETL 작업 생성 : AWS Glue Studio.
  - 간단한 UI로 ETL 작업 생성 가능.
• 작업 모니터링 및 로그.
• 서비리스 실행.

---

4-5. Redshift Spectrum 실습

외부 Fact 테이블 생성)

• S3에 usc라는 폴더를 S3 버킷 밑에 생성.
• 해당 폴더에 user_session_channel.csv 파일을 복사.
• 아래 SQL 실행.

CREATE EXTERNAL TABLE external_schema.user_session_channel (
	userid INTEGER,
    sessionid VARCHAR(32),
    channel VARCHAT(32)
)
-- 한 줄이 한 레코드.
row format delimited
-- csv 파일이기에 ','로 구분.
fields terminated by ','
sotred as textfile
location 's3://buckect_name/usc/';

내부 Dimension 테이블 생성)

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 사용하기

5-1. Amazon SageMaker란?

• 머신러닝 모델 개발을 처음부터 끝까지 해결해주는 AWS 서비스
• 크게 4가지 기능 제공
  • 트레이닝 셋 준비
  • 모델 훈련
  • 모델 검증
  • 모델 배포와 관리 (API 엔드포인트, 배치 서빙, ...)
• 다양한 머신러닝 프레임웤 지원
  • Tensorflow/Keras, PyTorch, ...
  • 자체 SageMaker 모듈

• SageMaker Studio라는 웹기반(노트북) 환경 제공
• 다양한 개발 방식 지원.
  • 기본적으로는 Python Notebook을 통해 모델 훈련.
  • AutoPilot을 통해 코딩 없이도 모델 훈련 가능. (훈련용 데이터셋을 보고 모델을 알아서 생성해 줌.)

---

5-2. 실습

1. 캐글 Orange Telecom Customer Churn 데이터셋 다운로드.

2. S3 버킷의 redshift_ml 폴더 생성 후 파일 저장.

3. 해당 S3 데이터를 raw_data.orange_telecom_customers로 COPY.

4. SageMaker 사용 권한을 Redshift cluster에 지정.

   • IAM ROLE 생성 후 지정 (AmazonSageMakerFullAccess)

5. CREATE MODEL 명령 사용.

6. Model SQL 함수를 사용하여 테이블상의 레코드들을 대상으로 예측 수행.

7. 실습 후 SageMaker 관련 리소스 제거.

---

6. Redshift 중단/제거하기

• Redshift 고정비용 서비스는 주기적으로 버전 업그레이드를 위해 15분 정도 중단됨.
  • 이를 Maintenance window라고 부름.
  • Serverless에는 이게 존재하지 않음.

6-1. 테이블 청소와 최적화 - VACCUM 명령

• 테이블 데이터 정렬
• 디스크 공간 해제
• 삭제된 행에서 공간 회수
• 테이블 통계 업데이트
• 큰 테이블에 대한 VACCUM 명령은 리소스를 많이 잡아 먹음.

---

6-2. 고정비용 Redshift 클러스터 중지/실행

• 해당 클러스터에 들어가서 Actions의 Pause or Delete를 선택하면 됨.

---

6-3. 가변비용 Redshift 클러스터 삭제

• 모든 Workgroup을 삭제 후 namespace 삭제.