[데브코스][데이터 분석] SQL 심화 (SQL과 데이터베이스)

< 5주차 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (1) >

 

1. SQL의 중요성

: 모든 데이터 직군에게 필요한 기술은 SQL

 

- 데이터 엔지니어

: 파이썬, 자바, 스칼라, SQL, 데이터베이스, ETL/ELT, Spark, Hadoop

 

- 데이터 분석가

: SQL, 비즈니스 도메인에 대한 지식, 통계(AB 테스트 분석)

 

- 데이터 과학자

: 머신러닝, SQL, 파이썬, 통계

 

==> 데이터 요약과 데이터 분석을 위한 SQL

: 백엔드/프론트엔드 개발자와는 SQL을 사용하는 목적이 다름

 

2. 배움이란?

배움에는 시간과 노력이 걸림

: 정체기는 자연스러운 과정임

 

1) 가장 중요한 것은 버티는 힘 : 어느정도는 즐겨야함. 

2) 내가 뭘 모르는지 생각해봐야함 : 내가 어디서 막혔는지 구체적으로 질문할 수 있나?

3) 잘 하는 사람 보고 기죽지 않기

 

새로운 것을 배울 때의 좋은 자세

- 자신이 아는 것과 모르는 것을 분명히 이해하는가?

: 멍청한 질문은 없다. 대충 알거나 모르면서 안 물어보는 것이 더 큰 문제

: 이는 피드백을 잘 받아들일 수 있는지와도 연계됨

 

- 마음을 편하게 먹기

: 내가 이해하기 힘들다면 남들도 이해하기 힘듦. 

: 나보다 잘 하는 사람들은 그만큼 시간을 쏟았기 때문

 

- 배움의 발전은 tipping point를 거치면서 폭발하는 형태임. 

: 발전이 더딘 기간을 즐기는 자세가 필요

 

3. 관계형 데이터베이스란?

: 구조화된 데이터를 저장하고 질의하는데 사용되는 스토리지(저장소)

(비구조화된 데이터는 관계형 DB에 저장할 수 없다 -> RDBMS의 강점과 약점)

 

: 엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장

(테이블에는 컬럼(열)과 레코드(행)이 존재)

 

: 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL

-> 테이블 정의를 위한 DDL(Data Definition Language) -> 테이블 스키마 정의

-> 테이블 데이터 조작/질의를 위한 DML(Data Manipulation Language) 

=> 빅데이터 세상에서도 SQL의 인기가 큼

 

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

- 프로덕션 데이터베이스 ( = OLTP)

: MySQL, PostgreSQL, Oracle, ...

* OLTP : Online Transaction Processing

- 빠른 응답 속도. 서비스에 필요한 정보 저장

-> 보통 웹이나 앱에 연동되어 사용되기 때문

==> 주로 백엔드/프론트엔드 개발자들이 많이 사용

 

* 보통 회사가 작으면 프로덕션 데이터베이스만 있음

-> 이러한 상황에서 데이터 일을 하는 사람들은 프로덕션 데이터베이스의 SQL을 사용해야함

-> 서비스에 연동되어 있는 DB이기 때문에 큰 쿼리를 날려 DB가 느려지면 전체 서비스의 실행 속도에 영향을 끼치게 됨

-> 백엔드 개발자들과의 불화 가능

 

- 데이터 웨어하우스 ( = OLAP)

: Redshift, Snowflake, BigQuery, Hive, ..

* OLAP : Online Analytical Processing

- 처리 데이터 크기에 집중. 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장 (빠른 속도는 필수적이지 않음)

-> 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장

==> 데이터 일을 하는 사람들은 보통 데이터 웨어하우스를 사용

 

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

: 관계형 데이터베이스는 2단계로 구성됨

- 가장 밑단에는 테이블들이 존재 (엑셀에서의 시트)

- 테이블은 데이터베이스(혹은 스키마) 라는 폴더 밑으로 구성 (엑셀에서의 파일)

 

테이블 = 스키마

- 테이블들은 레코드로 구성 (행)

