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

🙂 Snowflake

✔ 특징

• 스토리지와 컴퓨팅 인프라가 별도로 설정되는 가변 비용 모델
  • Redshift 고정비용처럼 노드 수를 조정할 필요가 없고 distkey 등의 최적화 불필요
• SQL 기반으로 빅데이터 저장, 처리, 분석 가능
  • 비구조화 데이터 처리와 ML 기능도 제공
• CSV, JSON, Avro, Parquet 등과 같은 다양한 데이터 포맷 지원
  • S3, GC 클라우드 스토리지, Azure Blog Storage도 지원
• 배치 데이터 중심이지만 실시간 데이터 처리 지원
• Time Travel: 과거 데이터 쿼리 기능으로 트렌드 분석 용이
• 웹 콘솔 외에도 Python API를 통한 관리/제어 가능
  • ODBC/JDBC 연결도 지원
• 자체 스토리지 이외에도 클라우드 스토리지를 외부 테이블로 사용 가능
• 대표 고객: Siemens, Flexpoert, Iterable, Affirm, PepsiCo, ...
• 멀티클라우드와 다른 지역에 있는 데이터 공유 (Cross-Region Replication) 기능 지원
• Data Marketplace & Data Sharing: "Data를 복사하지말고 공유하자."

 

🍦 Snowflake의 계정 구성도: Organization -> 1+Account -> 1+ Databases

• Organizations
  • 한 고객이 사용하는 모든 Snowflake 자원을 통합하는 최상위 레벨 컨테이너
  • 하나 혹은 그 이상의 Account로 구성되며 이 모든 Account의 접근권한, 사용트래킹, 비용을 관리하는데 사용
• Accounts
  • 하나의 Account는 자체 사용자, 데이터, 접근권한을 독립적으로 가짐
  • 한 Account는 하나 혹은 그 이상의 Database로 구성
• Databases
  • 하나의 Database는 한 Account에 속한 데이터를 다루는 논리적인 Container
  • 하나의 Database는 다수의 스키마와 거기에 속한 테이블과 뷰 등으로 구성
  • 하나의 Database는 PB단위까지 스케일 가능하고 독립적인 컴퓨팅 리소스(Warehouse)를 가짐
    • Warehouse(컴퓨팅 리소스), Database(스토리지)

🍦 비용 구조

• 컴퓨팅 비용: credit($2~$4)으로 결정
• 스토리지 비용: TB당 계산
• 네트워크 비용: 지역간 데이터 전송 혹은 다른 클라우드간 데이터 전송 시 TB당 계산

---

✔ 실습을 위한 초기 환경 설정

 

IAM에서 신규 사용자를 만들어서 credentials에 AWS_KEY_ID와 AWS_SECRET_KEY 생성.

 

-- Database 생성
CREATE DATABASE dev;

-- 3개의 스키마 생성
CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;

-- 3개의 테이블을 raw_data 밑에 생성
CREATE OR REPLACE TABLE dev.raw_data.session_transaction (
 sessionid varchar(32) primary key,
 refunded boolean,
 amount int
);

CREATE TABLE dev.raw_data.user_session_channel (
 userid integer,
 sessionid varchar(32) primary key,
 channel varchar(32)
);

CREATE TABLE dev.raw_data.session_timestamp (
 sessionid varchar(32) primary key,
 ts timestamp
);

-- 각 테이블에 대해서 COPY
COPY INTO dev.raw_data.session_transaction
FROM 's3://heecup-test-bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='AK...I' AWS_SECRET_KEY='BwM...p')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"')
;

COPY INTO dev.raw_data.user_session_channel
FROM 's3://heecup-test-bucket/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='AK...I' AWS_SECRET_KEY='BwM...p')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"')
;

COPY INTO dev.raw_data.session_timestamp
FROM 's3://heecup-test-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='AK...I' AWS_SECRET_KEY='BwM...p')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"')
;

-- CTAS로 analytics 스키마 하단에 mau_summary 생성
CREATE TABLE dev.analytics.mau_summary AS
SELECT
 TO_CHAR(A.ts, 'YYYY-MM') AS month,
 COUNT(DISTINCT B.userid) AS mau
FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;

-- 생성된 결과 확인
SELECT * FROM dev.analytics.mau_summary LIMIT 10;

---

✔ 사용자 권한 설정

🍦 Role과 User 생성

-- 3개의 ROLE 생성
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;

-- 사용자 생성
CREATE USER heecup PASSWORD= 'He...9';

-- 사용자에게 analytics_users 권한 지정
GRANT ROLE analytics_users TO USER heecup;

 

Snowflake는 Group 지원 X

🍦 analytics_users와 analytics_authors Role 설정

 

-- set up analytics_users
GRANT USAGE on schema dev.raw_data to ROLE analytics_users;
GRANT SELECT on all tables in schema dev.raw_data to ROLE analytics_users;
GRANT USAGE on schema dev.raw_data to ROLE analytics_users;
GRANT SELECT on all tables in schema dev.raw_data to ROLE analytics_users;
GRANT ALL on schema dev.adhoc to ROLE analytics_users;
GRANT ALL on all tables in schema dev.adhoc to ROLE analytics_users;


--set up analytics_authors
GRANT ROLE analytics_users TO ROLE analytics_authors;
GRANT ALL on schema dev.analytics to ROLE analytics_authors;
GRANT ALL on all tables in schema dev.analytics to ROLE analytics_authors;

 

🍦 Data Governance 관련 기능

필요한 데이터가 적재적소에 올바르게 사용됨을 보장하기 위한 프로세스

• 데이터 기반 결정에서의 일관성
• 데이터를 이용한 가치 생성
• 관련 법규 준수

🍙 Object Tagging

• Object: snowflake내의 다양한 객체들. database, table, schema, view 등
• 마케팅, 세일즈, 개인정보 등 객체별로 태그를 주는 것

🍙  Data Classification

• Object Tagging에서 매뉴얼하게 관리하기 위해 나온 개념
• 3가지 스텝
  • Analyze: 테이블에 적용하면 개인정보나 민감정보 칼럼 filter
  • Review: 사람(데이터 엔지니어)이 보고 리뷰
  • Apply: 최종 결과를 System Tag로 적용
    • SNOWFLAKE.CORE.PRIVACY_CATEGORY (상위레벨)
    • SNOWFLAKE.CORE.SEMANTIC_CATEGORY(하위레벨)
• Identifier(식별자): 개인을 바로 지칭
• Quasi Identifier(준식별자): 몇 개의 조합으로 지칭

🍙  Tag based Masking Policies

• Tag에 접근 권한 지정
  • 해당 Tag가 지정된 Snowflake Object의 접근 권한을 그에 맞춰 제한

🍙  Access History

• 데이터 접근에 대한 감사 추적 제공
• 'Access History'를 통해 DB 로그인, 실행 쿼리, 테이블 및 뷰 액세스, 데이터 조작 작업 추적 가능

🍙  Object Dependencies

• 데이터 거버넌스와 시스템 무결성 유지를 목적
• 테이블이나 뷰를 수정하는 경우 이로 인한 영향 자동 식별
  • e.g. 테이블 이름이나 컬럼 이름을 변경 또는 삭제
• 계승 관계 분석을 통한 더 세밀한 보안 및 접근 제어