🟨 SQL을 이용한 데이터 분석

📍 SQL의 중요성

모든 데이터 직군에게 필요한 기술은 SQL

• 데이터 직군은 크게 3가지가 존재

  1) 데이터 엔지니어

  • 데이터 웨어하우스에 데이터를 적재해주는 ETL, ELT와 같은 프로세스 담당하는 SW 엔지니어.

  • 데이터 웨어하우스 관리를 위한 SQL에 대한 지식 있어야함.

  2) 데이터 분석가

  • 데이터 엔지니어가 만들어놓은 데이터 웨어하우스에서 데이터를 읽어와 데이터 분석을 수행함.

  • 대시보드 만들고 지표 계산하고 해야하기 때문에 SQL없이 데이터 분석가가 할 수 있는게 없음(3가지 직군 중 SQL이 가장 중요한 직군).

  3) 데이터 과학자

  • 머신러닝/인공지능 모델링을 하는 사람.

  • 머신러닝/인공지능 지식,경험이 중요하지만 데이터 웨어하우스에 있는 데이터를 원하는 형태로 만들기 위해 SQL에 대한 지식이 필요.

• 데이터 직군이 사용하는 SQL의 특징은 큰 데이터를 작은 데이터로 바꾸거나 주어진 문제를 답하기 위한 데이터 분석 시에 사용한다는 것.

• 백엔드/프론트엔드가 사용하는 SQL은 이러한 목적과는 다름. 웹 서비스나 모바일을 사용하는 사용자들에 대한 정보, 상품에 대한 정보 등을 빠르게 저장/읽기를 운영하기 위함.

• 결론 : 데이터 직군에서 SQL에 대한 지식은 필수!

📍 관계형 데이터베이스란?

구조화된 데이터를 저장하는데 사용되는 관계형 데이터베이스가 무엇인지 알아보자.

관계형 데이터베이스

• 관계형 데이터베이스란?

  구조화된 데이터를 저장하는 스토리지. 비구조화된 데이터는 관계형 데이터베이스에 저장할 수 없다는 말.

• 비유를 하면 엑셀에서 테이블 형태를 관계형 데이터베이스의 테이블이라고 생각하면 됨.

• 컬럼들로 테이블의 형태가 정의 되는데, 이거를 테이블 스키마라고 부름.

  스키마가 정의되면 그거에 맞춰 레코드를 추가할 수 있고 그게 행이 되는 것임.

  즉, 테이블이라는 것에 포맷을 정하고(=테이블 스키마) 스키마에 맞춰 레코드를 넣어주는 것임.

• 우리가 분석하는 데이터는 대부분 구조화된 데이터.

  따라서 관계형 데이터베이스는 구조화된 데이터 분석 시 가장 좋은 기술.

• 관계형 데이터베이스에 저장된 데이터를 가지고 뭔가 질의를 하거나 조작을 하는데 사용되는 언어가 SQL.

  따라서 관계형 데이터베이스와 SQL은 데이터가 구조화 되어있다면 데이터 분석에서 가장 좋은 기술 옵션이 됨.

• SQL은 크게 두 종류가 존재

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

  • CREATE, DROP, ALTER 등의 스키마를 조작하는 명령들이 포함됨.

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

  • 정의된 테이블 스키마에 맞춰 데이터가 들어가있다고 할 때, 조건에 맞는 데이터를 읽어오거나 추가/삭제/머지해서 새로운 정보를 만들어 낼 때 사용.

  • SELECT, UPDATE, DELETE 등의 명령들이 포함됨.

대표적 관계형 데이터베이스

관계형 데이터 베이스는 크게 2종류가 존재

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

• OLTP(Online Transaction Processing)라고 부르기도함.

• 특징은 빠른 응답속도. 웹 서비스/앱 서비스에 연동 돼서 쓰이는거여서 빠른 속도에 집중(서비스에 필요한 정보 저장).

2) 데이터 웨어하우스

• OLAP(Online Analytical Processing)이라고 부르기도함.

