[빅쿼리] ARRAY / STRUCT / UNNEST. 정체가 무엇이냐??

BigQuery는 SQL을 제공하기 때문에 기존의 SQL과 같이 질의할 수 있어 접근성이 쉽다.

하지만, 우리에게 익숙한 RDB의 데이터 구조와 완전히 동일하지는 않다. 

자주보기 힘든 배열 ARRAY, 구조체 STRUCT, UNNEST 등의 문법을 사용하기 때문이다. 

 

예를 들어 우리에게 익숙한 RDB의 데이터 구조가 다음과 같다면,

 

ARRAY, STRUCT, UNNEST 문법이 사용된 데이터 구조는 다음과 같다. 

딱 봐도 하나의 행에 하나의 데이터 값이 저장되는 구조의 테이블과 다른 모습이 어색하다. 

 event_name 컬럼 까지는 하나의 데이터가 있지만 event_params.key 부터는 하나의 행에 데이터 타입이 동일한 여러값이 저장되어 있다. 이러한 형태가 배열 ARRAY 문법이다. 

 

또하나 어색한 점은 event_params 라는 이름의 컬럼은 점(.)으로 구분되어 여러개로 나뉜다. 

테이블 스키마를 살펴보면

event_params는 하위컬럼 key / value 로 나누어지고, value는 또다시 하위 4가지의 value 로 나누어 진다.

즉, 각 event_params.key 값은 각각 타입별 4가지 데이터를 가진다는 의미로 해석된다. 

이때 key는 여러값을 가지므로 ARRAY 형태인것을 알 수 있다. 

각각의 키들은 데이터 타입에 따라 타입에 맞는 value에 저장되어 있는 것을 확인할 수 있다. 

이러한 형태가 구조체 STRUCT 문법이다.  으아......... 팔수록 더더욱 복잡하게 느껴진다. ㅠ-ㅠ 

이렇게 복잡한 구조로 만든 이유가 뭔고하니

비정형적인 데이터를 쉽게 저장할 수 있고, 빠른 처리 속도를 낼 수 있기 때문이라 한다. 

빠른건 좋은데.. 문제는 배열형태의 데이터에는 일반적인 쿼리로 직접 접근을 할 수 없다는게 문제다. 

 

예를 들어 나는 event_params.key 값만 조회하고 싶다.

다음과 같이 쿼리를 날려보면, 

SELECT event_params.key FROM `table` ;

다음과 같이 배열값에는 필드키로 접근할수 없다고 한다. 

즉, 배열에 갇힌 NEST한 데이터를 UNNEST하는 과정이 필요하다.

 

이때 사용하는 것이  UNNEST함수이다.

 UNNEST함수는 ARRAY를 입력받아 ARRAY의 각 요소에 대한 행이 한개씩 포함된 테이블을 return 해준다. 

말로는 와닿지 않으니 UNNEST 함수를 사용한 쿼리로 확인해 보자.

 

SELECT event_params.key
FROM `table`, UNNEST(event_params) AS event_params

오! 원하는 값만 불러왔다. 

그렇다면.. event_params.key 가  page_title인 놈의 string_value만 보고 싶다. 

 

SELECT user_pseudo_id, event_params.key,event_params.value.string_value
FROM `table` ,UNNEST(event_params) AS event_params
WHERE key = 'page_title'

굿!! 먹힌다. value내의 값은 또 다시 점(.)을 사용하여 하위 컬럼을 불러올 수 있다. 

이러한 방식으로 UNNEST함수를 사용하여 원하는 데이터값을 뽑아내고, WITH절을 사용해 임시 테이블을 만들어 

기존 테이블에서 임시테이블을 조인하여 원하는 값을 추출해낼 수 있겠다. 

 

오늘의 삽질은 여기까지.. 한시네..