[DEV] 8주차. 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드(4)
1. Snowflake
- 2014년 클라우드 기반 데이터 웨어하우스로 시작됨
- 지금은 데이터 클라우드라고 부를 수 있을 정도로 발전
- 글로벌 클라우드 위에서 모두 동작 (AWS, GCP, Azure) - 멀티클라우드
- 데이터 판매를 통한 매출을 가능하게 해주는 Data sharing / Marketplace 제공
- 데이터 복사 X
- Snowflake 를 쓰는 기업끼리 저장되어 있는 데이터를 읽어가는 형식
- ETL과 다양한 데이터 통합 기능 제공
특징
- 가변 비용 모델
- 스토리지와 컴퓨팅 인프라가 별도로 설정됨
- 노드 수를 조정할 필요가 없고, distkey 등의 최적화 불필요
- SQL 기반으로 빅데이터 저장, 처리, 분석을 가능하게 해줌
- 비구조화된 데이터 처리와 머신러닝 기능도 제공
- CSV, JSON, Avro, Parquet 등과 같은 다양한 데이터 포맷 지원
- S3, GC 클라우드 스토리지, Azure Blog Storage도 지원
- 배치 데이터 중심이지만 실시간 데이터 처리 지원
- Time Travel: 과거 데이터 쿼리 기능으로 트렌드를 분석하기 쉽게 해줌
- 웹 콘솔 이외에도 Python API를 통한 관리/제어 가능
- ODBC / JDBC 연결도 지원
-
자체 스토리지 이외에도 클라우드 스토리지를 외부 테이블로 사용 가능
- 대표 고객: Siemens, Felxport, Iterable, Affirm, PepsiCo, …
- 멀티클라우드와 다른 지역에 있는 데이터 공유 (Cross-Region Replication) 기능 지원
- Snowflake 계정 구성도: Organization -> 1 + Account -> 1 + Databases
- Organizations
- 한 고객이 사용하는 모든 Snowflake 자원들을 통합하는 최상위 레벨 컨테이너
- 하나 혹은 그 이상의 Account들로 구성되며 이 모든 Account들의 접근 권한, 사용 트래킹, 비용들을 관리하는 데 사용됨
- Accounts
- 하나의 Account는 자체 사용자, 데이터, 접근 권한을 독립적으로 가짐
- 한 Account는 하나 혹은 그 이상의 Database로 구성됨
- Databases
- 하나의 Database는 한 Account에 속한 데이터를 다루는 논리적인 컨테이너
- 하나의 Database는 다수의 스키마와 거기에 속한 테이블과 뷰 등으로 구성되어 있음
- 하나의 Database는 PB 단위까지 스케일 가능하고, 독립적인 컴퓨팅 리소스를 갖게 됨
- 컴퓨팅 리소스를 Warehouses라고 부르며, Warehouses와 Databases는 일대일 관계가 아님
기본 데이터 타입
- Numeric : TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER, NEMERIC, DECIMAL, FLOAT, DOUBLE, REAL
- Boolean : BOOLEAN
- String : CHAR, VARCHAR, TEXT, BINARY, VARBINARY
- Date and Time : DATE, TIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_TZ
- Semi-structed data : VARIANT (JSON, OBJECT)
- Binary : BINARY, VARBINARY
- Geospatial : GEOGRAPHY, GEOMETRY
- Array : ARRAY
- Object : OBJECT
Credit
- 쿼리 실행과 데이터 로드 및 기타 작업 수행에 소비되는 계산 리소스를 측정하는 단위
- 1 credit은 상황에 따라 다르지만 대략 $2-$4의 비용을 발생시킴
비용 구조
- 컴퓨팅 비용: 앞서 크레딧으로 결정됨
- 스토리지 비용: TB 당으로 계산 (지역마다, 클라우드마다 가격 차이 존재)
- 네트워크 비용: 지역 간 데이터 전송 혹은 다른 클라우드 간 데이터 전송시 TB 당 계산
2. Snowflake 무료 체험판 시작
원하는 클라우드와 region 선택
이후에 접속할 땐 아래의 주소로 들어가야 함!! - 기록해두기
- worsheet: python의 노트북 개념!
- 같은 snowflake account를 사용하는 사람들과 공유 가능
- 같은 snowflake account를 사용하는 사람들과 공유 가능
- databases
- 기본으로 2개가 만들어져 있음
- 기본으로 2개가 만들어져 있음
- warehouse: 컴퓨팅 리소스
-
무료 체험판: X-Small 사이즈
-
새로 추가도 가능
- 보통 다양한 사이즈의 warehouse를 만들어 놓고, 하려는 일의 성격에 따라 warehouse를 다르게 설정
- 이렇게 사용할 수 있는 SQL Syntax들이 있음
-
3. 초기 환경 설정 (스키마, 테이블)
Worksheet 사용: 웹 SQL 에디터
- ACCOUNTADMIN ROLE 먼저 확인
worksheet 생성
worksheet rename
warehouse 선택
Schema, DB 생성
CREATE DATABASE dev;
-- 먼저 3개의 스키마 생성
CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;
테이블 생성
-- 테이블 생성
-- S3에서 데이터 가져올 것
CREATE OR REPLACE TABLE dev.raw_data.session_transaction (
sessionid varchar(32) primary key,
refunded boolean,
amount int
);
CREATE OR REPLACE TABLE dev.raw_data.user_session_channel (
userid integer,
sessionid varchar(32) primary key,
channel varchar(32)
);
CREATE OR REPLACE TABLE dev.raw_data.session_timestamp (
sessionid varchar(50) primary key,
ts timestamp
);
create or replace table: 테이블이 이미 있으면 drop하고 생성해줌
COPY를 이용해 벌크 업데이트 수행
- s3에 데이터 적재
- 사용자 생성
- AWS admin 사용자의 AWS KEY ID와 AWS SECRET KEY를 사용하면 절대 안됨!
- Snowflake의 S3 버킷 액세스를 위한 전용 사용자를 IAM으로 만들고 S3 읽기 권한 부여
- 그 사용자의 AWS KEY ID와 AWS SECRET KEY 사용
S3 Read 정책만 부여
- 액세스 키 생성
COPY명령 수행
COPY INTO dev.raw_data.session_timestamp
FROM 's3://bk-snowflake-test-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='AKIASHAJ2P2BARMFTRXH' AWS_SECRET_KEY='')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
COPY INTO dev.raw_data.user_session_channel
FROM 's3://bk-snowflake-test-bucket/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='AKIASHAJ2P2BARMFTRXH' AWS_SECRET_KEY='')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
COPY INTO dev.raw_data.session_transaction
FROM 's3://bk-snowflake-test-bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='AKIASHAJ2P2BARMFTRXH' AWS_SECRET_KEY='')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
analytics 스키마에 테이블 생성
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;
4. Snowflake 사용자 권한 설정
Role과 User 생성
- Snowflake는 Group 지원 X
-- ROLE 생성
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;
-- 사용자 생성
CREATE USER bokyung PASSWORD='bk124';
-- 사용자에게 권한 지정
GRANT ROLE analytics_users TO USER bokyung;
ROLE 설정
- 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.analytics TO ROLE analytics_users;
GRANT SELECT ON ALL TABLES IN SCHEMA dev.analytics 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
-- analytics_users에서 analytics 스키마에 대한 쓰기 권한만 추가됨
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;
컬럼 레벨 보안
- 테이블 내의 특정 컬럼들을 특정 사용자나 특정 그룹/역할에만 접근 가능하게 하는 것
- 보통 개인정보 등에 해당하는 컬럼을 권한이 없는 사용자들에게 감추는 목적으로 사용됨
- 사실 가장 좋은 방법은 아예 그런 컬럼을 별도 테이블로 구성하는 것
- 더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것!
레코드 레벨 보안
- 테이블 내의 특정 레코드들을 특정 사용자나 특정 그룹/역할에만 접근 가능하게 하는 것
- 특정 사용자/그룹의 특정 테이블 대상 SELECT, UPDATE, DELETE 작업에 추가 조건을 다는 형태로 동작
- 일반적으로 더 좋은 방법은 별도의 테이블로 관리하는 것
- 더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것!
Data Governance 관련 기능
- Enterprise 레벨에서만 사용 가능
- Object Tagging
- Data Classification
- Snowflake이 자체적으로 컬럼을 분류해주는 기능 (개인정보)
- Tag based Masking Policies
- tag에 따라서 access 권한을 다르게 부여하는 것
- Access History
- 사용자별로 언제 시스템에 로그인했는지, 어떤 테이블을 select했고, join했는지 등 모든 행동을 컬럼 기준으로 기록
- 나중에 audit할 수 있도록 (보안 이슈 등)
- Object Dependencies
- ELT 과정에서 테이블을 만들 때 보안이 걸린 컬럼이 테이블에 들어갈 수 있음
- 원본 테이블의 속성이 (특정 컬럼의 접근 권한) 새로운 테이블을 만들 때 같이 따라가야 함
- 계승 관계를 명확히 하면 원본 테이블을 수정할 때 더 주의를 기울일 수 있음 (경고를 줌)
- ELT 과정에서 테이블을 만들 때 보안이 걸린 컬럼이 테이블에 들어갈 수 있음
Data Governance란?
- 필요한 데이터가 적재적소에 올바르게 사용됨을 보장하기 위한 데이터 관리 프로세스
- 품질 보장과 데이터 관련 법규 준수를 주 목적으로 함
- 다음을 이룩하기 위함이 기본 목적
- 데이터 기반 결정에서 일관성 유지
- ex) KPI 등의 지표 정의와 계산에 있어 일관성 유지 (우리의 상황을 정확하게 나타내는가)
- 데이터를 이용한 가치 만들기
- Citizen data scientist가 더 효율적으로 일할 수 있게 도와주기
- Data silos를 없애기
- 데이터 관련 법규 준수
- 개인정보 보호 -> 적절한 권한 설정과 보안 프로세스 필수!
- 데이터 기반 결정에서 일관성 유지
Object Tagging
CREATE TAG로 생성- 문자열을 Snowflake object에 지정 가능 (계정, 스키마, 테이블, 컬럼, 뷰 등)
- 시스템 태그도 있음
- 이렇게 지정된 tag는 구조를 따라 계승됨
- 메타데이터를 만들기 위함!
- 특히 개인정보, 민감한 정보 tagging을 위함
Data Classification
- 앞서 Object Tagging은 개인정보 관리가 주요 용도 중 하나
- 하지만 이를 매뉴얼하게 관리하기 쉽지 않아 나온 기능이 data classification
- 3가지 step으로 구성됨
- Analyze: 테이블에서 개인정보나 민감정보가 있는 컬럼을 분류해냄
- Review: 사람 (보통 데이터엔지니어)이 보고 최종 리뷰 (수정 가능)
- Apply: 최종 결과를 System Tag로 적용
SNOWFLAKE.CORE.PRIVACY_CATEGORY(상위레벨)- IDENTIFIER, QUASI_IDENTIFIER, SENSITIVE
SNOWFLAKE.CORE.SEMACTIC_CATEGORY(하위레벨 - 더 세부정보)
Tag based Masking Policies
- 먼저 Tag에 access 권한 지정
- 해당 Tag가 지정된 Snowflake Object의 액세스 권한을 그에 맞춰 제한하는 방식
- 보통 개인정보와 같은 Tag에 부여하는 것이 가장 많이 사용되는 패턴
Access History
- 데이터 액세스에 대한 감사 추적을 제공하여 보안과 규정 준수
- 잠재적인 보안 위반이나 무단 액세스 시도의 조사를 가능하게 해줌
- 캡쳐된 정보에는 사용자 신원, IP 주소, 타임스탬프 및 기타 관련 세부 정보 포함
- Access History를 통해 다음 활동의 추적 가능
- DB 로그인, 실행된 쿼리, 테이블 및 뷰 액세스, 데이터 조작 작업
- 다른 모든 클라우드 데이터 웨어하우스에서 제공되는 기능
Object Dependencies
- 데이터 거버넌스와 시스템 무결성 유지를 목적으로 함
- 테이블이나 뷰를 수정하는 경우 이로 인한 영향을 자동으로 식별
- 테이블 이름이나 컬럼 이름을 변경하거나 삭제하는 경우
- 즉 데이터 리니지 분석을 자동으로 수행해줌
- 계승 관계 분석을 통한 더 세밀한 보안 및 액세스 제어
- 어떤 테이블의 개인정보 컬럼이 새로운 테이블을 만들 때 사용된다면
- 원본 테이블에서의 권한 설정이 그대로 전파됨 (Tag 포함)
- 어떤 테이블의 개인정보 컬럼이 새로운 테이블을 만들 때 사용된다면
5. 무료 시행판 종료
- 무료 시험 기간이 끝나면 계정은 자동으로 Suspended 모드로 변경됨
- 첫 신청시 카드 정보를 입력하지 않음
- Suspended 모드에서 벗어나려면 카드 정보 입력 필요
- 그 전에 끝내고 싶다면 Snowflake support에 이메일을 보내서 종료 가능