카테고리 없음

데이터분석 > 4주차 > 파트 03 > 0904

나는야석사 2024. 9. 3. 00:01

SQL과 데이터 베이스 소개

1. SQL을 이용한 데이터 분석

데이터 관련 3개의 직군

  • 데이터 엔지니어
    • 파이썬, 자바/스칼라
    • SQL, 데이터베이스
    • ETL/ELT(Airflow, DBT)
    • Spark, Hadoop
  • 데이터 분석가
    • SQL, 비즈니스 도메인에 대한 지식
    • 통계(AB 테스트 분석)
  • 데이터 과학자
    • 머신러닝
    • SQL, 파이썬
    • 통계

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)
    • 처리 데이터 크기에 집중. 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장
      • 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장

관계형 데이터베이스의 구조 : 관계형 데이터베이스엔 다수의 테이블이 존재

  • 관계형 데이터베이스는 2단계로 구성됨
    • 가장 밑단에는 테이블들이 존재(테이블은 엑셀의 시트에 해당)
    • 테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성(엑셀에서는 파일)
  • 테이블의 구조(테이블 스키마라고 부르기도 함)
    • 테이블은 레코드(행)들로 구성
    • 레코드는 하나 이상의 필드(컬럼, 열)로 구성
    • 필드(컬럼)는 이름과 타입과 속성(primary key)으로 구성됨

3. SQL이란?

SQL 소개

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

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

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

SQL의 단점

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

데이터 모델링을 하는 방법 2가지

Star schema

  • Production DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용해 데이터를 저장
  • 데이터를 논리적 단위로 나눠 저장하고 필요 시 조인. 스토리지의 낭비가 덜하고 업데이트가 쉬움
  • 가운데 있는 테이블은 각 테이블의 일련 번호만 가지고 있음 -> 연합학습과 비슷함

Denormalized schema

  • 데이터 웨어하우스에서 사용하는 방식
    • 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요없는 형태를 말함
  • 이는 스토리지를 더 사용하지만 조인이 필요 없기에 빠른 계산이 가능
  • 스토리지가 많이 필요함

 

데이터 웨어하우스 소개

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

  • 여전히 SQL 기반의 관계형 데이터베이스
    • 프로덕션 데이터베이스와는 별도이어야 함
      • OLAP (OnLine Analytical Processing) vs. OLTP(OnLine Transaction Processing)
    • AWS의 Redshift, Google Cloud의 Big Query, Snowflake 등이 대표적
      • 고정비용 옵션 vs. 가변비용 옵션
  • 데이터 웨어하우스는 고객이 아닌 내부 직원을 위한 데이터베이스
    • 처리 속도가 아닌 처리 데이터의 크기가 더 중요해짐
  • ETL 혹은 데이터 파이프라인
    • 외부에 존재하는 데이터를 읽어다가 데이터 웨어하우스로 저장해주는 코드들이 필요해지는데 이를 ETL 혹은 데이터 파이프라인이라고 부름

데이터 인프라란?

  • 데이터 엔지니어가 관리함
    • 여기서 한 단계 더 발전하면 Spark과 같은 대용량 분산처리 시스템이 일부로 추가됨
    • 사이트 내/외부에 다양한 소스들이 있고 ETL을 통해 데이터를 사용하기 쉬운 형태로 웨어하우스에 적재
    • 만든 summary table을 가지고 데이터 시각화
    • 다양한 데이터 분석을  통해 (개인화등을 통한) 제품 서비스 개선
    • 다시 반복

클라우드 정의

  • 컴퓨팅 자원(HW, SW 등)을 네트워크를 통해 서비스 형태로 사용하는 것
  • 키워드
    • "No Provisioning"
    • "Pay As You Go"
  • 서버 같은 자원을 필요한만큼 실시간으로 할당하여 사용한만큼 지불
    • 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요

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

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

클라우드 컴퓨팅의 장점

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

AWS 소개

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

EC2 - Elastic Compute Cloud(2)

세 가지 종류의 구매 옵션

On-Demand : 시간 당 비용을 지불하며 가장 흔히 사용하는 옵션

Reserved : 1년이나 3년간 사용을 보장하고 1/3 정도에서 40% 디스카운트를 받는 옵션

Spot Instance : 일종의 경매방식으로 놀고 있는 리소스들 ??

 

S3 - Simple Storage Service(1)

  • 아마존이 제공하는 대용량 클라우드 스토리지 서비스
  • S3는 데이터 저장관리를 위해 계층적 구조를 제공
  • 글로벌 네임스페이스

기타 중요 서비스 - Database Services

  • RDS(Relational Database Service)
    • MySQL, PostgreSQL, Aurora
    • Oracle, MS SQL Server
  • DynamoDB
  • Redshift
  • ElasticCache
  • Neptune(Graph database)
  • ElasticSearch
  • MongoDB

