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

🙂 Redshift 고급기능

✔ Redshift 권한과 보안

🍦 테이블 권한 설정

• 사용자별로 테이블 권한 설정 X
• 역할(Role) 혹은 그룹(Group) 별로 스키마별 접근 권한을 주는 것이 일반적
  • RBAC(Role Based Access Control)가 새로운 트렌드
  • Inclusive: 여러 역할에 속한 사용자의 경우는 각 역할의 권한 일괄 부여
• 개인정보와 관련한 테이블에는 별도 스키마 설정

---

사용자 그룹 권한 설정

analytics_authors

GRANT ALL ON SCHEMA analytics TO GROUP analytics_authors;
GRANT ALL ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_authors;

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

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

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

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

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

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

 

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

• 테이블 내의 특정 컬럼을 특정 사용자나 특정 그룹/역할에만 접근 가능하게 하는 것
• 보통 개인정보 등에 해당하는 컬럼
  • 그러한 컬럼을 별도 테이블로 구성하는 것이 바람직
  • 더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것

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

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

---

✔ Redshift 백업과 테이블 복구

🍦 데이터 백업 방식

• Snapshot: 마지막 백업으로부터 바뀐 것들만 저장하는 방식
  
  • 백업을 통해 과거로 돌아가 그 시점의 내용으로 특정 테이블을 복구하는 것이 가능 (Table Restore)
  • 과거 시점의 내용으로 Redshift 클러스터를 새로 생성하는 것도 가능
• 자동 백업
  • Redsift 클러스터 - Maintenance - Backup details - Edit
  • 기본은 하루이지만 최대 과거 35일까지의 변경 백업 가능
  • 백업은 같은 지역에 있는 S3에 이뤄짐
  • 다른 지역에 있는 S3에 하려면 Cross-regional snapshot copy 설정. 재난 시 데이터 복구에 유용
• 매뉴얼 백업
  • Redshift 클러스터 - Maintenance - Backup and disaster recovery - Create snapshot 
  • 원할 때 만드는 백업으로 명시적으로 삭제하거나 보존 기한 만료 전 까지 유지

 

백업에서 테이블 복구

1. 해당 Redshift 클러스터에서 'Restore table' 메뉴 선택
2. 복구 대상이 있는 백업(Snapshot) 선택
3. 원본 테이블 (Source table) 선택
4. 어디로 복구될 지 타겟 테이블 선택

 

🍦 Redshift Serverless 데이터 백업

• 고정비용 Redshift에 비하면 제한적이고 복잡함
• Snapshot 이전에 Recovery Point 존재
  • Recovery Point를 Snapshot으로 바꾼 다음에 여기서 테이블 복구를 하거나 이것으로 새로운 Redshift 클러스터 등을 생성하는 것이 가능
  • 최대 과거 24시간 동안 30분 마다 생성

---

✔ Redshift Spectrum

• S3의 데이터가 너무 커서 Redshift로 로딩하기 어려울 때 사용
• S3에 있는 파일을 SQL로 처리 가능
  • S3 파일을 외부 테이블로 처리하면서 Redshift 테이블과 Join 가능
  • Fact: S3 외부 테이블 / Dimension: Redshift 테이블
  • 1TB당 $5
• Redshift 클러스터 필요
  • S3와 Redshift 클러스터는 같은 region
• S3 Fact 데이터를 외부 테이블로 정의해야함.
• 별도로 설정하거나 Launch하는 것이 아니라 Redshift의 확장 기능으로 사용하고 비용 부담
  
  

🍦 AWS Glue

AWS Serverless ETL 서비스

• 데이터 카탈로그
  • AWS Glue Data Catalog는 데이터 소스 및 대상의 메타데이터를 대상으로 검색 기능 제공.
  • 주로 S3나 다른 AWS 서비스 상의 데이터 소스를 대상으로 함
• ETL 작업 생성
  • 간단한 드래그 앤 드롭 인터페이스를 통해 ETL 작업 생성
  • 사용자는 데이터 소스 및 대상을 선택하고 데이터 변환 단계를 정의하는 스크립트 생성
• 작업 모니터링 및 로그
  • AWS Glue 콘솔을 통해 사용자는 ETL 작업의 실행 상태 및 로그 모니터링 가능
• Serverless
  • AWS Glue는 Serverless 아키텍처를 사용하므로 Auto Scaling

 

🍦 Athena

• AWS의 Presto 서비스로 Redshift Spectrum과 비슷한 기능 제공
• S3에 있는 데이터를 기반으로 SQL 쿼리 기능 제공
  • 여기서는 S3가 Data Lake

 

🍦 Fact / Dimension Table

• Fact Table: 중앙 테이블
  • 분석의 초점이 되는 양적 정보를 포함
  • 매출 수익, 판매량 또는 이익과 같은 사실 또는 측정 항목을 포함하여 비즈니스 결정에 사용
  • Fact 테이블은 일반적으로 FK를 통해 여러 Dimension 테이블과 연결
  • 보통 Fact 테이블의 크기가 훨씬 더 큼
  • user_session_channel
  • order: 사용자들의 상품 주문에 대한 정보가 들어간 테이블
• Dimenstion 테이블: Fact 테이블에 대한 상세 정보를 제공하는 테이블
  • 고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공
  • Fact 테이블의 데이터에 맥락을 제공하여 사용자가 다양한 방식으로 데이터를 조각내고 분석 가능하게 함.
  • Dimension 테이블은 일반적으로 PK를 가지며, Fact 테이블의 FK 참조
  • user / channel
  • product / user

 

🍦 외부 테이블

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

---

• redshift.read.s3 ROLE에 AWSGlueConsoleFullAccess 권한 지정
• 외부 테이블용 스키마 생성
  

  CREATE EXTERNAL SCHEMA external_schema
  from data catalog
  database 'myspectrum_db'
  iam_role '[role arn 주소]'
  create external database if not exists;​​​

🍦 내부 테이블

• 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://heecup-test-bucket/usc/';​

 

🍦 Dimension 테이블

• 테스트를 위해 user 테이블을 하나 raw_data 스키마 하단에 생성
• SQL 실행
  

  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 테이블 조인

• SQL 실행

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 모델을 훈련하고 사용할 수 있게 하는 Redshift 기능
• AWS SageMaker에 의해 지원
  • SageMaker: Auto Pilot으로 최적화된 모델을 자동 생성해주는 기능 제공
  • BYOM(Bring Your Own Model): 이미 모델이 만들어져 있다면 이를 사용하는 것도 가능

 

🍦 Amazon SageMaker

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

 

🍦 AutoPilot

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