[SQL] 관계형 데이터베이스, SQL이란? w.스타 스키마 구조

현재 프로그래머스 AI 데브코스를 수강하면서 조금씩 내용을 노션에 정리해오고 있었는데,

강의 자료 자체가 아닌 해당 내용을 바탕으로 내가 공부한 내용은 저작권에 문제가 되지 않는다는 답변을 받아

공부한 내용도 정리할겸 블로그에 조금씩 옮겨보고자 한다!

 

예전에 w3 school에서 이론적으로 sql에 대해서 공부하고 시험도 치렀었는데,

이번에는 한기용 강사님의 SQL 강의로 도움을 많이 받아 해당 내용을 공유해보고자 한다.

 

SQL은 왜 중요한가?

어떤 데이터 관련 직군을 선택하더라도 SQL은 필수 언어이다.

데이터 엔지니어

데이터웨어하우스와, 거기에 데이터를 적재하는 ETL을 담당하는 SW 엔지니어. 파이썬, 자바, 스칼라. ETL,ELT(Airflow, DBT). Spark, hadoop 등 빅데이터 관련 지식도 필요하다.  데이터베이스의 프로그래밍 언어인 SQL을 알아야 데이터 웨어하우스를 관리할 수 있다.

 

데이터 분석가

데이터웨어하우스에서 SQL로 데이터를 읽어와서, 대시보드를 만들고 지표 계산을 하기 때문에 SQL이 필수이다. 특히 비즈니스 도메인에 대한 지식과, 통계(AB테스트 분석) 지식 등이 필요하다.

 

데이터 과학자

머신러닝, 인공지능 모델링. SQL로 데이터 웨어하우스의 데이터를 읽어다가, 피처 엔지니어링과 훈련에 필요한 형태로 가공할 수 있다. 코딩이 필수이고, 수학과 통계적 지식이 많은 도움이 된다.

 

SQL은 큰 데이터를 프로세싱해서 작은 데이터로 변환하거나, 주어진 데이터를 이용해 데이터 분석을 하기 위해 필요하다.

데이터 직군에서는 SQL의 사용방향이 프론트, 백엔드 개발자와는 조금 다르다.

 

SQL이란?

SQL : structured Query Language.(씨퀄이라고 읽기도 함)

관계형 데이터베이스(정확히는 테이블)에 존재하는 레코드들을 질의할 수 있도록 하는 '언어'

1970년대 초반에 IBM이 개발했다.

• DDL : Data Definition Language. 테이블 정의 : 관계형 DB에 어떤 테이블이 들어갈지 테이블 자체를 정의.
• DML : Data Manipulation Language. 테이블 데이터 조작, 질의 : 정의된 테이블 스키마에 맞춰서 조건에 맞는 레코드를 읽고, 추가하고, 수정하고, 삭제하거나, merge하는 등.. ex) SELECT, UPDATE, DELETE ...

빅데이터에서 SQL을 쓸 수 있는가?

구조화된 데이터이기만 하면, 데이터 크기와 관계없이 사용할 수 있다.

모든 대용량 웨어하우스는 SQL 기반. Redshift, Snowflak,BigQuery, Hive.

spark는 SQL 뿐만 아니라 판다스, 스트리밍, 그래프 형태 모두 지원함.

Spark은 SparkSQL, Hadoop은 Hive로 SQL 언어를 지원한다.

 

SQL의 단점

비구조화된 데이터(text)는 정규표현식을 사용할 수는 있지만 제약이 심하고 비효율적일 수 있다.

그리고 구조화된 정보가 JSON처럼, 필드 안에 또 필드가 있는 nested structure는 구글 빅쿼리만 지원.

어느 시점부터는 SQL만으로는 비구조화 데이터 처리가 불가능해지므로 Spark, Haddop과 같은 분산컴퓨팅 환경이 필요하다.

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

 

관계형 데이터베이스란?

'구조화된' 데이터를 저장하고, 질의할 수 있도록 해주는 스토리지. 엑셀처럼 테이블 형태로 데이터 정의 및 저장.

(테이블-시트, 컬럼-열, 레코드-행)

ex) 유저 테이블에 이름이라는 컬럼에 문자열 타입, 생년월일이라는 컬럼에 date 타입 등..

구매 테이블에도 다양한 컬럼을 정의해놓고, 구매가 발생할 때마다 추가할 수 있을 것이다.

 

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

 

관계형 데이터베이스의 단점은 데이터가 구조화되어 있지 않다면 저장할 수 없다는 것. 위에서 말한 SQL의 단점과 연결된다.

