Snowflake 실행 및 세팅

yjbenkang·2024년 11월 5일
@data warehousedata engineeringsnowflake

https://signup.snowflake.com
에서 30일 무료 시험판 사용 가능

  • 30일 혹은 최대 $400까지 사용 가능

Work Sheet


일종의 python의 jupyter notebook과 비슷하다고 보면 된다.

이메일에 로그인 URL이 있으니 이걸 확인 후 사용하면 된다.

Snowflake Warehouse에서 Credit이란 ?

  • 쿼리 실행과 데이터 로드와 기타 작업 수행에 소비되는 계산 리소스를 측정하는 단위
  • 1 credit는 상황에 따라 다르지만 대락 $2-$4의 비용을 발생시킴

Snowflake 비용 구조

  • 크게 아래 3가지 컴포넌트로 구성됨

    • 컴퓨팅 비용 : 앞선 크레딧으로 결정됨
    • 스토리지 비용 : TB 당으로 계산(지역별 클라우드별 가격차이)
    • 네트워크 비용 : 지역간 데이터 전송 혹은 다른 클라우드간 데이터 전송시 TB당 계산
      • 시스템 안으로 데이터를 외부에서 로딩할땐 X
      • 시스템 안에서 데이터바깥으로 나갈 때 charge
      • snowflake 안에서 데이터가 이동할 때 상황에 따라 charge되기도 하고 안되기도 하고
        • charge가 되는 경우는 지역이 달라지는 경우 미국snowflake data -> 아시아snowflake
        • 같은 지역에 있어도 멀티클라우드 쓰면서 클라우드 업체가 달라지는 경우
          ex) azure기반의 snowflake -> aws 기반의 snowflake

Snowflake Schema

  • SNOWFLAKE 데이터베이스 밑에 3개의 스키마를 생성
    • raw_data
    • analytics
    • adhoc

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

Snowflake 실습을 위해 초기 환경(스키마, 테이블)을 설정하고 Summary 테이블도 만들어본다.

Worksheet 생성

먼저 Worksheet을 생성해주고 아래의 sql문들을 작성해준다. 드래그 해서 실행하면 원하는 sql문만 실행가능하다.

데이터베이스 생성

CREATE DATABASE dev;

그 밑에 3개의 스키마 생성

CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;

그 밑에 3개의 테이블 생성

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
);

Snowflake의 S3 접근을 위한 AWS IAM ROLE 세팅


IAM 콘솔에서 사용자 클릭

사용자 생성 클릭

사용자 이름 지정하고 다음 클릭

직접 정책 연결 클릭 후 검색하여 AmazonS3ReadOnlyAccess 정책 선택 후 다음 클릭 후 사용자 생성

생성한 사용자 클릭 후 보안 자격 증명 클릭 후 액세스키 메뉴에서 액세스키 만들기 누른 후 AWS 외부에서 실행되는 애플리케이션 션택 후 액세스 키 생성한다. 그럼 액세스키와 시크릿키가 만들어지는데 이걸 복사해서 저장하면 된다.

COPY SQL을 사용해서 S3의 데이터를 bulk update

COPY INTO dev.raw_data.session_transaction
FROM 's3://redshift-yjbenkang-bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='xxxx' AWS_SECRET_KEY='xxxx')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO dev.raw_data.user_session_channel
FROM 's3://redshift-yjbenkang-bucket/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='xxxx' AWS_SECRET_KEY='xxxx')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO dev.raw_data.session_timestamp
FROM 's3://redshift-yjbenkang-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='xxxx' AWS_SECRET_KEY='xxxx')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

raw_data 스키마의 테이블들을 바탕으로 analytics 스키마 밑에 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;

-- mau_summary 테이블 확인
SELECT * FROM dev.analytics.mau_summary LIMIT 10;

Role과 User 생성

Snowflake는 Group을 지원하지 않음 !

-- 3개의 ROLE을 생성한다
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;
-- 사용자 생성
CREATE USER yongjin PASSWORD='xxxx';
-- 사용자에게 analytics_users 권한 지정
GRANT ROLE analytics_users TO USER yongjin;

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.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란?

  • 필요한 데이터가 적재적소에 올바르게 사용됨을 보장하기 위한 데이터 관리 프로세스
    • 품질 보장과 데이터 관련 법규 준수를 주 목적으로 함
  • 다음을 이룩하기 위함이 기본 목적
    • 데이터 기반 결정에서 일관성
      • 예 : KPI 등의 지표 정의와 계산에 있어 일관성
    • 데이터를 이용한 가치 만들기
      • Citizen data scientist가 더 효율적으로 일할 수 있게 도와주기
      • Data silos를 없애기
    • 데이터 관련 법규 준수
      • 개인 정보 보호 -> 적절한 권한 설정과 보안 프로세스 필수 !

