[5주차] SQL을 이용한 데이터 분석(1)

🙂 SQL의 중요성

✔ 데이터 관련 3개의 직군

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

---

🙂 관계형 데이터베이스

✔ 구조화된 데이터를 저장하고 질의하는 스토리지

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

테이블에는 컬럼과 레코드가 존재

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

테이블 정의를 위한 DDL (Data Definition Language)

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

✔ 프로덕션 데이터베이스

MySQL, PostgreSQL, Oralce, ...

OLTP(OnLine Transaction Processing): 빠른 속도에 집중. 서비스에 필요한 정보 저장

Backend, Frontend 개발자 들이 사용.

✔ 데이터 웨어하우스 (DW)

Redshift, Snowflake, BigQuery, Hive, Spark, ...

OLAP(OnLine Analytical Processing): 처리 데이터 크기에 집중. 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장

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

 

🍿 회사가 작은 경우 프로덕션 데이터베이스만 존재

원하는 데이터를 뽑을 때 쿼리를 통해서만 접근.

부하가 걸리거나 실수가 생기면 개발자들과 불화가 생길 수 있음.

 

그래서 별도의 DB가 필요한 데 그 것이 DW

DW를 가지고 있는 회사가 데이터적으로 진보되었다고 볼 수 있음.

 

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

가장 밑단에 테이블이 존재 (엑셀의 Sheet)

테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성 (엑셀의 File)

 

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

• 테이블은 레코드로 구성 (행)
• 레코드는 하나 이상의 필드(칼럼)로 구성 (열)
• 필드(칼럼)는 이름과 타입과 속성(primary key)으로 구성
• Primary Key로 지정된 칼럼은 그 값이 테이블 내에서 유일.

---

🙂 SQL

✔ SQL: Structured Query Language

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

DDL (Data Definition Language): 테이블 구조 정의

DML (Data Manipulation Language): 테이블에서 원하는 레코드를 CRUD

 

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

Redshift, Snowflake, BigQuery, Hive, Spark, Hadoop

 

✔ 단점

• 구조화된 데이터를 다루는 데만 최적화
  • 정규표현식을 통해 비구조화된 데이터를 다루는 것이 가능하지만 제약적
  • 많은 관계형 DB가 플랫한 구조만 지원
  • 비구조화된 데이터를 다루는 데 Spark, Hadoop과 같은 분산 컴퓨터 환경이 필요
  • SQL만으로는 비구조화 데이터 처리 불가
• 관계형 DB마다 SQL 문법이 조금씩 상이

---

🙂 데이터 모델링

✔ Star Schema

Production DB용 관계형 DB에서 사용.

데이터를 논리적 단위로 나누어서 저장하고 필요시 조인.

스토리지 낭비가 덜하고 업데이트가 용이.

다수의 테이블을 특정 키를 이용해 조인이 필요하기 때문에 느림.

✔ Denormalized Schema

데이터 웨어하우스에서 사용.

단위 테이블로 나누어서 저장하는 것이 아니라 별도의 조인이 필요하지 않음.

스토리지를 더 사용하지만 조인이 필요 없기 때문에 빠름.

보통 Production DB에서 복사해서 만들기 때문에 업데이트가 자주 일어나지 않음.

---

🙂 데이터 웨어하우스

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

• SQL 기반의 관계형 DB
  • 프로덕션 데이터베이스와는 별도
  • OLAP vs. OLTP
  • 고정비용 vs. 가변비용
• DW는 고객이 아닌 내부 직원을 위한 데이터베이스: 처리 속도 < 데이터 크기
• 외부에 존재하는 데이터를 읽어서 DW로 저장하는 것이 필요: ETL or 데이터 파이프라인

✔ 데이터 인프라

• 데이터 엔지니어가 관리
• 한 번 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 일부 요소로 추가

---

🙂 Cloud

✔ Cloud

컴퓨팅 자원(HW, SW 등)을 네트워크를 통해 서비스 형태로 사용하는 것

• No Provisioning: 사용자가 준비할 필요가 없음.
• Pay As You Go: 쓴 만큼 돈을 지불.

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

탄력적으로 필요한 만큼의 자원을 유지하는 것이 중요.

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

• 서버 / NW / Storage 구매와 설정 등을 직접 수행
• 데이터센터 공간 직접 확보
• Peak time 기준으로 Capacity Planning
• 직접 운용비용 vs. 클라우드 비용

✔ 클라우드 컴퓨팅의 장점

• 초기 투자 비용 감소: CAPEX (Capital Expenditures) vs. OPEX (Operating Expenditures)
• 리소스 준비를 위한 대기시간 감소
• 글로벌 확장 용이
• SW 개발 시간 단축: SaaS 이용

---

🙂 AWS

• 세계에서 가장 큰 클라우드 컴퓨팅 서비스 업체
• 2002년 아마존의 상품데이터를 API로 제공하면서 시작.
  • 100여개의 서비스를 전세계 15개의 지역에서 제공
  • 대부분 오픈소스 프로젝트 기반
• 많은 국내 업체도 사용 (Seoul Region)
• 다양한 종류의 SW/플랫폼 서비스 제공.

✔ EC2 - Elastic Compute Cloud

AWS의 서버 호스팅 서비스

 

구매옵션

On-Demand: 시간당 비용 지불

Reserved: 1년이나 3년간 사용을 보장하고 1/3 정도에서 40% 할인

Spot Instance: 일종의 경매방식

 

✔ S3 - Simple Storage Service

AWS의 클라우드 스토리지 서비스

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

Bucket: S3에서 디렉토리를 지칭

 

✔ 기타 서비스 - Database Services

• RDS(Relational Database Service)
• DynamoDB
• Redshift
• ElasticCache
• Neptune(Graph database)
• ElasticSearch
• MongoDB

✔ 기타 서비스 - AI & ML Services

• SageMaker: Deep Learning and Maching Learning end-to-end framework
• Lex: Conversational Interface (Chatpot)
• Polly: Text to Speech Engine
• Rekognition: Image Recognition Service

✔ 기타 중요 서비스

• Amazon Alexa
• Amazon Connect
• Lambda
  • Event-driven, serverless computing engine
  • 서비스 구현을 위해서 EC2를 런치할 필요가 없음

---

🙂 Redshift

✔ Scalable SQL 엔진

• 2 PB까지 지원.
• 응답속도가 빠르지 않기 때문에 프로덕션 DB로 사용불가
• 칼럼별 압축이 가능
  • 칼럼을 추가하거나 삭제하는 것이 아주 빠름.
• 벌크 업데이트 지원
  • 레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift 일괄 복사
• 고정 용량/비용 SQL 엔진
  • vs. Snowflake vs. BigQuery
• 다른 DW처럼 primary key uniqueness 보장 X
  • 프로덕션 데이터베이스들은 이를 보장함.

✔ Postgresql 8.x와 SQL 호환

• 모든 기능 지원하지 않음. text타입은 없음
• Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스: JDBC / ODBC
• SQL이 메인 언어이기 때문에 테이블 디자인이 아주 중요.