- 레코드들은 하나 이상의 필드(컬럼)으로 구성 (열)

- 필드(컬럼)는 이름과 타입과 속성(primary key)으로 구성 

* primary key는 값이 유일해야 함.

 

4. SQL이란?

: 데이터 처리의 기본이 되는 SQL

SQL : Structured Query Language 

- SQL은 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어

- DDL : 테이블의 스키마 정의

- DML : 테이블에서 원하는 레코드들을 읽어오는 질의 언어. 테이블에 레코드를 추가/삭제/갱신하는데 사용

 

- 구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰임

-> 모든 대용량 데이터 웨어하우스는 SQL 기반

(Redshift, Snowflake, BigQuery, Hive, Spark, Hadoop)

 

SQL의 단점

: 구조화된 데이터를 다루는데 최적화가 되어있어, 비구조화된 데이터를 다루는데 제약이 심함(가능하기는 함)

: 많은 관계형 데이터베이스들이 플랫한 구조를 지원함 (no nested like JSON)

(JSON처럼 필드 안에 필드가 들어가는 형태가 안됨)

: 비구조화된 데이터를 다루는데 Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요해짐

즉, SQL 만으로 비구조화 데이터를 처리하지 못함

: 관계형 DB마다 SQL 문법이 조금씩 다름

 

데이터를 모델링하는 방법

1. Star schema

: Producion DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용해 데이터를 저장

데이터를 논리적 단위로 나눠 저장하고 필요시 조인 (여러가지 단위에 따라 별도의 테이블이 존재함)

-> 스토리지의 낭비가 덜하고 업데이트가 쉬움

-> 그러나, 다른 테이블과 관계가 엮여있어(JOIN) 특정 키로 매핑이 필요

-> 시간이 오래걸림

 

2. Denormalized schema

: 모든 데이터가 다 들어가 있음

"데이터 웨어하우스에서 사용하는 방식 -> 스토리지 크기에 제약이 없기 때문에"

-> 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요 없는 형태를 말함

--> 스토리지 용량을 더 사용하지만 조인이 필요없기에 빠른 계산이 가능

 

 

5. 데이터 웨어하우스란?

: 회사에 필요한 모든 데이터를 저장

: SQL 기반의 관계형 데이터베이스

: 프로덕션 DB와는 별도

: AWS의 Redshift, Google Cloud의 BigQuery, Snowflake 등이 대표적

: 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 데이터베이스

-> 처리 속도가 아닌 처리 데이터의 크기가 더 중요

: ETL 또는 데이터 파이프라인

-> 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 코드들이 필요해지는데

이를 ETL 혹은 데이터 파이프라인이라고 부름(외부 데이터를 추출하는 과정)

 

* Redshift -> 고정비용 옵션(얼마나 쓰든 같은 금액 부과)

Big Query -> 가변비용 옵션(내가 사용한 만큼 비용 부과)

 

데이터 인프라란?

: 데이터 엔지니어들이 관리하며, 데이터 웨어하우스에 데이터를 적재하는 프로세스를 의미

이를 한 단계 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 추가됨

 

데이터 순환 구조

1. 사이트 방문 트래픽과 외부 데이터 (이메일, 마케팅 등) 등의 데이터들 존재

2. 데이터 팀에서 ETL을 통해 데이터 웨어하우스에 적재 (데이터 엔지니어)

3. 저장된 데이터를 통합하여 시각화, 지표 정의 -> 비즈니스 인사이트 도출 (데이터 분석가)

4. 제품 서비스에 적용 및 개선 (데이터 과학자)

 

 

6. Cloud와 AWS 소개

클라우드란?

: 컴퓨팅 자원(하드웨어, 소프트웨어 등등)을 네트워크를 통해 서비스 형태로 사용

"No Provisioning", "Pay As You Go"

 

: 자원을 필요한 만큼 실시간으로 할당하여 사용한 만큼 지불

ex. 서버

-> 이미 설치되어있는 자원(SQL 등)을 별도의 세팅 없이 바로 사용 가능

 

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

