[AI class W11 D1] SQL Analysis 1

Contents

 

1. SQL의 중요성

2. 관계형 데이터베이스란?

3. SQL이란?

4. 데이터 웨어하우스란?

5. Cloud, AWS 소개

6. Redshift 소개

 

1. SQL의 중요성

데이터 관련 3개 직군(데이터 엔지니어, 데이터 분석가, 데이터 과학자) 모두에서 중요한 스킬셋

 

2. 관계형 데이터베이스란?

• 구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지 (이미지, 텍스트 등은 비구조화된 데이터)
  • 엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장
    • 테이블에는 컬럼(열)과 레코드(행)가 존재
• 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL
  • 테이블 정의를 위한 DDL(Data Definition Language)
  • 테이블 데이터 조작/질의를 위한 DML(Data Manipulation Language) 
• 관계형 데이터베이스의 종류
  • 프로덕션 데이터베이스 : MySQL, PostgreSQL, Oracle... (백엔드, 프론트엔드 개발자가 다루는..)
    • OLTP(OnLine Transaction Processing)
    • 빠른 속도에 집중. 서비스에 필요한 정보 보장 (웹서비스 등에 바로 연동되어 많이 사용하는)
  • 데이터 웨어하우스 : Redshift, Snowflake, BigQuery, Hive, ... (데이터팀에서 다루는...)
    • OLAP (OnLine Analytical Processing)
    • 처리 데이터의 크기에 집중. 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장
      • 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장

* 프로덕션 데이터베이스만 있는 상황에서 데이터 분석을 하게 된다면?
분석팀이 computation 큰 쿼리를 날린다면, 프로덕트에 연결되어 있기 때문에, 사용자가 사용하는 서비스의 속도까지 느려지게 된다.
-> 백엔드, 프론트엔드 개발자와의 불화의 시발점
-> 회사의 서비스와 상관없는 데이터 웨어하우스가 있어야 한다 (작은 스타트업에서는 없는 경우가 많다.)

• 관계형 데이터베이스의 구조
  • 2단계로 구성
    
    1. 가장 밑단은 테이블들이 존재(테이블은 엑셀의 시트에 해당)
    2. 테이블들은 데이터베이스 (또는 스키마) 라는 폴더 밑으로 구성(엑셀이라면 파일들) 
    
  • 테이블의 구조(테이블 스키마)
    • 테이블은 레코드(행)들로 구성
    • 레코드는 하나 이상의 필드(컬럼)으로 구성(열)
    • 필드(컬럼)는 이름과 타입과 속성(primary key)로 구성됨
    •  primary key로 지정된 컬럼은 그 값을 가진 record가 유일해야 한다. (ex> userId) (프로덕션 데이터베이스의 경우는 primary key 값이 동일한 record가 들어오면 rejecte된다)
    

3. SQL이란?

1. SQL소개

• SQL : Structured Query Language
  • 관계형 데이터베이스에 있는 데이터(테이블)을 질의하거나 조작해주는 언어
• SQL은 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
• 두 종류의 언어로 구성됨
  1. DDL (Data Definition Language):
     • 테이블의 구조를 정의하는 언어
  2. DML (Data Manipulation Language):
     • 테이블에서 원하는 레코드를 읽어오는 질의 언어
     • 테이블에 레코드를 추가/삭제/갱신 해주는데 사용되는 언어

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

• 구조화된 데이터를 다루는 한 SQL은 데이터 규모와 상관없이 쓰임
• 모든 대용량 데이터 웨어하우스는 SQL기반
  • Redshift ,Snowflake, BigQuery, Hive
• Spark, Hadoop도 예외는 아님
  
  • SparkSQL과 Hive라는 SQL언어가 지원됨
• 데이터 분야에서 일하고자 하면 반드시 익혀야할 기본 기술
  • 데이터 엔지니어, 데이터 분석가, 데이터 과학자 모두 알아야만 한다.

3. SQL의 단점

• 구조화된 데이터를 다루는데 최적화 되어 있음
  • 정규표현식을 통해 빅구조화된 데이터를 어느 정도 다루는 것은 가능하나 제약이 심함
  • 많은 관계형 데이터베이스들은 플랫한 구조만 지원(no nested like JSON)
    • 구글 빅쿼리는 nested structure를 지원함
  • 비구조화된 데이터를 다루는데 Spark, Hadoop 같은 분산 컴퓨팅 환경이 필요해짐 (회사가 성장했음을 의미)
    • 즉, SQL만으로는 비구조화된 데이터를 처리하지 못함
• 관계형 데이터베이스마다 SQL, 문법이 조금씩 상이

4. 데이터 모델링 (데이터를 어떻게 표현하고 저장할 것이냐?)- production DB와 DB warehouse의 차이

 

• Star schema
  • production DB용 관계형 데이터베이스는 보통 스타 스키마를 사용해 데이터 저장
  • 데이터를 논리적 단위로 나눠 저장하고 필요시 join.
  • 스토리지 낭비가 덜하고 업데이트 쉬움
  • 매칭을 위해 시간이 많이 걸리고 리소스도 더 필요함
  
