데이터 엔지니어링 34일차 TIL

학습 내용

Snowflake
: 2014년 클라우드 기반 데이터웨어하우스로 시작(2020년 상장)
- 스토리지와 컴퓨팅 인프라가 별도로 설정되는 가변 비용 모델
- SQL 기반으로 빅데이터 저장, 처리, 분석이 가능(비구조화된 데이터처리와 머신러닝 기능도 제공)
- CSV, JSON, Avro, Parquet 등과 같은 다양한 데이터 포맷을 지원
- 배치 데이터 중심이지만, 실시간 데이터 처리 지원
- Time Travel : 과거 데이터 쿼리 기능으로 트렌드를 분석하기 쉽게 해줌
- 웹 콘솔 이외에도 Python API를 통한 관리/제어 가능(ODBC,JDBC 연결도 지원)
- 자체 스토리지 이외에도 클라우드 스토리지를 외부 테이블로 사용가능
- Group을 지원하지 않는다!!

Snowflake 특징
Organizations
: 한 고객이 사용하는 모든 Snowflake 자원들을 통합하는 최상위 레벨 컨테이너
- 하나 혹은 그 이상의 Account들로 구성되며 이 모든 Account들의 접근권한, 사용트래킹, 비용들을 관리하는데 사용된다.

Accounts
: 하나의 Account는 자체 사용자, 데이터, 접근권한을 독립적으로 가진다
- 한 Account는 하나 혹은 그 이상의 Database로 구성됨

Databases
: 하나의 Database는 한 Account에 속한 데이터를 다루는 논리적인 컨테이너
- 한 Database는 다수의 스키마와 거기에 속한 테이블과 뷰 등으로 구성되어 있음
- 하나의 Database는 PB단위까지 스케일 가능하고 독립적인 컴퓨팅 리소스를 갖게 됨
- 컴퓨팅 리소스를 Warehouses라고 부름, 웨어하우스와 데이터베이스는 1대1 관계가 아니다

Snowflake의 기본 데이터 타입
Numeric: TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER, NUMERIC,
DECIMAL, FLOAT, DOUBLE, REAL.
Boolean: BOOLEAN.
String: CHAR, VARCHAR, TEXT, BINARY, VARBINARY.
Date and Time: DATE, TIME, TIMESTAMP, TIMESTAMP_LTZ,
TIMESTAMP_TZ.
Semi-structured data: VARIANT (JSON, OBJECT.
Binary: BINARY, VARBINARY
Geospatial: GEOGRAPHY, GEOMETRY.
Array: ARRAY
Object: OBJECT

Snowflake 데이터베이스, 스키마, 테이블 생성
--데이터베이스 생성

CREATE DATABASE dev;

-- 먼저 3개의 스키마를 생성한다

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

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

-- 카피

COPY INTO dev.raw_data.session_timestamp
FROM 's3://woo-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.user_session_channel
FROM 's3://woo-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='"');

COPY INTO dev.raw_data.session_transaction
FROM 's3://woo-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='"');

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

Snowflake 권한 부여
-- 3개의 ROLE을 생성한다

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

-- 사용자 생성

CREATE USER woo PASSWORD='Wshtngk120';

-- 사용자에게 analytics_users 권한 지정

GRANT ROLE analytics_users TO USER woo;

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

 

느낀 점

요즘 가장 핫하다는 Snowflake에 대해서 배웠다.

Snowfalke내에서 GUI환경으로 직관적으로 데이터베이스의 구조, 스키마, 테이블 등을 볼 수 있다는 점에서 정말 좋았다.

기존의 다른 것들로 배울 때는 확인하려면 명령어를 직접 쳐보면서 구조를 대충 알아가는 식이었는데 Snowflake는 눈으로 바로 구조를 알 수 있어서 초보자에게 굉장히 좋은 환경인 것 같다.

Redshift와 비슷하면서 조금씩 다른 부분이 있어서 좀 더 면밀히 공부해야할 것 같다.