[W12D3] Spark SQL

SQL이란?

SQL은 빅데이터 세상에서도 중요!

• 구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰임
• 모든 대용량 데이터 웨어하우스는 SQL 기반
  • Redshift, Snowflake, BigQuery, Hive
• Spark도 예외는 아님
  • SparkSQL이 지원됨
• 데이터 분야에서 일하고자 하면 반드시 익혀야할 기본 기술

관계형 데이터베이스

• 대표적인 관계형 데이터베이스
  • MySQL, Postgres, Oracle, …
  • Redshift, Snowflake, BigQuery, Hive, …
• 관계형 데이터베이스는 2단계로 구성됨
  • 가장 밑단에는 테이블들이 존재(테이블은 엑셀의 시트에 해당)
  • 테이블들은 데이터베이스라는 폴더 밑으로 구성
• 테이블의 구조(스키마라고 부르기도 함)
  • 테이블은 레코드들로 구성
  • 레코드는 하나 이상의 필드(컬럼. 이름과 타입으로 구성됨)로 구성
  • 필드는 이름과 타입으로 구성됨

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

• 사용자 ID:
  • 보통 웹서비스에서는 등록된 사용자마다 유일한 ID를 부여 -> 사용자 ID
• 세션 ID:
  • 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
  • 즉 하나의 사용자 ID는 여러 개의 세션 ID를 가질 수 있음
  • 보통 세션의 경우 세션을 만들어낸 소스를 채널이란 이름으로 기록해둠
    • 마케팅 관련 기여도 분석을 위함
  • 또한 세션이 생긴 시간도 기록
• 이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
  • 마케팅 관련
  • 사용자 트래픽 관련
• 사용자 ID 100번: 총 3개의 세션을 갖는 예제
  • 세션1: 구글 키워드 광고로 시작한 세션
  • 세션2: 페이스북 광고를 통해 생긴 세션
  • 세션3: 네이버 광고를 통해 생긴 세션
  • 이를 raw data 상태에서 데이터베이스에 저장하면 다음과 같다.
    • user_session_channel
    • session_timestamp

SQL 소개

• SQL: Structured Query Language
• SQL은 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
  • 주로 관계형 데이터베이스에 있는 데이터(테이블)를 질의하는 언어
• 두 종류의 언어로 구성됨
  • DDL(Data Definition Language):
    • 테이블의 구조를 정의하는 언어
  • DML(Data Manipulation Language):
    • 테이블에 레코드를 추가/삭제/갱신해주는데 사용하는 언어

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

• CREATE TABLE
• DROP TABLE
• ALTER TABLE

CREATE TABLE raw_data.user_session_channel(
    userid int,
    sessionid varchar(32),
    channel varchar(32)
);

raw_data는 폴더(데이터베이스)를, user_session_channel은 테이블이다.
userid, sessionid, channel은 테이블에 들어갈 칼럼들이다.

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

• SELECT FROM: 테이블에서 레코드와 필드를 읽어오는데 사용
  • 보통 다수의 테이블을 조인해서 사용하기도 함
• INSERT INTO: 테이블에 레코드를 추가하는데 사용
• UPDATE FROM: 테이블의 레코드를 수정
• DELETE FROM: 테이블에서 레코드를 삭제

SELECT 필드(칼럼)이름1, 필드이름2, ...
FROM 테이블 이름
WHERE 선택조건
ORDER BY 필드이름[ASC|DESC] -> 내림차순/오름차순 정렬
LIMIT N; -> 모든 레코드들을 다 읽어오지 말고 처음 N개만 읽어온다.

SELECT * FROM raw_data.user_session_channel LIMIT 10;

raw_data의 user_session_channel이라는 테이블에서 모든 데이터를 다 읽어오는데, 처음 레코드 10개까지만 읽어와라.

SELECT COUNT(1) FROM raw_data.user_session_channel;

raw_data의 user_session_channel이라는 테이블의 레코드 수를 반환한다.

