BigQuery의 REPEATED 필드와 RECORD 형식 데이터 완벽 가이드

BigQuery를 사용하다 보면 테이블에 RECORD 형식의 데이터가 포함된 경우를 자주 볼 수 있습니다. 이는 JSON 형태의 중첩된 데이터를 효율적으로 저장하고 조회할 수 있도록 도와줍니다. 이번 글에서는 RECORD 형식의 데이터를 다루는 방법을 자세히 설명하고, 실제 데이터 조회 및 응용 사례까지 다뤄보겠습니다.

Table of Contents

1. RECORD 형식이란?

BigQuery의 RECORD 형식은 테이블 내에서 하나의 열(Column)에 여러 개의 속성(필드)이 포함될 수 있도록 하는 구조입니다. 이는 관계형 데이터베이스의 정규화된 데이터 모델과는 다르게, 중첩된 데이터를 쉽게 표현할 수 있도록 도와줍니다.

📌 RECORD 형식의 예제

아래와 같은 GA4의 event_params 및 user_properties 데이터를 BigQuery 테이블에 저장한다고 가정해 보겠습니다.

{
  "event_bundle_sequence_id": 12345,
  "event_name": "purchase",
  "event_params": [
    {
      "key": "currency",
      "value": { "string_value": "USD" }
    },
    {
      "key": "value",
      "value": { "double_value": 49.99 }
    }
  ],
  "user_properties": [
    {
      "key": "user_id",
      "value": { "string_value": "user_123" }
    },
    {
      "key": "loyalty_status",
      "value": { "string_value": "gold" }
    }
  ]
}

위 데이터를 BigQuery의 테이블 스키마로 표현하면 다음과 같이 됩니다.

Column Name	Type	Mode
event_bundle_sequence_id	INTEGER	NULLABLE
event_name	STRING	NULLABLE
event_params	RECORD	REPEATED
├── key	STRING	NULLABLE
├── value	RECORD	NULLABLE
│ ├── string_value	STRING	NULLABLE
│ ├── int_value	INTEGER	NULLABLE
│ ├── double_value	FLOAT	NULLABLE
user_properties	RECORD	REPEATED
├── key	STRING	NULLABLE
├── value	RECORD	NULLABLE
│ ├── string_value	STRING	NULLABLE
│ ├── int_value	INTEGER	NULLABLE
│ ├── double_value	FLOAT	NULLABLE

event_params와 user_properties 필드는 REPEATED RECORD 형식으로, 여러 개의 속성을 포함할 수 있습니다.

2. RECORD 데이터 조회하기

📌 기본적인 조회

RECORD 필드를 포함한 데이터를 조회할 때는 . 연산자를 사용하여 하위 필드에 접근할 수 있습니다.

SELECT
  event_bundle_sequence_id,
  event_name,
  event_params.key AS param_key,
  event_params.value.string_value AS param_string_value,
  event_params.value.double_value AS param_double_value
FROM `my_project.my_dataset.events_table`

📌 위 쿼리의 결과는 다음과 같이 표시됩니다:

event_bundle_sequence_id	event_name	param_key	param_string_value	param_double_value
12345	purchase	currency	USD	NULL
12345	purchase	value	NULL	49.99

📌 UNNEST()를 사용한 조회

BigQuery에서 REPEATED RECORD 형식을 개별 행으로 변환할 때는 UNNEST() 함수를 사용합니다.

SELECT
  event_bundle_sequence_id,
  event_name,
  ep.key AS param_key,
  ep.value.string_value AS param_string_value,
  ep.value.double_value AS param_double_value
FROM `my_project.my_dataset.events_table`, UNNEST(event_params) AS ep;

UNNEST(event_params)를 사용하면 event_params 필드의 각 요소가 개별 행으로 변환됩니다.

3. RECORD 데이터 활용하기

📌 특정 이벤트의 특정 파라미터 값 조회

예를 들어, purchase 이벤트에서 value 파라미터 값을 조회하고 싶다면 다음과 같은 쿼리를 작성할 수 있습니다.

SELECT 
  event_bundle_sequence_id,
  event_name,
  ep.value.double_value AS purchase_value
FROM `my_project.my_dataset.events_table`, UNNEST(event_params) AS ep
WHERE event_name = 'purchase' AND ep.key = 'value';

📌 사용자의 특정 속성 조회

사용자의 loyalty_status를 조회하려면 user_properties에서 loyalty_status를 필터링해야 합니다.

SELECT 
  up.value.string_value AS loyalty_status
FROM `my_project.my_dataset.events_table`, UNNEST(user_properties) AS up
WHERE up.key = 'loyalty_status';

📌 결과 예시:

loyalty_status

gold

📌 JSON 형태로 다시 변환하기

분석 후 데이터를 JSON 형태로 변환하고 싶다면 ARRAY_AGG()와 STRUCT()를 활용할 수 있습니다.

SELECT
  event_bundle_sequence_id,
  event_name,
  ARRAY_AGG(STRUCT(ep.key, ep.value.string_value, ep.value.double_value)) AS event_params
FROM `my_project.my_dataset.events_table`, UNNEST(event_params) AS ep
GROUP BY event_bundle_sequence_id, event_name;

4. 결론

BigQuery의 RECORD 형식은 JSON과 유사한 계층적 데이터를 효율적으로 저장하고 분석하는 데 유용합니다. 특히 UNNEST() 함수를 활용하면 중첩된 데이터를 손쉽게 펼쳐서 분석할 수 있으며, 이를 활용한 다양한 데이터 분석 기법을 적용할 수 있습니다.

GA4 데이터를 다룰 때 event_params와 user_properties를 적절히 활용하면 보다 직관적이고 강력한 데이터 모델을 설계할 수 있습니다. 이를 바탕으로 다양한 응용 사례를 실무에 적용해 보세요!