: 서버/네트워크/스토리지 구매와 설정 등을 직접 수행해야 함

: 데이터센터 공간을 직접 확보 필요

: 그 공간에 서버를 구매하여 설치하고 네트워크 설정 필요

: 트래픽이 몰릴 때를 대비하여 공간을 설정해야 함

 

클라우드 컴퓨팅의 장점

: 초기 투자 비용이 크게 줄어듬

: 리소스 준비를 위한 대기시간 대폭 감소 -> 기회비용에 이점

: 노는 리소스 제거로 비용 감소 -> 트래픽이 몰릴 때 대비 필요

: 글로벌 확장 용이

: 소프트웨어 개발 시간 단축

 

AWS 소개

: 가장 큰 클라우드 컴퓨팅 서비스 업체

: 2002년 아마존의 상품데이터를 API로 제공하면서 시작

: 현재 100여개의 서비스를 전세계 15개의 지역에서 제공

-> Netflix, Zynga 등의 업체들이 사용

 

: 다양한 종류의 소프트웨어/플랫폼 서비스를 제공

-> AWS의 서비스만으로 쉽게 온라인 서비스 생성

 

EC2 - Elastice Compute Cloud

: AWS의 서버 호스팅 서비스

: 다양한 종류의 서버 타입 제공

 

<3가지 구매 옵션>

- On Demand : 시간당 비용 지불

- Reserved : 몇 년간의 사용 보장 및 할인

- Spot Instance : 일종의 경매방식으로 놀고 있는 리소스들을 싼 가격으로 사용

S3 - Simple Storage Service

: 아마존이 제공하는 대용량 클라우드 스토리지 서비스

: 데이터 저장관리를 위해 계층적 구조 제공

: 디렉토리를 버킷(Bucket)이라 부름. 버킷이나 파일별로 액세스 컨트롤 가능

1TB 한 달에 23달러

 

기타 중요 서비스 - Database Services

: RDS (MySQL, Oracle 등)

: DynamoDB

: Redshift

: MongoDB

: 등 

 

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

: SageMaker

-> 딥러닝 혹은 머신러닝을 통해 모델 생성 & 테스트 & API 형태로 만드는 것을 자동화하는 프레임워크

 

: Lex

-> 챗봇 서비스 제작 시 사용

 

: Polly

-> 텍스트를 음성으로 바꿔줌

 

: Rekognition

-> 이미지를 인식

 

기타 중요 서비스

: Amazon Alexa

-> 아마존의 보이스봇 플랫폼(다양한 API, 플러그인 지원)

 

: Amazon Connect

-> 콜센터 솔루션

 

: Lambda

-> 서비스 구현을 위해 EC2를 론칭할 필요 없음

-> Google Cloud의 Cloud Function과 동일

 

글로벌 클라우드 시장 점유율

 

 

7. Redshift 소개

: Scalable SQL 엔진

- 2PB까지 지원

 

- Still OLAP : 응답 속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용 불가

(데이터 웨어하우스)

 

- Columnar storage

: 컬럼별 압축이 가능

: 컬럼을 추가하거나 삭제하는 것이 아주 빠름

 

- 벌크 업데이트 지원

: 레코드가 들어있는 파일은 S3으로 복사 후 COPY 커맨드로 Redshift로 일괄 복사

 

- 고정 용량/비용 SQL 엔진

-> 고정 용량을 저장해두고 고정 비용을 냄 (사용을 안하더라도 비용이 나감)

 

- 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음

-> 이를 보장하면 레코드를 추가할 때마다 값이 유일하게 존재하는지 확인해야함 -> 속도가 느림

(프로덕션 DB들은 보장함)

 

- 여러가지 옵션이 있으며, 옵션에 따라 가격이 달라짐

 

Redshift는 Postgresql 8.X와 SQL이 호환됨

-> 그러나 Postgresql의 모든 기능을 지원하지는 않음

툴이나 라이브러리로 엑세스 가능 (JDBC/ODBC)

그러나, SQL이 메인 언어라는 점을 명시! => 테이블 디자인이 아주 중요!