분석 요청을 SQL로 바꾸는 법: Hive 애널리틱스 신규 버전 구축기 2편

“이 이벤트 이후 매출이 얼마나 달라졌나요?”, “특정 채널로 유입된 유저의 리텐션은 어떤가요?”, “로그인 유저 대비 결제 유저 비율을 국가별로 볼 수 있나요?” 서비스를 운영하다 보면 이런 질문을 자주 마주친다. 질문 자체는 자연스럽지만, 이를 데이터로 답하기 위해서는 꽤 복잡한 과정을 거쳐야 한다.

분석가는 이벤트, 지표, 차원, 필터, 기간, 시간대, 세그먼트, 제외 유저 같은 조건을 조합해 결과를 보고 싶어 한다. 하지만 BigQuery 입장에서는 이 요청을 그대로 실행할 수 없다. 어떤 테이블을 조회할지, 어떤 필드가 실제로 존재하는지, 시간대는 어떻게 보정할지, 여러 이벤트를 어떻게 조합할지까지 모두 SQL로 풀어내야 한다.

이 글에서는 새로운 버전의 애널리틱스를 개발하면서 동적 분석 쿼리 생성기를 설계하게 된 배경과 해결 과정을 정리한다. 특정 내부 구현명이나 테이블명은 제외하고, 문제를 어떤 구조로 풀었는지에 초점을 맞췄다.

왜 동적 쿼리 생성기가 필요했을까?

처음에는 기능별로 SQL을 직접 만드는 방식도 가능해 보였다. 차트 화면은 차트용 SQL을 만들고, 퍼널 화면은 퍼널용 SQL을 만들고, 리텐션 화면은 리텐션용 SQL을 만들면 된다. 하지만 분석 기능이 늘어나면서 이 방식은 곧 한계에 부딪혔다.

가장 큰 문제는 기준의 일관성이었다. 같은 기간, 같은 프로젝트, 같은 필터를 사용했는데 차트와 퍼널, 리텐션이 서로 다른 기준으로 데이터를 해석하면 분석 도구의 신뢰도가 떨어진다. 시간대 보정, 프로젝트 그룹 조건, 제외 유저 조건, 중복 로그 제거 기준처럼 모든 분석에 공통으로 적용되어야 하는 규칙이 기능별 코드에 흩어져 있으면, 운영 중 문제의 원인을 추적하기도 어렵다.

또 다른 문제는 이벤트 저장 구조였다. 일부 이벤트는 정제된 조회용 테이블에서 바로 읽을 수 있지만, 일부 이벤트는 원본 로그의 JSON 속성에서 필요한 필드를 추출해야 한다. 이 차이를 기능마다 직접 처리하면 SQL 생성 로직은 빠르게 복잡해진다.

사용자 요청과 실행 SQL 사이의 간극

사용자 요청은 “무엇을 보고 싶은가”에 가깝다. 예를 들어 로그인 이벤트를 국가별로 나누고, 고유 유저 수를 일 단위로 보고 싶다는 식이다. 하지만 실행 가능한 SQL은 “어떻게 계산할 것인가”를 요구한다.

• 실제 조회 대상 테이블 또는 뷰를 결정해야 한다.
• 선택한 차원이 해당 이벤트에 존재하는지 확인해야 한다.
• 원본 로그라면 JSON 필드를 안전하게 추출해야 한다.
• 회사 또는 프로젝트 기준 시간대를 BigQuery timestamp 조건으로 변환해야 한다.
• 세그먼트와 스냅샷 조건을 분석 쿼리에 결합해야 한다.

사용자 요청을 바로 SQL 문자열로 만들기보다, 중간 표현으로 한 번 정규화한 뒤 분석 유형별 생성기로 넘기는 구조가 필요한 이유다.

공통 정규화 계층을 먼저 두다

해결 방향은 분석 요청을 몇 단계로 나눠 처리하는 것이었다. 요청을 받은 뒤 저장된 파라미터를 복원하고, 공통 분석 조건을 정규화한 다음, 차트·퍼널·리텐션 같은 분석 유형별 쿼리 생성기로 넘긴다.

공통 정규화 단계에서는 다음 작업을 먼저 처리한다.

• 회사 또는 프로젝트 기준의 기본 프로젝트 그룹 결정
• 사용자 시간대 기준 기간 조건 보정
• 분석 대상 이벤트에 존재하지 않는 차원 처리
• 전역 필터와 개별 이벤트 필터 병합
• 제외 유저 조건 적용
• 세그먼트/스냅샷 조건을 하위 쿼리 필터로 변환
• 지표 계산식 안에 포함된 다른 지표를 실제 이벤트 계산식으로 확장

이 단계를 먼저 거치면 이후 생성기는 자신이 맡은 SQL 형태에만 집중할 수 있다. 공통 규칙은 한곳에서 유지되고, 분석 유형별 차이는 생성기 단위로 분리된다.

일반 차트는 이벤트 단위 CTE로 쪼갠다

일반 차트에서 하나의 지표는 이벤트 하나만 사용할 수도 있고, 여러 이벤트의 계산식으로 구성될 수도 있다. 예를 들어 결제 유저 수를 로그인 유저 수로 나눈 전환율처럼 두 이벤트의 집계 결과를 조합해야 하는 경우가 있다.

이때 각 이벤트를 독립적인 CTE로 먼저 집계하고, 공통 차원 기준으로 조인한 뒤 최종 계산식을 적용하는 방식을 사용했다. 이벤트마다 존재하는 필드가 다를 수 있기 때문에, 이벤트 단위로 안전하게 집계한 뒤 결과를 합치는 편이 구조적으로 다루기 쉽다.

