Snowflake 실습 - DB, 스키마, 테이블 생성

실습의 단계별 목표

 

 

1. DEV 데이터 베이스 만들기

 

2. 3개의 스키마 만들기

 

3. COPY SQL을 이용해 S3의 csv파일들을 벌크 업데이트하여 raw_data 스키마 밑에 테이블들 만들기

 

4. 그 과정에서 AWS IAM User(사용자)를 설정

 

5. analytics 스키마 밑에 summary 테이블을 만들기

 

 

이번 시간에는 위의 볼드체로 표시된 1, 2, 3, 5번을 실습해 보려합니다.

 

 

Snowflake의 Setup-Env Worksheet에서 실행합니다.

 

 

 

 

1. DEV 데이터 베이스 만들기

 

CREATE DATABASE dev;

 

 

 

 

 

 

 

2. 3개의 스키마 만들기

 

-- 먼저 3개의 스키마를 생성한다.
CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;

 

 

 

 

 

 

 

 

 

 

3-1. 테이블 만들어 놓기

 

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

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

 

 

 

 

 

3-2. COPY로 S3의 파일들을 테이블에 입력하기

 

-- COPY INTO table from local files. Select the right db.shema in the top dropdown
COPY INTO dev.raw_data.session_timestamp
from 's3://devjon-test-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='(~~)' AWS_SECRET_KEY='(~~)')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO dev.raw_data.session_transaction
from 's3://devjon-test-bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='(~~)' AWS_SECRET_KEY='(~~)')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO dev.raw_data.user_session_channel
from 's3://devjon-test-bucket/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='(~~)' AWS_SECRET_KEY='(~~)')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

 

 

 

 

 

 

 

 

5. Analytics 스키마에 CATS를 이용해 ELT 해보기

보통은 dbt를 사용하지만 아주 간단하게 CATS로 ELT를 해봅니다.

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

 

만들어진 ELT 결과물이 잘 생성되었는지 SELECT로 확인