빅쿼리(BigQuery)의 Array(repeated)와 Struct(nested) 데이터 타입 완전 정복하기

빅쿼리(BigQuery)의 반복되는(repeated) Array(배열)와 중첩되는(nested) Struct 타입에 대해 알아보겠습니다. 

• 빅쿼리(BigQuery) 퍼블릭 데이터(public data)에 있는 Google Analytics sample 데이터 테이블의 스키마(schema)를 보면 totals 필드의 데이터 타입이 레코드(record)로 되어있고 totals.visits, totals.hits는 nested된 struct 형태로 되어있습니다.  totals.visits과 같은 컬럼은 total의 자식(child) 컬럼이라고 이해하면 쉽습니다.

• 빅쿼리 UI에서 아래 쿼리를 실행하고 난 결과입니다.

• nested된 컬럼(column)을 아래와 같이 풀 수도 있습니다. 결과를 보시면 visits, hits 이런식으로 표시됩니다.

• address 필드의 타입을 record로 하고 address.street과 address.city를 자식으로 가지는 테이블을 만들었습니다. (Struct)

• 해당 테이블에 데이터를 넣으려면 아래와 같이 쿼리해야 합니다. 

• 이번에는 struct와 array 구조를 동시에 갖는 test1 테이블을 만들었습니다.

• 아래와 같이 쿼리를 하면 데이터 타입이 안맞아 에러가 납니다.  validator도 에러를 표시해 줍니다.

• Array 데이터 타입으로 바꿔주면 쿼리를 실행할 수 있습니다. ( [ ] 오퍼레이터 추가)

• 아래와 같은 스키마를 가진 테이블을 만들었습니다.

• 아래와 같이 쿼리를 하려 하면 에러가 뜹니다. 

• 다음과 같이 테이블 스키마에 맞추어 쿼리를 실행합니다. 

• 진짜 마지막으로 아래의 스키마를 가지는 테이블을 만들었습니다.

• 아래와 같이 쿼리를 해야 하고 테이블에는 아래와 같은 구조로 저장됩니다.

* 한 행(row)에 저렇게 복잡한 구조로 저장됩니다.  빅쿼리는 RDBMS와 다르게 정규화 대신 denormalize를 합니다.  그래서 비싼 JOIN과 같은 연산을 피하고 빠른 연산 속도를 낼 수 있게 됩니다.

• struct 타입만 있는 테이블은 아래와 같이 쿼리하면 됩니다. 

•  address.city 필드 쿼리

• struct 구조 펼치는 쿼리

• 하지만 array와 struct 구조가 섞여 있을 때에는 UNNEST() 함수를 사용해야 합니다. (더 정확히 말하면 struct (record) 구조를 가진 address 필드가 repeated 모드이면)

* UNNEST() 사용법 3가지  (여기 클릭)