Bigquery11. ARRAY, STRUCT, UNNEST

• ARRAY, STRUCT
  • 다음과 같이, 하나의 행에 여러 데이터가 포함된 형태를 ARRAY(배열), STRUCT(구조체)라고 부름
    
  • 보통 Firebase, Google Analytics 4 데이터를 사용할 때 접하는 자료형
  • ARRAY, STRUCT와 같은 중첩된 형태의 데이터는 저장 용량이 효율적임

---

1. ARRAY

• ARRAY(배열)는 하나의 컬럼(행)에 같은 타입의 여러 값들을 저장하는 자료형을 말함

• 빅쿼리에서 ARRAY인 값들은 세로로 나열됨
  

  • 쿼리 결과를 보면, 행 1 안에 array_sample은 1, 2, 3이 세로로 출력됨. 반면에 단순 숫자값을 나열한 int_sample은 1개의 값만 보임

  • 행 하나에 동일한 타입의 데이터 값이 여러 개 있는 경우 ARRAY라고 보면 됨

ARRAY 생성 방법

• 1) 대괄호 [ , ] 사용
• 2) ARRAY<자료형>[ , ] 사용

• 다른 타입의 값을 저장한 경우 => 오류 발생
  

  • 1) "가"는 INT64가 아닌 STRING인 자료형
  • 2) ARRAY는 같은 타입의 값만 저장 가능함

• 3) GENERATE 함수 사용

  • GENERATE_ARRAY, GENERATE_DATE_ARRAY, GENERATE_TIMESTAMP_ARRAY 등 사용
  • GENERATE_ARRAY(시작, 종료, 간격) ; python의 range(start, end, step)과 동일한 개념
    

• 4) ARRAY_AGG 함수 사용

  • Table에 저장된 데이터를 SELECT해서 ARRAY로 묶고싶은 경우 사용

• ARRAY 안에 NULL이 하나라도 있는 경우, ARRAY_AGG의 결과는 NULL 나옴
• 이 경우 IGNORE NULLS을 추가하면 NULL을 제외하고 연산함

ARRAY 데이터 접근하기

• 1) ARRAY 데이터의 N번째 값을 가져오고 싶은 경우

  • OFFSET : 0부터 시작

    • OFFSET(0): ARRAY의 첫번째 값, OFFSET(1): ARRAY의 두번째 값, ...

  • ORDINAL : 1부터 시작

    • ORDINAL(1): ARRAY의 첫번째 값, ORDINAL(2): ARRAY의 두번째 값, ...

• 단, 배열의 길이보다 큰 값을 지정하면 오류 발생
  • 이를 방지하기 위해 SAFE_를 항상 추가

• 2) ARRAY를 역순으로 반환하고 싶은 경우

  • ARRAY_REVERSE 함수 사용
    

• 3) ARRAY의 길이가 알고싶은 경우

  • ARRAY_LENGTH 사용
    

---

2. STRUCT

• STRUCT(구조체)는 서로 다른 타입의 여러 값을 하나의 컬럼(행)에 저장하는 자료형을 말함
• ARRAY와 자주 쓰이며, Array in Struct, Struct in Array, Struct in Struct 등이 가능함
• 각각의 데이터 타입(필수)이 존재하며 데이터 별로 이름이 있는(선택 사항) 정렬된 필드의 컨테이너

STRUCT 생성 방법

• 1) 소괄호 ( , ) 사용
  • 소괄호 사용 시 이름이 따로 지정되지 않음
    
    
• 2) STRUCT<자료형, 자료형>( , ) 사용
  • <> 안에 각 데이터 타입을 지정해서 사용
  • 각 데이터 타입 앞에 이름을 지정할 수 있음

- INT64, INT64, STRING : 각 데이터의 데이터 타입
- f1, f2, f3 : 각 데이터에 지정해준 이름 = key 값

• 또는 타입을 지정하지 않고, AS로 이름을 지정할 수 있음
  
  

STRUCT 데이터 접근하기

• STRUCT.key값 사용
  

ARRAY와 STRUCT의 관계

• ARRAY(STRUCT), STRUCT(ARRAY), STRUCT(STRUCT), ARRAY(ARRAY) 등 유연하게 중첩된 구조로 저장할 수 있음
  • STRUCT 안에 STRUCT를 사용하고 싶은 경우
    

