[데브코스 TIL] DAY22 데이터 웨어하우스와 SQL 기본(1)

오늘의 학습 주제

---

1. 관계형 데이터베이스
2. SQL
3. 데이터 웨어하우스
4. Cloud와 AWS 소개
5. Redshift 소개

 

1. 관계형 데이터베이스

• 구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지
  • 엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장
    • 테이블에는 컬럼(열)과 레코드(행)이 존재
• 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL
  • 테이블 정의를 위한 DDL
  • 테이블 데이터 조작/질의를 위한 DML

• 대표적 관계형 데이터베이스
  • 프로덕션 데이터베이스 : MySQL, PostgreSQL, Oracle, ... (주로 백엔드 관련)
    • OLTP(OnLine Transaction Processing)
    • 빠른 속도에 집중. 서비스에 필요한 정보 저장
  • 데이터 웨어하우스 : Redshift, Snowfloke, BigQuery, Hive, Spark ... (주로 데이터 직무 관련)
    - OLAP(OnLine Analytical Processing)
    - 처리 데이터 크기에 집중. 데이터 분석 혹은 모델 빌딩등을 위한 데이터베이스
    - 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장

    만약 회사에 프로덕션 데이터베이스 밖에 없는 경우, 데이터 처리를 위해 큰 쿼리를 날려야 하는데, 데이터베이스가 서비스와 연결되어 있기 때문에 서비스 성능에 영향을 미치게 되는 문제가 있다. 그렇기 때문에 별도의 데이터 웨어하우스를 갖춰야 한다.

• 관계형 데이터베이스의 구조
  • 관계형 데이터베이스는 2단계로 구성됨
    • 가장 밑단에는 테이블들의 존재 (테이블은 엑셀의 시트에 해당)
    • 테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성(엑셀에서는 파일)

• 테이블의 구조(테이블 스키마라고 부르기도 함)
  • 테이블은 레코드들로 구성 (행)
    - 레코드는 하나 이상의 필드(컬럼)로 구성 (열)
    - 필드(컬럼)는 이름과 타입과 속성(primary key)로 구성됨

    primary key의 값은 유일해야 한다.

 

2. SQL

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

• SQL은 빅데이터 세상에서도 중요하다.
  • 구조화된 데이터를 다루는한 SQL은 데이터 규모와 상관없이 쓰임
  • 모든 대용량 데이터 웨어하우스는 SQL 기반
  • Spark나 Hadoop도 예외는 아님
    • SparkSQL과 Hive라는 SQL 언어가 지원됨

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

• Star schema
  • Production DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용해 데이터를 저장
  • 데이터를 논리적 단위로 나눠 저장하고 필요시 조인. 스토리지의 낭비가 덜하고 업데이트가 쉬움
• Denomarlized schema
  • 데이터 웨어하우스에서 사용하는 방식
    • 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요 없는 형태를 말함
  • 이는 스토리지를 더 사용하지만 조인이 필요없기에 빠른 계산이 가능

 

3. 데이터 웨어하우스

• 데이터 웨어하우스 : 회사에 필요한 모든 데이터를 저장
  • 여전히 SQL 기반의 관계형 데이터베이스
    • 프로덕션 데이터베이스와는 별도여야 함
      • OLAP vs. OLTP
    • AWS의 Redshift, Google Cloud의 Big Query, Snowflake 등이 대표적
      • 고정비용 옵션 vs. 가변비용 옵션
  • 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 데이터베이스
    • 처리속도 아닌 처리 데이터의 크기가 더 중요해짐
  • ETL 혹은 데이터 파이프라인
    • 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 프로세스가 필요해지는데 이를 ETL 혹은 데이터 파이프라인이라고 부름

• 데이터 인프라란?
• 데이터 엔지니어가 관리함
  • 여기서 한 단계 더 발전하면 Spark과 같은 대용량 분산처리 시스템이 일부로 추가됨.

• 데이터 순환 구조
  사이트 방문 트래픽과 외부 데이터 > 데이터 팀 > 비즈니스 인사이트 > (개인화등을 통한) 제품 서비스 개선 > 사이트 방문 트래픽과 외부 데이터

• 클라우드의 정의
  • 컴퓨팅 자원을 네트워크를 통해 서비스 형태로사용하는 것
• 키워드
  • No Provisioning
  • Pay As You Go
• 자원(예를 들면 서버)을 필요한만큼 (거의) 실시간으로 할당하여 사용한만큼 지불
  • 탄력적으로 필요한만큼의 자원을 유지하는 것이 필요

