[DEV] 6주차. 데이터 웨어하우스와 SQL과 데이터분석(1)

1. 데이터 분야에서 SQL

• 큰 데이터를 processing하여 작은 데이터로 변환
• 주어진 문제를 답하기 위해서 데이터 분석을 하기 위한 관점에서 사용
• 서버에서의 DB와는 목적이 조금 다름
  • 사용자들에 대한 정보, 상품에 대한 정보, 사용자의 행동에 대한 정보 등을 빠르게 저장하고 읽어서 서비스를 운영하기 위한 목적

2. 관계형 데이터베이스

• 구조화된 데이터를 저장하는 데 사용되는 스토리지
• 비구조화된 데이터는 저장할 수 없다는 의미
  • 강점이자 약점
  • 우리가 분석하는 데이터는 대부분 구조화된 데이터임 -> 이때는 가장 좋은 기술이 됨!
• 테이블로 데이터를 정의하고 저장
  • 테이블에는 컬럼(열)과 레코드(행)이 존재
• 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL
  • 테이블 정의를 위한 DDL (Data Definition Language)
  • 테이블 데이터 조작/질의를 위한 DML (Data Manipulation Language)

대표적인 RDB

• 프로덕션 데이터베이스: MySQL, PostgreSQL, Oracle, …
  • OLTP (OnLine Transaction Processing)
  • 빠른 속도에 집중
  • 서비스에 필요한 정보 저장
• 데이터 웨어하우스: Redshift, Snowflake, BigQuery, Hive, …
  • OLAP (OnLine Analytical Processing)
  • 처리 데이터 크기에 집중
    • 빠른 속도가 중요하지는 않음
    • 처리할 수 있는 데이터가 얼마나 크냐가 포인트!
  • 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장
    • 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장
• 프로덕션 데이터베이스만 있는 회사에 데이터 팀으로 조인할 경우
  • 필요한 데이터를 뽑으려면 프로덕션 데이터베이스의 SQL을 실행해야 함
  • 프로덕션 데이터베이스는 서비스에 연동이 되어있기 때문에 큰 쿼리를 날려서 DB가 느려지면 전체 서비스에 영향을 끼치게 됨 -> 백엔드와 마찰이 있을 것
  • 실수를 하게 될 수도 있음 or 계산이 매우 오래 걸리는 연산이 될 수 있음
  • 성장하는 스타트업에서 꼭 발생할 수 있는 일! -> 별도의 DB로 데이터 웨어하우스가 필요해짐
    • 서비스에는 영향이 가지 않음

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

• 가장 밑단에는 테이블들이 존재
• 테이블들은 데이터베이스 (혹은 스키마)라는 폴더 밑으로 구성
  • raw data 스키마와 raw data를 가공해서 생긴 부가적인 데이터를 저장하는 스키마 등으로 분류할 수 있음

테이블 구조

• 테이블은 레코드들로 구성 (행)
• 레코드는 하나 이상의 필드(컬럼)로 구성 (열)
• 필드는 이름과 타입과 속성(primary key)로 구성됨
• 테이블 스키마 예시

컬럼	타입	속성
userId	int	PK
sessionId	varchar(32)
channel	varchar(32)

4. SQL

• 관계형 데이터베이스에 있는 데이터를 질의하거나 조작해주는 언어

• DDL
  • 테이블의 구조를 정의하는 언어
• DML
  • 테이블에서 원하는 레코드들을 읽어오는 질의 언어
  • 테이블에 레코드를 추가/삭제/갱신해주는데 사용하는 언어

장점

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

단점

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

Star schema

• 프로덕션 데이터베이스용 관계형 데이터베이스에서는 보통 스타 스키마를 통해 데이터 저장
• 데이터를 논리적 단위로 나누어 저장하고 필요시 조인
• 스토리지 낭비가 덜하고 업데이트가 쉬움

Denormalized schema

• 데이터 웨어하우스에서 사용하는 방식
  • 데이터 웨어하우스는 스토리지 크기에 크게 상관이 없고, 데이터 웨어하우스에서 데이터를 업데이트 할 경우는 많지 않기 때문
  • 단위 테이블로 나눠 저장하지 않아 별도의 조인이 필요없는 형태
• 스토리지를 더 사용하지만, 조인이 필요없기 때문에 빠른 계산이 가능

5. 데이터 웨어하우스

• 회사에 필요한 모든 데이터들을 저장
• 여전히 SQL 기반의 관계형 데이터베이스
  • 프로덕션 DB와는 별도이어야 함
    • OLAP vs. OLTP
  • AWS의 Redshift, Google Cloud의 Big Query, Snowflake 등이 대표적
    • 고정비용 옵션 (Redshift) vs. 가변비용 옵션 (Big Query, Snowflake)
    • 고정비용 옵션은 시작하기엔 좋지만, 나중에 scalable issue가 발생할 수 있음 -> 회사에 조인하여 DW를 결정해야 한다면 Big Query, Snowflake를 더 추천하긴 함
• 데이터 웨어하우스는 고객이 아닌 내부 데이터 팀을 위한 데이터베이스
  • 처리 속도가 아닌 처리 데이터의 크기가 더 중요해짐
• ETL 혹은 데이터 파이프라인
  • 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 과정이 필요해짐 -> ETL / 데이터 파이프라인이라고 부름
  • ETL (Extrack Transform Load)
    • 외부에 존재하는 데이터 추출 -> 원하는 포맷으로 변환 -> 변환한 데이터를 DW의 테이블로 저장

6. 데이터 인프라

• 데이터 엔지니어가 관리함
• ETL & 데이터 웨어하우스
• 한 단계 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 인프라의 일부로 추가됨

데이터 순환 구조

[반복]

