[Big Data]Spark2
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
컬럼 타입 userId(ex: 779) int sessionId(ex: 923fdksljfldkjdjfdjfkdljf) varchar(32) channel(ex: Google) varchar(32) -
session_timestamp
|컬럼|타입| |—|—| |sessionId(ex: 923fdksljfldkjdjfdjfkdljf)|varchar(32)| |ts(ex: 2020-01-01 15:11:22)|timestamp|
-
SQL 소개
- SQL: Structured Query Language
- SQL은 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
- 주로 관계형 데이터베이스에 있는 데이터(테이블)를 질의하는 언어
- 두 종류의 언어로 구성됨
- DDL(Data Definition Language):
- 테이블의 구조를 정의하는 언어
- DML(Data Manipulation Language):
- 테이블에 레코드를 추가/삭제/갱신해주는데 사용하는 언어
- 테이블에 레코드를 추가/삭제/갱신해주는데 사용하는 언어
- DDL(Data Definition 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으로 할 수 있는 일이 비슷함을 알 수 있다.