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

석형원·2024년 4월 26일

TIL redshift sql

TIL

목록 보기

20/52

✏️ 오늘 학습한 내용

1. 트랜잭션
2. SQL 고급 문법

🔎 트랜잭션

트랜잭션이란?

Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
- Atomic(원자성) : 여러 개의 sql이 동시에 성공을 하거나 혹은 동시에 실패를 해야지만 데이터의 정합성에 문제가 없음을 의미
- 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음.
  ( UPDATE, INSERT, DELETE )
- SELECT에는 트랜잭션을 사용할 이유가 없음
- BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
- ROLLBACK
은행 계좌 이체가 아주 좋은 예시
- 계좌 이체 : 인출과 입금의 두 과정으로 이루어짐
- 만일 인출은 성공했는데 입금이 실패한다면?
- 이 두 과정은 동시에 성공하던지 실패해야함
  -> Atomic하다는 의미
- 이런 과정들을 트랜잭션으로 묶어줘야함
- 조회만 한다면 이는 트랜잭션으로 묶일 이유가 없음

e.g.)

BEGIN;
	A의 계좌로부터 인출; -- a
    B의 계좌로 입금; -- b
END; -- END OR COMMIT

이 a와 b 명령어 들은 마치 하나의 명령어처럼 처리된다. 다 성공하던지 다 실패하던지 둘 중의 하나가 됨

END와 COMMIT은 동일
만일 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK을 실행
이 동작은 commit mode에 따라 달라짐!

트랜잭션 커밋 모드 : autocommit

autocommit = TRUE
- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐.
  ( 이를 commit 된다고 함. )
- 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리
autocommit = False
- 모든 레코드 수정/삭제/추가 작업이 COMMIT 호출될 때까지 커밋되지 않음
  ( commit 전까지 데이터베이스에 반영이 되지 않음 )

트랜잭션 방식

Google Colab의 트랜잭션
- 기본적으로 모든 SQL statement가 바로 커밋됨 (autocommit=True)
- 이를 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT을 사용 (혹은 ROLLBACK;)
psycopg2의 트랜잭션
- autocommit이라는 파라미터로 조절가능
- autocommit=True가 되면 기본적으로 PostgreSQL의 커밋 모드와 동일
- autocommit=False가 되면 커넥션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절 가능
- 무엇을 사용할지는 개인 취향

e.g.)
Redshift는 autocommit=True가 default이기 때문에 아래 명령어를 실행시 실제 DB에 바로 적재가 된다.

%%sql

DROP TABLE IF EXISTS adhoc.keeyong_name_gender;
CREATE TABLE adhoc.keeyong_name_gender (
  name varchar(32),
  gender varchar(16)
);
INSERT INTO adhoc.keeyong_name_gender VALUES ('Ben', 'Male'), ('Maddie', 'Female');

DELETE FROM vs. TRUNCATE

DELETE FROM table_name (not DELETE * FROM)
- 테이블에서 모든 레코드를 삭제
- VS. DROP TABLE table_name
  (테이블 자체를 삭제)
- WHERE을 사용해 특정 레코드만 삭제 가능
TRUNCATE table_name
- 테이블에서 모든 레코드를 삭제
- DELETE FROM은 속도가 느림
- TRUNCATE가 전체 테이블의 내용 삭제시에는 여러모로 유리
- 단점 : WHERE, 트랜잭션을 지원하지 않는다.

DELETE FROM은 트랜잭션 내에서 사용 가능,
TRUNCATE은 트랜잭션 안에서 사용하면 ROLLBACK이 안됨.

Googlecolab에서 psycopg2 사용

psycopg2 - Redshift 연결

import psycopg2

# Redshift connection 함수
def get_Redshift_connection(autocommit):
    host = "redshift연결과 동일한 host"
    redshift_user = ""
    redshift_pass = ""
    port = 
    dbname = ""
    conn = psycopg2.connect("dbname={dbname} user={user} host={host} password={password} port={port}".format(
        dbname=dbname,
        user=redshift_user,
        password=redshift_pass,
        host=host,
        port=port
    ))
 	#  여기서 autocommit을 설정
    conn.set_session(autocommit=autocommit)
    return conn

INSERT SQL을 autocommit=False로 실행

# Redshift Connection을 만드는데,
# autocommit=False로 지정
conn = get_Redshift_connection(False)
# Redshift의 커서(입력을 기다리는 object)를 가져옴
cur = conn.cursor()