• 백엔드/프론트엔드 직군이 집중하는 DB가 프로덕션 데이터베이스. 데이터 직군은 보통 데이터 웨어하우스를 사용.

• 특징은 처리 데이터 크기가 크다는 것. 빠른 정도는 그리 중요하지 않음. 빠르면 좋지만 필수 요구사항은 아님.

• 데이터 분석하거나 모델 빌딩 할 때 필요한 데이터를 저장하는 관계형 데이터 베이스

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

• 회사가 작은 경우 데이터 웨어하우스가 없고 프로덕션 데이터베이스만 있음. 이런 경우 데이터 직군이 원하는 데이터를 뽑으려면 프로덕트 데이터베이스에서 SQL문을 실행해야함.

  그러나, 서비스에 연동이 되어있는 DB이기 때문에 서비스에 영향을 미치게 됨. 그래서 별도의 DB(데이터 웨어하우스)가 필요한 것임.

  데이터 직군 면접 시, 사용하는 데이터 웨어하우스 물어보고 대표적인거 사용한다면 데이터쪽으로 나름 진보한 회사!

이번 강의에서 데이터 웨어하우스 중심의 SQL에 대해 배움.

관계형 데이터베이스 구조

• 관계형 데이터 베이스는 2단계로 구성됨.

  테이블이 데이터베이스에 존재하는데 다수의 테이블이 존재하면 나중에 혼동이 됨.

  테이블 이름으로만 관리하면 관리가 힘드니까 폴더 컨셉을 만들어 내기 시작함. 폴더 밑에 테이블들을 놓는 구조.

  

  관계형 데이터베이스에 따라서 폴더를 스키마라고 부르기도하고 데이터베이스라고 부르기도 함.

  raw_data : 밖에서 copy해서 들어온 테이블들
  analytics : raw data에서 의미있는 값들을 뽑아낸 테이블들

• 프로덕션 데이터베이스, 데이터 웨어하우스 관련없이 다 이러한 구조를 동일하게 갖고 있음.

• 테이블 자체는 테이블 스키마가 존재. 이 스키마에 맞춰서 레코드가 존재.
  

• 테이블 스키마에서 중요한 건 속성(primary key)이 있음. 컬럼은 이름, 타입과 primary key로 구성됨.

  예를 들어, user id로 primary key로 지정하면 중복되는 값이 없어야함. 테이블에서 특정 컬럼 값이 단 하나만 있는걸 지정해주면 지정해준 필드에 값이 유일하다는걸 보장해줌.

  같은 값을 갖는 데이터가 추가되면 막아버림.
  

📍 SQL이란?

데이터 처리에서 기본이 되는 SQL에 대해 배워보자.

SQL 소개

• SQL이란?

  Structured Query Language로 관계형 데이터베이스에 저장되어있는 데이터(테이블)을 질의하거나 조작해주는 언어를 말함.

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

• 테이블 스키마를 정의해주는 DDL이 있고, 정의된 테이블의 레코드를 추가/수정/삭제해주고 조건에 맞춰 읽어오는데 쓰이는 DML이 있음.

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

• hadoop이 발표되면서 빅데이터 세상이 왔고 SQL은 스몰 데이터에만 사용하는 질의언어로 취급되는 시기가 있었음.

  그러나 구조화된 데이터이기만 하면 SQL 자체 문법은 크기와 관계없이 사용할 수 있고 스몰 데이터에만 동작하는게 아닌 것을 깨달아서(크기에 상관없다는 것을 깨달아서) 데이터 웨어하우스가 SQL기반으로 동작함.

SQL 단점

• SQL은 구조화된 데이터를 다루는데 최적화 되어있고 비구조화된 데이터(ex.텍스트)를 다룰 때는 정규표현식을 통해 원하는 데이터를 파싱해서 정규화된 형태로 바꾸는 걸 할 수 있지만 제약이 심하고 비효율적일 수 있음.

