[2024.02.25] chapter3. 빅데이터의 분산 처리 (2) - MY

[chominyeong]

3-3 데이터 마트의 구축

팩트 테이블

시계열 데이터 축적

✅ 추가(append) : 기존의 데이터에 새로운 데이터만 추가 → 효율면에서는 추가가 유리

• 추가로 인해 발생하는 문제
  • 추가에 실패한 것을 알아채지 못하면 팩트 테이블의 일부에 결손 발생
  • 추가를 잘못해서 여러 번 실행하면 팩트 테이블의 일부가 중복
  • 나중에 팩트 테이블을 다시 만들고 싶은 경우 관리가 복잡
• 테이블 파티셔닝(table partitioning) 으로 문제 해결
  • 하나의 테이블을 여러 물리적인 파티션으로 나눔
  • 일반적으로 1일 1회, or 1시간에 1회로 파티션을 만들고 팩트 테이블에 붙인다.
  • 데이터 웨어하우스 구축하는 데 유용
    

✅ 치환(replace) : 과거의 데이터를 포함하여 테이블 전체를 치환

• 데이터 마트 구축에 유용
• 중간에 데이터가 중복되거나 빠뜨릴 가능성 거의 없음
• 스키마 변경 등에도 유연하게 대응
• 오래된 데이터는 자동으로 지워져서 데이터 마트가 계속 확대되는 일도 없음
• 데이터의 양이 너무 많다면 쓰는 데 시간이 오래 걸림
• MPP 데이터베이스로 쓰기를 병렬화하여 어느 정도 속도를 높일 수 있음

집계 테이블(summary table)

레코드 수 줄이기

• 팩트 테이블을 모아서 집계한 테이블
  • 일일 집계(daily summary) : 1일 단위로 집계

## 일별 액세스 수와 바이트 수 집계
hive> CREATE TABLE access_summary STORED AS ORC AS
    > SELECT time, status
    >        count(*) count, sum(bytes) bytes
    > FROM (
    > # 일자에서 'time'을 잘라냄
    >        SELECT cast(substr(time, 1, 10) AS date) time, status, bytes
    >        FROM access_log_orc
    >        # 집계 범위를 추려냄
    >        WHERE time BETWEEN '1995-07-10' AND '1995-07-20'
    > ) t
    > GROUP BY time, status  # 디멘전으로 그룹화
    > ;
...
Time taken: 4,321 seconds

## 51개의 레코드로 줄어들었다
hive> SELECT * FROM access_summary;
OK
1995-07-10    200    65970    1.376570168E9
1995-07-10    302    1472     101439.0
...
1995-07-10    404    639      0.0
Time taken: 0.124 seconds, Fetched: 51 row(s)

• 카디널리티(cardinality) : 각 칼럼이 취할 수 있는 값의 범위
• 집계 테이블을 작게 하려면 모든 칼럼의 카디널리티를 줄여야 한다.

스냅샷 테이블(snapshot table)

마스터 상태 기록

• 정기적으로 업데이트 되는 테이블을 통째로 저장 하는 테이블
• 스냅샷의 날짜를 지정하여 과거의 마스터 테이블 확인

# 2017-01-01 시점의 마스터 참고
WITH users AS (
    SELECT * FROM users_snapshot WHERE data = '2017-01-01'
)
# 팩트 테이블과 마스터 결합
SELECT ... FROM fact_table f
JOIN users u ON u.id = f.user_id

• 팩트 테이블과 스냅샷 테이블을 날짜를 포함해서 결합 ← 매일 변하는 마스터 정보를 이용해서 데이터 분석을 할때

SELECT ... FROM fact_table f
JOIN users_snapshot u ON u.id = f.user_id AND u.date = f.date

• 스냅샷은 특정 시점의 테이블의 상태를 기록한 것 → 나중에 다시 만들 수 없음
• 스냅샷 테이블은 데이터 레이크나 데이터 웨어하우스와 같은 영구적인 저장소에 저장하면 안된다.

이력 테이블

마스터 변화 기록

• 정기적으로 업데이트되는 테이블을 변경 내용만을 저장 하는 테이블
• 마스터 테이블을 복원하는 쿼리

SELECT * FROM (
    SELECT *,
        #'user_id'별로 최신 레코드를 1로 한 후 일련 번호를 붙인다.
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) number
    FROM users_history
    #최근 365일 동안의 데이터를 대상으로
    WHERE date >= current_date() - INTERVAL '365' DAYS
) t
#번호가 1(=최신)인 것만 채택
WHERE number = 1

디멘전 추가

비정규화 테이블 완성

• 팩트 테이블과 디멘전 테이블 결합 → 비정규화 테이블 생성

#세션 정보를 저장한 테이블 'sessions'를 작성
CREATE TABLE sessions AS
SELECT session_id,
       min(time) AS min_time,   #처음 액세스 시간
       max(time) AS max_time   #마지막 액세스 시간
FROM access_log GROUP BY session_id

• 세션 ID는 카디날리티가 매우 커서 테이블을 집약해도 작아지지 않는다 + 시각화도 안됨

• → 좀 더 카디널리티가 작은 디멘전을 만들어 결합하고, 시각화에 필요하지 않은 칼럼은 제거한다.
  

• 데이터를 집계하는 전형적인 쿼리 #7

# 쿼리의 결과는 데이터 마트에 써넣는다(또는 CSV로 보관)
SELECT
    - 디멘전
    date_trunc('day', a.time) time,       #1일 단위로 그룹화
    - 추가 디멘전
    date_diff('day', b.min_time, a.time) days,   #방문한 후의 일 수
    - 측정값
    count(*) count
FROM (
    # ⑴ 팩트 테이블로부터 필요한 칼럼만 추출
    SELECT time, session_id FROM access_log
    ## 우선 집계 기간을 검색한다
    WHERE time BETWEEN TIMESTAMP '2017-01-01' AND TIMESTAMP '2018-01-01'
) a
# ⑵ 디멘전 테이블과 결합
JOIN sessions b ON b.session_id = a.session_id
# ⑶ 그룹화
GROUP BY 1, 2