[Week6 데이터 웨어하우스와 SQL 기본] TIL 21일차 - 관계형 데이터베이스와 SQL

0) 데이터 그룹원의 역할

1. 데이터 엔지니어

데이터웨어하우스와 데이터를 적재하는 ETL/ELT 등의 프로세스를 담당

 

필요한 스킬

• 프로그래밍 언어(python, java)
• ETL/ELT
• 분산환경 Spark, Hadoop
• 데이터베이스

 

2. 데이터 분석가 : DB에서 데이터를 읽어서 데이터 분석을 수행(통계적 지식)

3. 데이터 과학자 : 효과적인 데이터모델을 찾고, 개발하고, 이를 활용하여 예측을 수행

 

 

1) 관계형 데이터베이스와 SQL

관계형 데이터베이스

구조화된 데이터를 저장하고 질의할 수 있도록 하는 저장 장치

 

1. 프로덕션 데이터 베이스

OLTP(Online Transaction Processing)

실시간 트랜잭션 처리 시스템으로 간단하고 반복적인 트랜잭션을 신속하게 처리하는데 특화되어 있다.

(웹/모바일) 서비스에 필요한 정보를 저장하므로 빠른 속도가 중요하다.

ex) Mysql, PostgreSQL, Oracle

 

2. 데이터 웨어하우스

OLAP(Online Analytical Processing)

의사결정 지원 및 데이터 분석을 위한 데이터 처리 시스템으로 별도의 데이터 웨어하우스에 저장된 데이터를 사용한다.

처리하는 데이터의 크기가 큰 편이므로 상대적으로 OLTP보다 속도가 느리다. 보통은 OLTP를 복사해서 DW에 저장한다. ex) Redshift, Snowflake, BigQuery, Hive

 

P : 프로덕션 데이터베이스를 데이터 웨어하우스로 사용하는 경우, 대용량 쿼리를 사용하는 경우 백엔드팀과 마찰이 생길 수 있다.

데이터팀에서 사용하는 데이터 웨어하우스가 무엇인지에 대해 질의하면 데이터팀의 규모와 비중을 알 수 있음!

 

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

관계형 데이터베이스는 보통 데이터베이스(스키마) 하위에 테이블을 넣어 필요에 따라 사용한다.

📂데이터베이스(스키마)

    📄테이블 1 

    📄테이블2

 

 

SQL

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

 

SQL 언어의 종류

• 데이터 정의어 DDL  : CREATE, ALTER, RENAME, DROP
• 데이터 조작어 DML  : INSERT, SELECT, UPDATE, DELETE
• 데이터 제어어 DCL  : GRANT, REVOKE
• 트랜잭션 제어어 TCL  : COMMIT, ROLLBACK

 

분산 컴퓨팅 환경의 필요성

구조화된 데이터를 다루는데 최적화가 되어 있다 보니, 비 구조화된 데이터를 다루는 데는 제약이 있어 Spark나 Hadoop과 같은 분산 컴퓨팅 환경이 필요하게 되었다.

실제로 많은 관계형 데이터베이스들이 플랫한 구조의 구조화된 데이터만을 지원한다.

각 데이터베이스마다 SQL 문법이 다르다.

 

 

데이터 모델링

데이터 모델링은 현실 세계의 복잡한 구조와 프로세스를 추상화하여 데이터베이스 시스템에 적용하기 위한 방법을 정의하는 단계로 크게 Star schema와 Denormalized schema가 자주 사용된다.

 

1. Star schema

프로덕션 관계형 데이터베이스에서 많이 사용하는 구조이다.

데이터를 논리적 단위로 나눠 저장하고, 필요시 조인한다.

장점) 나눠서 저장하기 때문에 스토리지 낭비가 덜하고 업데이트가 쉽다.

단점) 다수의 테이블을 매핑해서 조인연산을 수행해야 한다. (시간+공간)

 

2. Denormalized schema

데이터 웨어하우스에서 사용하는 방식으로, 단위 테이블에 나눠 저장하지 않고 하나의 테이블에 모든 데이터를 표시하는 구조이다.

단점) 여러 데이터가 중복되기 때문에 스토리지를 낭비한다.

특정 컬럼의 데이터가 바뀌면(ex 매장명) 다수의 데이터를 바꿔야 해서 업데이트가 쉽지 않다.

장점) 조인연산이 필요 없기 대문에 빠른 계산이 가능하다.

 

 