• 즉, 가장 큰 단점은 구조화된 데이터만 다루는데 최적화 되어있다는 점.

  비구조화된 데이터를 다루는데 문제가 있다보니 많은 경우 데이터 웨어하우스 기반으로 데이터 인프라를 만들어도 어느 시점에는 hadoop같은 분산환경 시스템을 데이터 인프라에 도입해야했음.

  정리 : 비구조화된 데이터 처리하지 못해서 hadoop,spark 등 새로운 기술이 필요함.

• 많은 관계형 데이터베이스들이 nested structure 는 지원하지 않음.

• 관계형 데이터베이스마다 약간씩 SQL 문법이 다름.

Star schema vs. Denormalized schema

이거는 프로덕션 데이터베이스와 데이터 웨어하우스의 차이라고 볼 수 있음.

어떤 형태로 데이터를 모델링(데이터를 어떻게 표현할 것인지) 할 것인지가 2가지 있는 것!

가상의 매출 데이터로 어떤 형태로 데이터를 정의할 수 있는지 살펴보자.

1) Star schema

• 기간, 매장, 제품, 직원에 대한 정보를 한번에 넣는게 아니라 별도의 테이블로 빼는 형태 ➡️ star schema

• 예를 들어 매출을 발생시킨 직원을 알고자한다면 직원 번호를 보고 직원 번호에 해당하는 테이블을 찾아봐야함(join을 해보는 식으로).

• 이렇게 어떤 정보를 한 군데에 다 넣는게 아니라 논리적인 단계에 맞게 별도의 테이블을 만들고 그 테이블에 링크를 거는 형태.

• 주로 프로덕션 데이터베이스에 많이 사용됨.

• 스토리지 낭비가 덜하고 업데이트가 쉬움. 단점은 수많은 테이블과 join을 해야한다는 점(join을 하면 시간이 더 걸리고 메모리도 매칭해야돼서 리소스도 더 필요함).

2) Denormalized schema

• 다른 테이블 볼 필요 없이 하나의 테이블만 보면 원하는 정보가 다 들어가있는 형태

• 장점은 join을 안해도돼서 속도가 빨라짐.

  단점은 모든게 반복돼서 스토리지가 많이 필요.

• 데이터 웨어하우스에서 사용하는 방식(보통은 데이터 업데이트를 할일이 없고 프로덕션 데이터베이스에 있는 데이터 복사해와서 원하는 형태로 join하는 형태니까)

📍 데이터 웨어하우스 소개

데이터 웨어하우스가 무엇이고 다른 관계형 데이터베이스와 어떻게 다른지 알아보자.

데이터 웨어하우스

• 데이터 웨어하우스란?

  여전히 SQL 기반의 관계형 데이터베이스인데, 프로덕션 데이터베이스와는 별도이어야 함(서비스의 불안정성, 중단으로 연결되기 때문).

• 데이터 웨어하우스는 회사에 필요한 모든 데이터를 저장하는 장소가 됨.

  프로덕션 데이터베이스에 있는 데이터를 데이터 웨어하우스로 복사해오기 때문에 데이터 직무의 사람들은 쿼리를 마음대로 보낼 수 있음.

• 대표적 3가지는 Redshift, Big Query, Snowflake

  ➡️ Redshift보다 Big Query, Snowflake가 스케일이 더 크고 좋은 데이터 웨어하우스.

  ➡️ Redshift는 고정비용 옵션(한 달에 얼마 이런식) / Big Query, Snowflake는 가변비용 옵션(쓴 만큼 지불하는 방식)

• 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 데이터베이스.

  따라서 처리속도가 그리 중요하지는 않음. 고객이 사용하는 DB면 처리속도가 중요함!

• 외부(프로덕션 데이터 베이스도 일부가 됨)에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 프로세스를 만들어야하는데 그러한 프로세스들을 ETL 혹은 데이터 파이프라인이라고 부름.

  데이터 웨어하우스, ETL 같은거를 통 틀어서 데이터 인프라라고 함

데이터 인프라