• 클라우드 컴퓨팅이 없다면?
  • 서버/네트워크/스토리지 구매와 설정 등을 직접 수행해야 함
  • 데이터센터 공간을 직접 확보 (Co-location)
    • 확장이 필요한 경우 공간을 먼저 더 확보해야함
  • 그 공간에 서버를 구매하여 설치하고 네트워크 설정
    • 보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
  • 또한 Peak time을 기준으로 Capacity planning을 해야함
    • 놀고 있는 자원들이 높게 되는 현상 발생
  • 직접 운영비용 vs. 클라우드 비용
    • 기회비용

• 클라우드 컴퓨팅의 장점
• 초기 투자 비용이 크게 줄어듬
  • CAPEX(Capital Expenditure) vs. OPEX(Operating Expense)
• 리소스 준비를 위한 대기시간 대폭 감소
  • Shorter Time to Market
• 노는 리소스 제거로 비용 감소
• 글로벌 확장 용이
• 소프트웨어 개발 시간 단축
  • Managed Service(Saas) 이용

• AWS 소개
  • 가장 큰 클라우드 컴퓨팅 서비스 업체
  • 2002년 아마존의 상품데이터를 API로 제공하면서 시작
    • 현재 100여개의 서비스를 전세계 15개 지역에서 제공
    • 대부분의 서비스들이 오픈소스 프로젝트들을 기반으로 함
    • 최근 들어 ML/AI 관련 서비스들도 내놓기 시작
  • 다양한 종류의 소프트웨어/플랫폼 서비스를 제공

• EC2 : Elastic Compute Cloude
  • AWS의 서버 호스팅 서비스
    • 리눅스 혹은 윈도우 서버를 론치하고 어카운트를 생성하여 로그인 가능 (구글앱엔진과의 가장 큰 차이점)
  • 가상 서버들이라 전용서버에 비해 성능이 떨어짐
  • Bare-metal 서버도 제공하기 시작
  • 다양한 종류의 서버 타입 제공
    • http://aws.amazon.com/ec2/
  • Incoming network bandwidth는 공짜이지만 outgoing은 유료
  • 세 가지 종류의 구매 옵션
    • On-Demand : 시간당 비용을 지불하며 가장 흔히 사용하는 옵션
    • Reserved : 1년이나 3년간 사용을 보장하고 1/3 정도에서 40% 디스카운트 받는 옵션
    • Spot Instance : 일종의 경매방식으로 놀고 있는 리소스들을 합리적인 비용으로 사용할 수 있는 옵션

• S3 : Simple Storage Service
  • http://aws.amazon.com/s3/
  • 아마존이 제공하는 대용량 클라우드 스토리지 서비스
  • S3는 데이터 저장관리를 위해 계층적 구조를 제공
  • 글로벌 내임스페이스를 제공하기 때문에 톱레벨 디렉토리 이름 선정에 주의
  • S3에서는 디렉토리를 버킷(Bucket)이라고 부름
  • 버킷이나 파일별로 액세스 컨트롤 가능

• 기타 중요 서비스 : Database Services
  • RDS
  • DynamoDB
  • Redshift
  • ElasticCache
  • Nepturn(Graph database)
  • ElasticSearch
  • MongoDB
• 기타 중요 서비스 : AL & ML Services
  • SagaMaker
  • Lex
  • Polly
  • Rekognition
• 기타중요 서비스
• Amazon Alexa
• Amazon Connect
• Lambda
  • 서비스 구현을 위해서 EC2를 론치할 필요가 없음
  • Google Cloud에는 Cloud Function이란 이름으로 존재
  • Azure에는 Azure Function이란 이름으로 존재

• Redshift 소개
  • Scalable SQL 엔진
    • 2 PB까지 지원
    • Still OLAP
      • 응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용불가
    • Columnar storage
      • 컬럼별 압축이 가능
      • 컬럼을 추가하거나 삭제하는 것이 아주 빠름
  • 벌크 업데이트 지원
    • 레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift로 일괄 복사
    • Insert는 시간이 너무 오래걸리기 때문
  • 고정 용량/비용 SQL 엔진
    • vs. Snoflake vs. BigQuery
  • 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음
    • 프로덕션 데이터베이스들은 보장함
    • 데이터 엔지니어가 다른 operation을 통해 primary key가 unique함을 보장해줘야한다.
  • Redshift는 Postresql 8.x와 SQL이 호환됨
    • 하지만 Postresql 8.x의 모든 기능을 지원하지는 않음
      • 예를 들어 text타입이 존재하지 않음
    • Postresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능
      • JDBC/ODBC
    • 다시 한번 SQL이 메인 언어라는 점 명심
      • 그러기에 테이블 디자인이 아주 중요
    • Redshift Scehm (폴더) 구성
      - DEV : raw_data(외부에서 읽어 온 데이터를 그대로 저장), analytics(분석이나 대시보드 개발에 사용하기 편하게, 보통 데이터분석가가 관리), adhoc(테스트 등을 위해, 관리는 거의 필요음)
      • CREATE SCHEMA raw_data;