데이터 분석을 위한 SQL 레시피 - 2장 도구와 데이터

🗄️ 데이터분석을 위한 SQL 레시피 책을 읽고 정리 / 요약 한 내용입니다.

빅데이터 처리 시스템

1. PostgreSQL

• 오픈 소스 RDB(Relational Database)로 다양한 플랫폼 지원 및 GUI 인스톨러 제공
• 다른 오픈 소스 RDB와 비교시 표준 SQL을 잘 준수하며 윈도 함수, CTE(WITH 구문) 등 분석에 필수적으로 사용하는 구문 모두 구현 가능
• 많은 확장 기능을 제공하여 편리하게 사용 가능

2. Apache Hive

• HDFS(Hadoop File System)이라는 분산 파일 시스템 위의 데이터를 SQL 스러운 인터페이스로 간단하게 처리해주는 시스템
  • 거대한 데이터를 작게 분할하여 여러 개의 디스크에 분산하여 저장 각 디스크에 동시에 데이터를 읽어 들여 고속으로 대량의 데이터를 처리
• MapReduce 알고리즘을 통해 분산 파일 시스템 위의 데이터를 동시에 읽어 들인 데이터의 순서를 맞춤
• Hive는 HDFS, MapReduce 아키텍처를 구현한 Apache Hadoop의 생태계의 일부
• HiveQL 이라는 SQL스러운 쿼리 언어로 작성한 쿼리를 자동으로 MapReduce 잡으로 변환하여 간단하게 병렬 분산 처리가 가능

Hive의 특징

• 파일 기반시스템이므로 특정 레코드 하나를 변경하거나 제거하는 것이 어렵고 인덱스도 디폴트로 존재하지 않아 쿼리 실행 때 파일 전체를 조작(다만 최근에는 파일 형식과 서브시스템이 등장)
• 쿼리 실행시 동적으로 데이터를 정의, 데이터를 HDFS위에 그냥 축적해두고 이후 필요한 시점에 동적으로 스키마를 정의
• 풍부한 UDF(User-Defined Function)을 활용해 SQL만으로 구현하기 어려운 문자열 처리 등을 간한하게 사용
• 처리율(throughput)높이기 위한 아키텍처를 가지고 있어 리액턴시가 낮은 처리를 요구하는 경우는 적합하지 않음
• 추가로 쿼리를 실행시 HiveQL로 작성된 처리를 자바 코드를 변환 후 생성된 jar를 각각의 연산 노드에 배치하고 처리를 시작하는 복잡한 과정을 거치므로 간단한 쿼리라도 결과를 얻는데 많은시간이 걸리는 경우도 꽤 있음

3. Amazon Redshift

• AWS에서 제공하는 분산 병렬 RDB
• Hive 처럼 분산 환경에서 병렬 처리를 하는 것은 비슷하지만 Redshift는 그냥 RDB이므로 레코드를 업데이트 및 제거가 가능하고 트랜잭션도 지원
• PostgreSQL과 호환성을 가지므로 ODBC/JDBC 드라이버 혹은 psql 클라이언트에서 Redshift에 접속 가능

Redshift 특징

• RDB에서 다룰 수 없는 대량의 데이터와 상호 작용하는 쿼리를 실행하고 싶을 때 효과적
• Hive에서 MapReduce를 사용한 분산 처리를 실행할 경우 가벼운 잡이라도 10초 이상 걸리나 Redshift는 굉장히 짧은 시간(몇 밀리 초) 만에 결과를 리턴
• 클라우드 서비스이므로 데이터가 많아질 경우 스케일 아웃 등 쉽게 변경 가능
• 사용기간에 따라 비용 발생 - 최소 구축이여도 1년간 1,000만원 비용이 발생
• 성능 튜닝 및 비용을 줄이기 위한 최적의 노드수를 구성하기 위해서는 전문적인 지식이 필요
• 컬럼 기반 스토리지를 사용
  • 데이터를 물리적으로 레코드 별이 아닌 컬럼별로 저장하는 아키텍처
  • 데이터의 압축율을 높일 수 있고 쿼리 실행 때 디스크 I/O를 줄일 수 있음
  • 일반적인 RDB의 정규화를 사용하기 보다 분석에 필요한 데이터를 모두 하나의 컬럼에 추가하는 형태로 진행
  • 모든 컬럼을 추출하는 쿼리는 성능이 괸장이 낮게 나오므로 필요한 컬럼만 추출하는 쿼리를 사용

4. Google BigQuery

• 빅데이터 분석을 위해 구글이 제공하는 클라우드 서비스
• Redishift와는 다르게 직접 노드 인스턴스를 관리할 필요가 없고 읽어 들인 데이터의 양으로 비용이 발생
• 다루는 데이터가 적으면 적은 비용으로 운용이 가능
• 유료버전의 구글 애널리틱스를 사용하면 데이터를 쉽게 BigQuery로 넘겨서 처리
• 구글이 제공하는 다른 클라우드 서비스와도 쉽게 연동

