[BigQuery] 예제로 배우는 SQL로 JSON 데이터 처리하기

이번 글에서는 BigQuery SQL에서 JSON 형식의 데이터를 처리를 위해 JSON_EXTRACT_SCALAR와 UNNEST 함수를 중심으로 실제 예제를 통해 배보겠습니다.

■ 예제: 프로모션과 쿠폰 데이터를 이용한 매출 분석

회사는 다양한 프로모션 캠페인을 진행하고 있습니다. 이를 위해 각 캠페인별로 고유한 쿠폰을 제공하고, 이 쿠폰들은 고객에게 다양한 할인 혜택을 제공합니다. 이러한 쿠폰 데이터는 BigQuery의 promotions 테이블에 JSON 형식으로 저장되어 있으며, 여러분은 데이터 분석가로서 각 프로모션의 성과를 분석해야 합니다. promotions 테이블은 다음과 같습니다.

여기서 JSON 데이터란? JSON (JavaScript Object Notation)은 데이터를 저장하거나 전송할 때 사용되는 경량의 데이터 교환 형식입니다. 이 형식은 사람이 읽고 쓰기 쉬울 뿐만 아니라 기계가 파싱하고 생성하기도 간단합니다. JSON은 키-값 쌍을 사용하여 데이터를 구조화합니다. 위 예시에서는 각 쿠폰의 세부 정보가 키(예: id, discount, valid_from)와 이에 대응하는 값으로 표현되어 있습니다.

■ JSON_EXTRACT_SCALAR 함수 소개

JSON_EXTRACT_SCALAR 함수는 JSON 객체에서 특정 키에 해당하는 스칼라 값(문자열, 숫자 등)을 추출합니다. 이 함수는 특정 JSON 필드의 값을 직접적으로 조회할 때 사용되며, SQL 쿼리 내에서 JSON 데이터를 쉽게 활용할 수 있도록 도와줍니다.

예를 들어, 쿠폰 정보가 JSON 형태로 저장된 coupon_data 필드에서 쿠폰 ID를 추출하는 경우, 다음과 같은 SQL 쿼리를 작성할 수 있습니다:

SELECT
  JSON_EXTRACT_SCALAR(coupon_data, '$.id') AS coupon_id
FROM
  promotions

하지만 이렇게만 하면, 다음과 같은 에러가 발생합니다.

No matching signature for function JSON_EXTRACT_SCALAR for argument types: ARRAY<STRING>, STRING. Supported signatures: JSON_EXTRACT_SCALAR(STRING, [STRING]); JSON_EXTRACT_SCALAR(JSON, [STRING]) at [5:3]

이는 coupon_data 필드가 문자열의 배열 형태로 저장되어 있기 때문에 발생하는 에러입니다. JSON_EXTRACT_SCALAR 함수는 단일 JSON 문자열이나 JSON 객체에서 정보를 추출하기 위해 설계되었으며, 배열 형식의 입력을 직접 처리할 수 없습니다. 따라서 배열의 각 요소를 개별적으로 처리하기 위해 UNNEST 함수를 사용해야 합니다.

■ UNNEST 함수의 이해

UNNEST 함수는 배열이나 리스트를 포함한 컬럼을 개별 요소로 분해하여, 각 요소를 별도의 행으로 만들어 줍니다. 이는 BigQuery에서 배열 내부의 JSON 객체를 다룰 때 필수적인 기능입니다

SELECT
  JSON_EXTRACT_SCALAR(c, '$.id') AS coupon_id
FROM
  promotions,
  UNNEST(coupon_data) AS c

이제 UNNEST 함수를 사용하여 coupon_data 배열을 개별 JSON 문자열로 분해하고, 각 문자열에 대해 JSON_EXTRACT_SCALAR 함수를 적용하여 id 값을 추출합니다. 이 방식으로 각 쿠폰의 ID를 성공적으로 조회할 수 있습니다.

■ 분석 예제: 캠페인의 쿠폰 사용 횟수와 총 할인액 계산해보

이제 이 데이터를 사용하여 각 캠페인의 쿠폰 사용 횟수와 총 할인액을 계산해보겠습니다. 다음은 SQL 쿼리를 사용하여 coupon_data 필드에서 각 쿠폰의 id와 discount를 추출하고, 캠페인별로 요약하는 방법을 보여줍니다:

SELECT
  campaign_id,
  campaign_name,
  COUNT(JSON_EXTRACT_SCALAR(c, '$.id')) AS total_coupons_used,
  SUM(CAST(JSON_EXTRACT_SCALAR(c, '$.discount') AS INT)) AS total_discount_offered
FROM
  promotions,
  UNNEST(coupon_data) AS c
GROUP BY
  campaign_id, campaign_name

이 쿼리는 UNNEST 함수를 사용하여 coupon_data 배열을 개별 JSON 객체로 분해하고, JSON_EXTRACT_SCALAR 함수로 필요한 데이터를 추출하여 각 캠페인의 쿠폰 사용 통계를 계산합니다. 이와 같은 분석은 프로모션의 효과를 평가하고 더 나은 마케팅 전략을 수립하는 데 도움이 됩니다.

■ 자주 발생하는 문제 및 해결 방법

• JSON 데이터 처리 시 자주 발생하는 오류 중 하나는 경로 오류입니다. JSON 경로가 정확히 지정되지 않아 데이터를 올바르게 추출하지 못하는 경우가 많습니다. 이를 위해서는 JSON 경로를 정확하게 지정해야 합니다.
• 데이터의 양이 많을 때는 쿼리의 성능을 고려해야 합니다. 가능하면 필요한 데이터만을 최소한으로 추출하고, 쿼리는 간결하게 유지하는 것이 좋습니다.