[TIL - 33일 차] 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드 (3)

Redshift 고급 기능 실습

Redshift 권한과 보안

사용자별로 권한을 설정하면 복잡하고 실수의 가능성이 높기 때문에 일반적으로 사용자별, 테이블별 권한은 설정하지 않는다. 따라서 역할(Role) 혹은 그룹(Group) 별, 스키마별로 권한을 주는 것이 일반적이다. 개인정보와 연관된 테이블이라면 일부 사람만 접근할 수 있도록 별도 스키마를 설정한다.

사용자별 테이블 권한 설정

• 그룹(Group) vs 역할(Role) : 그룹은 계승이 안되며, 역할은 계승이 됨
• RBAC(Role Based Access Control)가 그룹보다 편리 -> 새로운 트랜드

아래 예제는 GROUP으로 진행하였지만, 키워드를 ROLE로 바꾸어도 동작한다.

사용자 그룹 권한 설정

원하는 그룹 권한

사용자 그룹 권한 설정 - analytics_authors

테이블 별로 두 번째 명령이 해당 테이블에 권한을 주는 명령이다. 해당 명령이 실행되려면, 스키마에 접근할 수 있는 권한이 있어야 하므로 두 명령이 함께 사용되어야 한다. 또한 raw_data의 경우 읽기만 가능하므로 스키마 권한은 USAGE, 접근 권한은 SELECT로 설정된 것을 확인할 수 있다.

# analytics
GRANT ALL ON SCHEMA analytics TO GROUP analytics_authors; # schema 권한
GRANT ALL ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_authors; # read, write

# adhoc
GRANT ALL ON SCHEMA adhoc to GROUP analytics_authors; # schema 권한
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_authors; # read, write

# raw_data
GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_authors; # schema 권한
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_authors; # read

사용자 그룹 권한 설정 - analytics_users

# analytics
GRANT USAGE ON SCHEMA analytics TO GROUP analytics_users; # schema 권한
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_users; # read, write

# adhoc
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_users; # schema 권한
GRANT ALL ON SCHEMA adhoc to GROUP analytics_users; # read, write

# raw_data
GRANT USAGE ON SCHEMA raw_data TO GROUP analytics_users; # schema 권한
GRANT SELECT ON ALL TABLES IN SCHEMA raw_data TO GROUP analytics_users; # read

사용자 그룹 권한 설정 - pii_users

# pii
GRANT USAGE ON SCHEMA pii TO GROUP pii_users; # schema 권한
GRANT SELECT ON ALL TABLES IN SCHEMA pii TO GROUP pii_users; # read

컬럼 레벨 보안 (Column Level Security)

위의 설명은 테이블마다의 접근 권한을 설정한 것이다. 그러나 컬럼 단위로도 접근 권한을 설정할 수 있다. 즉, 테이블 내의 특정 컬럼을 특정 사용자나 그룹/역할의 사용자만 접근 가능하게 하는 것이다. 보통 개인정보 등에 해당하는 컬럼을 권한이 없는 사용자에게 감추는 것이 목적으로 사용된다.

• 가장 좋은 방법은 별도의 테이블을 구성하는 것
• 더 좋은 방법은 보안이 필요한 정보를 데이터 시스템으로 로딩하지 않는 것

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

테이블 내의 특정 레코드를 특정 사용자나 그룹/역할의 사용자만 접근 가능하게 하는 것이다. SELECT, UPDATE, DELETE 별로 권한을 다르게 줄 수 있다. 이를 RLS (Record Level Security) Policy라고 부르며, CREATE RLS POLICY 명령을 사용하여 Policy를 만들고 이를 ATTACH RLS POLICY 명령을 사용해 특정 테이블에 추가할 수 있다.

• 가장 좋은 방법은 별도의 테이블을 구성하는 것
• 더 좋은 방법은 보안이 필요한 정보를 데이터 시스템으로 로딩하지 않는 것

Redshift 백업과 테이블 복구

고정비용과 가변비용에 따라 백업 방식의 차이가 존재한다. 먼저 고정비용에 대해 살펴본 후 가변비용에 대해 살펴볼 것이다.

Redshift가 지원하는 데이터 백업 방식

기본적으로 백업 방식은 마지막 백업으로부터 바뀐 것만 저장하는 방식이며, 이를 Snapshot이라고 부른다. 백업을 통해 과거로 돌아가 그 시점의 내용으로 특정 테이블을 복구하는 것(Restore Table)이 가능하다. 또한 과거 시점의 내용으로 Redshift 클러스터를 새로 생성하는 것도 가능하다.

자동 백업