BigQuery 특징

• 레거시 SQL, 스탠다드 SQL(표준 SQL) 두 종류가 존재
• 일반적인 SQL과 같은 방식으로 CTE(WITH 구문), 상관 서브 쿼리 등을 지원하며 일반적인 SQL과 같은 방식으로 쿼리를 작성 가능
• 읽어 들이는 데이터양을 기반으로 비용이 발생하여 사용 요금을 생각보다 예측하기 어려움
  • 결과가 레코드 하나라도 결과를 계산하기 위해 대량의 데이터를 로드한다면 비용이 많이 발생
  • 실행 때 데이터 로드를 줄일 수 있도록 자주 읽어 들이는 데이터만 모아서 별도의 테이블로 분할
  • 필요한 컬럼만 SELECT 하는 구문을 활용

5. SparkSQL

• MapReduce를 사용한 분산 처리 프레임워크인 Apache Spark의 기능 중에서 SQL 인터페이스와 관련된 기능을 나타내는 용어
• Spark는 오픈소스 프레임워크로 가장 빠른 속도로 개발이 이루어지고 있음
• 기계학습, 그래프 처리, 실시간 스트리밍 등 다양한 처리를 쉽게 분산 처리 할 수 있는 기능 제공

Spark 특징

• 빅데이터 활용과 관련된 대부분의 처리를 한번에 구현
• Spark는 인터페이스로 SQL 뿐만 아니라 파이썬, 스칼라, 자바, R 등 프로그래밍 언어를 지원하고 데이터의 익스포트, 임포트 기능이 다양
• DataFrames API를 사용하여 SQL스러운 선언적인 구문으로 데이터를 조작 뿐만 아니라 절차적인 프로그래밍과 비슷한 방법으로 프로그램 구현

데이터

데이터의 종류

업무 데이터

• 서비스와 시스템을 운용하기 위한 목적으로 구축된 데이터베이스에 존재하는 데이터
• 업무 데이터의 대부분은 갱신형 데이터
• 업무 데이터는 트랜잭션 데이터와 마스터 데이터로 분류 할 수 있음

트랜잭션 데이터

• 사용자 행동을 기록한 데이터 (구매, 리뷰, 게임 플레이 데이터 등)
• 데이터에는 날짜, 시각, 마스터 데이터의 ID 등을 포함하는 경우가 많고 사용자 또는 운용상 이유로 변경 혹은 제거가 가능하다
• 리포트 추출시 마스터 데이터(회원의 성별, 주소, 상품 카테고리 등)의 ID가 저장된 경우가 많으므로 곧바로 추출할 수 없는 경우가 있음

마스터 데이터

• 서비스와 시스템을 정의하고 있는 데이터 (회원 정보, 카테고리 마스터, 상품 마스터 등)
• 트랜잭션 데이터와 마스터 데이터를 결합하여 리포트 업무의 폭을 넓힐 수 있음
• 트랜잭션 데이터만으로는 분석 범위가 한정되어 버리므로 트랜잭션 데이터에 포함된 마스터 데이터는 리포트 업무 전 확인이 필요

로그 데이터

• 통계 또는 분석을 주 용도로 설계된 데이터
• 특정 태그를 포함해서 전송된 데이터
• 특정 행동을 서버 측에 출력한 데이터
• 출력 시점에 정보를 축척해두는 누적형 데이터
• 로그 출력 이후 정보가 변경되어도 기존의 데이터는 수정하지 않음

특징과 주의점

업무 데이터의 특징

데이터의 정밀도가 높다.

• 업무데이터는 데이터를 처리를 하는 중 문제가 발생하면 트랜잭션과 롤백이라는 기능을 사용하여 문제를 제거한다. 정합성이 보장
• 정확한 값이 요구되는 매출 관련 리포트 등을 만들 때 업무 데이터를 사용

갱신형 데이터

• 다양한 데이터 추가, 갱신, 제거 등이 실행
• 사용자가 탈퇴한 경우 데이터를 물리적으로 제거
• 주문을 취소하는 경우 플래그를 통해 상태를 변경해서 논리적으로 제거
• 이사 등으로 주소가 변경된 경우 사용자 정보 갱신

다루어야 하는 테이블의 수가 많다

• 데이터의 확장성을 배제, 데이터의 정합성을 쉽게 유지하기 위해 RDB를 사용하는 경우가 많음
• ER 다이어그램을 통해 설계 문서를 파악하고 여러 테이블을 결합해야 데이터 전체 내용을 파악 가능

업무 데이터의 축적 방법

모든 데이터를 변경하기

