📖 학습주제
ETL, ELT, Redshift, 데이터 분석/처리용 고급SQL, BI대시보드 (4)
Snowflake 특징 소개
Snowflake 소개
- 2014년에 클라우드 기반 데이터웨어하우스로 시작
- 글로벌 클라우드위에서 모두 동작 (AWS, GCP, Azure) : 멀티클라우드
- 데이터 판매를 통한 매출을 가능하게 해주는 Data Sharing/Marketplace 제공
- ETL과 다양한 데이터 통합 기능 제공
Data Sharing (“Share, Don’t Move”)
- 데이터 셋을 사내 혹은 파트너에게 스토리지 레벨에서 공유하는 방식
Snowflake 특징
- 스토리지와 컴퓨팅 인프라가 별도로 설정되는 가변 비용 모델
- 시스템이 자동으로 노드에 맞게 scale하기 때문에 Redshift 고정비용처럼 노드 수를 조정할 필요가 없고 distkey등의 최적화가 불필요함
- SQL 기반으로 빅데이터 저장, 처리, 분석을 가능하게 해줌
- 비구조화된 데이터 처리와 머신러닝 기능도 제공
- CSV, JSON, Avro, Parquet 등과 같은 다양한 데이터 포맷을 지원
- S3, GC 클라우드 스토리지, Azure Blog Storage도 지원함
- 배치 데이터 중심이지만 실시간 데이터 처리 지원
- Time Travel: 과거 데이터 쿼리 기능으로 트렌드를 분석하기 쉽게 해줌
- 웹 콘솔 이외에도 Python API를 통한 관리/제어 가능
- ODBC/JDBC 연결도 지원함
- 자체 스토리지 이외에도 클라우드 스토리지를 외부 테이블로 사용 가능
- 멀티클라우드와 다른 지역에 있는 데이터 공유(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는 일대일 관계가 아님
Snowflake의 기본 데이터 타입
- Numeric: TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER, NUMERIC, DECIMAL, FLOAT, DOUBLE, REAL
- Boolean: BOOLEAN
- String: CHAR, VARCHAR, TEXT, BINARY, VARBINARY
- Date and Time: DATE, TIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_TZ
- Semi-structured data: VARIANT (JSON, OBJECT
- Binary: BINARY, VARBINARY
- Geospatial: GEOGRAPHY, GEOMETRY
- Array: ARRAY
- Object: OBJECT
Snowflake 실습을 위한 초기 환경 설정
(참고) Snowflake 계정 생성 후 이메일을 확인해서 로그인 URL을 기록해둘 것
Worksheet 사용 : 웹 SQL 에디터
좌측에서 ACCOUNTADMIN ROLE을 먼저 확인한 후 Worksheets 선택
우측 + 를 누르고 SQL Worksheet를 선택해 Worksheet를 생성한다.
데이터베이스와 스키마 생성
SQL문으로 데이터베이스와 스키마를 생성한다.
CREATE DATABASE dev;
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
); -- s3://trick-test-bucket/test_data/session_transaction.csv
CREATE TABLE dev.raw_data.user_session_channel (
userid integer ,
sessionid varchar(32) primary key,
channel varchar(32)
); -- s3://trick-test-bucket/test_data/user_session_channel.csv
CREATE TABLE dev.raw_data.session_timestamp (
sessionid varchar(32) primary key,
ts timestamp
); -- s3://trick-test-bucket/test_data/session_timestamp.csvCOPY를 사용해 벌크 업데이트를 수행
각 테이블에 아래를 수행한다.
COPY INTO dev.raw_data.session_timestamp
FROM 's3://trick-test-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='A…EK' AWS_SECRET_KEY='X…UH')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');FIELD_OPTIONALLY_ENCLOSED_BY='"' : 문자열 등이 "로 감싸져 있다면 " 빼고 적재
credentials : 파일에 엑세스 할 권한이 있는지 검증
- AWS 어드민 사용자의 AWS KEY ID와 AWS SECRET KEY를 사용하면 안됨
- Snowflake의 S3 버킷 액세스를 위한 전용 사용자를 IAM으로 만들고 S3 읽기 권한 부여
- 그 다음에 그 사용자의 AWS KEY ID와 AWS SECRET KEY를 사용
Snowflake에서 S3 버킷을 접근하기 위한 IAM 사용자 생성
IAM 대시보드 좌측에서 사용자를 클릭하고 사용자를 생성한다.
권한 설정에서 직접 권한 설정을 선택하고 AmazonS3ReadOnlyAccess를 검색해 체크한다. 이후 다음을 눌러 최종적으로 사용자를 생성한다.
만들어진 사용자를 클릭해 보안 자격 증명 탭에서 액세스 키 만들기를 통해
AWS KEY ID와 AWS SECRET KEY를 생성할 수 있다.
AWS 외부에서 실행되는 애플리케이션을 선택하고 다음을 눌러 생성한다.
analytics 스키마 밑에 테이블을 CTAS로 생성
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;Snowflake 사용자 권한 설정
Role과 User 생성
3개의 ROLE을 생성한다
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;
-- 사용자 생성
CREATE USER trickster PASSWORD='*******';
-- 사용자에게 analytics_users 권한 지정
GRANT ROLE analytics_users TO USER trickster;analytics_users와 analytics_authors Role 설정
권한을 다음과 같이 줄 것이다.
| analytics_authors | analytics_users | |
|---|---|---|
| raw_data 테이블들 | 읽기 | 읽기 |
| analytics 테이블들 | 읽기, 쓰기 | 읽기 |
| adhoc 테이블들 | 읽기, 쓰기 | 읽기, 쓰기 |
analytics_users의 권한을 지정하고 analytics_authors에게 계승한다.
-- 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
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;컬럼 레벨 보안 (Column Level Security)
- 테이블내의 특정 컬럼(들)을 특정 사용자나 특정 역할(Role)에만 접근
가능하게 하는 것 - 보통 개인정보 등에 해당하는 컬럼을 권한이 없는 사용자들에게 감추는
목적으로 사용됨
레코드 레벨 보안 (Row Level Security)
- 테이블내의 특정 레코드(들)을 특정 사용자나 특정 역할에만 접근 가능하게
하는 것 - 특정 사용자/그룹의 특정 테이블 대상 SELECT, UPDATE, DELETE 작업에
추가 조건을 다는 형태로 동작
가장 좋은 방법은 아예 그런 컬럼을 별도 테이블로 구성하는 것(더 좋은 방법은 보안이 필요한 정보를 아예 데이터 시스템으로 로딩하지 않는 것)
Data Governance
- 필요한 데이터가 적재적소에 올바르게 사용됨을 보장하기 위한 데이터 관리 프로세스
- 품질 보장과 데이터 관련 법규 준수를 주 목적으로 함
Data Governance의 기본 목적
- 데이터 기반 결정에서 일관성
e.g.) KPI등의 지표 정의와 계산에 있어 일관성 - 데이터를 이용한 가치 만들기
- Citizen data scientist가 더 효율적으로 일할 수 있게 도와주기
- Data silos를 없애기
- 데이터 관련 법규 준수
- 개인 정보 보호 -> 적절한 권한 설정과 보안 프로세스 필수
Data Governance 관련 기능
Object Tagging
- Enterprise 레벨에서만 가능한 기능. CREATE TAG로 생성
- 문자열을 Snowflake object에 지정 가능 (계정, 스키마, 테이블, 컬럼, 뷰 등등)
- 시스템 태그도 있음 (뒤의 Data Classification에서 다시 이야기) - 이렇게 지정된 tag는 구조를 따라 계승됨
- 개인 정보 관리가 주요 용도 중의 하나
- 하지만 이를 매뉴얼하게 관리하기는 쉽지 않음. 그래서 나온 기능이 Data Classification
Data Classification
- Enterprise 레벨에서만 가능한 기능
- Snowflake가 알아서 특정 테이블의 컬럼 내용을 살펴보고 자체적으로 분류
- 3가지 스텝으로 구성됨
- Analyze: 테이블에 적용하면 개인정보나 민감정보가 있는 컬럼들을 분류해냄
- Review: 이를 사람(보통 데이터 엔지니어)이 보고 최종 리뷰 (결과 수정도 가능)
- Apply: 이 최종 결과를 System Tag로 적용
◦SNOWFLAKE.CORE.PRIVACY_CATEGORY (상위레벨)
IDENTIFIER, QUASI_IDENTIFIER, SENSITIVE
◦SNOWFLAKE.CORE.SEMANTIC_CATEGORY (하위레벨 - 더 세부정보)
식별자와 준식별자
- 개인을 바로 지칭하는 식별자 (Identifier)
- 몇 개의 조합으로 지칭가능한 준식별자 (Quasi Identifier)
Tag based Masking Policies
- Enterprise 레벨에서만 가능한 기능. 태그에 따라 권한을 지정
- 먼저 Tag에 액세스 권한을 지정
- 해당 Tag가 지정된 Snowflake Object의 액세스 권한을 그에 맞춰 제한하는 방식 - 보통 앞서 본 개인정보와 같은 Tag에 부여하는 것이 가장 많이 사용되는 패턴
- Tag Lineage가 여기에도 적용됨
Access History
- Enterprise 레벨에서만 가능한 기능. 사용자의 모든 행동을 컬럼 기준으로 기록
- 목적은 데이터 액세스에 대한 감사 추적을 제공하여 보안과 규정 준수
- 잠재적인 보안 위반이나 무단 액세스 시도의 조사를 가능하게 해줌
- 캡처된 정보에는 사용자 신원, IP 주소, 타임스탬프 및 기타 관련 세부 정보 포함
- 'Access History'를 통해 다음 활동의 추적이 가능
- 데이터베이스 로그인, 실행된 쿼리, 테이블 및 뷰 액세스, 데이터 조작 작업
- 다른 모든 클라우드 데이터 웨어하우스에서도 제공됨
Object Dependencies
- 어떤 원본 테이블이 갖고있는 속성들은 조인되어 새로운 테이블을 생성할 때 참조되어야 함
- 데이터 거버넌스와 시스템 무결성 유지를 목적으로 함
- 테이블이나 뷰를 수정하는 경우 이로 인한 영향을 자동으로 식별
e.g.) 테이블 이름이나 컬럼 이름을 변경하거나 삭제하는 경우
- 즉 데이터 리니지 분석을 자동으로 수행해줌
- 계승 관계 분석을 통한 더 세밀한 보안 및 액세스 제어
- 어떤 테이블의 개인정보 컬럼이 새로운 테이블을 만들때 사용된다면 본 테이블에서의 권한 설정이 그대로 전파됨 (Tag 포함)
Snowflake 기타 기능과 사용 중단하기 살펴보기
Marketplace
- 외부 데이터 소스 등을 사고 팔 수 있는 공간
Data Sharing
- 다른 사람들과 데이터 소스를 공유
- 대부분 읽기만 됨
Activity
- Query History : 쿼리문 실행 기록
- Copy History : 벌크 업데이트 기록
- Task History : 특정 SQL들이 주기적으로 실행(Task)된 기록
무료 시험판을 끝내는 방법
- 무료 시험 기간이 끝나면 계정은 자동으로 “Suspended” 모드로 변경됨
- 첫 신청시 크레딧 카드 정보를 입력하지 않음
- Suspended 모드에서 벗어나려면 크레딧 카드 정보 입력이 필요
- 그전에 끝내고 싶다면 Snowflake 서포트에 이메일을 보내서 종료 가능
https://docs.snowflake.com/en/user-guide/admin-trial-account#canceling-a-trial-account
.