SQL을 이용한 데이터 분석 - Redshift (TIL 17)

석형원·2024년 4월 23일

TIL redshift sql

TIL

목록 보기

17/52

✏️ 오늘 학습한 내용

Redshift & SQL

AWS Console을 통한 Redshift launch

관계형 데이터베이스 예제

Redshift 중심의 SQL (DDL, DML) 소개

GoogleColab을 통한 실습

🔎 Redshift & SQL

AWS Console을 통한 Redshift launch

AWS 웹 진입 -> Redshift 선택 -> cluster 생성

cluster 설정
( region : Seoul 선택 )
Node type = dc2.large ( 가장 저렴한 )
Number of nodes = 1
Additional configurations의 Use defaults를 비활성
Network and security의 Publicly accessible을 Turn on 시켜줌
( AWS 내부가 아닌 외부 환경 - colab에서도 접근할 수 있게 하기 위함 )

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보

(테이블 소개)

사용자 ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID : 세션마다 부여되는 ID
- 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
  (일반적으로 google analytics의 정의를 따름)
  - 사용자가 외부 링크를 타고 오거나 직접 방문해서 올 경우 세션을 생성
  - 사용자가 방문 후 30분 간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
    (time bound)
- 즉, 하나의 사용자는 여러 개의 세션을 가질 수 있다.
- 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
  - 마케팅 기여도 분석을 위함
- 또한 세션이 생긴 시간도 기록함
이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
- 마케팅 관련, 사용자 트래픽 관련
- DAU, WAU, MAU 등의 일,주,월 별 Active User 차트
- Marketing Channel Attribution 분석
  - 어느 채널에 광고를 하는 것이 가장 효과적인가?
e.g.) 사용자 ID 100번이 상품을 구매하기까지의 세션 생성
- 세션 1 : 구글 키워드 광고로 시작한 세션 (9:30)
- 세션 2 : 페이스북 광고를 통해 생긴 세션 (10:00)
- 세션 3 : 네이버 광고를 통해 생긴 세션 (10:30)
- 상품 구매 (10:35)
-> 이 세션 중에 상품에 구매에 도달한 기여도가 가장 많은 것을 알아내는 것이 중요함 (데이터 분석)
( 어느 채널(세션)에게 크레딧을 줄 것이냐? )
- 방법론 1: First Channel Attribution (처음 발견하게 해준 채널에게 크레딧(기여도)를 몰아줌)
- 방법론 2: Last Channel Attribution (상품 구매 직전, 마지막 채널에게 몰아주는 것)
- 방법론 3: Multichannel Attribution ( 세션 별로 일정 부분씩 크레딧를 나눠 줌)
폴더 : 데이터베이스 (스키마) -> 폴더 내부 : 테이블
테이블 내용

Redshift 중심의 SQL (DDL, DML) 소개

SQL 기본

다수의 SQL 문 실행 시, 세미콜론을 사용해 분리를 해줘야한다.
- SQL문 1; SQL문 2;
SQL 주석
- -- : 인라인 한줄짜리 주석. c,java의 //와 동일
- /*--*/ : 여러 줄에 걸쳐 사용 가능한 주석
SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅(일종의 규칙)이 필요
- 팀 프로젝트라면 팀에서 사용하는 공통 format이 필요
테이블/필드 이름의 명명 규칙을 정하는 것이 중요
- 단수형 vs. 복수형
  - User vs. Users
- _ vs. CamelCasing
  - user_session_channel vs. UserSessionChannel

SQL DDL - 테이블 구조 정의 언어

CREATE TABLE
Primary key 속성을 지정할 수 있으나 무시됨 (OLAP)
- Primary key uniqueness
  - Big Data 데이터 웨어하우스에서는 지켜지지 않음 (Redshift, Snoflake, BitQuery)
- CTAS : 테이블을 만듦과 동시에 내용까지 채워버림
  ( create + insert를 동시에 )
  
  -> CTAS : CREATE TABLE table_name AS SELECT문
  - vs. CREATE TABLE and then INSERT