• Denormalized schema
  • 데이터 웨어하우스에서 사용하는 방식
    • 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요없는 형태
  • 스토리지는 더 사용하지만, 조인이 필요없기에 빠른 계산이 가능
  • 보통 프로덕션 DB를 그대로 복사해서 사용하지 안의 내용을 업데이트 하지는 않는다

4. 데이터 웨어하우스란?

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

1. 데이터 웨어하우스란: 회사에 필요한 모든 데이터를 저장

• 여전히 SQL 기반의 관계형 DB
  • 프로덕션 DB와는 별도여야 함 (회사 서비스와 안정성 문제)
    • OLAP(OnLine Analytic Processing)(DB warehouse) vs. OLTP(OnLine Transacton Processing)(production DB)
  • 프로덕션 DB를 복사해온 것에 추가로 다양한 데이터를 복사해와서 저장 (마케팅 데이터, 외부 데이터 등) : 데이터는 많이 모여있을 수록 더 힘을 발 휘한다
  • AWS 의 Redshift(더 scalable한 장점), Google Cloud의 Big query, Snowflake 등이 대표적
    • 고정비용 옵션 (Redshift) vs. 가변비용 옵션(Big query, Snowflake)
• 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 DB
  • 처리 속도가 이난 처리 데이터의 크기가 더 중요해짐
• ETL 혹은 데이터 파이프라인
  
  • 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 코드들이 필요해지는데 이를 ETL(Extract, Transform, Load) 혹은 데이터 파이프라인이라고 부름

2. 데이터 인프라란?

• 데이터 엔지니어가 관리함
  • 여기서 한 단계 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 데이터 인프라의 일부로 추가됨

데이터 인프라

 

• 데이터 순환구조 

데이터 순환구조

5. Cloud, AWS 소개

1. 클라우드 정의

• 컴퓨팅 자원(하드웨어, 소프트웨어 등등)을 네트웤을 통해 서비스 형태로 사용하는 것
• 키워드
  • "No Provisioning" : 내가 준비를 할 필요가 없다
  • "Pay As You Go" : 쓴만큼 지불
• 자원(ex> 서버)이 필요한 만큼(거의) 실시간으로 할당하여 사용한 만큼 지불
  • 탄력적으로 필요한 만큼의 자원을 유지하는 것이 중요

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

• 서버/네트웤/스토리지 구매와 설정 등을 직접 수행해야 함
• 데이터 공간을 직접 확보(Co-location)
  • 확장이 필요한 경우 공간을 먼저 더 확보해야 함
• 그 공간에 서버를 구매하여 설치하고 네트웤 설정
  • 보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
• 또한 peak time 기준으로 capacity planning 을 해야함
  • 놀고 있는 자원들이 높게 되는 현상 발생
• 직접 운영비용 vs. 클라우드 비용
  • 기회비용 (기다려야 하는 시간)

3. 클라우드 컴퓨팅의 장점

• 초기 투자 비용이 크게 줄어듦
  • CAPEX(Capital Expenditure) vs. OPEX(Operating Expense)(클라우드 컴퓨팅 도입시)
• 리소스 준비를 위한 대기 시간 대폭 감소
  • Shorter Time to Market
• 노는 리소스 제거로 비용 감소
• 글로벌 확장 용이
• 소프트웨어 개발 시간 단축
  • Managed Service(SaaS) 이용

4. AWS 소개

• 가장 큰 클라우드 컴퓨팅 서비스 업체
• 2002년 아마존의 상품 데이터를 API로 제공하면서 시작
  • 현재 100여개의 서비스를 전세계 15개의 지역에서 제공
  • 대부분 서비스들이 오픈소스 프로젝트들을 기반으로 함
  • 최근들어 ML/AI 관련 서비스들도 내놓기 시작
• 사용고객
  • Netflix, Zynga 등의 상장업체들도 사용
  • 많은 국내 업체들도 사용 시작 (서울 리전)
• 다양한 종류의 소프트웨어/ 플랫폼 서비스를 제공
  • AWS의 서비스 만으로 쉽게 온라인 서비스 생성
  • 뒤에서 일부 서비스 따로 설명 예정

5. EC2 - Elastic Compute Cloud

• AWS의 서버 호스팅 서비스
  • 리눅스 혹은 윈도우 서버를 론치 하고 어카운트를 생성하여 로그인 가능(구글 앱엔진과 가장 큰 차이점)
  • 가상 서버들이라 전용 서버에 비해 성능이 떨어짐
  • Bare-metal 서버도 제공하기 시작
• 다양한 종류의 서버 타입 제공
  • http://aws.amazon.com/ec2/
  • 예를 들어 미국 동부에서 스몰타입(tw.small)의 무료 리눅스 서버 하나 할당시
    • 시간당 2.3센트 비용 지불
    • 2GB 메모리, 1가상코어, 160GB 하드 디스크
    • 12년에는 8.5센트였음
    • 타입별, 지역별 가격을 알고 싶다면?
  • incoming network bandwidth는 공짜이지만,outgoing은 유료
  • 세 가지 종류 구매 옵션
    1. On- demand : 시간당 비용 지불하며 가장 흔히 사용됨
    2. Reserved : 1년 or 3년 사용 보장하고 1/3 정도에서 40% 디스카운트 받는 옵션
    3. Spot Instance : 일종의 경매방식으로 놀고 있는 리소스들을 저렴한 비용으로 사용할 수 있는 옵션

