[데이터 엔지니어링 데브코스 2기] TIL-8주차-파트05 데이터 웨어하우스 관리와 고급 SQL과 BI 대시보드(4)

1. Snowflake란?

• 2014년에 클라우드 데이터 웨어하우스로 시작됨.
  • 데이터 클라우드로 큰 발전.
  • 글로벌 클라우드(AWS, GCP, Azure)에서 모두 동작. (멀티 클라우드)
  • 데이터 판매를 통한 매출을 가능하게 해주는 Data Sharing/Marketplace 제공.
  • ETL과 다양한 데이터 통합 기능 제공.

1-1. Snowflake 특징

• 스토리지와 컴퓨팅 인프라가 별도로 설정되는 가변 비용 모델임.
• SQL 기반으로 빅데이터 저장, 처리, 분석을 가능하게 해줌.
• CSV, JSON, Avro, Parquet 등과 같은 다양한 데이터 포맷(다른 클라우드 서비스의 클라우드 스토리지, 예시: S3)을 지원.
• 배치 데이터 중심이지만 실시간 데이터 처리 지원.
• Time Travel: 과거 데이터 쿼리 기능으로 트렌드를 분석하기 쉽게 해줌.
• 웹 콘솔 외에도 Python API를 통한 관리/제어 가능.
• 자체 스토리지 외에도 클라우드 스토리지를 외부 테이블로 사용 가능.
• Snowflake의 계정 구성도: Organization(Schema) -> 1+Account(User) -> 1+Databases

---

1-2. Snowflake의 계정 구성도.

• Oraganization:
  • 한 고객이 사용하는 모든 Snowflake 지원들을 통합하는 최상위 레벨 컨테이너.
  • 하나 혹은 그 이상의 Account들로 구성되며 이 모든 Account들의 접근 권한, 사용 트레킹, 비용들을 관리하는 데 사용됨.
• Accounts:
  • 한 Account는 자체 사용자, 데이터, 접근 권한을 독립적으로 가짐.
  • 한 Account는 하나 혹은 그 이상의 DB로 구성됨.
• Databases:
  • 한 DB는 한 Account에 속한 데이터를 다루는 논리적인 컨테이너.
  • 한 DB는 다수의 스키마와 거기에 속한 테이블과 뷰 등으로 구성되어 있음.
  • 한 DB는 PB 단위까지 스케일 가능하고 독립적인 컴퓨팅 리소스를 갖게 됨.
    • 컴퓨팅 리소스는 Warehouses라고 부르며, Warehouses와 Databases는 일대일 관계가 아님.

---

1-3. Data Sharing, Data Marketplace

• Data Marketplace:
  • 데이터 메시 용어가 생기기 전부터 "데이터 마켓플레이스"라는 서비스 제공.
• Data Sharing ("Share, Don't Move"):
  • "Data Sharing": 데이터 셋을 사내 혹은 파트너에게 스토리지 레벨에서 공유하는 방식.

---

1-4. Snowflake의 기본 데이터 타입

• Redshift와 크게 다르지 않으며 일부 강력한 타입은 다음과 같다.
• Semi-structed data: VARIANT (JSON, OBJECT)
• Array: ARRAY
• Object: OBJECT
• 이를 통해 nested structure를 처리할 수 있음.

2. Snowflake 무료 시험판 실행

• Snowflake에서 회원가입을 진행하며, 이때 Standard 버전으로 가입한다.
• 이메일 전송된 전용 로그인 URL을 잘 저장해 두어야 함.

회원가입이 완료된 화면.

"SNOWFLAKE_SAMPLE_DATA"라는 데이터베이스 안에 있는 "TPCDS_SF100TCL"라는 스키마의 테이블 목록을 보여주는 이미지.

웨어하우스(컴퓨팅 리소스 확인 페이지)

2-1. Snowflake Warehouse에서 Creditd이란?

• 쿼리 실행과 데이터 로드와 기타 작업 수행에 소비되는 계산 리소스를 측정하는 단위.
• 1 credit이 거의 $2~$4의 비용.

---

2-2. Snowflake 비용 구조

• 크게 3가지의 컴포넌트로 구성됨.
• 컴퓨팅 비용: credit.
• 스토리지 비용: TB 당으로 계산.
• 네트워크 비용: 지역 간 데이터 전송 혹은 다른 클라우드 간 데이터 전송 시에, TB 당 계산.

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

• 초기 환경(스키마, 테이블) 설정 및 Summary 테이블 생성.

1. SQL Worksheet 생성.

2. DB 및 스키마 생성.

   