WITH
  event_a AS (
    SELECT normalized_date, dimension_key, COUNT(DISTINCT user_key) AS value
    FROM source_a
    WHERE ...
    GROUP BY normalized_date, dimension_key
  ),
  event_b AS (
    SELECT normalized_date, dimension_key, SUM(amount) AS value
    FROM source_b
    WHERE ...
    GROUP BY normalized_date, dimension_key
  )
SELECT
  COALESCE(event_a.normalized_date, event_b.normalized_date) AS normalized_date,
  COALESCE(event_a.dimension_key, event_b.dimension_key) AS dimension_key,
  IFNULL(event_a.value, 0) + IFNULL(event_b.value, 0) AS metric_value
FROM event_a
FULL OUTER JOIN event_b
  ON event_a.normalized_date = event_b.normalized_date
 AND event_a.dimension_key = event_b.dimension_key

퍼널과 리텐션은 별도 전략으로 분리한다

퍼널과 리텐션은 일반 차트와 SQL의 모양이 다르다. 퍼널은 단계별 이벤트를 순서대로 연결해야 하고, 리텐션은 기준 이벤트 이후 복귀 이벤트가 며칠 뒤 발생했는지를 계산해야 한다.

퍼널은 각 단계를 CTE로 만든 뒤 첫 단계 기준 또는 이전 단계 기준으로 다음 단계를 연결한다. 여기에 추적 기간 조건이 붙는다. 예를 들어 첫 이벤트 발생 후 N일 이내에 다음 이벤트가 발생했는지를 조인 조건에 포함해야 한다.

리텐션은 기준 이벤트와 복귀 이벤트를 분리하고, 날짜 프레임을 생성해 N일 후 복귀 수와 복귀율을 계산한다. 기준일과 복귀일 사이의 차이를 계산해야 하므로 날짜 처리와 조인 조건이 일반 차트보다 복잡하다.

분석 유형마다 SQL 구조가 다르기 때문에, 하나의 거대한 생성기에서 모든 경우를 처리하기보다 공통 정규화 이후 생성 전략을 분리하는 편이 유지보수에 유리했다.

운영에서 더 중요했던 것들

동적 SQL 생성기는 코드 문제이기도 하지만 운영 문제이기도 하다. 실제 서비스에서는 쿼리를 만들 수 있는지만큼, 만든 쿼리가 일관되고 안전하며 추적 가능한지가 중요했다.

결과 일관성

같은 조건이라면 어떤 분석 화면에서 조회하더라도 같은 기준으로 데이터를 해석해야 한다. 시간대 보정, 프로젝트 그룹 조건, 제외 유저 조건, 중복 제거 기준을 공통 계층에서 처리한 이유다.

BigQuery 비용과 응답 시간

동적 쿼리는 사용자가 선택한 조건에 따라 조회 범위가 크게 달라진다. 기간 조건을 필수로 두고, 필요한 컬럼만 선택하며, 중복 제거 기준을 명확히 하는 방식으로 불필요한 스캔을 줄였다.

동적 SQL 보안

사용자 입력을 그대로 SQL에 연결하지 않고, 가능한 경우 메타데이터에 등록된 필드만 허용했다. 세그먼트처럼 하위 쿼리가 필요한 경우에도 쿼리 생성 위치와 조건 조합 방식을 제한하는 것이 중요했다.

디버깅 가능성

운영 중 “결과가 이상하다”는 문의가 들어오면 실제로 어떤 SQL이 실행되었는지 확인할 수 있어야 한다. 동적 쿼리 시스템에서는 생성된 쿼리와 요청 조건을 추적할 수 있는 구조가 장애 분석의 출발점이 된다.

얻은 결과

이 구조를 통해 분석 유형별 구현은 분리하면서도 공통 규칙은 일관되게 유지할 수 있었다.

• 분석 요청 파싱과 SQL 생성 책임을 분리했다.
• 차트, 퍼널, 리텐션별 쿼리 생성 전략을 분리했다.
• 이벤트 메타데이터 기반 필드 검증을 공통화했다.
• 원본 로그와 조회용 테이블 처리 방식을 분리했다.
• 세그먼트/스냅샷 필터 변환을 공통화했다.
• 시간대, 제외 유저, 프로젝트 그룹 조건의 일관성을 확보했다.

특히 기능이 추가될 때 수정 범위가 줄었다. 새로운 분석 유형을 추가하더라도 기존 필터 처리나 시간대 보정 로직을 다시 만들 필요가 없고, 새로운 쿼리 생성 전략만 추가하면 된다.

동적 분석 쿼리 생성기는 자칫 단순한 문자열 조립 코드가 되기 쉽다. 하지만 운영 환경에서는 문자열을 어떻게 붙였는가보다 어떤 기준으로 쿼리를 만들었는가가 더 중요하다.

사용자 요청을 바로 SQL로 만들지 않고 중간 표현으로 정규화하면, 분석 조건을 검증하고 기능별 차이를 통제할 수 있다. 공통 규칙을 한곳에 모으면 결과의 일관성도 지킬 수 있다. 반면 동적 SQL은 확장성과 위험을 동시에 가지므로, 입력 검증과 실행 쿼리 추적이 반드시 함께 가야 한다.

BigQuery 기반 분석 시스템에서 중요한 것은 쿼리를 잘 만드는 것만이 아니다. 다양한 분석 요구가 들어와도 같은 기준으로 해석하고, 문제 발생 시 원인을 추적할 수 있는 구조를 갖추는 것이 더 본질적인 과제였다.