[2주차] SQL for Data Engineers(1)

Redshift 데모 환경 설명, Redshift를 구글 Colab으로 접근해보는 데모를 하고 SQL 연습

 

관계형 데이터베이스의 구조

 - 관계형 데이터 베이스는 2단계로 구성

  > 가장 밑단에는 테이블들이 존재(테이블은 엑셀의 시트에 해당)

  > 테이블들은 데이터베이스 (or 스키마)라는 폴더 밑으로 구성

 

테이블의 구조 (테이블 스키마라고 부르기도 함)

- 테이블은 레코드들로 구성

- 레코드는 하나 이상의 필드로 구성

- 필드는 이름과 타입으로 구성됨

테이블 스키마

 

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

- 사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID

- 세션 ID: 세션마다 부여되는 ID

 > 세션: 사용자의 방문을 논리적인 단위로 나눈 것

  ㄴ사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성

 > 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음

 > 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠

  ㄴ마케팅 관련 기여도 분석을 위함

 > 또한 세션이 생긴 시간도 기록

 

- 이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능

 > 마케팅 관련, 사용자 트래픽 관련

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;

 

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

- 사용자 ID 100번: 총 3개의 세션(파란 배경)을 갖는 예제

 > 세션 1: 구글 키워드 광고로 시작한 세션

 > 세션 2: 페이스북 광고를 통해 생긴 세션

 > 세션 3: 네이버 광고를 통해 생긴 세션

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;
-- COUNT의 동작을 잘 이해하는 것이 중요!
-- DISTINCT와 연동

 

SQL의 장단점, 빅데이터 세상에도 SQL은 중요할까?

 

Easy to Use and Vetted over Time, 시간이 지남에 따라 검증되고 간편한 사용

- 구조화된 데이터(관계형 데이터베이스) 작업에 가장 적합한 언어
- 데이터는 레코드(행)와 필드(유형이 있는 열)가 있는 테이블(구조화)에 저장
- DDL: 데이터 정의 언어(테이블 만들기, 테이블 삭제, 테이블 변경)
- DML: 데이터 조작 언어(선택, 삽입)
- 2000년대 중반에는 새로운 빅 데이터 기술이 등장하면서 인기가 떨어졌다.
- 하지만 결국에는 모두 일부 형태의 SQL(Hive, Presto)을 지원하게 되었다.

 

Not Great in Handling Unstructured Data, 비정형 데이터 처리에 취약

- 정형 데이터 처리에 최적화
 > 비정형 데이터 처리에 정규식 및 JSON을 어느 정도 사용할 수 있습니다.
 > 레드쉬프트는 평면 구조(중첩 구조가 아님)만 처리 가능

- 빅쿼리는 중첩 구조 지원 (매우 강력함)
 >스타 스키마가 정규화 스키마에 비해 항상 좋은 것은 아님
 > 대부분 비슷하지만 표준 구문이 없음 (다양한 SQL 방언)

- 반정형 또는 비정형 데이터 처리에 유연하지 않음:
○ 보다 유연한 대규모 비정형 데이터 처리를 위해서는 다른 프레임워크 필요
○ Spark, Hadoop(MapReduce -> Hive) 등