SQL Analysis
데이터 관련 3개의 직군
데이터 엔지니어
파이썬, 자바/스칼라
SQL, 데이터 베이스
ETL/ELT(Airflow, DBT)
Spark, Hadoop
데이터 분석가
SQL, 비즈니스 도메인 지식
통계(AB 테스트 분석)
데이터 과학자
머신러닝 모델링
SQL, 파이썬
통계
관계형 데이터베이스
- 구조화된 데이터를 저장하고 질의할 수 있다록 해주는 스토리지
엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장, 테이블에는 컬럼과 레코드, 행이 존재 - 관계형 데이터베이스를 조작하는 프로그래밍 언어 SQL
테이블 정의를 위한 DDL(Data Definition Language), 포맷을 정의
테이블 데이터 조작/질의를 위한 DML(Data Manipulation Lagnuage)
대표적 관계형 데이터베이스
프로덕션 데이터 베이스 : MySQL, PostgreSQL, Oracle
OLTP(OnLine Transaction Processing)
빠른 속도에 집중, 서비스에 필요한 정보를 저장
백엔드, 프론트엔드에서 사용
데이터 웨어하우스 : Redshift, Snlwflake, BigQuery, Hive, Spark
OLAP(OnLine Analytical Processing)
처리 데이터 크기에 집중, 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장
보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장
관계형 데이터베이스의 구조
2단계로 구성됨
가장 밑단에는 테이블들이 존재(테이블은 엑셀의 시트에 해당)
테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성(엑셀에서는 파일)
테이블의 구조(테이블 스키마)
테이블은 레코드들로 구성(행)
레코드는 하나 이상의 필드로 구성(열)
필드는 이름과 타입과 속성(primary key)로 구성됨
primary key는 중복을 방지한다. like user id
SQL structured query language
관계형 데이터베이스에 있는 데이터(테이블)를 질의하거나 조작해주는 언어
1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
두 종류의 언어로 구성됨
- DDL : 테이블의 구조를 정의하는 언어
- DML : 테이블에서 원하는 레코드들을 읽어오는 질의 언어, 테이블에 레코드를 추가/삭제/갱신 해주는데 사용
빅데이터
구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰임
모든 대용량 데이터 웨어하우스는 SQL기반
Spark Hadoop도 예외는 아님, SparkSQL HIVE라는 SQL언어가 지원됨
데이터 분야에서 일하고자 하면 반드시 익혀야할 기본 기술
SQL단점
구조화된 데이터를 다루는데 최적화가 되어있음
정규표현식을 통해 비구조화된 데이터를 어느 정도 다루는 것은 가능하나 제약이 심함
많은 관계형 데이터베이스들이 플랫한 구조만 지원함(no nested like JSON)
구글 빅쿼리는 nested structure를 지원함
비구조화된 데이터를 다루는데 Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요해짐
즉, SQL만으로는 비구조화 데이터를 처리하지 못함
관계형 데이터베이스마다 SQL 문법이 조금씩 상이
Star schema
production DB용 관계형 데이터베이스에서는 보탕 스타 스키마를 사용해 데이터를 저장한다.
데이터를 놀리적 단위로 나눠 저장하고 필요시 조인. 스토리지의 낭비가 덜하고 업데이트가 쉬움
Denormalized schema
데이터 웨어하우스에서 사용하는 방식
단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요없는 형태를 말함
이는 스토리지를 더 사용하지만 조인이 필요 없기에 빠른 계산이 가능
데이터 웨어하우스
회사에 필요한 모든 데이터를 저장
여전히 SQL 기반의 관계형 데이터베이스
-
프로덕션 데이터베이스와는 별도여야 함
OLAP vs OLTP -
AWS의 Redshift // Google Cloud의 Big Query, Snowflake 등이 대표적
고정비용 옵션 vs 가변비용 옵션
스케일이 커지면 가변비용이 더 저렴 -
데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 데이터 베이스
처리 속도가 아닌 처리 데이터의 크기가 더 중요해짐 -
ETL(extract transform load) 혹은 데이터 파이프라인
외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 프로세스가 필요해지는데 이를 ETL 혹은 데이터 파이프라인이라고 부름
데이터 인프라
데이터 엔지니어가 관리함
여기서 한 단계 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 일부로 추가됨
ETL을 거쳐서 데이터 웨어하우스에 저장
데이터 엔지니어들의 업무
데이터 순환 구조
Cloud, AWS
컴퓨팅 자원(하드웨어, 소프트웨어)을 네트워크를 통해 서비스 형태로 사용하는 것
no provisioning, pay as you go
자원(서버)을 필요한만큼 실시간으로 할당하여 사용한만큼 지불, 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요
클라우드 컴퓨팅이 없다면
서버/네트워크/스토리지 구매와 설정 등을 직접 수행해야 함
데이터센터 공간을 직접 확보(co-location)
확장이 필요한 경우 공간을 먼저 더 확보할 필요
그 공간에 서버를 구매하여 설치하고 네트워크 설정
보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
또한 peak time 기준 capacity planning을 해야함
놀고 있는 자원들이 높게되는 현상 발생
직접 운영비용 vs 클라우드 비용
장점
초기 투자비용이 크게 줄어듬
CAPEXcapital expenditue vs. OPEXoperating expense
리소스 준비를 위한 대기시간 대폭 감소
노는 리소스 제거로 비용 감소
글로벌 확장 용이
소프트웨어 개발 시간 단축
SaaS, managed service
AWS
가장 큰 클라우드 컴퓨팅 서비스 업체
2002년 아마존의 상품데이터를 API로 제공하면서 시작
현재 100여개의 서비스를 전세계 제공
대부분의 서비스들이 오픈소스 프로젝트들을 기반으로 함
최근들어 ML/AI 관련 서비스들도 내놓기 시작
Netflix, Zynga등의 업체들도 사용
다양한 종류의 소프트웨어/플랫폼 서비스를 제공
AWS의 서비스만으로 쉽게 온라인 서비스 생성
EC2 - elastic compute cloud
-
AWS의 서버 호스팅 서비스
리눅스 혹은 윈도우 서버를 런칭하고 어카운트를 생성하여 로그인 가능
가상 서버들이라 전용서버에 비해 성능이 떨어짐
bare-metal 서버도 제공하기 시작 -
다양한 종류의 서버 타입, 구매옵션 제공
on-demand : 시간당 비용을 지불
reserved : 1년이나 3년간 사용을 보장 받고 디스카운트
spot instance : 일종의 경매방식으로 놀고 있는 리소스들을 좀더 적은 비용으로 사용, 다른 사람이 더 큰 비용을 내면 뺏김
S3 - simple storage service
아마존이 제공하는 대용량 클라우드 스토리지 서비스
S3는 데이터 저장관리를 위해 계층적 구조를 제공
디렉토리를 버킷이라고 부름
1TB standard $23, infrequent $12.5, glacier $4
기타 중요 서비스 database services
- RDS relational database service
MySQL, PostgreSQL, Aurora
Oracle, MS SQL Server - DynamoDB
- Redshift
- ElasticCache
- Neptune
- ElasticSearch
- MongoDB
AI & Ml services
- SageMaker
Deep Learning and Machine Learning end to end framework, - Lex
Conversational Interface (Chatbot service) - Polly
Text to Speech Engine - Rekognition
Image Recognition Service
기타 중요 서비스
- Amazon Alexa
Amazon's voice bot platform - Amazon Connect
Amazon's contact center solution, call center - Lambda
Event-driven, serverless computing engine
서비스 구현을 위해서 EC2를 론치할 필요가 없음, API 로직만 구현하면 됨
Google Cloud, Cloud Function
Azure, Azure Function과 동일
Redshift : Scalable SQL 엔진
2PB까지 지원
-
still OLAP
응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용불가 -
Columnar storage
컬럼별 압축이 가능
컬럼을 추가하거나 삭제하는 거싱 아주 빠름 -
벌크 업데이트 지원
레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift로 일괄 복사 -
고정용량/비용 SQL 엔진
vs. Snowflake vs. BigQuery -
다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음
값이 유일하게 존재한다는 것을 입력마다 체크해야함
프로덕션 데이터베이스들은 보장함
Postgresql 8.x SQL이 호환됨
text타입이 존재하지 않음
Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능, JDBC/ODBC
SQL이 메인언어이기 때문에 테이블 디자인이 아주 중요하다.
Redshift Schema폴더 구성
Dev 밑에
CREATE SCHEMA raw_data;
CREATE SCHEMA analytics;
CREATE SCHEMA adhoc;
1.raw_data : 외부에서 읽어온 데이터, 데이터 엔지니어
2.analytics : 쓰기 편하게 요약 및 분석, 데이터 분석가
3.adhoc : 개발자를 위한 테스트용