e.g.)
```
-- CREATE TABLE skema_name.table_name(...);
CREATE TABLE raw_data.user_session_channel(
	userid int,
  sessionid varchar(32) primary key,
  channel varchar(32)
);
```
레코드 수정 언어:
- INSERT INTO : 테이블에 레코드를 추가하는데 사용
  -> 시간이 오래걸리기에, 이를 안쓰고 copy를 사용
  ( bulk update 명령 : 레코드들을 csv, json으로 저장해놓고 웹 스토리지(S3)에 업로드,
  S3에서 내가 원하는 테이블로 bulk로 한번에 업데이트를 진행 )
- UPDATE FROM
- DELETE FROM ( 테이블의 구조는 남기고 레코드만 제거 )
  - vs. TRUNCATE ( )
- DROP TABLE ( 테이블 자체 제거 )
  - DROP TABLE table_name;
    - 없는 테이블을 지우려는 경우 에러 발생
  - DROP TABLE IF EXISTS table_name;
    ( 없는 테이블을 지우려하는 경우 에러를 내지 않고 그냥 종료 )
- ALTER TABLE
  - 새로운 컬럼 추가 :
    - ALTER TABLE table_name ADD COLUMN field_name field_type;
  - 기존 컬럼 이름 변경 :
    - ALTER TABLE table_name RENAME 현재필드이름 to 새필드 이름;
  - 기존 컬럼 제거 :
    - ALTER TABLE table_name DROP COLUMN 필드이름;
  - 테이블 이름 변경 :
    - ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

SQL DML - 테이블 데이터 조작 언어

레코드 질의 언어 : SELECT
- SELECT FROM
- WHERE
- GROUP BY
- ORDER BY
- JOIN

GoogleColab을 통한 실습

실습에 앞서 기억할 점

현업에서 깨끗한 데이터란 존재하지 않음
- 항상 데이터를 믿을 수 있는지 의심할 것
- 실제 레코드를 몇 개 살펴보는 것 만한 것이 없음 -> 노가다가 꼭 필요
데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요
- 중복된 레코드를 체크
- 최근 데이터의 존재 여부 체크하기 (freshness)
- Primary key uniqueness가 지켜지는지 체크하기
- 값이 비어있는 칼럼들이 있는지 체크하기
- 위의 체크는 !!코딩의 unit test 형태로 만들어 매번 쉽게 체크!!해볼 수 있음
=> unit test를 거친 후에 분석으로 넘어갈 수 있는 자동화 파이프라인을 만들어야한다!!
어느 시점이 되면 너무나 많은 테이블이 존재하게 됨
- 회사 성장과 밀접한 관련
- 중요한 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것 (규칙이 필요)
그 시점부터는 Data Discovery 문제들이 생겨남
- 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
- 테이블에 대해 질문하고 싶은데 누구에게 질문을 해야하나?
이 문제를 해결하기 위한 다양한 오픈소스와 서비스들이 출현
- DataHub (LinkedIn), Amundsen (Lyft), ...
- Select Star, DataFrame, ...
SQL 예제

-- 유일한 채널 이름을 알고 싶은 경우
SELECT DISTINCT channel
FROM raw_data.user_session_channel;

-- 채널별 카운트를 하고 싶은 경우
SELECT channel, COUNT(1)
FROM raw_data.user_session_channel
GROUP BY 1;

-- 테이블의 모든 레코드 수 카운트. COUNT(*)를 써도 무방
SELECT COUNT(1)
FROM raw_data.user_session_channel

-- 채널 이름이 Facebook 경우만 고려해 레코드 수를 카운트
SELECT channel, COUNT(1)
FROM raw_data.user_session_channel
WHERE channel = 'Facebook';

/* CASE WHEN

필드 값의 변환을 위해 사용 가능
ex) CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름
여러 조건을 사용하여 변환하는 것도 사용 가능
*/

