[인공지능 데브코스 TIL/프로그래머스 데브코스]SQL을 이용한 데이터 분석

SQL의 중요성

• 모든 데이터 직군에게 필요한 기술은 SQL
• 데이터 관련 3개의 직군
  • 데이터 엔지니어
    • 파이썬, 자바/스칼라
    • SQL, 데이터베이스
    • Spark, Hadoop
    • ETL/ELT(Airflow, DBT)
  • 데이터 분석가
    • SQL, 비즈니스 도메인에 대한 지식
    • 통계(AB 테스트 분석)
  • 데이터 과학자 ( 통계 중요)
    • 머신러닝
    • SQL, 파이썬
    • 통계
• 데이터 요약과 데이터 분석을 위한 SQL

 

배움이란?

• 배움에는 시관과 노력이 걸린다.
• 시간에 따른 배움의 정도 = 실력? 의 그래프를 다음과 같다 했을때,

• 저 빨간 박스부분에서 견뎌야 한다고 한다.
  • 가장 중요한것을 버티는 힘: 이걸 즐겨야 한다. => shit;; 포기하지 마라. 아무리 못해도 세달은 풀타임으로 메달리라는데, 난 포기 안한다. 포기는 배추 셀 때 하는 말.
  • 내가 뭘 모르는지 생각해봐야 한다. 내가 어디서 막혔는지 구체적으로 질문할 수 있나?
    • 내가 뭘 모르는지 알아야 한다. 이건 문제를 어느정도 이해하고 있단 말과 동일한 말이다. 백종원 선생님 조차 질문한다. 계속 자문하라.
  • 잘하는 사람 보고 기죽지 말자.
    • 이전의 '나'가 경쟁상대다. 뭔가를 잘한다? 이쪽에 경험이 있다는 말과 같다.
• 새로운 것을 처음 배울 때의 좋은 자세(1)
  • 자신이 아는 것과 모르는 것을 분명히 이해하는지?
    • 멍청한 질문이란 없다는 점 명심. 대충 알거나 모르면서 안 물어보는 것이 더 큰 문제
    • 이는 피드백을 잘 받아들일 수 있는지와도 연계됨
  • 마음을 편하게 먹기
    • 내가 이해하기 힘들다면 남들도 이해하기 힘듬
    • 나보다 잘 하는 사람들은 그만큼 시간을 쏟았기 떄문
• 새로운 것을 처음 배울 때의 좋은 자세(2)
  • 배움의 발전은 tipping point를 거치면서 폭발하는 형태임
    • "The secret is to build the resolve and split to enjoy the plateaus the times when it doen't feel like you're impoving and you question why you are doing this. If you're patient, the plateaus will become springboards" (Quotes from Steve Nash)
    • 발전이 더딘 기간을 즐기는 자세가 필요

 

관계형 데이터베이스란?

• 구조화된 데이터를 저장하는데 사용되는 관계형 데이터베이스가 무엇인지 알아보자
• 구조화된 데이터를 저장하는 데이터 스토리지(??)
• 구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지
  • 엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장
    • 테이블에는 컬럼(열)과 레코드(행)이 존재
  • 관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL
    • 테이블 정의를 위한 DDL (Data Definition Language)
    • 테이블 데이터 조작/질의를 위한 DML( Data Manupulation Language)
• 테이블 스키마(?) > 테이블 칼럼과 칼럼의 타입이 결정되고 레코드가 쌓인다.

 

• 대표적인 관계형 데이터베이스
  • 프로덕션 데이터베이스: MYSQL, PostgreSQL, Oracle ...
    
    • OLTP(Online Transaction Processing)
    • 빠른 속도에 집중, 서비스에 필요한 정보 저장
  • 데이터웨어하우스: Redshift, Snowflake, BigQuery, Hive...
    • OLAP(Online Analytical Processing)
    • 처리 데이터 크기에 집중. 데이터 분석 혹은 모델 빌딩등을 위한 데이터 저장
      • 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장
• 데이터 웨어하우스가 없다면?
  • 보통 프로덕션 데이터베이스를 직접 사용하게 되는데 대용량의 데이터를 조회하게 되면 데이터베이스에 부하가 걸린다. => 서비스에 지장이간다. => 백엔드 개발자들 극대노
  • 회사에 데이터 웨어하우스가 있다 => 데이터 팀 문화가 어느정도 정립되어 있을 확률이 높다.
