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

💡Today I Learned

• 데이터 웨어하우스와 SQL, 데이터분석에 대한 첫 번째 수업을 진행했습니다.
• SQL, 관계형 데이터베이스, 데이터 웨어하우스, Cloud, AWS, Redshift에 대한 개념

 

---

 

1. SQL의 중요성

- 데이터 엔지니어(ETL/ELT 담당, Airflow, DBT, Spark, Hadoop, SQL)

- 데이터 분석가(통계, 비즈니스 도메인 지식, AB 테스트 분석, SQL)

- 데이터 사이언티스트(머신러닝, 통계, SQL)

→ 빅데이터를 처리해서 데이터 요약과 분석을 위한 목적으로 사용함

 

2. 관계형 데이터베이스

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

: 구조화된 데이터 = column(열) + record(행), 테이블로 데이터를 정의/저장

: SQL = DDL(테이블 스키마 정의) + DML(조작) + DCL(제어) 관계형 DB를 조작하는 프로그래밍 언어

 

- 프로덕션 데이터베이스

: MySQL, PostgreSQL, Oracle, ...

: OLTP(OnLine Transaction Processing) → 보통 웹 서비스, 모바일 앱 등과 연동돼 쓰임

: 빠른 응답속도에 집중(사용자의 대기 시간을 줄이도록), 서비스에 필요한 정보 저장

 

- 데이터 웨어하우스

: AWS의 Redshift, Snowflake, BigQuery, Hive, Spark, ...

: OLAP(OnLine Analytical Processing)

: 처리 데이터의 크기(처리 가능한 데이터가 큼)에 집중, 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장

: 보통 프로덕션 데이터베이스를 주기적으로 복사해서 데이터 웨어하우스에 저장 → 분석 등을 위해 프로덕션 DB에 바로 쿼리를 날리면 큰 부하가 걸릴 수도 잇음 

: 서비스에 사용되는 DB와 별개 → 서비스에 영향을 주지 않도록

 

3. 관계형 DB의 구조

- 관계형 DB의 2단계 (프로덕션 DB, 데이터 웨어하우스 모두)

1) 가장 밑 단에는 테이블들이 존재 (테이블 = 엑셀 시트)

2) 데이터베이스(혹은 스키마)라는 폴더 밑에 테이블을 구성 (폴더 이름을 주고 그 아래에서 테이블을 관리)

 

- 테이블의 구조 = 테이블 스키마

: 레코드 (행)

: 레코드는 하나 이상의 필드(열)으로 구성

: 각 필드는 이름+타입+속성(ex: primary key) 로 구성됨

 

4. SQL (Structured Query Language)

- DDL(Data Definition Language): 테이블의 구조를 정의하는 언어

- DML(Data Manipulation Language): 테이블에서 원하는 레코드들을 읽어오는 질의 언어, 레코드 추가/삭제/갱신

 

- 빅데이터와의 관계

: 모든 대용량 데이터 웨어하우스는 SQL 기반임 (Spark이나 Hadoop도 SparkSQL, Hive라는 SQL djsdjfmf wldnjsgka)

 

- 단점

: 구조화된 데이터를 다루는 데에 최적화 (비구조화 데이터 → 제약이 따름)

: 많은 관계형 DB들이 flat한 구조만 지원함 (JSON같이 nested structure 지원은어렵 → 구글 빅쿼리는 지원 o)

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

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

 

- 데이터 모델링

: 데이터를 표현/저장하는 방식

 

1) Star schema

: 보통 프로덕션 DB용 관계형 DB에서 보통 스타 스키마를 사용해 데이터를 저장

: 데이터를 논리적 단위로 테이블을 나눠 저장, 테이블 끼리 id의 형태로 링크를 검, 필요시 조인

: (+) 스토리지 낭비가 덜함, 업데이트가 쉬움

: (-) 디테일한 정보를 얻기 위해서 다른 테이블과의 조인이 필요함

 

2) Denormalized schema

: 데이터 웨어하우스에서 사용하는 방식

: 단위 테이블로 나눠 저장하지 않음 → 별도의 조인이 필요 없는 형태

: (+) 조인이 필요 없어 빠른 계산 가능 (프로덕션 DB를 복사해와서 사용하는 것만으로 충분, 특정 레코드를 수정하는 작업은 x)

: (-) 스토리지를 더 사용 (but, 데이터 웨어하우스는 스토리지 제약은 상관 x)

 

5. 데이터 웨어하우스 (OLAP)

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

: 여전히, SQL 기반의 관계형 DB임 (프로덕션 DB(OLTP)와는 별도)

: 고객이 아닌 내부 직원을 위한 DB →처리 속도 <<< 처리 데이터의 크기 (중요도)

: ETL 혹은 데이터 파이프라인 → 외부(ex: 프로덕션 DB)에 존재하는 데이터 읽어서 데이터 웨어하우스로 저장하는 프로세스

: ex) AWS의 Redshift, Google Cloud의 Big Query, Snowflake etc...