• 사이트 방문 트래픽과 외부 데이터
• ETL - DW: 데이터 통합 및 요약 테이블 생성
• 비즈니스 인사이트 도출
• 개인화 등을 통한 제품 서비스 개선

7. 클라우드와 AWS

클라우드

• 컴퓨팅 자원을 네트워크를 통해 서비스 형태로 사용하는 것
• No Provisioning
• Pay As You Go
• 자원을 필요한만큼 (거의) 실시간으로 할당하여 사용한만큼 지불
  • 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요
• 클라우드 컴퓨팅이 없었다면
  • 서버, 네트워크, 스토리지 구매와 설정 등을 직접 해야 함
  • 데이터센터 공간을 직접 확보해야 함 (Co-location)
    • 확장이 필요한 경우 공간을 먼저 더 확보해야 함
  • 그 공간에 서버를 구매하여 설치하고 네트워크 설정
    • 보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
  • 또한, Peak time을 기준으로 Capaticy planning을 해야 함
    • 놀고 있는 자원들이 많아지는 현상 발생
  • 직접 운영비용 vs. 클라우드 비용
    • 기회비용!
• 클라우드 컴퓨티의 장점
  • 초기 투자 비용이 크게 줄어듦
    • CAPEX (Capital Expenditure) vs. OPEX (Operating Expense): 클라우드
  • 리소스 준비를 위한 대기 시간 대폭 감소
    • Shorter Time to Market
  • 노는 리소스 제거로 비용 감소
  • 글로벌 확장 용이
  • 소프트웨어 개발 시간 단축
    • Managed Service (SaaS) 이용

8. AWS

EC2 - Elastic Compute Cloud

• AWS의 서버 호스팅 서비스
  • 리눅스 혹은 윈도우 서버를 론치하고 계정을 생성하여 로그인 가능 (구글 앱 엔진과의 가장 큰 차이)
  • 최근에는 mac os도 지원
• 다양한 종류의 서버 타입 지원
• 구매 옵션
  • On-Demand : 시간 당 비용 지불, 가장 흔히 사용
  • Reserved : 1년이나 3년 간 사용을 보장하고 3-40% 할인 받는 옵션
  • Spot Instance : 일종의 경매방식으로, 놀고있는 리소스들을 저렴한 비용으로 사용할 수 있는 옵션. 언제든지 서버가 다른 사람에게 넘어갈 수 있음

S3 - Simple Storage Service

• 아마존이 제공하는 대용량 클라우드 스토리지 서비스
• 데이터 저장 관리를 위해 계층적 구조 제공
• 디렉토리를 버킷이라고 부름
• 버킷이나 파일 별로 액세스 컨트롤 가능

기타 중요 서비스 - Database Service

• RDS (Relational Database Service)
  • MySQL, PostgreSQL, Aurora
  • Oracle, MS SQL Server
• Dynamo dB
• Redshift
• ElasticCache
• Neptune
• ElasticSearch
• MongoDB

기타 중요 서비스 - AL & ML Service

• SageMaker
  • 딥러닝, 머신러닝 모델 생성, 테스트, API 배포 자동화 (end-to-end framework)
• Lex
  • 대화형 인터페이스 (챗봇 서비스)
• Polly
  • Text to Speech 엔진
• Rekognition
  • 이미지 인식 서비스

기타 중요 서비스

• Amazon Alexa
  • voice bot 플랫폼
• Amazon Connect
  • Contact Center 솔루션
  • 콜센터 구현이 아주 쉬워짐
• Lambda
  • 서버리스 컴퓨팅 엔진
    • API 자체의 로직만 구현하면 됨
  • 서비스 구현을 위해서 EC2를 론치할 필요가 없음
  • 구글 클라우드에는 Cloud Funtion이라는 이름으로 존재
  • Azure에는 Azure Funtion이라는 이름으로 존재

9. Redshift

• Scalable SQL 엔진
• 2 PB까지 지원
• Still OLAP
  • 응답속도가 빠르지 않기 때문에 프로덕션 DB로 사용 불가
• Columnar storage
  • 컬럼별 압축 가능
  • 컬럼을 추가하거나 삭제하는 것이 아주 빠름
• 벌크 업데이트 지원
  • 레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift 일괄 복사
    • csv / JSON 등에 넣어서 한 번에 복사
  • INSERT 로 수행할 수 있는 레코드 수는 제한이 있음
• 고정 용량/비용 SQL 엔진
• 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음
  • 프로덕션 DB들은 보장함
  • 이를 보장하려면 레코드가 추가 될 때마다 유일성을 체크해야 함
  • 데이터 엔지니어가 따로 보장을 해주어야 함
• PostgreSQL 8.X와 SQL 호환이 됨
  • 모든 기능을 지원하는 것은 아님
    • ex) text 타입이 Redshift에는 존재하지 않음
  • PostgreSQL 8.X를 지원하는 툴이나 라이브러리로 액세스 가능
    • JDBC/ODBC
  • SQL이 메인 언어라는 점 명심 -> 테이블 디자인이 아주 중요!

Redshift Schema (폴더) 구성

CREATE SCHEMA raw_data;
CREATE SCHEMA analytics;
CREATE SCHEMA adhoc;

• admin 권한을 가진 사람만 수행할 수 있는 SQL
• raw_data : 데이터 엔지니어가 ETL을 통해 읽어온 데이터
• analytics : 데이터 분석가가 분석 및 대시보드 생성 등등
• adhoc : 개발자, 데이터 팀이 테스트를 할 때 사용할 공간

Redshift 액세스 방법

• 이번 강좌에서는 Google Colab 사용 예정
• PostgreSQL 8.X와 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능
  • SQL Workbench, Postico
  • psycopg2 모듈
  • Looker, Tableau 등 시각화 툴