• 데이터 인프라란?

  데이터 엔지니어가 관리하며 그 안에 데이터 웨어하우스가 존재.

  

• 데이터 인프라는 데이터 웨어하우스에 데이터를 적재해주는 ETL이라는 프로세스와 비구조화된 데이터가 생기면 그거를 프로세싱 하기 위한 spark, hadoop 같은 대용량 분산처리 시스템까지를 포함하는 개념.

데이터 순환 구조

• 데이터 인프라가 생기면 어떤일들이 가능해지는가?

  내/외부에 데이터 소스들이 있고 데이터 엔지니어가 ETL을 통해 데이터 웨어하우스에 데이터를 저장함.

  데이터 분석가는 중앙 저장된 데이터를 가지고 일종의 summary를 만들고 이거가지고 시각화 및 데이터 분석.

  데이터 과학자들이 이러한 분석을 통해 서비스 자체를 머신러닝/딥러닝 형태로 학습시켜 제품 자체의 기능을 개선 or 서비스 개선을 함.

  

📍 Cloud와 AWS 소개

Cloud와 AWS에 대해 간략히 알아보자.

클라우드 정의

• 클라우드란?

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

  클라우드가 처음 생겼을 때는 HW만을 지칭했음(ex.서버). 예를 들어, AWS에서 서버를 구축해놓으면 내가 그거를 빌려쓰고 쓴 만큼 돈을 지불하는 형태!

  발전해서 SW도 이러한 형태로 제공하게 됨. 예를 들어, MySQL 같은 거를 AWS에서 세팅해놓으면 나는 어떤 버전이 필요하고 어떤 사양이 필요한지 설정하면 론치가 되고 사용한만큼 돈을 내는 것.

• 클라우드 컴퓨팅에는 2가지 키워드가 존재

  1) No Provisioning : 내가 준비할 필요가 없다는거. 원하는 사양을 선택하면 HW,SW가 준비됨.

  2) Pay As You Go : 쓴만큼 돈을 지불한다는거. 원래는 내가 오래사용할지 말지 모르는 상황에서도 HW를 구매해서 사용해야했음. 하려던게 잘 안되면 큰 돈을 들여서 산 HW가 소용이 없어짐. 내가 쓴만큼 돈을 내는거는 초기비용 줄이는데 도움됨.

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

• 서버, 네트워크, 스토리지 구매와 설정 등을 직접 수행해야 함.

• 많은 수의 서버를 구매/설치하고 데이터센터 공간을 직접 확보해야 함.

• peak time기준으로 capacity planning을 해야함.

  이커머스 사이트의 경우 블랙 프라이데이와 같은 날 트래픽이 몰려도 문제 없이 서비스가 운영이 되도록 하는데, 트래픽이 낮을 때와 비교하면 100배 이상이 차이가 나고 놀고 있는 지원들이 높아지는 현상이 발생.

• 직접 운영하는 비용은 너무 크니까 클라우드를 써서 필요할 때 사용하는 식으로 운영하면 상대적으로 비용은 높을지라도 전체적으로 비용이 높은게 아니라 기회비용이라는 측면에서 장점이 있음.

클라우드 컴퓨팅의 장점

• 초기 비용 투자가 크게 줄어듬(CAPEX)
  OPEX는 운영 시 들어가는 돈(?).

• 리소스 준비를 위한 대기시간이 대폭 감소(기회비용이라는 측면에서 이점이 있음)

• 노는 리소스 제거

• 글로벌 확장 용이

• 클라우드 회사들이 SW도 Saas형태로 지원하기 때문에 SW 개발시간도 단축됨.

AWS 소개

• AWS는 클라우드 컴퓨팅 업체 중 가장 크고 가장 처음 클라우드 컴퓨팅 아이디어 가지고 서비스를 시작한 곳

• 처음에는 서버 빌려주는 거부터 시작해서 지금은 SW 서비스까지 확장이 되었고 ML/AI 관련 서비스들도 내놓기 시작