: Extract(외부에 존재하는 데이터 추출) + Transform(읽어온 데이터를 원하는 포맷으로 변경) + Load(데이터 웨어하우스 테이블로 저장)

: 데이터 웨어하우스, ETL(혹은 파이프라인) → 통틀어 데이터 인프라

: ETL = 누군가 작성한 코드

 

6. 데이터 인프라

: 데이터 엔지니어가 관리함

: 대용량 비구조화 데이터들을 프로세싱 하기 위해서는 Spark같은 대용량 분산처리 시스템이 일부로 추가됨

: 서비스에서 직접 생기는 데이터 + 써드파티를 통해 생기는 간접 데이터 → ETL → 데이터 웨어하우스 (데이터 인프라에 해당)

 

7. Cloud

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

: 지원을 필요할 때 필요한 만큼 (거의) 실시간으로 할당, 사용한만큼 지불(가변비용) → 탄력적으로 필요한만큼의 자원을 유지해야 함 (필요하지 않을 때 해제하고 반환하는 노력이 필요, 까다롭..)

: 클라우드 컴퓨팅이 없을 경우 서버/네트워크/스토리지 등 인프라 구매, 설정을 직접 수행 + 데이터센터 공간을 직접 확보 + peak time의 capacity planning이 필요 → 직접 운영비용&기회비용 vs 클라우드 비용

: (+) 초기 투자 비용이 줄어듦, 리소스 준비를 위한 대기시간 감소, 활용하지 않는 리소스 제거 → 비용 감소, 글로벌 확장 용이, sw 개발 시간 단축 (SaaS 이용)

 

8. AWS

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

: 다양한 종류의 SW/플랫폼 서비스를 제공 (SaaS), AWS의 서비스만으로 쉽게 온라인 서비스 생성 가능

 

- EC2 (Elastic Compute Cloud)

: AWS의 서버 호스팅 서비스

: 리눅스 or 윈도우 서버를 launch, account 생성해 로그인 가능

: 가상 서버들 → 전용 서버에 비해 성능이 떨어짐

: 구매 옵션 (On-Demand = 시간당 비용 지불, 가장 흔함 / Reserved = n년간 사용을 보장, 디스카운트 / Spot Instance = 경매 방식, 사용하지 않는 리소스들을 더 낮은 비용으로 사용)

 

- S3 (Simple Storage Service)

: AWS의 대용량 클라우드 스토리지 서비스

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

: S3에서는 디렉토리 = 버킷(Bucket)

: 버킷이나 파일별로 엑세스 컨트롤(AC) 가능 (permission control)

 

9. AI & ML 서비스

- SageMaker: 머신러닝 모델을 만들어 테스트, api 형태로 배포하는 과정을 자동화하는 end-to-end framework

- Lex: 챗봇 서비스

- Polly: TTS 엔진

- Rekognition: 이미지 인식 서비스

- Amazon Alexa: 보이스 봇 플랫폼

- Amazon Connect: 콜센터 솔루션

- Lambda: Event-driven, 서버리스 컴퓨팅 엔진, 서비스 구현을 위해 EC2를 launch할 필요가 없음 (like 구글 클라우드의 cloud function, MS Azure의 Azure Function..)

*) 보통 EC2로 서버 론치, 그 안에서 코딩해서 api 구동시킬 수 있는 프레임워크을 만드는 일반적인 과정 .. → 필요 없어짐

*) api 로직만 구현하면 나머지는 Lambda에서 처리 (기반이 되는 프레임워크 론치, 트래픽에 맞춰 서버 증감 관리 ...)

 

10. Redshift

: 사이즈는 2PB까지 지원

: OLAP → 응답속도가 빠르지 않기 때문에 프로덕션 DB로는 사용불가

: Columnar storage (레코드가 아닌 컬럼별로 저장 → 컬럼별 압축 가능, 컬럼 추가/삭제가 빠름)

 

- Scalable SQL 엔진

: 벌크 업데이트 지원 (레코드가 들어있는 파일을 S3로 복사 후 COPY(sql command)를 이용해 Redshift로 일괄 복사)

: 고정 용량/비용 SQL 엔진 (쓴만큼 가변 용량/비용 → Snowflake vs BigQuery)

: 다른 데이터 웨어하우스들과 마찬가지로 primary key uniqueness를 보장하지 않음 (레코드가 추가될 때마다 기본키가 unique한지 체크해야 함 → 검사에 상당 시간 소요..)

*) 프로덕션 DB들은 당연히 보장함

 

: PostgreSql 일부와 호환됨 → PostgreSql을 지원하는 툴, 라이브러리로 엑세스 가능 (JDBC/ODBC)

: 그럼에도 SQL이 메인 언어임 !!! → 테이블 디자인이 아주 중요

 

: Redshift Schema(폴더) 구성 → 해당되는 폴더(=스키마) 아래에 테이블들을 분류함, SQL command로 스키마 구성

 

---

💡Furthermore

• SQL 퀴즈 풀이