• ARRAY 안에 여러 STRUCT를 사용하고 싶은 경우
  - ARRAY(SELECT AS STRUCT) 사용
  
  

** 주의) 각 STRUCT 안에 있는 데이터 타입은 달라도 되지만, 같은 컬럼에 있는 ARRAY 데이터는 데이터 타입이 동일해야 함

---

3. UNNEST

• UNNEST는 ARRAY의 중첩된 데이터를 독립적인 행으로 풀 때 사용
• ARRAY 데이터는 SAFE_OFFSET, ORDINAL처럼 직접 접근해서 사용하는 것 보단,
• UNNEST로 독립적인 행으로 풀어서 사용
• 이때 ARRAY의 중첩된 데이터 구조를 각 독립적인 행으로 모두 펼치는 것을 평면화(Flatten)이라고 함

UNNEST 사용 방법

• 1) CROSS JOIN UNNEST(ARRAY컬럼)
• 2) , UNNEST(ARRAY컬럼)
  • CROSS JOIN을 생략
  • 쉼표가 CROSS JOIN을 암시

왼: 원본 array 데이터, 오: unnest한 array 데이터

---

연습문제

연습데이터 1.

• 다음 테이블에서 각 title 별로 actor, character, genres를 출력하세요.한 row에 title, actor, character, genre가 모두 표시되어야 합니다.

• 1) 데이터 파악
  • title: STRING
  • actors: ARRAY<STRUCT(actor STRING, character STRING)>[STRUCT( , ), STRUCT( , )]
  • genres: ARRAY[ , , ]
    • actors는 array 안에 struct가 있는 데이터 구조
      
• 2) 쿼리 고민
  • array 데이터에 어떻게 접근하나?
    - 1) SAFE_OFFSET(ORDINAL): actors에 직접 접근해서 각 데이터를 새로운 컬럼으로 만들기 가능.
    그러나 매번 SAFE_OFFSET을 지정해줘야 함 -> 비효율적.
    - 2) UNNEST: array데이터에 직접 접근이 아닌 unnest로 평면화시키는 게 효율적이어 보임
    
  • 두 개의 array 데이터를 어떻게 unnest?
    • UNNEST를 2번 연속 사용할 수 있음
    • JOIN을 연속 2번 하는 것과 같은 맥락
    • 2번 CROSS JOIN으로 인한 데이터 중복 문제는 어쩔 수 없는 이슈

• 3) 필터를 걸 때

  • where 절에 다음과 같이 쿼리 작성하면 오류 발생
    

  • 쿼리 실행 순서가 FROM 절 > JOIN > SELECT 절이기 때문

    • actors: ARRAY => UNNEST => STRUCT
    • genres: ARRAY
    • 때문에 where 절에 쓴 actor는 select절의 actor가 아닌, join할 때의 actor(struct형태인)로 바로 접근하려 했던 것

  • 따라서 where 절에 작성할 땐, STRUCT.key값을 제대로 명시해야 함

    

연습데이터 2. 앱 로그 데이터

• 다음 데이터의 배열을 풀어주세요.

• 기대하는 output :
  user_id | event_date | event_datetime | event_name | user_pseudo_id | key | string_value | int_value

0) 먼저 미리보기로 데이터 탐색

• 데이터 용량이 크므로 미리보기로 먼저 데이터 탐색
  • 스키마를 보면, event_params 유형이 RECORD임을 알 수 있음. RECORD는 해당 컬럼이 중첩된 자료임을 나타냄.
  • RECORD => STRUCT 구조구나 파악

• 미리보기로 데이터를 보면,
• 1. 컬럼 event_timestamp의 값들이 ms로 나타남 => 보기 쉬운 형태로 변환 필요해보임
• 2. event_params가 STRUCT 구조 => UNNEST 필요해보임

1) 필터링과 LIMIT 걸어서 데이터 탐색

• 처음 쿼리를 작성할 때는 비용과 속도를 줄이기 위해
• WHERE 조건 필터링과 LIMIT을 걸어서 데이터를 살펴봄

2) event_timestamp 변환

3) event_params에 UNNEST하기

응용) 이벤트 퍼널 별 dau 구하기