04. 쿼리 엔진

SQL-On-Hadoop의 예시: 'Hive'에 의한 구조화 데이터의 생성과 'Presto'에 의한 대화식 쿼리

데이터 마트 구축의 파이프라인

1. 분산 스토리지에 저장된 데이터를 구조화하고 열 지향 스토리지 형식으로 저장

• 다수의 텍스트 파일을 읽어 가공하므로 부하가 큰 작업이므로 Hive 사용

2. 완성된 구조화된 데이터를 결합, 집계하고 비정규화 테이블로 데이터 마트에 내보냄

• 열 지향 스토리지를 이용한 쿼리 실행에는 실행 시간 단축을 위해 Presto 사용

Hive 메타 스토어(Hive Metastore)

• Hive에서 만든 각 테이블의 정보를 저장하는 특별한 데이터베이스
• Hive뿐만 아니라 다른 쿼리 엔진에서도 공통의 테이블 정보로 참고됨(SQL-on-Hadoop 상황)

Hive에 의한 구조화 데이터 작성

외부 테이블(external table): Hive의 외부에 있는 특정 파일을 참고해 마치 테이블이 본재하는 것 처럼 읽어 들이기 위해 지정

• Hive에서 CSV 파일을 읽어 들이기 위해, CSV 파일을 외부 테이블로 정의하고 이 테이블에서 데이터를 추출해 구조화 데이터로 변환

$ Hive
...
-- 외부 테이블 'access_log_csv' 정의
hive> CREATE EXTERNAL TABLE access_log_csv(
    >  time string, request string, status int, bytes int
    > )
    > -- CSV 형식임을 지정
    > ROW FORMAT SERDE 'org.apche.hadoop.hive.serde2.OpenCSVSerde'
    > -- 경로 지정(디렉터리 내의 모든 파일이 읽혀짐)
    > STORED AS TEXTFILE LOCATION '/var/log/access_log/'
    > -- CSV의 헤더행 스킵
    > TBLPROPERTIES ('skip.header.line.count'='1');

• SQL-on-Hadoop의 쿼리 엔진은 MPP 데이터베이스처럼 데이터를 낸부로 가져오지 않아도 텍스트 파일을 그대로 집계 가능
  • 애드 혹 데이터 분석에 유리
  • 시간을 들이는 데이터 전송 없이 원하는 정보를 얻을 수 있음

-- 상태별로 레코드 수 세기
hive> SELECT status, count(*) cnt
    > FROM access_log_csv GROUP BY status LIMIT 2;
...

• 그러나 CSV 파일을 그대로 집계하는 것은 비효율적
  • 쿼리 실행 시 매번 텍스트를 읽어오기 때문에 처리 속도 느림
• 따라서 열 지향 스토리지로 변환

열 지향 스토리지로의 변환: 데이터 집계의 고속화(배치형 쿼리 엔진용)

테이블을 열 지향 스토리지 형식인 ORC 형식으로 변환. Hive의 경우, 테이블마다 스토리지 형식 지정 가능

• 새로운 테이블을 만들고, 외부 테이블에서 읽은 데이터 모두 저장

-- ORC 형식의 테이블 'access_log_orc'로 변환
hive> CREATE TABLE access_log_orc STORED AS ORC AS
    > SELECT cast(time AS timestamp) time,
    >         request,
    >         status,
    >         cast(bytes AS bigint) bytes
    > FROM access_log_csv;

-- ORC 형식의 테이블 집계
hive> SELECT status, count(*) cnt
    > FROM access_log_orc GROUP BY status LIMIT 2;
...

• 텍스트 데이터를 열 지향 스토리지로 변환함으로써 데이터의 집계 속도 크게 고속화 됨
• Hive에서 읽을 수 있는 형식(텍스트, 스키마 리스 데이터 등)이라면 쿼리 수정 만으로도 테이블 생성 가능

Hive로 비정규화 테이블 작성

데이터 구조화가 완료되면 데이터 마트를 구축함. 즉, 테이블을 결합 및 집약하여 '비정규화 테이블'을 만들어냄

• Presto 같은 대화형 쿼리엔진이나 Hive 같은 배치형 쿼리 엔진 사용
  • 시간이 걸리는 배치 처리이므로 Hive를 사용하는 편이 좋음 -> 리소스의 이용 효율 높임
  • 쿼리 엔진은 대량의 데이터를 처리하는 과정에서 최종 실행 시간에 많은 영향을 끼치지 않음
• Hive에서 가능한 효율적인 쿼리 작성해야 함
  • 서브 쿼리 안에서 레코드 수를 줄이는 방법
  • 데이터의 편향을 방지하는 방법

서브 쿼리 안에서 레코드 수 줄이기: 초기 단계에서 팩트 테이블 작게 하기

Hive는 데이터베이스가 아닌 데이터 처리를 위한 배치 처리 구조. 따라서 읽어 들이는 데이터 양을 의식하면서 쿼리를 작성하지 않으면 생각한 성능이 나오지 않음.

• 대량의 중간 데이터가 생성되는 쿼리를 피하기
• 가능한 초기에 팩트 테이블을 작게 하는 것 중요
• 즉, 테이블의 양을 감소시킨 후 테이블 결합

-- 1. 비효율적인 쿼리의 예
-- 테이블을 결합한 후에 WHERE로 검색
SELECT ...
FROM access_log a
JOIN users b ON b.id = a.user_id
WHERE b.created_at = '2017-01-01'

