DA

Snowflake 실습 - AWS S3에 Bucket, IAM 생성 | Snowflake에서 DB, Schema, Table 생성 | COPY 하기

ha_data 2024. 1. 26. 01:03

실습목표

Snowflake에 DW 생성, 클라우드 환경에서 데이터 불러와서 간단한 ELT 해보기

 

실습 진행 방식

1. Snowflake에 Warehouse, DB , Schema, Table 생성 -> 데이터 적재 

  • raw_data : ETL이 불러온 데이터 저장
  • analytics: DW에 존재하는 테이블 바탕으로 새로운 테이블 생성 -> ELT
  • adhoc: 개발용, 테스트 용으로 만드는 테이블 저장

2. COPY를 위한 IAM 사용자, Bucket 생성하기

3. 클라우드에 저장되어 있는 데이터 불러오기 -> COPY 

4. ELT 

Snowflake의 COPY

'파일 통으로 적재 -> 클라우드 스토리지에 저장 (ex.S3) -> 목적 테이블로 벌크업하기 '

  • 레코드들을 하나씩 적재하지 않고 벌크로 레코드들이 있는 파일을 통째로 적재하는 방식.
    • 클라우드 스토리지 (S3)에 적재하고 거기서 목적 테이블로 벌크업 하기 => COPY라는 명령어
    • Redshift나 빅쿼리에도 문법만 다르게 존재
    • Datawarehouse 관점에서는 Insert Into 보다 더 빠름.
  • 이러한 방식을 사용하려면 클라우드 스토리지와 접근 권한 설정이 중요해짐
    • S3라면 입력 레코드들이 있는 파일들이 적재될 버킷을 생성 -> 버킷에 파일 업로드
    • 클라우드 스토리지를 접근할 수 있는 권한 설정 (IAM을 사용해서 설정해볼 것임)
    • 그 권한 정보를 Snowlake COPY 명령에서 사용해야함

 

1. S3 버킷 생성하고 파일 업로드하기

AWS Console에서 Bucket을 생성하고 파일 업로드 해보기

 

1. Bucket 생성하기

bucket 구성하기
bucket 생성 완료!

 

test 폴더 생성
파일 업로드

 

2. IAM 사용자 생성하고 access key 만들기

Snowflake에서 S3 Bucket에 접근할 수 있도록 사용자 생성하고 access key 만들기

 

IAM 사용자 생성: Snowflake에서 S3 버킷 접근을 위해 생성하기.

admin이 콘솔 위에 새로운 account 생성 가능 -> account 마다 계정에 권한 다르게 부여 가능

사용자 생성하기
세부정보 입력

snowflake에게 권한을 부여하기 위해 IAM을 생성하는 것.

따라서 지금은 AWS Management Console에 대한 사용자 액세스 권한을 주지 않음.

* 직접 사용하는 사용자가 있을 경우에는 체크해주기

 

권한 정책 설정

권한 옵션은 S3를 읽기만 가능하도록 설정해주었음 => 필요에 따라 해당 옵션 추가하기

 

사용자 생성 완료

특정 bucket만 읽을 수 있도록 설정하는 것도 가능. 

이제 사용자에게 필요한 credential 키 두가지 ( AWS_KEY_ID, AWS_SECRET_KEY ) 생성해야함.

Credential key 만들기 
사용자 상세 정보페이지 -> Securtiy credential -> Access keys -> create access key 

AWS 외부에서 사용할 것이기 때문에 해당 옵션 체크 

키 생성 완료

생성한 키를 사용해서 Snowflake에서 데이터 COPY 가능해짐 !

 

1. Snowflake에 Warehouse 만들기

Worksheet에서 SQL worksheet 만들기

 

Admin 권한으로 COMPUTE_WH 웨어하우스 사용하기

어떤 데이터 베이스 밑에 있는 테이블을 사용할지 설정하기

선택 안 하는 경우 -> SQL 쿼리문에 데이터 베이스까지 함께 적어줘야함. 

 

2. Snowflake 에서 테이블 생성하기

Snowflake에서 SQL노트북을 실행하고 테이블 생성하는 코드 작성

- DEV라는 DB를 만들고 그 밑에 raw_data, analytics, adhoc이라는 스키마 생성

- 각 스키마 마다 용도가 다르고 주석으로 설명해놨음.

-- DEV라는 DB 생성
--CREATE DATABASE dev

-- DEV 밑에 세 개의 스키마 생성
-- raw_data: ETL이 불러온 데이터 저장
CREATE SCHEMA dev.raw_data;

-- analytics: ELT로 이미 데이터 웨어하우스에 있는 테이블 바탕으로 새로운 테이블 만드는데 사용
-- analytics 밑에 들어가는 테이블들은 CTAS 사용하는 것. 전문적으로 사용할 경우 DBT 사용
CREATE SCHEMA dev.analytics;

-- adhoc: 개발용, 테스트 용으로 만드는 테이블 저장
CREATE SCHEMA dev.adhoc;

 

3. raw_data 밑에 3개 테이블 생성하기

- user_session_channel, session_timestamp, session_transaction

-- 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 int,
    sessionid varchar(32) primary key,
    channel varchar(32)
);

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

 

4. S3 Bucket 데이터 COPY 하고 테이블에 저장하기

session_transcation 테이블 copy

 

 

5. Analytics 스키마 밑에 테이블을 CTAS로 생성하기

raw_data의 세 개의 테이블을 활용해 MAU 테이블 생성