SELECT COUNT(1) FROM raw_data.user_session_channel
WHERE channel = 'Facebook'; -- channel 이름이 Facebook인 경우만 고려

raw_data의 user_session_channel이라는 테이블의 channel이름이 Facebook인 경우만 센다.

SELECT COUNT(1) FROM raw_data.user_session_channel
WHERE channel ilike '%o%' -- channel 이름에 o나 O가 있는 경우만 고려

raw_data의 user_session_channel이라는 테이블의 channel이름이 o,O가 포함인 경우만 센다.

SELECT channel, COUNT(1) -- channel별로 레코드수 카운트하기
FROM raw_data.user_session_channel
GROUP BY channel;

유저 세션 채널의 사용자별로 세션이 생겼을때 어떤 채널을 통해서 왔는지, 채널별로 몇개의 레코드들이 있는지 카운트한다. 그룹바이를 이용한다.

• 세션에 대한 모든 정보 읽어오기: user_session_channel과 session_timestamp 조인하기!

SELECT *
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp ts ON us.sessionID = ts.sessionID;

• JOIN은 두개의 테이블 사이의 관계를 정의한다.
• INNER JOIN은 그냥 JOIN이라고 쓰고, 교집합이다. JOIN 컨디션이 만족하는 것만 select한다. ON뒤에 오는 것이 조인 컨디션이다. 여기서는 유저의 세션아이디와 타임스탬프의 세션아이디가 같은 경우에만 리턴한다.
• usc와 ts는 각각 raw_data.user_session_channe, raw_data.session_timestamp을 줄여쓰기로 한 것이다.
  • LEFT JOIN
  • FULL OUTER JOIN
  • INNER JOIN: 가장 많이 씀!
  • RIGHT JOIN
    
    

SparkSQL

SparkSQL이란?

• SparkSQL은 구조화된 데이터 처리를 위한 Spark 모듈
• 대화형 Spark 셸이 제공됨
• 하둡 상의 데이터를 기반으로 작성된 Hive 쿼리의 경우 변경없이 최대 100배까지 빠른 성능을 가능하게 해줌 (100배는 좀 과장이고 경험상 한 5배정도..)
• 데이터 프레임을 SQL로 처리 가능
  • RDD 데이터는 결국 데이터 프레임으로 변환한 후에 처리 가능
  • 외부 데이터(스토리지나 관계형 데이터베이스)는 데이터프레임으로 변환한 후 가능
  • 데이터프레임은 테이블이 되고(특정 함수 사용)그 다음부터 sql 함수를 사용가능

SparkSQL 사용법 - 외부 데이터베이스 연결

• 외부 데이터베이스 기반으로 데이터 프레임 생성
  • Spark Session의 read 함수를 사용하여 테이블 혹은 SQL 결과를 데이터프레임으로 읽어옴
• Redshift 연결 예
  • SparkSession을 만들때 외부 데이터베이스에 맞는 JDBC jar을 지정
  • SparkSession의 read 함수를 호출
    • 로그인 관련 정보와 읽어오고자 하는 테이블 혹은 SQL을 지정
    • 결과가 데이터 프레임으로 리턴됨
  • 앞서 리턴된 데이터프레임에 테이블 이름 지정
    • 데이터 프레임을 기반으로 테이블 뷰 생성: 테이블이 만들어짐
  • SparkSession의 sql 함수를 사용
    • Spark Session의 sql함수로 SQL 결과를 데이터 프레임으로 받음
    • 데이터프레임의 경우 Spark 클러스터 위에 존재하고, 이를 받아오려면 collect를 통해 불러 로컬에서 파이썬으로 작업할 수 있다.

SQL로 할 수 있는 일과 판다스로 할 수 있는 일은 굉장히 흡사하다.
Redshift SQL로 했던 분석을 SparkSQL로 다시 해보면 SparkSQL로 할 수 있는 일과 Spark Dataframe으로 할 수 있는 일이 비슷함을 알 수 있다.

실습