SQL과 데이터베이스 (TIL 16)

석형원·2024년 4월 22일

Database RDBMS TIL sql

TIL

목록 보기

16/52

✏️ 오늘 학습한 내용

1. 배움에 관하여
2. 관계형 데이터베이스
3. SQL
4. 데이터 웨어하우스
5. Cloud와 AWS

🔎 배움에 관하여

배움의 전형적인 패턴

-> what was I thinking? 단계에서 어떻게 하느냐가 중요.
1. 가장 중요한 것은 버티는 힘
  -> 즐기는 것이 중요
2. 내가 뭘 모르는지 생각해봐야한다
  -> 막혔다는 생각이 들 때, 무엇을 모르는 지 계속 파고 들고 계속 자문을 해야함.
  - 내가 어디서 막혔는 지 구체적으로 질문할 수 있는 능력을 길러야함
  - 보통 일반적으로 가정이 잘못되었을 가능성이 높음
  - 자문자답을 통해 아는 것이 무엇이고 모르는 것이 무엇인지 확실하게 알아가기
3. 잘 하는 사람을 보고 기죽지 않기
배움의 발전은 tipping point를 거치면서 폭발하는 형태
발전이 더딘 기간을 즐기는 자세가 필요!

🔎 관계형 데이터베이스

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

대표적인 관계형 데이터베이스
- 프로덕션 데이터베이스
  - MySQL, PostgreSQL, Oracle, ..
  - OLTP (OnLine Transaction Processing) 라고도 부름
  - 특징
    - 빠른 속도에 집중
    - 서비스에 필요한 정보 저장
  - 일반적으로 App에 연동되어 쓰이는 DB
- 데이터 웨어하우스
  - Redshift, Snowflake, BigQuery, Hive, ...
  - OLAP (OnLine Analytical Processing) 라고도 부름
  - 특징
    - 처리 데이터 크기에 집중
    - 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장

일반적으로
백엔드 개발자가 프로덕션 데이터베이스를 사용하고,
데이터 엔지니어가 데이터 웨어하우스를 사용함

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

( 프로덕션 데이터베이스에서 데이터 분석을 진행할 경우, 쿼리를 실행시키다가 서비스에 영향을 미칠 수 있다. -> 그렇기에 별도의 DB가 필요. )

관계형 데이터베이스의 구조
- 관계형 데이터베이스는 2단계로 구성됨
  - 가장 밑단에는 테이블들이 존재
  - 테이블들은 데이터베이스(or 스키마) 라는 폴더 밑에 구성

테이블들의 수가 많아질 수록 관리가 힘들어지기에, 용도를 짐작할 수 있도록 폴더를 만들어 이 테이블들을 관리
-> 이때, 이 폴더를 데이터베이스(혹은 스키마)라고 부름

( 관계형 데이터베이스마다 데이터베이스라고 부르기도하고 스키마라고 부르기도 함 )

관계형 데이터베이스의 구조
- 테이블의 구조 (테이블 스키마)
  - 테이블은 레코드들로 구성 (행)
  - 레코드는 하나 이상의 필드(컬럼)으로 구성 (열)
  - 필드(컬럼)은 이름과 타입과 속성(primary key)로 구성 됨

e.g.)

테이블 스키마

컬럼	타입
userId	int
sessionId	varchar(32)
channel	varchar(32)

🔎 SQL

SQL이란?
SQL : Structured Query Language
- 관계형 데이터베이스에 있는 데이터(테이블)을 질의하거나 조작해주는 언어
- 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
DDL (Data Defintion Language)
- 테이블 구조를 정의
DML (Data Manipulation Language)
- 테이블에서 원하는 레코드를 읽어옴
- 테이블에서 레코드를 추가/삭제/갱신하는데 사용

SQL은 빅데이터 세상에서도 중요!

구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰임
모든 대용량 데이터 웨어하우스는 SQL 기반

SQL의 단점

구조화된 데이터를 다루는데 최적화가 되어있음
- 정규표현식을 통해 비구조화된 데이터를 어느 정도 다루는 것은 가능하나 제약이 심함
- 많은 관계형 데이터베이스들이 플랫한 구조만 지원함 (no nested like JSON)

즉, SQL의 단점은 비구조화된 데이터를 처리하지 못한다는 것이고,

이를 해결하기 위해선, Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요하다.

관계형 데이터베이스마다 SQL 문법이 조금씩 상이

Production DB와 데이터 웨어하우스와의 차이

어떤 형태로 데이터를 모델링을 할 것이냐 ( 데이터를 어떻게 표현할 것이냐? )
- Star schema 방식
- Denormalized schema 방식
Star schema
- Production DB용 관계형 데이터베이스에서는 보통 Star schema를 사용해 데이터를 저장
- 데이터를 논리적 단위로 나눠 저장하고 필요시 join
- 스토리지의 낭비가 덜하고 업데이트가 쉬움
  ex) 매장의 이름이 변경된 경우,
  매장 테이블에서 매장 명만 변경하면 됨.
  하지만, Denormalized schema에서는 불가능.