• AWS 매출 변화를 보면 엄청나게 크게 변하고 있음. 분기 매출이 12조이고, 이익이 2.8조 정도. 이 뒤를 MS의 azure와 구글의 구글 클라우드가 쫓아가고 있음.

  

AWS 서비스들

1) EC2

• EC2는 서버 호스팅 서비스. 다양한 사양의 서버를 다양한 종류의 운영체제에 대해서 지원함.

• 3가지 구매 옵션이 존재

  1. On- Demand : 시간 당 쓴 만큼 돈을 내는 모델. 가장 흔함.

  2. Reserved : 몇 년 쓸 확신이 있으면 1년 or 3년 사용 보장하고 할인받는 옵션.

  3. Spot Instance : 일종의 경매방식. 보통 on-demand에서 조금 더 여유롭게 플래닝을 할테니까 놀고 있는 서버가 존재할 것. 놀고 있는 서버를 싸게 쓸 수 있게 옵션을 주는거(문제는 더 높은 가격으로 경매한 사람이 있으면 그 사람에게 서버들이 다 넘어감).

2) S3

• AWS의 스토리지 서비스. 웹 하드디스크라고 생각하면 됨.

• 탑 레벨 폴더가 있는데 그게 버킷. 버킷 밑에 서브 폴더를 만들고 파일을 올리고 하는게 가능. 파일이나 버킷별로 접속 권한 제한도 가능.

• 기본적으로 가격이 싼 편. 한달에 1TB가 23불.

  Standard storage는 데이터를 잃을 확률이 있지만 엄청 작음.

  데이터를 잃을 확률이 높은 경우가 infrequent access storage이고 가겨이 조금 더 저렴.

  테잎 같은데 저장해도 상관없으면 glacier storage에 저장하면 됨. 액세스하는데 오래걸리긴 함.

3) Database services

• 관계형 데이터베이스를 서비스 형태로 제공하기도 함.

• Redshift와 같은 데이터 웨어하스도 제공.

4) AI & ML services

• SageMaker

  • 딥러닝/머신러닝 통해서 모델 만들고 테스트하고 모델을 api형태로 디폴로이 하는거까지 자동화해주는 프레임워크.

  • 보통 모델 만들 때는 가설 세우기 -> train set 수집 -> 머신러닝 알고리즘 결정 -> 하이퍼파라미터 설정 -> train/test 셋 나누기 이러한 과정들이 필요하고, 최종적으로는 모델을 프로덕션의 api형태로 디폴로이 하는거.

    그래야지만 다른 사람이 내가 만든 모델을 불러서 사용할 수 있음.

  • SageMaker는 이러한 과정 중 3,4번째를 자동화해주는거라고 생각하면 됨.

• Lex

  • 챗봇 서비스를 만들 때 사용하는 서비스

• Polly

  • 텍스트를 주면 음성으로 바꿔주는 서비스

• Rekognition

  • 이미지를 인식하는데 사용하는 서비스

5) 기타 중요 서비스들

• Amazon Alexa

  • voice bot 플랫폼인데 나만의 음성인식을 하고 음성인식에 맞춰 특정 기능하는거를 쉽게 추가할 수 있음.

• Amazon Connect

  • 콜센터 구현을 위한 서비스

• Lambda

  • 서버리스 컴퓨팅.

    api를 만드려면 서버 론치하고 코딩해서 api를 구동시킬 수 있는 프레임 워크 만들어야하는데, 서버리스 컴퓨팅으로 가면 그런 과정 건너뛰고 api자체 로직만 특정 언어로 구현해놓으면 그거를 노출시켜 트래픽에 따라 서버의 수를 늘렸다 줄였다 하는 탄력적인 운영을 아마존에서 대신 해주는 거

  • 다른 클라우드에서 보면 비슷한 기능을 다른 이름으로 제공하고 있음(강의자료 참고).

글로벌 클라우드 시장 점유율

• 크게 변화는 없는데 MS의 azure가 마켓이 점점 커지고 있음. AWS, 구글 클라우드는 큰 변화는 없음.

