[TIL - 21일 차] 데이터 웨어하우스와 SQL과 데이터 분석 (1)

SQL과 데이터베이스 소개

SQL의 중요성

데이터 요약과 데이터 분석을 위해 SQL은 모든 데이터 직군에게 필수적으로 요구되는 역량이다.

데이터 관련 3개의 직군

• 데이터 엔지니어 : 파이썬, 자바/스칼라, SQL, 데이터베이스, ETL/ELT(Ariflow, DBT), Spark, Hadoop
• 데이터 분석가 : SQL, 비즈니스 도메인 지식, 통계(AB 테스트 분석)
• 데이터 과학자 : 머신러닝, SQL, 파이썬, 통계

관계형 데이터베이스란?

구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지이다. 엑셀 스프레드시트 형태의 테이블(행/열)로 데이터를 정의하고 저장한다. 이러한 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL이며, 언어 형태는 다음과 같다.

• 테이블 정의를 위한 DDL(Data Definition Languate) : 테이블의 스키마를 정의
• 테이블 조작/질의를 위한 DML(Data Manipulation Language) : 조건에 맞는 레코드 추출 및 레코드 추가/삭제 등

대표적인 관계형 데이터베이스

프로덕션 데이터베이스는 빠른 속도에 집중하며, 서비스에 필요한 정보를 저장한다. 데이터 웨어하우스는 처리 데이터 크기에 집중하며, 데이터 분석 혹은 모델 빌딩 등을 위해 데이터를 저장한다. 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장한다.

• 프로덕션 데이터베이스(OLTP) : MySQL, PostgreSQL, Oracle, ...
• 데이터 웨어하우스(OLAP) : Redshift, Snowflake, BigQuery, Hive, ... 

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

관계형 데이터베이스는 2단계로 구성되어 있다. 가장 밑단에는 테이블들이 존재하며, 테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성된다.

• raw_data : 외부에서 읽어온 데이터, 보통 프로덕션 DB에 위치
• analytics : raw_data를 가공한 부가적인 정보

데이터베이스	raw_data	analytics	PII
테이블	patient	parientSummary	...
	patientData	vitalSummary
	alert	alertSummary
	...	...

테이블의 구조(테이블 스키마)

테이블은 레코드(행)들로 구성되어 있고, 레코드는 하나 이상의 필드(컬럼)로 구성되어 있다. 필드는 이름, 타입, 속성(pk)으로 구성된다.

(예시)	컬럼	타입
테이블 스키마	userId	int(pk)
	sessionId	varchar(32)
	channel	varchar(32)

(예시)	userId	sessionId	channel
테이블 레코드	779	7cda...	Instagram
	230	94f1...	Naver
	369	7ed2...	Youtube

SQL이란?

SQL(Structured Query Languate)는 관계형 데이터베이스에 있는 데이터(테이블)를 질의하거나 조작해 주는 언어이다. 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어이며, DDL/DML로 구성된다.

빅데이터 세상에서의 SQL

구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰인다. 모든 대용량 데이터 웨어하우스는 SQL 기반이며, Spark나 Hadoop도 예외는 아니다. 따라서 데이터 분야에서 일하고자 한다면 반드시 익혀야 할 기본적인 기술이다.

SQL의 단점

• 정규표현식을 통해 비구조화된 데이터를 어느 정도 다루는 것은 가능하나 제약이 심함
• 많은 관계형 데이터베이스들이 플랫 한 구조만 지원(no nested like JSON)
• 비구조화된 데이터를 다루는데 Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요해짐
• 관계형 데이터베이스마다 SQL 문법이 조금씩 상이

데이터 모델링

Star schema

프로덕션 DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용해 데이터를 저장한다. 데이터를 논리적 단위로 나눠 저장하고 필요시 조인하는 방식으로 스토리지의 낭비가 덜하고 업데이트가 쉽다.

스타 스키마

Denormalized Schema

데이터 웨어하우스에서 사용하는 방식이다. 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요하지 않다. 이 방식은 스토리지를 더 사용하지만, 조인이 필요 없기 때문에 빠른 계산이 가능하다.

년 월 일

매장명

지역명

직원명

성별

매출단가

...

데이터 웨어하우스란?

회사에 필요한 모든 데이터를 저장한 것을 말한다. 프로덕션 DB와 달리 처리속도가 아닌 처리 데이터 크기가 더 중요하다. SQL 기반의 관계형 데이터베이스이며, 프로덕션 데이터베이스와는 별도이어야 한다. 대표적으로 AWS의 Redshift, Google Cloud의 Big Query, Snowflake가 있으며, 고객이 아닌 내부 직원을 위한 데이터베이스이다.