• 날짜를 기반으로 데이터가 계속 누적되는 경우가 아니면 데이터 전체를 한꺼번에 바꾸어 최신 상태로 만듬
• 모든 데이터를 한꺼번에 바꿔버리면 항상 최신 상태가 저장되나 과거의 정보를 읽어버리게 되므로 주의

모든 레코드의 스냅샷을 날짜별로 관리

• 출력 결과가 추출 시점에 따라 달라지면 신뢰성이 낮아짐
• 데이터 용량 측면에서는 좋지 않지만 모든 레코드를 날짜별로 누적하여 신뢰성을 보장

어제와의 변경 사항만 누적

• 트랜잭션 데이터 중 변경/삭제 없이 추가만 일어나는 테이블은 변경해도 상관없지만 어제 데이터와의 차이만 누적해도 상관없음

업무 데이터 다루기

매출액, 사용자 수처럼 정확한 값을 요구할 경우 활용

• 트랜잭션 기능으로 인해 데이터의 정합성이 보장되어 SQL을 잘못 작성하는 일이 없는 한 추출 결과 신뢰가 가능
• 로그 데이터는 전송방법에 따라 중간 손실이 발생할 수 있어 정확한 값을 요구할 때는 업무 데이터를 사용

서비스의 방문 횟수, 페이지 뷰, 사용자 유도 등의 데이터 분석에는 사용할 수 없음

• 사용자 에이전트 등 업무적으로 크게 필요하지 않는 정보는 서비스 처리에 영향을 줄 수 있으므로 업무 데이터로는 사용하지 않음
• 사이트 방문 횟수, 사용자 유도 상태 등을 분석하려면 업무 데이터가 아니라 로그 데이터를 사용해야 한다.

데이터 변경이 발생할 수 있으므로 추출 시점에 따라 결과가 변화할 수 있음

• 리포트를 만들어 제출할 때 추출 시점의 정보를 기반으로 작성된 리포트인 것을 명시
• 업무 데이터 분석을 염두해 둔다면 업무 데이터 변경의 영향을 최소화하며 데이터를 축적할 수 있는 방법을 찾아야함
• 업무데이터는 정합성을 확보하는 형태로 구성되므로 리포트를 만들때는 정합성을 완벽하게 보장할 수는 없으므로 데이터를 어떻게 축적하면 좋을지 여러 가지 방법을 검토

로그 데이터의 특징

시간, 사용자 엔드 포인트, IP, URL, 레퍼러, Cookie 등의 정보 저장하기

• 로그 데이터는 서비스의 처리에 영향이 거의 없는 사용자 엔드포인트, IP 주소, URL, 레퍼러, Cookie 등의 정보를 저장

로그 데이터는 추출 방법에 따라 데이터 정밀도가 달라짐

• 로그를 어떻게 추출하는지, 집계 대상 데이터가 어떤 상태로 있는지 제대로 파악하지 않고 사용하면 잘못된 판단을 내릴 수 있음

계속 기록을 추가하는 것뿐이므로 과거의 데이터가 변경되지는 않음

• 출력 시점의 정보를 기록하는 것이므로 상품의 가격을 변경하더라도 과거의 로그 데이터가 변경되지는 않음

로그 데이터의 축적 방법

태그, SDK를 통해 사용자 장치에서 데이터를 전송하고 출력하기(비컨 형태)

• 구글 애널리틱스처럼 HTML에 특정 태그를 집어넣고 데이터를 전송하는 형식을 나타내는 방법
• 웹사이트에서 자바스크립트를 통해 로그 데이터를 전송하는 경우 자바스크립트를 해석할 수 없는 크롤러 또는 브라우저의 데이터는 로그로 출력되지 않음

서버에서 데이터를 추출하고 출력하기(서버형태)

• 클라이언트에서 별도 처리를 하지 않고 서버에서 로그를 출력하는 방법
• 이상 크롤러의 접근도 출력이 될 수 있으므로 잘못된 판단을 내릴 수 있음 따라서 의도하지 않은 로그를 제거하는 과정을 반드시 거쳐야 함

로그 데이터 다루기

사이트 방문 횟수, 페이지 뷰, 사용자 유도 상황을 집계하고 분석할 때 주로 사용

최신 상태를 고려한 분석에는 적합하지 않음

• 로그 출력 이후 데이터의 변경 내용을 모두 고려해서 분석할 때는 별도의 데이터 가공이 필요

계속 기록을 누적하는 형태이므로 추출 결과가 변할 가능성이 적음

• 로그 데이터는 변경/제거 되지 않으므로 기간을 지정해서 집계했을 때 쿼리 결과가 바뀌지 않음

데이터의 정확도는 업무데이터에 비해 낮음

• 로그 추출 방법에 따라 누락되거나 크롤러의 로그가 함께 포함되어 집계될 가능성이 있으므로 정확한 값이 필요한 경우에는 적합하지 않음