SELECT CASE
	WHEN channel in ('Facebook','Instagram') THEN 'Social-Media'
    WHEN channel in ('Google','Naver') THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM raw_data.user_session_channel;

NULL
- 값이 존재하지 않음을 나타내는 상수
- 특수한 문법을 통해 비교
  ( is NULL, is not NULL )
- 사칙연산에 사용되는 경우?
  -> 결과가 NULL이 됨
COUNT 함수 예시
(table: count_test)

value
NULL
1
1
0
0
4
3

SELECT COUNT(1) FROM count_test;
--7
SELECT COUNT(0) FROM count_test;
--7
SELECT COUNT(NULL) FROM count_test;
--0
SELECT COUNT(value) FROM count_test;
--6
SELECT COUNT(DISTINCT value) FROM count_test;
--4

COUNT() 함수에 들어가는 값이 NULL인 경우 세지않고 넘어가고 그 외의 값은 레코드를 센다.

WHERE
- IN / NOT IN
  ( IN 뒤로 오는 조건들에 포함이 되는가 안되는가? )
- LIKE and ILIKE
  ( 문자열 매칭, LIKE : 대소문자 구분, ILIKE : 대소문자 미구분 )
  - NOT LIKE or NOT ILIKE
- BETWEEN
  ( 두 조건의 범위 사이 매칭 )
- 이 오퍼레이터들은 CASE WHEN에서도 사용 가능
STRING Functions
- LEFT(str, N) : 문자열 앞부분 N개만 추출
- REPLACE(str,exp1,exp2) : 첫번째 인자로 주어진 문자열에서 exp1 문자열을 찾아서 exp2 문자열로 바꿔치기
- UPPER(str) : 대문자로 변환
- LOWER(str) : 소문자로 변환
- LEN(str) : 길이를 리턴
- LPAD, RPAD : 문자열을 왼/오른쪽으로 패딩함
- SUBSTRING : 시작점을 선언해서 부분 문자열을 추출
ORDER BY
- ASC : 오른차순 (NULL값이 마지막에 위치)
- DESC : 내림차순 (NULL값이 처음에 위치)
- NULL의 위치를 바꾸고 싶다면
  -> NULLS FIRST / NULLS LAST 를 사용
타입 변환
- DATE Conversion :
  - 타임존 관련 변환
    - CONVERT_TIMEZONE('America/Los_Angeless',ts)
    - select pg_timezone_names(); ( 어느 타임존들이 있는지 알고 싶을 경우 )
  - DATE, TRUNCATE
    - TIMESTAMP를 입력받아 연도/월/날짜만 반환
  - DATE_TRUNC
    - 첫번째 인자가 어떤 값을 추출하는지 지정 (week, month, day, ... )
  - EXTRACT or DATE_PART
    - 날짜시간에서 특정 부분의 값을 추출 가능
  - DATEDIFF
    - 날짜간 차이 구하기
  - DATEADD
    - 날짜 더하기
  - GET_CURRENT, ...
- TO_CHAR, TO_TIMESTAMP
Type Casting
- 1/2의 결과는?
  -> 0
  - 분자나 분모 중 하나를 float로 캐스팅해야 0.5가 나온다.
- :: 오퍼레이터를 사용
  - category::float
    (float로 변환)
- cast 함수를 사용
  - cast(category as float)
    (cast함수를 사용해 float로 변환)

실습 설정

주피터 SQL 엔진 설정
%load_ext sql

%sql postgresql://guest:guest_name!password@redshift_host_name:port_num/db_name

주피터 sql 사용법

# %%sql로 sql 사용을 선언
%%sql

# 평범하게 sql문을 작성하면 됨
SELECT 1/2;

판다스와 연동하는 방법

result = %sql SELECT * FROM raw_data.user_session_channel

df = result.DataFrame()

df.head()

df.groupby(["channel"]).size()

# 위와 동일한 결과
df.groupby(["channel"])["sessionid"].count()