• 3개의 큰 업체를 제외한 나머지 클라우드 업체의 마켓 쉐어도 무시 못함.

📍 Redshift 소개

Redshift에 대해 자세히 알아보자.

Redshift

• Redshift는 SQL 엔진. SQL 기반의 데이터 베이스.

• Redshift의 경우 2PB까지 지원 가능

• Redshift는 데이터 웨어하우스이고 속도에 포커스 하는게 아니라 처리할 수 있는 데이터 크기에 집중하는 데이터베이스

• 특징 중 하나는 Columnar storage.

  레코드를 저장할 때 대부분의 데이터베이스는 레코드 하나를 저장하고 그 다음 레코드 저장하고 이런식임.

  예를 들어 컬럼이 a, b, c라고 할 때, 레코드1의 a b c 저장하고 그 다음 레코드2의 a b c 저장하고 이런식!

  그런데 Redshift는 컬럼 a만 레코드별로 쭉 저장하고 컬럼 b만 쭉 저장하고 이런식으로 저장됨!

  장점은 컬럼 추가/삭제가 빠르고 컬럼별 압축이 가능하다는 점

• 또 다른 특징은 벌크 업데이트를 지원한다는 점.

  보통 SQL 쓰면 insert로 동시에 추가할 수 있는 레코드 수가 제약이 있음. 데이터 웨어하우스처럼 수많은 데이터를 내부에 저장해야하는 경우에는 insert써서 레코드 별로 업데이트하는게 이상적이지 않음(시간 오래걸림).

  Redshift, snowflake, big query의 경우에는 레코드가 들어있는 파일(ex. csv파일)을 웹 스토리지(S3)에 저장하고 그거를 일괄 복사해주는 커맨드가 존재(100만개의 레코드라하더라도 금방 복사 가능).

• 또 다른 특징은 Redshift는 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않는다는 점.

  보장하려면 데이터가 추가될 때마다 primary key로 컬럼값이 유일하다는 것을 체크해야하는데 그걸 하다보면 속도가 안남(프로덕션 DB는 체크함).

  그럼 어떻게 보장을 할까?
  데이터 엔지니어가 다른 오퍼레이션을 해서 primary key가 유일한걸 보장해줘야함.

• 마지막 특징은 Postgresql 8.X와 SQL이 호환된다는 점.

  모든 기능을 지원하는건 아니고 일부 기능만 지원.

  Postgresql 8.X를 접근할 수 있는 클라이언트나 프로그래밍 라이브러리가 있으면 똑같은걸 사용해서 Redshift도 접근 가능.

Redshift는 데이터 웨어하우스이지만 SQL이 메인 언어라는 점 명심!

Redshift 옵션과 가격

• Dense Storage는 스토리지에 집중한 경우

• Dense Compute는 빠르게 처리하는게 중요한 컴퓨팅에 집중한 경우들.

  우리가 사용할 Redshift는 빨간색 옵션에 해당

• Managed Storage는 스토리지+컴퓨팅에 집중한 경우(둘 다 잡으려는 경우)

Redshift 스키마 구성

• Redshift를 론치하면 3개의 폴더를 만들 것임. 폴더 같은 컨셉을 만들어서 폴더 특성에 맞게 테이블을 분류하고 관리함.

• 스키마를 만들 때는 SQL 커맨드로 만들 수 있고 그 때 사용하는 명령어가 CREATE SCHEMA(이거는 Redshift의 admin권한 있는 사람만 가능).

• adhoc은 데이터 직군 누구든지 테이블을 만들고 테스트를 할 경우 여기 밑에 테이블을 두라는 거(여기는 누구도 관리하지 않음)

• raw_data 는 데이터 엔지니어, analytics는 데이터 분석가가 보통 관리를 함.

Redshift 액세스 방법

• 실습은 구글 코랩써서 진행할 것임.

• Postgresql 8.x와 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능(시각화 툴인 태블로, 룩커 사용하고 있으면 Redshift와 연결해서 차트 만들고 대시보드 만들 수 있음).