단점 :
다수의 테이블들을 특정 key 값을 가지고 mapping을 하는 join을 수시로 진행하기에 시간과 리소스의 소모가 상대적으로 크다.

Denormalized schema
- 데이터 웨어하우스에서 사용하는 방식
  - 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요 없는 형태를 말함
- 이는 스토리지를 더 사용하지만 조인이 필요 없기에 빠른 계산이 가능
  ( 즉, update는 불리하지만 search에는 강하다 )
단점 : 스토리지가 많이 필요하고, 업데이트가 비효율적

ex) 매장 이름이 변경된 경우,
테이블을 전부 스캔한 후, 모든 레코드들의 매장 이름을 전부 변경해주어야한다.

🔎 데이터 웨어하우스

데이터 웨어하우스 : 회사에 필요한 모든 데이터를 저장
SQL 기반의 관계형 데이터베이스
- 프로덕션 데이터베이스와는 별도
  - OLAP vs. OLTP
    
    OLAP : OnLine Analytical Processing
    -> Data Warehouse
    OLTP : OnLine Transaction Processing
    -> Production DB
- AWS의 Redshift, Google Cloud의 Big Query, Snowflake 등이 대표적
  - 고정비용 옵션 vs. 가변비용 옵션
  - Redshift < Big Query, Snowflake
    ( Scalability(확장성) issue가 존재 )
- 데이터 웨어하우스는 고객이 아닌 내부 데이터 팀원들을 위한 데이터베이스
  - 처리 속도가 아닌 처리 데이터의 크기가 더 중요해짐
- ETL 혹은 데이터 파이프라인
  - 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스에 저장해주는 프로세스들이 필요해지는데 이를 ETL 혹은 데이터 파이프라인이라 부름
    ( 외부 : Production DB, Data Source, ... )
=> 데이터 웨어하우스, ETL 등을 통틀어 데이터 인프라라고 부름

데이터 인프라란?

데이터 엔지니어가 관리함
데이터 인프라 : (ETL -> 데이터 웨어하우스)

처음에는 ETL, 데이터 웨어하우스만으로 데이터 인프라가 충분했지만,
비구조화된 데이터가 늘어남에 따라서 많은 양의 비구조화된 데이터를 proceesing을 위한 Spark와 같은 대용량 분산처리 시스템이 일부로 추가되었다.

=> 데이터 인프라 : ETL, 데이터 웨어하우스, Spark와 같은 대용량 분산처리 시스템

🔎 Cloud와 AWS

Cloud의 정의
- 컴퓨팅 자원(hardware, software)을 network을 통해 서비스 형태로 사용하는 것.
- 키워드 :
  - "No Provisioning"
  - "Pay As You Go"
- 자원을 필요한만큼 실시간으로 할당하여 사용한만큼 지불
  - 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요.
Cloud Computing이 없었다면?
- 서버/네트워크/스토리지 구매와 설정 등을 직접 수행해야 함
- 데이터센터 공간을 직접 확보
- 확보한 공간에 서버를 구매하여 설치하고 네트워크 설정 ( 2~3달 소요 )
- Peak time을 기준으로 Capacity planning을 해야함
  - 놀고 있는 자원들이 높게 되는 현상 발생
- 직접 운영비용 vs. 클라우드 비용
  - 기회비용! (시간을 절약할 수 있기에)
Cloud Computing의 장점
- 초기 투자 비용이 크게 줄어듬
  - CAPEX (초기 지출) vs. OPEX (운영 비용)
    -> 재무 팀이 cloud 도입을 반대하는 이유
    
    재무 팀의 경우,
    연초에 미리 쓸 돈을 가정하고 계획을 세우기에 CAPEX 모델을 선호하지만, Cloud Coumputig의 경우 매달 비용이 소모되는 OPEX모델로 바뀌기에 향후 얼마나 돈이 더 발생할지 예상을 하기 힘듬
- 리소스 준비를 위한 대기시간 대폭 감소
- 노는 리소스 제거로 비용 감소
- 글로벌 확장 용이
- 소프트웨어 개발 시간 단축
  - Managed Service (SaaS) 이용
AWS 소개
- 가장 큰 클라우드 컴퓨팅 서비스 업체
- 2002년 아마존의 상품 데이터를 API로 제공하면서 시작
- 다양한 종류의 소프트웨어/플랫폼 서비스를 제공
  - AWS의 서비스만으로 쉽게 온라인 서비스 생성

EC2 - Elastic Compute Cloud

AWS의 서버 호스팅 서비스
세 가지 종류의 구매 옵션