2) 데이터 웨어하우스

프로덕션 데이터베이스와는 별개로 내부 데이터 조직을 위한 별도의 데이터베이스

내부에서 사용하므로 속도보다는, 데이터 크기와 관계없이 데이터 인사이트를 도출하는 것이 중요하다!

 

종류

	AWS Redshift	GOOGLE Cloud Big Query	Snowflake
비용	고정비용	가변비용

 

보통 BigQuery나 Snowflake를 많이 사용하지만, 강의 상에서는 Redshift를 많이 사용한다.

하나의 구조를 배우면 나머지 구조에 쉽게 적용할 수 있다.

 

데이터 엔지니어의 역할

데이터 파이프라인 구축

1. ETL : 외부에 존재하는 데이터를 읽어서(Extract), 데이터형 변환을 수행해서(Transform), 데이터 웨어하우스로 저장(Load)해주는 과정이 필요하다.

2. 대용량 분산처리 시스템이 추가

 

데이터팀의 프로세스

ETL을 통해 데이터를 DW에 적재 → 적재된 데이터를 이용해 데이터 분석 → 분석된 내용을 바탕으로 서비스 개선

 

 

4) 클라우드

HW/SW 등을 네트워크를 통해 서비스 형태로 사용하는 것으로,

필요한 만큼 할당하여 사용한 만큼 지불하므로 필요한 자원을 탄력적으로 운영하는 것이 중요하다.

초기 투자비용은 줄어들고, 여유 리소스 제거를 통해 비용이 감소된다.

 

AWS

아마존의 여유 자원(서버, SW/HW)들을 제공하면서 시작되었다.

 

1. EC2(Amazon Elastic Compute Cloud) 서비스

1. 가상 서버 (인스턴스): 가상 서버, 즉 "인스턴스"를 제공
2. 다양한 인스턴스 유형: 여러 가지 인스턴스 유형을 제공하여 다양한 컴퓨팅 요구에 맞게 선택 가능(컴퓨팅, 메모리, 스토리지 등에 특화된 인스턴스 유형이 존재)
3. 스케일링 및 관리: 수평 및 수직 스케일링이 가능(여러 인스턴스를 시작하거나 중지하여 애플리케이션의 요구에 따라 유연하게 대응)
4. 다양한 운영 체제: Amazon Linux, Ubuntu, Windows Server 등을 지원
5. 네트워킹 기능: 가상 네트워킹 기능을 제공(가상 프라이빗 클라우드(VPC), 서브넷, 보안 그룹 등을 구성하여 네트워크 환경을 조절할 수 있음)
6. AMI (Amazon Machine Image): 이미 프로비저닝 된 운영 체제 및 애플리케이션 구성을 포함하는 이미지를 선택
7. 비용 모델: 사용한 리소스에 대해서만 비용을 지불(인스턴스 유형, 사용 시간, 데이터 전송 등에 따라 비용이 다르게 책정

구매 옵션

• On-Demand : 시간당 비용을 지불
• Reserved : 1~3년간의 사용을 보장 + 40%의 디스카운트
• Spot Instance : 경매방식으로 현재 우휴한 리소스들을 저비용으로 사용. 단, 더 높은 비용을 제시한 사용자에게 실시간으로 할당되므로 중요하지 않은 연산을 실행하도록 하여야 한다.

 

2. S3(Simple Storage Service)

아마존이 제공하는 대용량 클라우드 서비스(HW)

 

구매 옵션 1TB/month

• Standard : $23
• Glacier storage : $4

 

3. 데이터베이스 서비스

• Relational Database Service (Mysql, PostgreSQL, Oracle, MS SQL 등)
• Redshift
• ElasticCache
• ElasticSearch
• MongoDB

 

4. AI&ML 서비스

• SageMaker : 인공지능 자동화(딥러닝/머신러닝 모델을 만들어 주고 → 테스트 →  API 형태로 배포)
• Lex : charbot 서비스를 만들때 사용하는 서비스
• Polly : 텍스트를 음성으로 변환하는 서비스
• Rekognition : 이미지 인식 서비스

 

5. 기타

• Amazon Alexa : 아마존 음성인식 플랫폼
• Amazon Connect : 콜센터 구현 제공하는 서비스
• Lambda : API 자체의 로직만으로 따로 EC2를 론칭할 필요 없이 서버를 탄력적으로 관리하게 해줌