학습주제
snowflake 실습을 위한 초기 환경 설정
학습내용
aws iam role 같은거 세팅
ACCOUNTADMIN 확인 후 SQL worksheet 생성
이름을 바꿔줌
Setup-Env
편집화면으로가서
내가 사용하고 있는 역할, 어떤 웨어하우스를 쓰는지, 명령을 타입하고 실행할 때, 모든 SQL을 동시에 실행할지, 커서가 있는 부분만 실행할지, 하이라이트 부분만 실행할지. 결정.
지금은 하이라이트, 커서위치 실행
다른 유저와 공유하고 싶으면 Share 버튼을 누르면 됨
앞으로 실행하는 sql statement들이 어떤 데이터베이스 밑의 테이블인지?
아무것도 선택 X -> 테이블을 지정할 때 데이터베이스.스키마.테이블 형태로 적어줘야함.
데이터베이스 선택 -> 데이터베이스 이름은 안적을 수 있음.
강사님은 안적고 진행
CREATE DATABASE dev;
-- 3개의 스키마를 생성한다.
CREATE SCHEMA dev.raw_data;
CREATE SCHEMA dev.analytics;
CREATE SCHEMA dev.adhoc;raw_data: ETL로 외부 소스 가져옴
애널리틱스 - ELT, CTAS로 만듦. 전문적은 DBT 툴을 사용
adhoc - 개발용, 테스트용
일단 실행시킴
테이블 생성
Snowflake은 primary key(기본 키) 개념을 지원하지 않습니다. Snowflake은 데이터 웨어하우스를 위해 설계된 고유한 아키텍처를 가지고 있으며, 데이터의 무결성과 일관성을 보장하기 위해 다른 방법을 사용합니다. 대신, Snowflake은 Unique Key 제약 조건을 사용하여 중복 데이터를 방지하고 데이터의 일관성을 유지합니다.
PK가 개발자들을 위해 명시적
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개를 만들고 COPY로 s3의 csv를 벌크함
CREATE OR REPLACE - 만일 테이블이 존재하면 지우고(DROP) 새로 만들어줌
에러 방지 가능 (있는걸 모르고 삭제할 위험성 존재)
COPY
sql문이 조금 다른 것을 볼 수있다. 그러나 개념은 같음
credential을 보면 아이디와 비번식으로 들어간다
COPY INTO
FROM
credentials=(AWS_KEY_ID='...' AWS_SECRET_KEY='...')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');값중에 스트링 값이 ""로 둘러싸여 있으면 빼라.
credentials로 검증
aws 로그인 할때 ACCOUNT 정보로 로그인, credentials에 가서 확인
웹콘솔 사용자마다 부여됨
저 정보가 잘못노출되면, AWS의 모든 기능을 사용할 수 있음.
어드민은 절대로 사용하면 안됨. 난리남
admin으로 로그인해서 새로운 사용자 생성
오직 s3 버킷을 읽을 수 있는 권한만 부여
AWS 웹 콘솔 로그인
사용자 추가
웹콘솔 가능여부 설정 우리는 안함. 실제 사용이 아니라, 증명용
직접 read 정책만 연결
증명용 사용자 생성완료
이제 access key를 만들어야함
보안 자격 증명 - 엑세스 키
스노우플레이크는 AWS기준 외부에서 실행되는 애플리케이션
생성됨.
analytics 테이블
문법 차이 거의 없음.
실습
낯선 기술들을 배울 때, 한번에 다 알 수는 없음. 처음부터 잘하는 사람은 없음. 그냥 하다보면 깨달음이 오는 경우가 많음. '내가 이런것도 알아야돼? 다른 사람들은 바로 캐치하는거야?' 일 수 있는데 그냥 하는 경우가 많음.
IAM - 사용자 - 사용자 추가
웹 콘솔에서 사용케 하려면 다음과 같은 절차로 진행
Administrator는 조심해서 부여. 강력함.
직접 정책 연결 - s3ReadOnly 선택
특정 버킷에다가만 읽기권한을 주고 싶으면 정책을 눌러 편집버튼 들어가서, 설정할 수 있음. - 이게 더 안전한 방법
보안 정책으로 가서 - access key 생성
외부 애플리케이션 선택
엑세스 키 ID, 시크릿 키 생성
FIELD_OPTIONALLY_ENCLOSED_BY='"' 이게 쫌 신기했음. 안에 " 한개만 넣음
-- COPY INTO table from local files. Select the right db.schema in the top dropdown
COPY INTO dev.raw_data.session_timestamp
FROM 's3://jongwook-redshift-bucket/test_data/session_timestamp.csv'
credentials=(AWS_KEY_ID='AKIAX4WNKLBDM3ERMO6T' AWS_SECRET_KEY='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')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
COPY INTO dev.raw_data.session_transaction
FROM 's3://jongwook-redshift-bucket/test_data/session_transaction.csv'
credentials=(AWS_KEY_ID='AKIAX4WNKLBDM3ERMO6T' AWS_SECRET_KEY='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')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
COPY INTO dev.raw_data.user_session_channel
FROM 's3://jongwook-redshift-bucket/test_data/user_session_channel.csv'
credentials=(AWS_KEY_ID='AKIAX4WNKLBDM3ERMO6T' AWS_SECRET_KEY='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')
FILE_FORMAT=(type='CSV' skip_header=1 FIELD_OPTIONALLY_ENCLOSED_BY='"');
일반적으로 SQL에서 컬럼 이름은 대소문자를 구분하지 않습니다. 대부분의 SQL 데이터베이스 시스템은 컬럼 이름을 대소문자 구분 없이 처리합니다. 이는 SQL 표준에 따르는 동작입니다.
-- CTAS
CREATE TABLE dev.analytics.mau_summary AS
SELECT
TO_CHAR(A.ts, 'YYYY-MM') AS month,
COUNT(DISTINCT B.userid) AS mau
FROM dev.raw_data.session_timestamp A
JOIN dev.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;
아래 실행 결과도 보여줌
이걸 차트로도 만들고 할수 있고 공유도 할 수 있음
