1. ARRAY 란?
Oracle 과 같이 기존에 전통적인 RDBMS 문법을 기반으로 쿼리를 배웠을 경우,
BigQuery 에서 이런 저런 데이터를 다루다 보면 간혹 낯선 풍경과 조우하는 경우가 있다.
그 중 하나가 BigQuery 의 데이터형(data type) 중 하나인 ARRAY 이다.
BigQuery에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다.
Google BigQuery Doc
별로 와닿지 않는 정의다…
풀어서 설명을 해보도록 하겠다.
일반적으로 기업에서 BigQuery 를 사용한다고 하면,
Google Analytics (이하 GA) 와 연동해서 사용하는 경우가 많으므로
GA 데이터를 샘플 삼아 설명하도록 하겠다.
GA 는 Web 에서 고객이 남기는 행동들을 Log 형태로 수집을 하고,
수집된 raw data는 JSON 형식에서 대략 다음과 같다.
[
{
"visitId": "123456789",
"visitStartTime": "1622124798",
"totals": {
"visits": "1",
"hits": "1",
"pageviews": "1"
}
}
]
(참고) visitStartTime 값이 왜 1622124798 인지 궁금하신 분들은 관련 포스팅을 참고
JSON 문법을 잘 모르더라도,
위 내용을 아래와 같이 직관적으로 파악할 수 있을 것이다.
👨 visitId 가 123456789 이고
🕒 1622124798 UNIX time 에 들어와서
🔢 총 방문 수를 1회, hit 수는 1회, 페이지 조회 수는 1회 기록했구나.
자, 그러면 위 JSON 데이터를 쿼리가 가능하도록 table 형태로 보면 어떨까?
2. Why UNNEST?
위 데이터를 표로 만들 경우, 결과는 다음과 같을 것이다.
| visitId | visitStartTime | totals |
| 123456789 | 1622124798 | “visits”: “1”, “hits”: “1”, “pageviews”: “1” |
자, 한 번 보자.
딱 봐도 문제가 있지 않은가? (불편 +1)
totals 컬럼의 값을 펼쳐서 추가적인 컬럼으로 만들고 싶은 욕구가 생긴다.
마치 R 에서의 reshape 패키지 cast 함수처럼.
| visitId | visitStartTime | totals.visits | totals.hits | totals.pageviews |
| 123456789 | 1622124798 | 1 | 1 | 1 |
table1 에서 table2 로 변경을 해주는 작업.
이것이 바로 UNNEST 함수다.
3. UNNEST Syntax
UNNEST 의 사용 문법은 다음과 같다.
--visitId 별 페이지 조회 수
SELECT visitId, t.pageviews
FROM table1, UNNEST(totals) AS t
테이블 내 값을 컬럼으로 펼치는 과정에서 CROSS JOIN 이 들어가기 때문에,
FROM table1 뒤에 쉼표(,) 가 붙는다.
UNNEST(totals) 에 대한 alias 가 필수는 아니지만,
복잡한 JOIN 이 들어간 쿼리에서 해당 속성을 특정할 수 있어 사용을 권고한다.
외 참고 링크
Google 에서 직접 GA 샘플 데이터셋을 제공하기도 하니,
실무적인 연습이 필요한 분은 아래 링크를 참고.
Google Analytics sample dataset for BigQuery
Food for iThought 