[BigQuery] 와일드카드 테이블을 쿼리할 때 쓰는 _TABLE_SUFFIX

오늘은 BigQuery SQL에서 많이 사용하는 _TABLE_SUFFIX에 대해 알아보자. 특히, 여러 개의 일별 데이터 테이블을 대상으로 쿼리할 때 매우 유용한 문법이다.

_TABLE_SUFFIX는 BigQuery에서 와일드카드 테이블을 쿼리할 때 주로 사용된다. 와일드카드 테이블은 특정 테이블 이름을 기준으로 여러 테이블을 대상으로 쿼리하고자 할 때 편리하게 활용할 수 있는 기능이다. 예를 들어, 일별로 생성된 로그 테이블을 조회할 때, 모든 테이블 이름을 개별적으로 지정하지 않고도 필요한 데이터를 손쉽게 쿼리할 수 있다.

간단한 예시를 통해 이해해보자. 예를 들어, my_dataset.my_table_*와 같이 테이블 이름 뒤에 ”가 붙어 있다면, 이는 my_table_20230601, my_table_20230602 등 날짜별로 나뉜 모든 테이블을 대상으로 쿼리를 수행할 수 있다는 의미이다. 이때 _TABLE_SUFFIX는 각 테이블 이름에서 ” 부분에 매핑되는 문자열 값을 나타낸다. 예를 들어, 테이블 이름이 my_table_20230601이라면, _TABLE_SUFFIX의 값은 ‘20230601’이 된다.

이렇게 _TABLE_SUFFIX를 사용하면 특정 날짜 범위 내의 테이블만 선택하여 쿼리할 수 있다. 예를 들어 _TABLE_SUFFIX BETWEEN '20230601' AND '20230630'과 같이 조건을 사용하면, 2023년 6월 1일부터 6월 30일까지의 테이블만을 대상으로 쿼리를 수행하게 된다.

이제 GA4의 raw 데이터를 쿼리할 때의 실제 비즈니스 사례를 살펴보자. GA4 raw 데이터는 BigQuery와 쉽게 연동할 수 있는데. 이때 수집된 데이터는 BigQuery에 매일 자동으로 적재되며, 각 날짜별로 별도의 테이블이 생성된다. 이러한 일별 테이블은 일반적으로 events_YYYYMMDD 형식의 이름을 가지며, 수집된 이벤트 데이터를 일별로 관리할 수 있게 해준다.

이렇게 일별로 나누어진 테이블은 데이터를 효율적으로 관리하고, 필요한 날짜 범위에 따라 데이터를 빠르게 쿼리할 수 있는 장점이 있다. 예를 들어, 한 달 동안의 데이터를 분석하려면 해당 월에 해당하는 모든 일별 테이블을 와일드카드와 _TABLE_SUFFIX를 사용해 손쉽게 조회할 수 있다. 이 방식은 특히 데이터 양이 방대할 경우 처리 속도를 높이고 쿼리 비용을 절감하는 데 유리하다.

다음은 session_start 이벤트에 ga_session_id가 없는 이벤트의 개수 추이를 분석하기 위한 SQL 코드이다:

WITH session_start_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS event_date,
    EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)) AS year,
    EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)) AS month,
    user_pseudo_id,
    (
      SELECT ep.value.int_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'ga_session_id'
    ) AS ga_session_id
  FROM analytics_1234567.events_*
  WHERE event_name = 'session_start'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE('2023-06-01'))
                        AND FORMAT_DATE('%Y%m%d', DATE('2024-10-31'))
)

SELECT
  year,
  month,
  COUNT(*) AS total_events,
  SUM(CASE WHEN ga_session_id IS NULL THEN 1 ELSE 0 END) AS null_count,
  ROUND(SUM(CASE WHEN ga_session_id IS NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS null_percentage
FROM session_start_events
GROUP BY year, month
ORDER BY year, month;

이 쿼리를 보면 analytics_1234567.events_*와 같이 테이블 이름 뒤에 ‘*’가 붙어 있다. 이는 와일드카드를 사용하여 events로 시작하는 모든 테이블을 대상으로 쿼리를 수행하고자 하는 것이다. 이때, 각 테이블의 날짜를 나타내는 부분이 _TABLE_SUFFIX에 포함된다.

또한 PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)를 사용하여 _TABLE_SUFFIX를 날짜 형식으로 변환하고, 이를 이용해 이벤트 날짜(event_date), 연도(year), 월(month) 등을 추출할 수 있다. 이러한 방식으로 여러 테이블에서 공통된 이벤트를 효과적으로 분석할 수 있다.

이 쿼리의 두 번째 부분에서는 각 연도와 월별로 session_start 이벤트의 총 개수, ga_session_id가 비어 있는 경우(null)의 개수, 그리고 null 비율을 계산하고 있다. 이를 통해 특정 기간 동안 이벤트 데이터의 누락 여부를 쉽게 파악할 수 있다.

위 쿼리를 돌리면 볼 수 있는 결괏값이다. 최근들어서 null ga_session_id의 비중이 늘어난 것을 확인할 수 있다.

한 가지 주의할 점은, MSSQL과 같은 다른 SQL 데이터베이스에서는 _TABLE_SUFFIX와 같은 기능이 기본적으로 제공되지 않는다는 것이다. MSSQL에서는 여러 테이블을 대상으로 쿼리하려면 각각의 테이블을 UNION ALL로 연결하거나 동적 SQL을 작성해야 한다. BigQuery의 _TABLE_SUFFIX는 이러한 작업을 훨씬 간단하게 만들어주는 강력한 기능이다.

_TABLE_SUFFIX를 사용하면, 데이터가 일별로 적재되는 경우 여러 테이블을 간편하게 쿼리하고, 특정 기간의 데이터만 분석할 수 있다는 장점이 있다. GA4와 같이 대규모 데이터를 다룰 때 매우 유용하게 쓰이는 기능이다.