빅쿼리 Nested 칼럼을 CROSS JOIN으로 접근하면 데이터 분석을 망칩니다.
들어가는 글
Google Analytics 4의 BigQuery Export 기능을 사용 중인 분들이라면 누구나 경험해보신 “까다로운 칼럼 유형"이 있습니다. 바로 Nested Repeated 칼럼 유형입니다.
국내외 수많은 Medium 아티클, 티스토리, 그리고 심지어 SQL 온라인 강의 상에서 Nested Repeated 칼럼 유형에 대해 “CROSS JOIN” 방법을 추천하곤 합니다. 그러나 “CROSS JOIN”은 쿼리 가독성 측면에서 썩 좋은 방법이 아니며, 무엇보다도 데이터 분석을 위한 집계에 심각한 오류를 낳을 수 있는 위험성이 존재합니다.
본 아티클에서는 Nested Repeated 칼럼 유형의 정확한 의미, 그리고 CROSS JOIN의 위험성을 극복하기 위한 두 가지 대체 방법을 제시하도록 하겠습니다.
Nested Repeated 칼럼 유형이란 무엇인가
본격적으로 CROSS JOIN의 위험성에 대해 다루기 전에, 우선 Nested Repeated 성격을 지닌 칼럼이 정확히 무엇을 의미하는지 간결하게 짚어보겠습니다.
1. Nested Columns
Nested 칼럼 유형은 BigQuery 테이블 스키마에서 “RECORD” 이름으로 표현됩니다.
“큰 것 안에 작은 것을 넣다"라는 Nest의 사전적 정의를 고려한다면, Nested 칼럼이란 복수 개의 정보들을 한꺼번에 가지고 있는 칼럼을 의미합니다. 그리고 다음과 같이, Python의 딕셔너리 객체와 매우 유사한 (혹은 거의 똑같은) 형태를 지니고 있습니다.
{
"geo": {
"city": "Seoul",
"country": "South Korea",
"continent": "Asia",
"region": "Seoul",
"sub_continent": "Eastern Asia",
"metro": "(not set)",
...
}
}즉, geo라는 상위 필드 내에 city, country, continent 정보들이 key:value pair의 요소들로 구성되어 있는 것입니다. geo 필드를 시각적으로 표현해보면, 차원이 1개이므로 다음과 같이 벡터 형태로 표현할 수 있게 됩니다.
BigQuery에서 geo와 같은 Nested 칼럼을 조회하는 것은 매우 쉽습니다. Python에서 딕셔너리 객체의 각 값들을 리턴하기 위해 .{key} 메소드를 사용하는 것처럼 말이죠. 따라서 다음과 같이 매우 쉬운 쿼리문 작성을 통해 한국 고객의 접속 지역을 파악할 수 있습니다.
SELECT
geo.city
FROM
`project_name.analytics_*.events_yyyymmdd`
WHERE
geo.country = 'South Korea'
GROUP BY
geo.city
ORDER BY
COUNT(DISTINCT user_pseudo_id) DESC
LIMIT 3;2. Nested Repeated Columns
그러나 Nested Repeated 칼럼 유형은 좀 더 복잡합니다. 이 유형은 명칭 그대로, Dictionary 안에 여러 개의 Dictionaries를 가지고 있음을 의미합니다. 그래서 “Repeated”라는 용어가 추가된 것이죠.
대표적인 유형인 event_params 필드는 다음과 같은 형태를 지니고 있습니다.
{
"event_params": [
{
"key": "ga_session_number",
"value": {
"string_value": NULL,
"int_value": 1,
"float_value": NULL,
"double_value": NULL
}
},
...
{
"key": "ga_session_id",
"value": {
"string_value": NULL,
"int_value": 1700000000,
"float_value": NULL,
"double_value": NULL
}
},
{
"key": "page_location",
"value": {
"string_value": "https://google.com/data",
"int_value": NULL,
"float_value": NULL,
"double_value": NULL
},
}
]
}즉, event_params라는 상위 필드 내에 파라미터의 개수 만큼 Dictionaries가 존재하고, 각 Dictionary 안에는 key 정보와 value 정보가 key:value pair 요소들로 구성되어 있습니다. 특히, value 정보는 Data Type을 담은 또 다른 Dictionary를 한 번 더 품고 있습니다. event_params 필드를 시각적으로 표현해보면, 차원이 2개이므로 다음과 같이 매트릭스 형태로 표현할 수 있게 됩니다.
Nested Repeated 칼럼 유형은 Nested 칼럼 유형과 한 가지 중요한 점에서 차이가 있습니다.
- Nested 칼럼 유형은 key:value 쌍을 통해 정보를 쉽게 조회할 수 있습니다. 가령, geo.country를 통해 ‘South Korea’ 값을 즉각적으로 확인할 수 있는 것이죠.
- 그러나 Nested Repeated 칼럼 유형은 key:{Key 이름}, value.{datatype}_value:{Key 이름에 대한 값} 형태를 지닙니다. 즉, 파타미터의 이름과 파라미터 이름의 값이 서로 분리되어 있는 것이죠.
BigQuery가 이렇게 사용성이 떨어지는 스토리지 설계를 선택한 이유는 무엇일까요?
첫째, Google Analytics 4 서비스의 확장성을 고려해야 하기 때문입니다.
Google Analytics 4의 디폴트 이벤트와 사용자 정의 이벤트 모두 시간이 흐름에 따라 파라미터가 추가되기도 하고 삭제되기도 합니다. 즉, 이벤트 파라미터는 매우 변동성이 큰 필드인 것입니다.
예를 들어, GA4의 데이터 수집 능력이 추후 향상되어 session_start 이벤트의 파라미터 개수가 늘어날 경우, GA4 Export 테이블의 칼럼 수가 늘어나거나, 혹은 행의 수가 늘어나는 방향으로 설계된다면 스토리지 비용이 크게 늘어나고 쿼리 속도에도 악영향을 끼치게 될 것입니다.
결국, 이벤트 파라미터 수가 변동되더라도 하나의 행과 칼럼만을 차지한 채, Nested & Repeated 유형 내에서 Internally Incremental한 방향으로 늘어나는 것이 효율적일 것입니다.
둘째, 이벤트 파라미터의 값들은 제각기 다른 Data Type으로 구성되어 있기 때문입니다.
하나의 칼럼은 하나의 Data Type만 허용하는 제약 조건을 지니고 있습니다. 그러나, ga_session_id 파라미터 값은 Integer이고, page_location 파라미터 값은 String입니다. 그렇기 때문에 파라미터 값을 String, Integer, Float, Double 등 확장 가능한 모든 Data Type을 제각기 담을 수 있는 Nested 형태로 표현될 수밖에 없는 것이죠.
CROSS JOIN의 위험성과 대체 방법
위에서 언급한 Nested Repeated 칼럼 유형을 조회하기 위해서는 두터운 차원을 하나의 차원으로 풀어헤치는 작업을 진행해야 합니다. 본 글에서는 CROSS JOIN, LEFT JOIN, 그리고 SELECT절의 서브쿼리까지 총 3가지 방법을 차례대로 소개하겠습니다.
1. CROSS JOIN
SELECT
ga_session_id.value.int_value AS ga_session_id,
ga_session_number.value.int_value AS ga_session_number,
page_location.value.string_value AS page_location
FROM
`project_name.analytics_*.events_yyyymmdd`
CROSS JOIN
UNNEST (event_params) AS ga_session_id
CROSS JOIN
UNNEST (event_params) AS ga_session_number
CROSS JOIN
UNNEST (event_params) AS page_location
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...
AND ga_session_id.key = 'ga_session_id'
AND ga_session_number.key = 'ga_session_number'
AND page_location.key = 'page_location'
;위 쿼리문은 “김쿼리”라는 사용자가 특정 시간에 발생시킨 session_start 이벤트 하나만을 가지고, ga_session_id, ga_session_number, page_location 파라미터 값을 확인하기 위해 작성된 것입니다.
안타깝게도 위와 같은 CROSS JOIN 방법은 매우 치명적인 위험성이 있습니다. 즉, 해당 이벤트가 수집하지 못한 파라미터를 CROSS JOIN 상에서 추출할 경우, 이벤트 전체가 결과 상에서 소실된다는 것입니다.
많은 분들이 경험하셨겠지만, 똑같은 session_start 이벤트라고 하더라도, 브라우저 쿠키 미수집, UTM 미지정, 혹은 GTM 자체의 내부 결함 등의 다양한 이유로 인해 특정 파라미터 자체가 수집이 안 되는 경우가 허다합니다.
대표적인 것이 utm_campaign, utm_medium, utm_source와 같은 파라미터입니다. 위의 쿼리문에서 page_location 대신, campaign 파라미터 값을 확인할 수 있도록 아래와 같이 수정해보겠습니다.
SELECT
ga_session_id.value.int_value AS ga_session_id,
ga_session_number.value.int_value AS ga_session_number,
campaign.value.string_value AS campaign
FROM
`project_name.analytics_*.events_yyyymmdd`
CROSS JOIN
UNNEST (event_params) AS ga_session_id
CROSS JOIN
UNNEST (event_params) AS ga_session_number
CROSS JOIN
UNNEST (event_params) AS campaign
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...
AND ga_session_id.key = 'ga_session_id'
AND ga_session_number.key = 'ga_session_number'
AND campaign.key = 'campaign'
;만일 김쿼리 사용자가 ?utm_campaign=… 이 없는 링크를 클릭하여 session_start 이벤트를 발생시켰다면, 이 이벤트의 파라미터에는 campaign에 해당하는 key와 value 자체가 존재하지 않습니다. 결국, 위 퀴리문은 다음과 같이 빈 결과를 출력하게 됩니다.
왜냐하면, 다음과 같이 campaign에 해당하는 Unnested 테이블 자체가 아무런 데이터가 없으므로, 모든 CROSS JOIN 자체가 한꺼번에 실패했기 때문입니다. 이는, 1 + 2 + 3 + 4 + NULL = NULL 로 출력되는 SQL의 특성과 매우 유사합니다. 따라서 CROSS JOIN을 통해 특정 세그먼트 사용자 수를 집계하는 쿼리문을 작성한다면, 사용자 수가 과소 집계될 가능성이 매우 높을 것입니다. 즉, 데이터의 오염으로 인해 잘못된 의사결정을 야기할 수 있는 것이죠.
...
CROSS JOIN
UNNEST (event_params) AS campaign
WHERE
...
AND campaign.key = 'campaign'
...이 뿐만 아니라, CROSS JOIN은 쿼리의 가독성 측면에서도 썩 좋은 방법이 아닙니다. 다시 한 번 아래의 쿼리문을 보면, ga_session_id 파라미터 값을 출력하기 위해 SELECT절, JOIN절, WHERE절까지 총 세 번의 Line을 사용하게 됩니다.
SELECT
ga_session_id.value.int_value AS ga_session_id, -- ga_session_id
ga_session_number.value.int_value AS ga_session_number,
page_location.value.string_value AS page_location
FROM
`project_name.analytics_*.events_yyyymmdd`
CROSS JOIN
UNNEST (event_params) AS ga_session_id -- ga_session_id
CROSS JOIN
UNNEST (event_params) AS ga_session_number
CROSS JOIN
UNNEST (event_params) AS page_location
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...
AND ga_session_id.key = 'ga_session_id' -- ga_session_id
AND ga_session_number.key = 'ga_session_number'
AND page_location.key = 'page_location'
;UNNEST를 할 때마다 최소한 3번의 Line을 사용해야 하기 때문에 가독성이 좋지 못하고, 특히 WHERE절이 복잡해짐으로써 정작 중요한 조건을 누락할 여지도 큽니다. 우리가 풀어헤칠 이벤트 파라미터의 개수가 50개라면, 쿼리문의 길이가 최소한 150 Lines가 되겠죠.
2. LEFT JOIN
SELECT
ga_session_id.value.int_value AS ga_session_id,
ga_session_number.value.int_value AS ga_session_number,
page_location.value.string_value AS page_location
FROM
`project_name.analytics_*.events_yyyymmdd`
LEFT JOIN
UNNEST (event_params) AS ga_session_id ON ga_session_id.key = 'ga_session_id'
LEFT JOIN
UNNEST (event_params) AS ga_session_number ON ga_session_number.key = 'ga_session_number'
LEFT JOIN
UNNEST (event_params) AS page_location ON page_location.key = 'page_location'
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...LEFT JOIN은 CROSS JOIN에서 발생한 “데이터 소실" 현상을 방지하는 데 도움을 주는 대체 방법입니다. LEFT JOIN의 개념상 우측 테이블의 NULL 형태가 좌측 테이블의 존재 자체를 방해하지 않기 때문입니다.
가령, 만일 쿼리문 작성시 오타가 발생하여 다음과 같이 page_location을 page_rocation으로 오입력했다고 하더라도, page_rocation 값만 NULL일 뿐, ga_session_id와 ga_session_number 값은 정상적으로 출력됩니다.
SELECT
ga_session_id.value.int_value AS ga_session_id,
ga_session_number.value.int_value AS ga_session_number,
page_rocation.value.string_value AS page_rocation
FROM
`project_name.analytics_*.events_yyyymmdd`
LEFT JOIN
UNNEST (event_params) AS ga_session_id ON ga_session_id.key = 'ga_session_id'
LEFT JOIN
UNNEST (event_params) AS ga_session_number ON ga_session_number.key = 'ga_session_number'
LEFT JOIN
UNNEST (event_params) AS page_rocation ON page_rocation.key = 'page_rocation'
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...쿼리 가독성 측면에서도 CROSS JOIN보다 우월합니다. 기존의 CROSS JOIN에서 WHERE문에도 파라미터 이름 조건들을 입력해야 했다면, LEFT JOIN에서는 이를 LEFT JOIN ON … 상에 곧바로 작성하면 되므로 WHERE문을 깨끗하게 유지할 수 있습니다.
그러나 LEFT JOIN 역시, SELECT절과 JOIN절에 중복으로 Line을 사용해야 하므로 중복 측면에서 가독성이 완벽하지 않습니다.
3. SELECT절의 서브쿼리
개인적으로 가장 추천하는 방법인 SELECT절의 서브쿼리는 CROSS JOIN과 LEFT JOIN에서 나타난 “데이터 소실" 현상을 해결하고 가독성도 극대화할 수 있습니다.
SELECT
(SELECT value.int_value FROM UNNEST (event_params) WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.int_value FROM UNNEST (event_params) WHERE key = 'ga_session_number') AS ga_session_number,
(SELECT value.string_value FROM UNNEST (event_params) WHERE key = 'page_location') AS page_location
FROM
`project_name.analytics_*.events_yyyymmdd`
WHERE
user_pseudo_id = '김쿼리'
AND event_name = 'session_start'
AND event_timestamp = ...SELECT절에서 각각 독립적인 서브쿼리로 이벤트 파라미터 값들을 로드하기 때문에, 특정 파라미터의 NULL 여부가 다른 파라미터 값의 소실에 전혀 영향을 주지 않습니다.
이 뿐만 아니라, JOIN절과 WHERE절에서 중복으로 Line을 사용한 점도 개선이 되어, Unnest를 하기 위해 SELECT절에서만 작성해도 됩니다. 쿼리 가독성이 극대화된 것이죠.
쿼리 속도와 연산 비용 비교
쿼리문을 작성할 때 데이터 소실 여부, 가독성 뿐만 아니라 쿼리의 속도와 연산 비용도 매우 중요한 고려사항일 것입니다. 상술한 세 가지 방법의 쿼리문을 직접 실행하여 비교해봤습니다.
쿼리문에서 읽은 테이블 정보
- 행 수: 약 10,000개
- 총 논리 바이트: 약 8 MB (모두 활성 스토리지. 장기 스토리지 X)
- 총 실제 바이트: 약 500 KB (모두 활성 스토리지. 장기 스토리지 X)
CROSS JOIN 실행 결과
- 경과 시간: 162 msec
- 사용한 슬롯 시간: 9 msec
- 셔플 바이트: 81 B
- 컴퓨팅: 6 msec
LEFT JOIN 실행 결과
- 경과 시간: 166 msec
- 사용한 슬롯 시간: 10 msec
- 셔플 바이트: 17 B
- 컴퓨팅: 7 msec
SELECT절 서브쿼리 실행 결과
- 경과 시간: 131 msec
- 사용한 슬롯 시간: 13 msec
- 셔플 바이트: 81 B
- 컴퓨팅: 8 msec
결론
쿼리 속도 측면에서는 SELECT절 서브쿼리 방법이 가장 우수하며, 연산 비용 측면에서는 LEFT JOIN 방법이 가장 우수합니다.
물론, 읽어오는 테이블의 스토리지 유형과 파티셔닝 상태, 사이즈 등에 따라 상반된 결과가 나올 수 있으니 참고만 해주시길 바랍니다.
나가는 글
데이터 분석을 위해 SQL 작성을 할 때는 레퍼런스를 참고하되, 항상 회의적인 시각으로 바라보는 것이 중요한 것 같습니다. 또한 쿼리문 내의 각 중간 과정마다 집계 결과를 검토하여 데이터 정합성이 훼손되지는 않는지 확인하는 과정을 거치는 습관을 가지는 것 또한 훌륭한 데이터 분석가가 되기 위한 마인드셋일 것입니다.
쿼리문 작성 방법에는 DB, 쿼리 엔진, 그리고 스토리지 환경에 따라 워낙 다양하므로 고정된 작성 방법이 아니라, 여러 가지 레퍼런스를 참고하며 유연한 작성 역량을 키워나가되 비판적으로 받아들이며 자기 것으로 체득해가야 할 것 같습니다.
CROSS JOIN을 통해 Nested Columns를 다루는 방법에 대해 비판적으로 고찰하는 역량을 기를 때까지 상당히 오랜 기간이 소요된 만큼, 저 역시 현재 당연하게 사용하고 있음에도 분명히 잘못 작성하는 습관이 군데 군데 있으리라 생각합니다. 항상 회의적인 시각을 유지하며 면밀하게 검토할 줄 아는 데이터 담당자가 되기 위해 노력하겠습니다.