기타 중요 서비스 - AI & ML Services

  • SageMaker
    • Deep Learning and Machine Learning ene-to-end framework
  • Lex
    • Conversational Interface(Chatbot service)
  • Polly
    • Text to Speech Engine
  • Recognition
    • Image Recognition Service

기타 중요 서비스 

  • Amazon Alexa
    • Amazon's voice bot platform
  • Amazon Connect
    • Amazon's Contact Center Solution
    • 콜센터 구현이 아주 쉬워짐
  • Lambda
    • Event-driven, serverless computing engine
    • 서비스 구현을 위해서 EC2를 론치할 필요가 없음
    • Google Cloud에는 Cloud Function이란 이름으로 존재
    • Azure에는 Azure Function이란 이름으로 존재

Redshift 소개

Redshift : Scalable SQL 엔진(1)

  • 2 PB까지 지원
  • Still OLAP
    • 응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용 불가
  • Columnar storage
    • 컬럼별 압축이 가능
    • 컬럼을 추가하거나 삭제하는 것이 아주 빠름

Redshift : Scalable SQL 엔진(2)

  • 벌크 업데이트 지원
    • 레코드가 들어있는 파일을 S3로 복사 후 COPY 커맨드로 Redshift로 일괄 복사
  • 고정 용량/비용 SQL 엔진
    • vs. Snowflake vs. BigQuery
  • 다른 데이터 웨어하우스처럼 primary key uniqueness를 보장하지 않음
    • 프로덕션 데이터베이스들은 보장함

Redshift는 Postgresql 8.x와 SQL이 호환됨

  • 하지만 Postgresql 8.x의 모든 기능을 지원하지는 않음
    • 예를 들어 text 타입이 존재하지 않음
  • Postgresql 8.x를 지원하는 툴이나 라이브러리로 액세스 가능
    • JDBC/ODBC
  • 다시 한 번 SQL이 메인 언어라는 점 명심
    • 그러기에 테이블 디자인이 아주 중요

Redshift 액세스 방법

  • 이번 강좌에서는 Google Colab을 사용 예정
  • Postgresql 8.x와 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능
    • SQL Workbench(Mac과 윈도우), Postico(Mac)
    • Python이라면 psycopg2 모듈
    • 시각화/대시보드 툴이라면 Looker, Tableau, Power BI, Superset 등에서 연결 가능
  •  
     
    ① 처리할 수 있는 데이터의 크기가 중요하다
     
    ② 데이터의 처리 속도가 아주 빨라야 한다 V
     
    ③ 보통 클라우드 기반의 Redshift, BigQuery, Snowflake등을 사용한다
     
    ④ 회사 내부 직원들 (특히 데이터 팀원들)이 주 사용자이다
  • 객관식 1

    다음 중 데이터 웨어하우스의 특징이 아닌 것은? (프로덕션 데이터베이스와 비교했을 때)

  • 정답 2
    객관식 2

    다음 중 SQL에 대한 설명으로 잘못된 것은?

     
    ① 1970년대 초 IBM이 개발한 관계형 데이터베이스 질의/조작 언어이다
     
    ② 비구조화된 데이터를 처리하는데 적합한 언어이다 V
     
    ③ 데이터 일을 하는 사람이면 반드시 알아야할 기술이다
     
    ④ DDL과 DML 두 종류가 있다
  • 정답 2
    객관식 3

    흔히 이야기하는 데이터 인프라의 일부가 아닌 것은?

     
    ① 데이터 웨어하우스
     
    ② ETL 프로세스
     
    ③ Spark과 같은 대용량 분산처리 환경
     
    ④ 프로덕션 데이터베이스 V
  • 정답 4
    객관식 4

    우리가 이번 강좌에서 사용할 데이터 웨어하우스는 무엇인가?

     
    ① 구글 클라우드의 Big Query
     
    ② Snowflake
     
    ③ AWS의 Redshift V
     
    ④ MySQL
  • 정답 3
    객관식 5

    다음 중 관계형 데이터베이스에 대한 설명으로 잘못된 것은?

     
    ① 구조화된 데이터를 테이블의 형태로 표현한다
     
    ② SQL을 사용하여 구조화된 데이터를 질의하고 조작한다
     
    ③ 보통 데이터베이스 혹은 스키마라 부르는 일종의 폴더 밑에 테이블들을 생성하는 2단계 구조로 테이블들을 관리한다
     
    ④ Star schema보다는 Denormalized schema를 사용하는 것이 일반적이다 V
  • 정답 4
    객관식 6

    다음 중 클라우드의 장점을 모두 선택하시오

     
    ① 내가 필요한 자원을 필요한만큼 필요할 때 할당하여 사용할 수 있다 V
     
    ② 운영비용이 아닌 초기투자비용을 증대시킨다
     
    ③ 고정비용의 지출로 재무측면에서 플랜닝이 쉬워진다
     
    ④ 서비스에 구현에 필요한 시간을 단축하여 기회비용을 최소화하는 잇점이 존재한다 V
    정답  1, 4