cumulative table design

누적 테이블 디자인

누적 테이블 디자인은 모든 데이터 엔지니어가 알아야 할 매우 강력한 데이터 엔지니어링 도구입니다.

이 디자인은 임의로 큰(최대 수 천일) 기간에 대한 효율적인 분석을 제공할 수 있는 테이블을 생성합니다.

다음은 이 패턴에 대한 상위 수준 파이프라인 설계의 다이어그램입니다.

우리는 처음에 엔터티가 무엇이든 기본적으로 일일 측정 항목 테이블을 구축합니다. 이 데이터는 업스트림에 있는 모든 이벤트 소스에서 파생됩니다.

일일 메트릭을 얻은 후 어제의 누적 테이블을 오늘의 일일 데이터로 FULL OUTER JOIN 하고 각 사용자에 대한 메트릭 배열을 구축합니다. 이를 통해 우리는 모든 기록을 스캔할 필요 없이 새로운 기록을 가져올 수 있습니다. (큰 성능 향상)

이러한 측정항목 배열을 사용하면 ARRAY_SUM 과 같은 항목을 사용하여 배열이 허용하는 시간 프레임에 관계없이 원하는 측정항목을 계산하는 모든 사용자의 기록에 대한 쿼리에 쉽게 응답할 수 있습니다.

분석 기간이 길어질수록 이 패턴은 더욱 중요해집니다!!

예시 사용자 활동 및 참여 누적

모든 쿼리 구문은 Presto/Trino 구문과 함수를 사용합니다. 이 예는 다른 SQL 변형에 대해 수정되어야 합니다!

우리는 날짜를 사용할 것입니다:

• 2022-01-01 현재 Airflow 용어로 이것은 {{ ds }} 입니다.
• Airflow 템플릿 용어로 어제 인 2021-12-31 은 {{ yesterday_ds}} 입니다.

이 예에서는 일일, 주간, 월간 활성 사용자뿐만 아니라 사용자의 좋아요, 댓글, 공유를 계산하기 위해 이 디자인을 구축하는 방법을 살펴보겠습니다.

이 경우 소스 테이블은 events 입니다.

• 사용자가 해당 날짜에 대한 이벤트를 생성하면 해당 날짜에 활성 상태가 됩니다.
• 테이블에는 like , comment , share 또는 view 와 같은 event_type 있습니다.

이에 대한 해결책이 다음과 같은 파이프라인을 실행하는 것이라고 생각하기 쉽습니다.

    SELECT 
        COUNT(DISTINCT user_id) as num_monthly_active_users,
        COUNT(CASE WHEN event_type = 'like' THEN 1 END) as num_likes_30d,
        COUNT(CASE WHEN event_type = 'comment' THEN 1 END) as num_comments_30d,
        COUNT(CASE WHEN event_type = 'share' THEN 1 END) as num_shares_30d,
        ...
    FROM events
    WHERE event_date BETWEEN DATE_SUB('2022-01-01', 30), AND '2022-01-01'

문제는 이러한 숫자를 생성하기 위해 매일 30일간의 이벤트 데이터를 스캔한다는 것입니다. 꽤 낭비적이지만 단순한 파이프라인입니다. 이벤트 데이터를 한 번만 스캔하고 이전 29일의 결과와 결합하면 되는 방법이 있어야겠죠? 데이터 과학자가 데이터를 쿼리하고 지난 N일 동안 사용자가 수행한 작업 수를 쉽게 알 수 있는 데이터 구조를 만들 수 있습니까?

이 디자인은 단 3단계만 거치면 매우 간단합니다.

일일 테이블 단계

• 이 단계에서는 오늘의 이벤트만 집계하여 누가 매일 활동하는지 확인합니다. 테이블 스키마는 여기에 있습니다.
• 이 쿼리는 여기에서 매우 간단하고 간단합니다.
  • GROUP BY user_id 그런 다음 이벤트가 있으면 일일 활성으로 계산합니다.
  • 일일 좋아요, 댓글, 공유 횟수를 파악하기 위해 몇 가지 COUNT(CASE WHEN event_type = 'like' THEN 1 END) 문을 추가합니다.

누적 단계

• 이 단계의 테이블 스키마는 여기에 있습니다.
• 이 단계의 쿼리는 훨씬 더 복잡합니다. 여기 있어요
  • 이 단계에서는 일일 테이블에서 오늘의 데이터를 가져오고 누적 테이블에서 어제의 데이터를 가져옵니다.
  • today.user_id = yesterday.user_id 에서 이 두 데이터 세트를 FULL OUTER JOIN
  • 신규 사용자인 경우 어제의 데이터에 포함되지 않습니다. 또한 사용자가 오늘 활동하지 않았다면 오늘의 데이터에 포함되지 않습니다.
  • 따라서 모든 사용자를 추적하려면 COALESCE(today.user_id, yesterday.user_id) as user_id 가 필요합니다.
  • 다음으로 activity_array 열을 작성하겠습니다. 우리는 지난 30일의 데이터만 activity_array 에 저장하기를 원합니다.
    • 따라서 CARDINALITY(activity_array) < 30 인지 확인하여 오늘의 값을 배열 앞에 추가할 수 있는지, 아니면 오늘의 값을 앞에 추가하기 전에 배열 끝에서 요소를 잘라야 하는지 확인합니다.
    • 사용자가 활성 상태가 아닐 때 배열에 0 값을 넣으려면 COALESCE(t.is_active_today, 0) 수행해야 합니다.
  • 우리는 activity_array 구축한 방법과 매우 유사한 패턴을 따르지만 좋아요, 댓글, 공유에도 마찬가지입니다!
  • 모든 메트릭 배열을 구축한 후에는 주간 및 월간 활동을 계산하는 것이 매우 간단합니다.
    • CASE WHEN ARRAY_SUM(activity_array) > 0 THEN 1 ELSE 0 END 배열 크기를 30으로 제한하므로 월별 활성 수를 제공합니다.
    • CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END 배열의 처음 7개 요소 (즉, 지난 7일) 만 확인하므로 주간 활성을 제공합니다.
  • 지난 7일 동안의 좋아요, 댓글, 공유 수를 합산하는 것도 쉽습니다.
    • ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7d 지난 7일 동안 이 사용자가 누른 좋아요 수를 제공합니다.
    • ARRAY_SUM(like_array) as num_likes_30d 배열이 해당 크기로 고정되었기 때문에 지난 30일 동안 이 사용자가 한 좋아요 수를 제공합니다.

DAG 단계

• DAG 예시는 여기에서 찾을 수 있습니다.
• DAG에 대해 기억해야 할 주요 사항은 다음과 같습니다.
  • 누적 DAG는 항상 depends_on_past: True 여야 합니다.
  • 일일 집계 단계는 누적 단계의 업스트림이어야 합니다.