구매 옵션	설명
On-Demand	시간당 비용을 지불하며 가장 흔히 사용하는 옵션
Reserved	1년이나 3년간 사용을 보장하고 1/3정도에서 40% 디스카운트를 받는 옵션 (오래 쓰는 경우)
Spot Instance	일종의 경매방식으로 놀고 있는 리소스들을 비용으로 사용할 수 있는 옵션 (On-Demand 보다 조금 싸게)

S3 - Simple Storage Service
- AWS의 대용량 클라우드 스토리지
  (웹 하드 서비스)
- S3는 데이터 저장관리를 위해 계층적 구조를 제공
- 글로벌 namespace를 제공하기 때문에 톱레벨 디렉토리 이름 선정에 주의.
- S3에서는 디렉토리를 버킷(Bucket)이라 부름
- 버킷이나 파일별로 엑세스 컨트롤 가능
- 비용
  - Standard storage : $23
  - Infrequent Access storage : $12.5
    ( 데이터가 상실될 위험이 0.01%정도 있지만 감당할 수 있는 경우 )
  - Glacier storage : $4
    ( 데이터를 테잎 같은 곳에 저장해도 상관 없는 경우 )

기타 중요 서비스 - AI & ML Services
- SageMaker
  
  DeepLearning 혹은 ML을 통해서 모델을 만들고, 그 모델을 테스트하고 최종적으로 API 형태로 deploy까지 자동화해주는 framework
- Lex
  - Chatbot service
- Polly
  - 텍스트를 음성으로 변환 서비스
- Rekognition
  - 이미지 인식 서비스
- Amazon Alexa
  - voice bot platform
- Amazon Connect
  - 콜센터 솔루션 ( 콜센터 구현이 아주 쉬워짐 )
    ( 요즘은 Contact Center라고 부름 )
- Lambda
  - Event-driven, Serverless computing engine
    
    보통 API를 제작할 때,
    EC2로 서버를 론칭하고 내부에서 코딩을 통해 API를 구동시킬 수 있는 Framework를 만들어야하는데,
    
    Serverless Computing을 사용하면,
    API 자체의 로직만 구현하면 나머지는 탄력적인 capcity 운영을 Amazon에서 대신 해줌
  - 서비스를 구현을 위해 EC2를 론치할 필요가 없다.
  - Google Cloud -> Cloud Function이란 이름으로 존재
  - Azure -> Azure function이란 이름으로 존재

RedShift

SQL 기반의 DB
최대 2 PB까지 지원
Still OLAP
( 응답 속도가 빠르지 않기에 프로덕션 데이터베이스로 사용불가 -> 오로지 데이터 웨어하우스 용 )
Columnar storage
- 컬럼별 압축이 가능
- 컬럼을 추가하거나 삭제하는 것이 아주 빠름
  
  대부분의 DB는 레코드를 저장할 때, 레코드 별로 저장을 한다.
  하지만 RedShift나 다른 웨어하우스 기반의 DB들은 Column기반으로 저장을 한다.
벌크 업데이트 지원
- 레코드가 들어있는 파일을 S3(Web Storage)로 복사 후 COPY 커맨드로 RedShift로 일괄 복사
  -> 레코드 별로 저장하는 것은 너무 느리다, 이를 사용할 경우 100 만개의 레코드도 몇 십초안에 복사 가능
고정 용량/비용 SQL 엔진
( Scalability 관점에서는 좋지 않음 )
- vs. Snowflake vs. BigQuery
( 가변 용량 가변 비용 )
다른 데이터 웨어하우스처럼 primary key uniquesness를 보장하지 않음
- Production DB들은 보장
  
  primary key uniquesness를 보장하게 된다면, 레코드가 추가될 때마다 체크를 해야함 -> 속도가 저하됨
  
  그렇기에 개발자가 웨어하우스 내에서가 아닌 다른 Operation을 사용해서 primary key uniquesness를 보장을 해야함
RedShift는 Postgresql 8.x와 SQL이 호환됨
- 하지만 Postgresql 8.x의 모든 기능을 지원하지는 않음
Redshift Options
- Dense Storage (Storage에 초점을 맞춘 옵션)
  -> Large Storage, HDD
- Dense Compute ( 연산에 초점을 맞춘 옵션, 데이터가 작은 경우 )
  -> Small Storage, SDD, 가장 저렴
- Manage Storage ( 위 옵션 2개를 모두 챙기는 옵션 )
  -> Large Storage, SDD, 가장 비쌈
Redshift Schema ( 폴더 ) 구성
- DEV
  - raw_data (CREATE SCEMA raw_data;)
  - analytics (CREATE SCEMA analytics;)
  - adhoc (CREATE SCEMA adhoc;)
RedShift 액세스 방법
- Google Colab
- Postegresql 8.x와 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능
  - SQL Workbench (Mac&Window), Postico (MAC)
  - Python이라면 psycopg2 모듈
  - 시각화/대시보드 툴이라면 Looker, Tableau, Power BI, Superset등에서 연결 가능