Data Governance 관련 기능

Object Tagging

  • Enterprise 레벨에서만 가능한 기능. CREATE TAG로 생성
    • 문자열을 Snowflake object에 지정 가능 ( 계정, 스키마, 테이블, 컬럼, 뷰 등등)
    • 시스템 태그도 있음
  • 이렇게 지정된 tag는 구조를 따라 계승됨

Data Classification

  • Enterprise 레벨에서만 가능한 기능
  • 앞서 Object Tagging은 개인정보 관리가 주요 용도 중의 하나
    • 하지만 이를 매뉴얼하게 관리하기는 쉽지 않음. 그래서 나온 기능이 Data Classification
  • 3가지 스텝으로 구성됨
    • Analyze: 테이블에 적용하면 개인정보나 민감정보가 있는 컬럼들을 분류해냄
    • Review : 이를 사람(보통 데이터 엔지니어)이 보고 최종 리뷰 (결과 수정도 가능)
    • Apply : 이 최종 결과를 System Tag로 적용
      • SNOWFLAKE.CORE.PRIVACY_CATEGORY (상위레벨)
        • IDENTIFIER(개인정보), QUASI_IDENTIFIER(개인 준식별자), SENSITIVE(민감정보)
      • SNOWFlAKE.CORE.SEMANTIC_CATEGORY(하위레벨 - 더 세부정보) ex)이름,전화번호, 주소
  • 식별자와 식별자
    • 개인을 바로 지칭하는 식별자 (Identifier)
    • 몇개의 조합으로 지칭가능한 준식별자 (Quasi Identifier)
      PRIVACY_CATEGORYSEMANTIC_CATEGORY
      IDENTIFIER- EMAIL
      - IBAN, IMEI, IP_ADDRESS, PAYMENT_CARD
      - NAME
      - PHONE_NUMBER (US numbers only)
      - URL
      - US_BANK_ACCOUNT
      - US_DRIVERS_LICENSE
      - US_PASSPORT, VIN
      - US_SSN
      - US_STREET_ADDRESS
      QUASI_IDENTIFIER- AGE
      - GENDER
      - DATE_OF_BIRTH
      - ETHNICITY
      - LATITUDE, LAT_LONG, LONGITUDE
      - MARITAL_STATUS
      - OCCUPATION
      - US_POSTAL_CODE, US_STATE_OR_TERRITORY, US_COUNTY, US_CITY, COUNTRY
      - YEAR_OF_BIRTH

Tag based Masking Policies

  • Enterprise 레벨에서만 가능한 기능
  • 먼저 Tag에 액세스 권한을 지정
    • 해당 Tag가 지정된 Snowflake Object의 액세스 권한을 그에 맞춰 제한하는 방식
  • 보통 앞서 본 개인정보와 같은 Tag에 부여하는 것이 가장 많이 사용되는 패턴
    • Tag Lineage가 여기에도 적용됨.

Access History

  • Enterprise 레벨에서만 가능한 기능
  • 목적은 데이터 액세스에 대한 감사 추적을 제공하여 보안과 규정 준수
    • 잠재적인 보안 위반이나 무단 액세스 시도의 조사를 가능하게 해줌
    • 캡처된 정보에는 사용자 신원, IP 주소, 타임스탬프 및 기타 관련 세부 정보 포함
  • 'Access History'를 통해 다음 활동의 추적이 가능
    • 데이터베이스 로그인, 실행된 쿼리, 테이블 및 뷰 액세스, 데이터 조작 작업
  • 이 기능은 사실 다른 모든 클라우드 데이터 웨어하우스에서도 제공됨

Object Dependencies

  • 데이터 거버넌스와 시스템 무결성 유지를 목적으로 함
  • 테이블이나 뷰를 수정하는 경우 이로 인한 영향을 자동으로 식별
    • 예를 들어 테이블 이름이나 컬럼 이름을 변경하거나 삭제하는 경우
    • 즉 데이터 리니지 분석을 자동으로 수행해줌
  • 계승 관계 분석을 통한 더 세밀한 보안 및 액세스 제어
    • 어떤 테이블의 개인정보 컬럼이 새로운 테이블을 만들 때 사용된다면?
      • 원본 테이블에서의 권한 설정이 그대로 전파됨 (Tag 포함)
profile
yjbenkang
keep growing
이전 포스트

Snowflake란?

다음 포스트

데이터 시각화 툴

0개의 댓글