빅쿼리(BigQuery)에서 3중 UNNEST 함수로 GA 데이터 쿼리하기

빅쿼리(BigQuery)에서 퍼블릭 데이터셋 구글 아날리틱스 샘플 데이터로 unnest를 3번 사용하여 복잡한 array, struct 구조를 파헤쳐서 쿼리를 해보겠습니다.  여러 가지 변형 방식을 보여드리겠습니다.

관련 이전 내용 :

빅쿼리 기본 UI 사용 실습 (click)

빅쿼리 Array(repeated), Struct(nested) 타입 이해 (click)

빅쿼리 unnest() 함수 사용법 (click)

• 빅쿼리에서 unnest() 함수를 사용해야 하는 경우는 array와 struct 구조가 함께 있는 필드가 있는 경우입니다.  데이터 타입이 RECORD 이면서 Mode가 REPEATED인 경우입니다. 아래의 그림과 같이 빅쿼리 구글 아날리틱스(Google Analytics) 퍼블릭 데이터 샘플의  hits.product.customDimensions.index를 select하려고 하면 3번의 unnest() 함수를 써야합니다. 

select 하고자 하는 필드

• hits 필드는 RECORD, REPEATED 이므로 unnest() 를 해줘야 합니다.

• hits의 자식 필드인 product 또한 RECORD, REPEATED 이므로 unnest() 를 해줘야 합니다.

• product의 자식 필드인 customDimensions는  RECORD, REPEATED 이므로 unnest() 를 해줘야 합니다.

• GA (Google Analytics) 데이터 안에 customDimensions라는 필드가 3개 있습니다. 나중에 쿼리할 때 필요한 정보입니다. 어느 customDimensions인지를 빅쿼리한테 알려줄 필요가 있습니다. 

• 아래와 같이 aliasing을 사용하여 unnest해서 쿼리할 수 있습니다. 샘플 테이블에 값이 들어있지 않아 결과는 안나타날 수 있습니다. 쿼리가 valid한지만 확인하셔도 됩니다. (대소문자 무시하고 쿼리하셔도 됩니다.) 

• aliasing을 안하고 쿼리하는 방법은 아래와 같습니다. 다만, 특수한 상황이라 한 번은 aliasing을 해줘야 합니다. (설명은 제일 밑에서) 

• 아래와 같이도 쿼리가 가능합니다.