• 데이터 파이프라인(ETL) : 외부의 데이터를 읽어 데이터 웨어하우스로 저장해 주는 코드

데이터 인프라

데이터 인프라란 ETL, 데이터 웨어하우스를 포함한 구조를 말한다. 여기서 데이터가 많아지고, 비구조화 데이터가 많아지면 Spark와 같은 대용량 분산처리 시스템이 일부로 추가된다. 

• 데이터 순환 구조 : 외부 데이터 -> 데이터 인프라 -> 비즈니스 인사이트(분석) / 서비스 개선

Cloud

클라우드

클라우드란 컴퓨팅 자원(하드웨어, 소프트웨어 등)을 네트워크를 통해 서비스 형태로 사용하는 것이다. 자원을 필요한 만큼 실시간으로 할당하여 사용한 만큼 지불하는 형태이다. 탄력적으로 필요한 만큼의 자원을 유지하는 것이 중요하다.

클라우드 컴퓨팅이 없었다면?

• 서버/네트워크/스토리지 구매와 설정 등을 직접 수행
• Peek time을 기준으로 Capacity planning를 수행하여 활용되지 않는 자원이 높아짐

클라우드 컴퓨팅의 장점

• 초기 투자 비용이 크게 줄어듦
• 리소스 준비를 위한 대기시간 대폭 감소
• 활용되지 않는 리소스 제거로 비용 감소
• 글로벌 확장 용이
• 소프트웨어 개발 시간 단축

AWS

2002년 아마존의 상품데이터를 API로 제공하면서 시작된 가장 큰 클라우드 컴퓨팅 서비스 업체이다. 현재 100여 개의 서비스를 전 세계 15개 지역에서 제공하며, 최근 ML/AI 관련 서비스도 나오고 있다. Netflix, Zynga 등의 상장업체도 사용하며, 많은 국내 업체들도 사용하고 있다.

EC2 - Elastic Compute Cloud

AWS의 서버 호스팅 서비스로 다양한 종류의 서버 타입을 제공한다. 리눅스 혹은 윈도우 서버를 론치하고 계정을 생성하여 로그인이 가능하다. 그러나 가상 서버이므로 전용서버에 비해 성능이 떨어진다.

구매 옵션	설명
On-Demand	시간당 비용을 지불되며, 가장 흔히 사용하는 옵션
Reversed	1년이나 3년간 사용을 보장하고 1/3정도에서 40% 할인받는 옵션
Spot Instance	일종의 경쟁방식으로 활용되지 않는 리소스를 보다 싼 비용으로 사용할 수 있는 옵션

S3 - Simple Storage Service

AWS의 대용량 클라우드 스토리지 서비스이다. 데이터 저장관리를 위해 계층적 구조를 제공한다.

기타 서비스 - DB

• RDS (Relational Database Service) : MySQL, PostgreSQL, Aurora, Oracle, MS SQL Server
• DynamoDB
• Redshift
• ...

기타 서비스 - AI & ML

• SageMaker : 머신러닝, 딥러닝으로 모델 생성 및 학습을 통해 API로 배포해 주는 서비스
• Lex : 챗봇 서비스
• Polly : 텍스트를 음성으로 바꿔주는 서비스
• Rekognition : 이미지 인식 서비스

기타 서비스

• Amazon Alexa : 보이스 봇 플랫폼
• Amazon Connect : 콜센터 솔루션
• Lambda : Serverless computing

Redshift 소개

SQL 기반의 데이터베이스로 2PB까지 지원한다. 응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스가 아닌 데이터 웨어하우스에 사용된다. 레코드가 아닌 컬럼별로 저장이 되므로 컬럼을 추가하거나 삭제하는 것이 매우 빠르다.

• 벌크 업데이트 지원 : INSERT로 레코드를 삽입하는 것보다 csv, json 파일을 S3에 저장 및 COPY 커맨드로 Redshift로 일괄 복사하는 것이 효율적
• 고정 용량/비용 SQL 엔진 : 사용하지 않더라도 고정된 비용이 지불
• Primary Key Uniqueness 보장 X : 데이터 웨어하우스가 아닌 개발자가 보장해야 함

Postgresql 8.x와 SQL 호환

Postgresql 8.x의 모든 기능을 지원하지는 않지만, 지원하는 툴이나 라이브러리로 액세스가 가능하다. 그러나 SQL이 메인 언어이므로 테이블 디자인이 중요하다는 것을 명심해야 한다.

참고링크

IT위키 : 스타 스키마

https://itwiki.kr/w/%EC%8A%A4%ED%83%80_%EC%8A%A4%ED%82%A4%EB%A7%88