# 커서로 sql를 지정
cur.execute("SELECT * FROM adhoc.keeyong_name_gender;")
# sql의 결과는 fetchall()로 받을 수 있음
res = cur.fetchall()
for r in res:
  print(r)
  
# autocommit=False일 때의 delete
# 결과는, Redshift에 있는 DB에는 반영이 되지않고 이 세션에서만 적용됨.
cur.execute("DELETE FROM adhoc.keeyong_name_gender;")

# autocommit=False일 때의 insert
# 이 결과도, Redshift에 있는 DB에는 반영이 되지않고 이 세션에서만 적용됨.
cur.execute("INSERT INTO adhoc.keeyong_name_gender VALUES ('Keeyong', 'Male');")

# commit을 했으니 위에서 수행한 명령들이 Redshift의 DB에도 반영됨
cur.execute("COMMIT;")

# 연결 해제
conn.close()

INSERT SQL을 autocommit=True로 실행
- 트랜잭션을 하고 싶은 경우 BEGIN;END; 혹은 BEGIN;COMMIT을 사용

cur.execute("BEGIN;")
cur.execute("DELETE FROM adhoc.keeyong_name_gender;")
cur.execute("INSERT INTO adhoc.keeyong_name_gender VALUES ('Benjamin', 'Male');")
cur.execute("END;")

-> 명령어 하나가 잘못된 경우, 트랜잭션 내의 모든 명령어가 실행이 되지 않는다.

🔎 SQL 고급 문법

알아두면 유용한 SQL 문법들

UNION, EXCEPT, INTERSECT

COALESCE, NULLIF

LISTAGG

LAG

WINDOW 함수

ROW_NUMBER OVER

SUM OVER

FIRST_VALUE, LAST_VALUE

JSON Parsing 함수

UNION, EXCEPT, INTERSECT

UNION (합집합)
- 여러 개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
- UNION vs. UNION ALL
  - UNION은 중복을 제거
  - UNION ALL은 중복을 포함
EXCEPT (minus)
- 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능
INTERSECT (교집합)
- 여러 개의 SELECT 문에서 같은 레코드들만 찾아줌

COALESCE, NULLIF

COALESCE(Expression1, Expression2, ...)
- 첫번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL인 경우 NULL을 리턴한다.
- NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
NULLIF(Expression1, Expression2, ...)
- exp1과 exp2의 값이 같으면 NULL을 리턴한다.

LISTGG

GROUP BY에서 사용되는 Aggregate 함수 중의 하나
사용자 ID별로 채널을 순서대로 리스트:

SELECT
	userid,
    -- within ... 을 붙이지 않으면 순서가 랜덤하게 리스트가 만들어진다.
    LISTAGG(channel) WITHIN GROUP (ORDER BY ts) channels
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1
LIMIT 10;

-> ex) 68 YoutubeGoogleInstagramYoutube...

%%sql

SELECT 
	userid,
    -- -> 구분자를 추가
	LISTAGG(channel, '->') WITHIN GROUP (ORDER BY ts) channels
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1
LIMIT 10;

-> ex) 68 Youtube->Google->Instagram->Youtube->...

WINDOW

Syntax :
- function(expression) OVER ([PARTITION BY expression] [ORDER BY expression])
Useful_functions:
- ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
- Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

LAG함수

어떤 사용자 세션에서 시간 순으로 봤을 때
- 앞 세션의 채널이 무엇인지 알고 싶다면?
- 혹은 다음 세션의 채널이 무엇인지 알고 싶다면?
이전 채널 찾기

%%sql

SELECT usc.*, st.ts, LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts) prev_channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
ORDER BY usc.userid, st.ts
LIMIT 100;

다음 채널 찾기

%%sql

-- DESC로 순서만 바꿔주면 다음 채널 찾기로 바뀜
SELECT usc.*, st.ts, LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts DESC) prev_channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
ORDER BY usc.userid, st.ts
LIMIT 100;

JSON Parsing Functions

JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
- JSON String을 입력으로 받아 특정 필드의 값을 추출 가능 (nested 구조 지원)
ex) JSON_EXTRACT_PATH_TEXT

%%sql

/*
f2 : {
	f3 : 1
}
f4 : {
	f5 : 99
    f6 : star
}
이렇게 되있을 때, 
f4의 f6을 가져오겠다.
즉, star가 반환된다.
*/
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3": "1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');

-> 내가 이미 JSON String의 구조를 알고 있고, 뭐를 읽어야하는 지 알고 있는 경우 매우 유용