-- 2. 효율적인 쿼리의 예
SELECT ...
FROM(
-- 처음에 시간으로 팩트 테이블 검색
    SELECT * access_log
    WHERE time >= TIMESTAMP '2017-01-01 00:00:00'
)a
JOIN users b ON b.id = a.user_id
WHERE b.created_at = '2017-01-01'

데이터 편향 피하기: 분산 시스템의 성능 발휘를 방해

고속화를 방해하는 또 다른 문제는 '데이터 편차(data skew)'

• 만약 웹 페이지당 고유 방문자 수를 알고 싶다고 가정하면, 웹 페이지마다 이용자 수의 편차가 있기 때문에 한 페이지에 대한 쿼리 실행 시간이 늘어남
  • 그 페이지 실행이 끝나야만 전체 작업이 완료되므로 전체 실행 시간에 영향을 미침
• 분산 시스템의 성능을 발휘하기 위해서는 데이터 편차를 최대한 없애고, 몯은 노드에 데이터가 균등하게 분산되도록 해야 함
• 데이터 편차가 발생하기 쉬운 구문으로는 테이블 결합과 OREDER BY에 의한 정렬 등이 있음

-- 1. 비효율적 쿼리의 예
-- distinct count는 분산되지 않는다.
SELECT date, count(distinct user_id) users
FROM access_log GROUP BY date

-- 2. 효율적인 쿼리의 예
SELECT date, count(*) users
FROM(
    -- 최초에 중복 제거
    SELECT DISTINCT date, user_id FROM access_log
) t
GROUP BY date

---

대화형 쿼리 엔진 Presto의 구조: Presto로 구조화 데이터 집계

Hive와 같은 배치형 쿼리 엔진은 대규모 데이터 처리에 적합하나, 작은 쿼리 실행하는 대화형 데이터 처리에는 적합하지 않음. 쿼리 실행의 지연 감소를 위해 개발된 것이 '대화형 쿼리 엔진'

• Presto: 2013년 말 페이스북에서 출시

플러그인 가능한 스토리지: 하나의 쿼리 안에서 여러 데이터 소스에 연결 가능

Presto는 전용 스토리지를 작고 있지 않으므로 Hive와 마찬가지로 다양한 데이터 소스에서 직접 데이터를 읽어 들임

• Presto는 Hive 메타 스토어에 등록된 테이블을 가져올 수 있음
  • Hive에서 만든 구조화 데이터를 좀 더 집계하는 목적에 적합
• Presto가 성능을 최대한 발휘하기 위해서는 원래 스토리지가 열 지향 데이터 구조로 되어 있어야 함
  • ORC 형식의 로드에 최적화 되어 있음
• Hive 메타 스토어 이외에도 다양한 데이터 소스 참고 가능
  • 예를 들어, 하나의 쿼리 안에서 분산 스토리지 상의 팩트 테이블과 MySQL의 마스터 테이블을 조인 가능
  • Cassandra와 같은 NoSQL 데이터베이스에 저장된 데이터 집계도 가능

CPU 처리의 최적화: 읽기와 코드 실행 병렬 처리

코드의 실행이 멀티 스레드화되어 단일 머신에서 수백 태스크가 병렬로 실행됨. 열 지향 스토리지에서의 읽기도 병렬화됨

• Presto의 CPU 이용 효율이 높으므로 메모리와 CPU 리소스만 충분하다면 데이터의 읽기 속도가 쿼리 실행 시간을 결정함

인 메모리 처리에 의한 고속화: 쿼리 실행헤는 가급적 대화형 쿼리 엔진 사용

Hive와 달리 Presto는 쿼리 실행 과정에서 디스크에 쓰기 X. 모든 데이터처리 메모리 상에서 함

• 취급하는 데이터 양이 아무리 많아도 그에 비례하여 메모리 소비가 늘어나지는 않음
  • 예를 들어, GROUP BY에 의한 데이터의 집약은 단순한 반복 처리이므로 메모리 소비량은 거의 고정임
• 메모리 상에서 할 수 잇는 것은 메모리 상에서 실행하고, 디스크가 있어야 하는 일부 데이터 처리만을 Hive등에 맡기는 것이 효과적
• 하지만 그 이외 단시간 쿼리 실행에는 대화형 쿼리 엔진 사용이 효율적

분산 결합과 브로드 캐스트 결합

분산 결합(distriute join)

• 테이블 조인 시, 특히 2개의 팩트 테이블 결합하는 경우 매우 많은 조인 키를 메모리 상에 계속 유지해야 함
• Presto는 기본적으로 분산 결합을 실시하며, 같은 키를 갖는 데이터는 동일한 노드에 모임
• 노드 간 데이터 전송을 위한 네트워크 통신이 발생하므로 종종 쿼리의 지연을 초래함

브로드캐스트 결합(broadcast join)

• 처리 속도 크게 고속화 가능
• 결합하는 테이블의 모든 데이터가 각 노드에 복사됨
• 하나의 팩트 테이블에 복수의 디멘전 테이블을 결합하는 경우, 디멘전 테이블은 메모리에 충분히 들어갈 정도로 작은것이 대부분
  • 처음에 한번만 복사하면 팩트 테이블 재배치할 필요 없어 테이블의 결합이 훨씬 빨라짐
• Presto에서 브로드캐스트 결합을 유효로 하려면 분산 결합을 명식적으로 무효화 해야함열 지향 스토리지 집계

열 지향 스토리지 집계: Presto에 의한 고속 집계

Presto에서는 열 지향 스토리지의 집계를 매우 빠르게 할 수 있음