✅ Redshift 실습
cluster 생성 (AWS Management console에서 진행)
예제 테이블 1) 웹서비스 사용자/세션 정보 1
- 사용자 ID (웹서비스에서 등록된 사용자마다 부여하는 유일 ID)
- 세션 ID (사용자 방문을 논리적인 단위로 나눈 것 : 세션) 마다 부여되는 ID
💯 세션은 일반적으로 크게 아래 두가지 방법으로 나눔
- 사용자가 외부 링크 타고 들어오거나 직접 방문해서 오는 경우 생성
- 사용자가 방문 후 30분 간 interaction이 없다가 무언가 하는 경우 새로 생성
-> 즉 하나의 사용자는 여러 세션을 가질 수 있음.
-> 보통 세션의 경우, 마케팅 관련 기여도 분석을 위해 세션을 만들어낸 접점을 채널이란 이름으로 기록함 + 세션 생성 시간도 기록예제 테이블 2) 웹서비스 사용자/세션 정보 2
이 정보를 기반으로 다양한 데이터 분석 및 지표 설정 가능
- 마케팅 관련, 사용자 트래픽 관련
- DAU, WAU, MAU 등 일주월별 Active User 차트
- Marketing Channel Attribution 분석 (어느 채널에 어떤 광고가 효과적?)
✅ PostgreSQL
RedShift와 호환되는 PostgreSQL 8.X 버전에 대해 학습 진행!
- 다수의 SQL문 실행 시 ; 필요
- 주석 달 때 한 줄 : -- , 여러 줄 : / -- / (슬래시 앞에 *존재)
- 테이블, 필드 이름의 명명 규칙 중요 단수 복수, 또는, user_session_channel vs UserSessionChannel
🎈 DDL
폴더(스키마) 생성 혹은 스키마 내부에 존재하는 테이블 만드는 것으로 존재
PK 속성은 지정할 수 있지만 DW에서는 무시됨
ex) CREATE TABLE 스키마(폴더명).테이블 이름 (
컬럼명 타입,
컬럼명 타입 Primary key );CTAS 문법 : CREATE TABLE 스키마명.테이블명 AS SELECT VS CREATE TABLE and then INSERT
-> CREATE와 SELECT 동시에 바로 진행하는 문법❗ 레코드 수정
INSERT INTO : 테이블에서 레코드 추가 (시간 오래 걸려서 COPY (벌크 업데이트) 사용)
UDATE FROM : 테이블에서 레코드의 필드 값 수정
DELETE FROM : 테이블에서 존재하는 레코드 삭제
벌크 업데이트 ? : 레코드들을 csv, json 등 파일로 저장해 AWS 웹 storage인 S3에 업로드하여 원하는 테이블로 업데이트 하는 방식❗ 테이블 삭제
DROP TABLE 테이블명 : 테이블 삭제
-> DROP TABLE IF EXISTS 테이블명 으로 수정❗ 테이블 변경
ALTER TABLE 테이블명 ADD COLUMN 필드명 필드타입; : 새로운 필드(컬럼) 추가
ALTER TABLE 테이블명 RENAME 현재필드명 to 새로운필드명; : 기존 컬럼 이름 변경
ALTER TABLE 테이블명 DROP COLUMN 필드명; 기존 필드 제거
ALTER TABLE 테이블명 RENAME to 새테이블명 : 기존 테이블 이름 변경❗ SELECT
- 레코드 질의 언어
- SELECT FROM 테이블명 : 테이블에서 레코드, 필드 읽어옴
- WHERE : 레코드 선택 조건 지정
- GROUP BY : 정보 그룹 단위로 뽑기 가능 (DAU, WAU, MAU 등)
- ORDER BY : 레코드 순서 결정
- JOIN : 보통 다수 테이블 조인해서 사용
ex)
일반적인 SELECT 문으로 레코드 읽어오는 방법SELECT FIELD_NAME FROM TABLE_NAME WHERE CONSTRAINT1 GROUP BY FIELD_NAME ORDER BY FIELD_NAME DESC (default : asc) LIMIT N; (N개만 조회)❗ STRING Function
- LEFT(str,N) : str문자에서 왼쪽 N번째까지
- REPLACE(str, exp1, exp2) : 주어진 문자에서 exp1을 exp2로 변경
- UPPER(str) : str 대문자로
- LOWER() : 소문자
- LEN() : 길이
- LPAD() : 문자 왼쪽에 padding
- RPAD() : 문자 오른쪽에 padding
- SUBSTRING : 문자 A~B까지 contract가능
❗ 타입 변환
CONVERT_TIMEZONE('America/Los_Angeles', ts)
SELECT pg_timezone_names(); : 타임존 종류 확인 가능
DATE, TRUNCATE : 연월일만 출력
DATE_TRUNC('month', TIMESTAMP) : 첫 인자가 어떤 값 추출하는지 지정 (특정 파트만 출력)
EXTRACT OR DATE_PART : (특정 파트만 출력)
DATEDIFF : 날짜 차이
DATEADD : 두 날짜 합
GET_CURRENT : 현재시각 출력
TO_CHAR, TO_TIMESTAMP 로 변환도 가능
이외에도 ::오퍼레이터를 사용 가능 ex) category::float
CAST함수로도 가능 CAST(category as float)
< 코드로 SELECT 문 알아보기! >
# SQL 연결
%load_ext sql
# SQL install
!pip install SQLAlchemy==1.4.47
# %sql PostgreSQL 사용://사용자 ID:패스워드@호스트:포트번호/접속할 DB 입력
# 결과
이후 셀마다 %%sql 입력 후 sql문 작성하여 원하는 정보 추출 혹은, sql문 결과를 변수로 저장해 DataFrame화도 가능!
