Snowflake 스키마 생성, COPY, 테이블 생성, CTAS 등

들어가기 전에

서울엔 2023.02.16에 첫 런칭함

오늘 8주차 4일자는 Snowflake를 학습했다.

어제까지는 Redshfit를 사용했는데, Snowflake로 넘어오니까 UI가 빛이 난다...🎇✨

 

UI가 직관적으로 짜여있어서 작업환경 왔다갔다할때 편리했고, 

무엇보다 워크시트 옵션(warehouse, 권한 설정, 특정 줄만 실행 등등)들이 너무 편리했다.

 

물론 Redshift와 Snowflake를 많이 깊게 다뤄본건 아니지만, 지금까지 사용한 후기는 그렇다!

이제 본론을 들어가서 Snowflake 실습 내용은 같다.

 

실습 내용 

1. Snowflake로 database와 스키마를 생성한다.

2. 스키마 아래에 테이블을 만든다.

3. AWS S3에 있는 csv 파일을 2번에서 만든 테이블로 data를 copy한다. (Burk Update)

4. 가져온 데이터로 분석 테이블을 만든다. 

5. 추가로 role 생성하고 사용자를 만든다.

이제 바로 실습을 해보자!

---

 

준비 단계

1. 무료 크레딧 계정 (링크) 만들기

주의! 만들고 로그인 URL 북마크해두기

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

• USER 이름 : snowflake_s3
• console 옵션 : X
• 권한 부여 : AmazonS3ReadOnlyAccess 권한 부여
  • Attack policies directly 선택 후, S3READ 필터링 후 ****
• Access key 생성 → 실습에 사용할 예정임
  • 보안자격증명 > 액세스 키 만들기 > ‘AWS 외부에서 실행되는 애플리케이션’ 선택

IAM 설정

 

💻실습

1. worksheet 새로 생성 (SQL)

• worksheet rename: Setup-Env
• 우측 상단에 role, warehouse, 실행 버튼 확인 가능함
• ‘No Database selected’ 옵션 칸에서, database 선택 가능
  • 작성하면 테이블 지정 시 database 이름 생략 가능

 

2. Database ‘dev’ 생성

권한 admin

CREATE DATABASE dev;

 

3. 아래에 스키마 3개 생성

권한 admin

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

 

4. raw_data 스키마 밑에 3개 테이블 생성

CRATE OR REPLACE TABLE : 테이블 존재하면 지우고 새로 만듦

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

 

5. AWS S3에서 데이터 COPY해서 Burk Update 수행

COPY INTO (database).(schema).(table)
FROM ‘’
credentials=(AWS_KEY_ID=’’, AWS_SECRET_KEY=’’)
FILE_FORMAT = ();

credentials은 FROM에 s3가 있을때 접근 권한이 있는지 인증해주는 역할임

COPY INTO dev.raw_data.session_timestamp
FROM 's3://(버킷명)/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://(버킷명)/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://(버킷명)/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='...' AWS_SECRET_KEY='...')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"')

 

6. CTAS로 analytics 스키마 아래에 테이블 생성

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

 

 

6. 3개의 role과 1명의 user 생성 후 Role 2개 설정

-- Role 생성 
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;

-- 사용자 생성
CREATE USER hyemin PASSWORD='';

-- 사용자에게 analytics_users 권한 지정
GRANT ROLE analytics_users TO USER hyemin;

-- Role 2개 설정
-- 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;

 

결과 화면

결과를 char로도 확인가능하다 (갓 snowflake..)

 

역 시 snowflake가 비용이 다소 비싸지만 많은 기업에서 사용하는 이유가 있나보다 👍

 

 

 

코드 전문은 아래 첨부📌

-- database 생성
CREATE DATABASE dev;

-- 스키마 3개 생성
CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;

--  raw_data 스키마 밑에 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
); 

-- AWS S3에서 데이터 COPY해서 Burk Update 수행
COPY INTO dev.raw_data.session_timestamp
FROM 's3://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.session_transaction
FROM 's3://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://bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='' AWS_SECRET_KEY='')
FILE_FORMAT = (type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"')



-- CTAS로 analytics 스키마 아래에 테이블 생성
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

----- Role 생성
CREATE ROLE analytics_users;
CREATE ROLE analytics_authors;
CREATE ROLE pii_users;

-- USER 생성
-- 사용자 생성
CREATE USER hyeminKim PASSWORD='***';


-- 사용자에게 analytics_users 권한 지정
GRANT ROLE analytics_users TO USER hyeminKim;

-- role 2개 설정 
-- 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;