기본은 하루이지만, 최대 과거 35일까지의 변경을 백업할 수 있다. 이 경우 백업은 같은 지역에 있는 S3에 이뤄진다. 만약 보관 기간을 늘리고 싶다면, 해당 Redshift 클러스터의 Maintenance 탭 > Backup details > Edit에서 변경이 가능하다.

매뉴얼 백업

언제든 원할 때 만드는 백업으로 명시적으로 삭제할 때까지 유지하거나 생성 시 보존 기간을 지정할 수도 있다. 해당 Redshift 클러스터의 Actions > Create snapshot에서 백업이 가능하다.

테이블 복구 과정

같은 이름의 테이블이 존재하는 경우 에러가 발생하기 때문에 새로운 테이블을 생성해야 한다.

1. 해당 Redshift 클러스터의 Actions
2. Restore Table
3. 복구 대상이 있는 백업(Snapshot)을 선택
4. 원본 테이블(Source Table) 선택
5. 어디로 복구될지 타켓 테이블 선택

Redshift Serverless (가변비용) 백업과 테이블 복구

고정비용 Redshift에 비하면 제한적이고 조금 더 복잡하다. Snapshot 이전에 Recovery Points라는 것이 존재하며, 이를 Snapshot으로 바꾼 다음 테이블 복구를 하거나 이것으로 새로운 Redshift 클러스터를 생성하는 것이 가능하다. Recovery Points는 과거 24시간에 대해서만 유지된다.

Redshift 관련 기타 서비스 소개

Redshift Spectrum

Redshift의 확장 기능으로 S3에 있는 파일을 마치 테이블처럼 SQL로 처리 가능하다. S3 파일들을 외부 테이블(external table)로 처리하면서 Redshift 테이블과 조인이 가능하다. S3 외부 테이블은 보통 Fact 테이블이 되고, Redshift 테이블은 Dimension 테이블이 된다.이를 사용하려면 Redshift 클러스터가 필요하며, S3와 Redshift 클러스터는 같은 region에 있어야 한다.

• 비용 : 1TB 스캔 당 $5 

Redshift Serverless

Redshift의 경우 용량을 미리 결정하고 월정액을 지불한다. 이와 달리 Redshift serverless는 쓴 만큼 비용을 지불하는 옵션이다. BigQuery와 같은 사용한 자원에 따른 비용 산정 방식이며, 데이터 처리 크기와 특성에 따라 오토 스케일링이 적용된다.

Athena

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

Redshift ML

SQL만 사용하여 머신러닝 모델을 훈련하고 사용할 수 있게 해주는 Redshift 기능이다. 이 기능은 AWS SageMaker에 의해 지원된다. 이미 모델이 만들어져 있다면 이를 사용하는 것도 가능하다.

• SageMaker : Auto Pilot이라 하여 최적화된 모델을 자동 생성해주는 기능 제공

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

S3에 큰 데이터가 존재하고, 이를 Redshift로 로딩하기가 버겁다면 이를 외부 테이블로 설정해서 Redshift에서 조작이 가능하다.

Fact Table

분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블이다. 일반적으로 매출 수익, 판매량, 이익 또는 측정 항목을 포함하여 비즈니스 결정에 사용한다. Fact Table은 일반적으로 외래 키를 통해 여러 Dimension 테이블로 연결되며, 보통 Fact Table의 크기가 훨씬 더 크다.

Dimension Table

Fact Table에 대한 상세 정보를 제공하는 테이블이다. 고객, 제품과 같은 테이블로 Fact Table에 대한 상세 정보를 제공한다. Fact Table의 데이터에 맥락을 제공하여 사용자가 다양한 반식으로 데이터를 분할하고 분석 가능하게 해준다. 일반적으로 PK를 가지며, Fact Table의 foreign key에서 참조한다.

Fact Table과 Dimension Table의 예

• 예시 1
  • Fact Table : 앞서 사용했던 user_session_channel
  • Dimension Table : 사용자나 채널에 대한 정보 (user, channel)
• 예시 2
  • Fact Table : 사용자의 상품 주문에 대한 정보가 들어있는 테이블 (Order)
  • Dimension Table : 상품과 사용자 정보가 들어있는 테이블 (Product, User)

외부 테이블 (External Table)

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

Redshift Spectrum 사용 유스 케이스

• S3에 대용량 Fact Table이 파일로 존재
• Redshift에 소규모 Dimension Table이 존재
• Fact Table을 Redshift에 적재하지 않고 두 테이블을 조인하고 싶을 경우 Redshift Spectrum 사용

Redshift Spectrum 실습을 위한 외부 테이블용 스키마 설정

앞서 만든 redshift.read.s3 ROLE에 AWSGlueConsoleFullAccess 권한을 지정해준 후 아래의 코드로 외부 테이블용 스키마를 생성한다.