3. dev 데이터베이스의 raw_data 스키마 밑에 테이블 생성.

   CREATE OR REPLACE TABLE session_transaction (
       sessionid VARCHAR(32) primary key,
       refunded BOOLEAN,
       amount INT
   );
   
   CREATE OR REPLACE TABLE user_session_channel (
       sessionid VARCHAR(32) primary key,
       userid INTEGER,
       channel VARCHAR(32)
   );
   
   CREATE OR REPLACE TABLE session_timestamp (
       sessionid VARCHAR(32) primary key,
       ts TIMESTAMP
   );

   

4. COPY를 사용해 벌크 업데이트 수행.

   COPY INTO session_timestamp
   FROM 's3://jaeho-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='"');

   • 하지만 이 경우, Admin 계정의 AWS_SECRET_KEY를 사용하면 절대 안 됨.
   • 대신 S3 버킷 액세스를 위한 전용 사용자를 IAM으로 만들고 S3 읽기 권한 부여하는 것이 좋음.

5. 다른 스키마에 대해서도 반복 작업(3, 4) 수행.

3-1. Snowflake에서 S3 버킷에 접근하기 위한 IAM 사용자 생성

1. AWS의 IAM에서 사용자(User)를 생성.
2. 해당 사용자의 권한 정책(Attach policies directly)에서 AmazonS3ReadOnlyAccess 권한을 부여함.
3. 해당 유저를 선택한 후, Security credentials라는 하단의 탭을 선택한 다음, Create access key를 선택.
4. COPY INTO 명령에 사용될 키 값을 확인.

---

3-2. CTAS로 새 테이블 생성.

• AWS Redshift와 동일한 문법.

4. Snowflake 사용자 권한 설정

• Snowflake는 Group을 지원하지 않음.

4-1. Role과 User 생성

• 3개의 ROLE을 생성한다.

  CREATE ROLE analytics_users;
  CREATE ROLE analytics_authors;
  CREATE ROLE pii_users;

• 사용자를 생성한다.

  CREATE USER jaeho PASSWORD = '*****';

• 사용자에게 analytics_users 권한을 지정한다.

  GRANT ROLE analytics_users TO USER jaeho;

---

4-2. analytics_users와 analytics_authors Role 설정

• analytics_authors는 analytics_users의 권한을 계승받는 구조.

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

• analytics_authors)

  -- analytics_users를 그대로 계승 받음.
  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;

---

4-3. Data Governance 관련 기능

• Object Tagging
• Data Classification : 데이터를 보고 알아서 분류해 주는 기능. (예: 이 칼럼은 개인 정보, 식별자, ...)
• Tag based Masking Policies
• Access History : 사용자의 모든 행동을 칼럼 기준으로 기록해 두는 기능. (audit을 위해 필요한 기능.)
• Object Dependencies : 원본 테이블이 갖고 있는 속성들은, 이를 참조하는 새로운 테이블을 만들 때 똑같이 속성을 따라가야 한다. (예시: 칼럼명 수정 시 확인 메시지 출력.)

Data Governance란?

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

- 데이터 기반 결정에서의 일관성. (예: KPI 지표 정의 및 계산)
- 데이터를 이용한 가치 생성.
- 데이터 관련 법규 준수.

---

4-4. Object Tagging

• Enterprise 레벨에서만 사용 가능한 기능.
• 모든 오브젝트에 태그를 붙일 수 있는 기능. (Account, User, ..., Schema)
• 오브젝트 태깅을 함으로써 메타 데이터를 생성할 수 있고, 민감한 정보 및 개인 정보를 확인할 수가 있음.

---

4-5. Data Classification

• Enterprise 레벨에서만 사용 가능한 기능.
• Object Tagging의 경우 개인 정보 관리가 주요 기능이지만, 이를 매뉴얼하게 관리하기 힘듦.
• Object Tagging의 기능을 보완하기 위해 나온 기능.
• 3가지 스텝으로 구성됨.
  • Analyze : 테이블에 적용하면 개인 정보나 민감한 정보가 있는 칼럼들을 분류해냄.
  • Review : 위 결과를 엔지니어가 보고 최종 리뷰함. (매뉴얼한 기능 추가)
  • Apply : 최종 결과를 System Tag로 적용.

---

4-6. 식별자와 준식별자

• 식별자 : 개인을 바로 지칭하는 식별자(Identifier)
• 준식별자 : 몇 개의 조합으로 지칭 가능한 준식별자(Quasi Identifier)

5. Snowflake 기타 기능 및 사용 중단

5-1. Marketplace

• ETL과 관련된 기능.
• 데이터 파이프라인(패턴)을 플러그인으로 제공하고 있음. (SaaS)
• 유로 버전.

---

5-2. Data Sharing

• 커스텀 DB를 다른 유저에게 공유해 줄 수 있는 기능.

---

5-3. Activity-History

• Query, Copy, Task 등에 대한 로그 확인 가능.
• Access History는 더욱 디테일한 로그를 기록하고 있다는 점에서 해당 기능과 차이점이 있음.