6. S3 - Simple Storage Service

• http://aws.amazon.com/s3/
• 아마존이 제공하는 대용량 클라우드 스토리지 서비스 (웹 하드 서비스)
• S3는 데이터 저장관리를 위해 계층적 구조를 사용
• 글로벌 네임스페이스를 제공하기 때문에 톱레벨 디렉토리 이름 선정에 주의해야함
• S3에서는 디렉토리를 버킷이라고 부름 
• 버킷이나 파일 별로 액세스 컨트롤 가능
• low cost 1TB per month 
  • standard storage :$23 (데이터를 잃을 확률이 거의 없음)
    • infrequent access storage: $12.5 (데이터를 잃을 확률 0.01% 쯤)
    • SLA가 다름
  • glacier storage : $4 (엑세스 하는데 오래 걸림)

7. 기타 중요 서비스 - Database Services

• RDS (Relational Database Service)
  • MySQL, PostgreSQL, Aurora
  • Oracle, MS SQL Server
• DynamoDB
• Redshift - data warehouse
• Elastic Cache
• Neptune(Graph database)
• Elastic Search
• MongoDB

8. 기타 중요 서비스 - AI & ML services

• SageMaker
  • Deep Learning and Machine Learning end-to-end framework (모델 만들기 -> 테스트 -> API deploy 까지 자동화)
  • 모델을 만드는 일반적인 process : 가설 수립 -> training set수집 -> 알고리즘 사용 결정/ 하이퍼 파라미터 선택 -> cross validation -> 학습 -> production에 API 형태로 deploy 
• Lex
  • Conversational Interface (Chatbot service)
• Polly
  • Text to Speach Engine
• Rekognition
  • Image Recognition Service

9. 기타 중요 서비스

• Amazon Alexa
  • Amazon's voice bot platform
• Amazon Connect
  • Amazon's contact center solution
  • 콜센터 구현이 아주 쉬워짐
• Lambda
  • event-driven, serverless computing engine (일반적으로 EC2로 서버를 만들고 , 코딩으로 API를 구동할 수 있는 프레임워크를 만들어야 했음)
  • 서비스 구현을 위해 EC2를 론칭할 필요가 없음 (API 로직만 구현하면 이를 노출하고, 서버를 탄력적으로 운영할 수 있게 해줌)
  • Google Cloud에서는 Cloud Function이란 이름으로 존재
  • Azure에서는 Azure Function이라는 이름으로 존재

6. Redshift 소개

1. Redshift : scalable SQL 엔진

• 2PB까지 지원 (실제로는 그 전에 문제 발생)
• still OLAP
  • 응답 속도가 빠르지 않기 때문에 프로덕션 DB로는 사용 불가
• columnar storage (대부분의 DB는 record 단위로 저장 but data warehouse 는 컬럼별로 저장)
  • 컬럼별 압축 가능
  • 컬럼 추가, 삭제가 아주 빠름
• 벌크 업데이트 지원
  • 레코드가 들어있는 파일을 S3로 복사후 COPY 커맨드로 Redshift로 일괄 복사 (INSERT는 많은 양 record 입력 한계)
• 고정 용량 / 비용 SQL 엔진
  • vs. Snowflake(google cloud, aws 모두 사용 가능) vs. Big Query(google cloud)
• 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음 (uniqueness 확인하는데 시간 많이 걸림)
  • 프로덕션 DB은 보장함
  • 그럼 어떻게 보장할거냐? 엔지니어가 다른 operation으로 보장을 해줘야 한다

2. Postgresql 8.x 와 SQL이 호환됨

• 그러나  Postgresql 8.x 의 모든 기능을 지원하지는 않음
  • ex. text 타입이 존재하지 않음
• Postgresql 8.x을 지원하는 툴이나 라이브러리로 액세스 가능
  • JDBC / ODBC
• 빅데이터 데이터 웨어하우스 이지만, SQL이 메인 언어라는 점 명심
  • 테이블 디자인이 매우 중요

3. Redshift Schema (폴더) 구성

admin 권한을 가진 사람만 수행할 수 있는 명령어

CREATE SCHEMA raw_data; (데이터 엔지니어가 관리)

CREATE SCHEMA analytics; (데이터 분석가들이 관리)

CREATE SCHEMA adhoc; (테스트 등을 위해 테이블을 만들 수 있는 공간)

 

4. Redshift 액세스 방법

• Google Colab
•   Postgresql 8.x 과 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능
  • SQL Workbench (Mac&Windows), Postico (Mac)
  • Python 이라면 psycopg2모듈
  • 시각화/대시모드 툴이라면 Looker, Tableau, Power BI, Superset 등에서 연결 가능