• 관계형 데이터베이스의 구조
  • 관계형 데이터베이스는 2 단계로 구성됨
    • 가장 밑단에는 테이블들이 존재(테이블은 엑셀의 시트에 해당)
    • 테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성(엑셀에서는 파일)
  • 테이블의 구조(테이블 스키마라고 부르기도 함)
    • 테이블은 레코드들로 구성 (행)
    • 레코드는 하나 이상의 필드(컬럼)로 구성(열)
    • 필드(컬럼)는 이름과 타입과 속성(primary key)으로 구성됨

 

SQL이란?

• 데이터 처리에서 기본이 되는 SQL에 대해 배우자!
• SQL
  • SQL: Structured 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 nested like JSON)
      • 구글 빅쿼리는 nested structure를 지원함
    • 비구조화된 데이터를 다루는데 Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요해짐
      • 즉 SQL만으로는 비구조화된 데이터를 처리하지 못함
    • 관계형 데이터베이스마다 SQL 문법이 조금씩 상이
• 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 혹은 데이터 파이프라인이라고 부름
    • ETL(Extract Transform Load) - 외부의 데이터를 추출(Extract), 읽어온 데이터를 내가 원하는 format으로 변환(Transform) 이런 데이터를 데이터 웨어하우스의 테이블에 저장해주는 마지막 스텝(Load)
• 데이터 인프라란?
  • 데이터 엔지니어가 관리함
    • 여기서 한 단계 더 발전하면 Spark와 같은 대용량 분산처리 시스템이 일부로 추가됨

• 데이터 순환구조

 

 

Cloud와 AWS 소개

• 클라우드 정의
  • 컴퓨팅 자원(하드웨어, 소프트웨어 등등)을 네트웍을 통해 서비스 형태로 사용하는 것.
  • 키워드:
    • No Provisioning
    • Pay as you Go
  • 자원(예를 들면 서버)을 필요한만큼 (거의) 실시간으로 할당하여 사용한만큼 지불
    • 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요
• 클라우드 컴퓨팅이 없었다면?
  • 서버/네트워크/스토리지 구매와 설정등을 직접 수행해야 함
  • 데이터센터 공간을 직접 확보(Co-location)
    • 확장이 필요한 경우 공간을 먼저 더 확보해야함
  • 그 공간에 서버를 구매하여 설치하고 네트워크 설정
    • 보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
  • 또한 Peak time을 기준으로 Capactiy 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의 서비스만으로 쉽게 온라인서비스 생성.
    • 뒤에서 일부 서비스를 따로 설명
• AWS 인공지능과 관련된 서비스들
  • 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
    • 콜센터 구현이 아주 쉬워짐
  • Lambda
    • Event-driven, serverless computing engine
    • 서비스 구현을 위해 EC2를 론치할 필요가 없음
    • Google Cloud에는 Cloud Function이란 이름으로 존재
    • Azure에는 Azure Function이란 이름으로 존재

 

Redshift 소개

• Redshift: Scalable SQL 엔진(1)
  • 2 PB까지 지원(2 Peta Byte)
  • Still OLAP
    • 응답속도가 빠르지 않기 때문에 프로덕션 데이터베이스로 사용불가
  • Columnar storage
    • 컬럼별 압축이 가능
    • 컬럼을 추가하거나 삭제하는 것이 아주 빠름
    • 일반 데이터 베이스는 레코드 단위로 저장을 한다고 한다. 근데 이건 컬럼별로 저장을 한다고 한다. 무슨 차이지??
  • 벌크 업데이트 지원
    • 레코드가 들어있는 파일을 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 Schema (폴더) 구성

• Redshift 액세스 방법
  • Colab 사용 예정
  • Postgresql 8.x와 호환되는 모든 툴과 프로그래밍 언어를 통해 접근 가능
    • SQL Workbench (Mac과 윈도우), Postico(Mac)
    • Python이라면 psycopg2 모듈
    • 시각화/대시보드 툴이라면 Looker, Tableau, Power BI, Superset등에서 연결가능