반면 데이터가 아무리 커져도 데이터가 구조화되어 있기만 하다면, 데이터분석에 가장 쉽게 쓸 수 있는 것이 SQL이라는 뜻이다.

 

 

대표적 관계형 데이터베이스의 종류

프로덕션 데이터베이스 = OLTP(online transaction processing)

MYSQL, PostgreSQL, Oracle

빠른 응답속도. 웹서비스나 앱과 연동해서, 새로운 사용자가 오면 이 프로덕션 데이터베이스에 저장되고, 읽고, 하는..

사용자가 오래 기다리면 안되므로 '빠른 속도'에 집중. 웹, 앱과 바로 연동되어 서비스에 필요한 정보 읽어오고 쓰게 된다.

보통 백, 프론트 개발자들이 사용함.

 

데이터 웨어하우스=OLAP(Online analytical processing)

Redshift, snowflake, bigquery, hive...

처리할 수 있는 데이터의 크기가 크다는 게 특징. 여기에는 데이터 분석이나, 모델 빌딩을 위한 데이터를 저장해둔다.

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

데이터 분석 직군에서 많이 사용한다.

 

만일 프로덕션 데이터베이스밖에 없는 회사에 데이터 직군으로 조인했다면?

내가 필요한 데이터를 뽑으려면 프로덕션 데이터베이스에  SQL을 실행해야 한다. 그런데 서비스에 연동돼있으므로 큰 쿼리를 날리게 되면, 전체 서비스에 영향을 미치게 된다. 

그래서 데이터 웨어하우스를 별도로 구축해야 한다. 여기서 무슨일이 발생하건 서비스에 영향을 미치지 않기 때문.

 

 

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

1. 테이블들이 존재한다.(엑셀의 시트)

2. 테이블들은 폴더 컨셉으로 묶고, 그 폴더들 밑에 테이블들을 놓는다. raw_data 폴더, analytics 폴더 등.. 이 폴더를 '스키마'라 부르거나, '데이터베이스'라고 부른다. 

 

raw_data 폴더 : 외부에서 가져온 데이터들을 테이블로 넣어둠.

analytics 폴더 : raw_data를 가공해서 summary하는 등의 결과.

 

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

테이블 자체는 테이블 스키마가 있고, 테이블의 이름과 테이블을 구성하는 컬럼과 컬럼타입이 주어진다.

이게 결국 테이블의 구조를 정의한다. 예시에서의 테이블 스키마에는 userId, channel이라는 두 개의 컬럼이 있다.

특정 유저가 어떤 채널을 통해 들어왔는지 나타낸다.

 

이 스키마에 맞는 레코드들이 실제로 존재하게 된다. 

즉, 테이블은 레코드(행)들로 구성되어 있다. 예시에서는 3개의 레코드가 이 테이블을 구성하고 있다.

 

하나의 레코드는 하나 이상의 필드(컬럼)으로 구성되어 있다.

예시에서는 첫 번째 레코드에는 453이라는 userID, instagram이라는 채널을 확인할 수 있다.

 

테이블 스키마에는 '속성' 이 있다. primary key로 지정된 컬럼은 그 값이 유일해야 한다.

userID는 동일한 값을 가진 레코드가 하나만 존재해야 한다는 점에 유의하자! 즉 '유일성'을 유지한다.

실제로는 동일한 값을 가진 데이터가 추가되면 reject되도록 구성하는 편이다.

주민번호, 이메일주소 등.

 

데이터 모델링 : 데이터를 표현하고 저장하는 방식.

어떤 매출 데이터가 있고, 날짜, 제품, 직원 등의 정보가 있다고 하자.

Star schema

Dimension(차원) 테이블과 Fact(팩트) 테이블로 분류한다.

차원 테이블은 가운데 있는 테이블로, 다른 각 테이블의 고유 식별자 역할을 하는 키와 설명이 포함되어 있다. 

팩트 테이블은 세분화된 정보를 다 저장한다. 실제로 Join을 해서, 테이블의 레코드를 찾아서 상세 정보를 봐야 한다. 

production DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용한다.

데이터를 논리적 단위로 나눠서 저장하고 필요 시 조인하므로 느리지만, 스토리지 낭비가 덜하고 업데이트가 쉽다.

보통 프로덕션DB에서 많이 사용한다.

 

 

Denormalized schma

위에서 말한 정보들을 한 개의 테이블에 다 밀어넣는 것.

모든 게 반복되므로 스토리지 낭비가 심하지만, 단위 테이블로 나눠서 저장하지 않고, 별도의 조인이 필요 없어 빠르다.

스토리지 제한이 없는 데이터 웨어하우스에서 사용하는 방식. 보통 프로덕션 DB를 그대로 카피한다.