CREATE EXTERNAL SCHEMA external_schema
from data catalog
database 'myspectrum_db'
iam_role 'arn:aws:iam::521227329883:role/redshift.read.s3'
create external database if not exists;

AWS Glue

AWS Glue는 Serverless ETL 서비스로 아래와 같은 기능을 제공한다. 전체적으로 봤을 때, 많이 쓰이는 서비스는 아니다.

• 데이터 카탈로그 : 데이터 소스 및 대상의 메타 데이터를 대상으로 검색 기능 제공(S3, AWS 상의 데이터)
• ETL 작업 생성 : 드래그 앤 드롭 인터페이스로 ETL 작업 생성 가능
• 작업 모니터링 및 로그 : AWS Glue 콘솔을 통해 ETL 작업의 실행 상태 및 로그 모니터링 가능
• 서버리스 실행 : 사용자는 작업을 실행하는 데 필요한 인프라를 관리할 필요가 없음 (Auto Scaling)

Redshift Spectrum 실습을 위한 외부 Fact Table 정의

S3에 usc라는 폴더를 각자 S3 버킷 밑에 만들고, 폴더 내에 user_session_channel.csv 파일을 복사 후 아래의 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://keeyong-test-bucket/usc/';

Redshift Spectrum 실습을 위한 내부 Dimension Table 생성

테스트를 위해 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
);

Redshift Spectrum Fact + Dimension Table 조인

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;

Redshift ML 사용하기

SQL에서 ML 모델을 빌드하고 사용하는 유스케이스가 증가하고 있으며, 대부분의 데이터 웨어하우스 서비스에서 지원하고 있다.

머신러닝과 머신러닝 모델

컴퓨터가 학습할 수 있도록 알고리즘과 기술을 개발하는 분야이다. 배움이 가능한 기계(혹은 알고리즘)이 데이터의 패턴을 보고 흉내내는 방식으로 학습한다. 딥러닝은 머신러닝의 일부이며, 인공지능은 머신러닝을 포괄하는 개념이다.

머신 러닝의 최종 산물이 머신 러닝 모델이며, 블랙박스 형태로 학습된 패턴에 따라 예측을 진행한다. 훈련 데이터의 품질이 머신러닝 모델의 품질을 결정한다.

• 지도 학습 (Supervised)
• 비지도 학습 (Unsupervised)
• 강화 학습 (Reinforcement)

Amazon SageMaker

머신러닝 모델 개발을 처음부터 끝까지 해결해주는 MLOps 프레임워크 AWS 서비스이다. Tenserflow/Keras, PyTorch, MXNet 등 다양한 머신러닝 프레임워크를 지원하며, 자체 Sagemaker 모듈로 머신러닝 모델 훈련이 가능하다. SageMaker Studio라는 웹기반 환경을 제공하고, Python Notebook 등의 개발 방식도 지원한다. AutoPilot이라는 코딩이 불필요한 모델 훈련 기능도 제공한다.

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

AutoPilot 소개

SageMaker에서 제공하는 AutoML로 최종적으로는 사용자가 모델을 선택 후 API로 만드는 것도 가능하다. AutoPilot은 훈련용 데이터 셋 입력으로 다음을 자동으로 수행한다. 

• 데이터 분석(EDA) 수행 및 파이썬 노트북으로 생성
• 다수의 머신러닝 앍고리즘과 하이퍼 파라이머의 조합에 대해 작업 수행
• 선택 옵션에 따라 모델 테스트까지 수행하기도 하지만 코드를 만드는 단계에서 마무리 가능

Redshift 중단 / 제거하기

Redshift 관련 유지보수

Redshift 서비스는 주기적으로 버전 업그레이드를 위해 중단된다. 이를 Maintanance window라고 부르며, Serverless에는 존재하지 않는다.

테이블 청소와 최적화 - VACUUM

VACUUM 명령은 주기적으로 실행하면, 삭제나 수정이 빈번한 경우 생기는 문제들을 해결할 수 있다.

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

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

Redshift가 당분간 필요 없다면 해당 Redshift 클러스터 > Actions > Pause를 선택하면 된다. 이 경우 스토리지 비용만 부담하며, 다시 필요해질 경우 같은 메뉴에서 Resume을 선택하면 된다.

Redshift 클러스터 삭제 (고정비용)

해당 Redshift 클러스터 > Actions > Delete를 선택하면 삭제가 가능하다. 이때, 데이터베이스 내용 백업 여부를 선택 가능하다. 이 백업으로부터 Redshift 클러스터를 새로 론치가 가능하다.

Redshift 클러스터 삭제 (가변비용)

모든 Workgroup들을 삭제한 뒤 Namesapce를 삭제하면 된다.