Bigquery ARRAY, STRUCT 구조

베하 ! 

문땅훈과 루피 입니다 !

즐거운 한 주 보내셨나요~?

 

오늘은 GCP의 하이라이트 빅쿼리에 ARRAY, STRUCT에 대해서 알아보겠습니다.

 

 

레츠꼬 !

 

---

개요

• BigQuery Array
• BigQuery Struct
• UNNEST

 

 

• BigQurey는 SQL 문법을 사용하고 있지만 BigQuery 에서만 사용되는 ARRAY, STRUCT 그리고 UNNEST의 개념이 있습니다.
• ARRAY 형태의 데이터를 쿼리할 때 어떻게 사용하는지에 대해 알아보고자 합니다!

 

BigQuery ARRAY

• ARRAY는 키와 값을 가지는 형태로 구성되어 있습니다.
• 아래 데이터와 같이 address_history 키에 status, address, postcode 라는 3개의 속성을 가지게 됩니다. 즉, 여러개의 값을 가지는 key는 ARRAY 형태라고 보면 됩니다.
• 이러한 형태의 데이터는 . 으로 구분되어 여러개로 나뉘어 집니다.

 

✍️ 데이터를 위와 같은 형태로 데이터를 저장하는 이유는 뭘까요?

• 비정형적인 데이터를 쉽게 저장할 수 있습니다.
• 빠른 처리 속도를 낼 수 있습니다.
• 스키마가 바뀌어도 유연하게 대응 가능
• 데이터 저장 용량의 효율

이러한 이유로 데이터를 위와 같은 구조로 저장하게 됩니다 ! 😊

 

---

 

• address_history 스키마를 보게 되면 REPEATED의 형태를 가지는 것을 볼 수 있습니다.

https://medium.com/google-cloud/how-to-work-with-array-and-structs-in-bigquery-9c0a2ea584a6

 

• 또한, type은 RECODE로 표기되며 키와 값을 가지는 데이터는 중첩이 된 레코드 형태로 구성되어 있습니다

 

• 위와 같은 형태의 데이터를 쿼리하기 위해서는 UNNEST를 필수로 사용해야 합니다.
• UNNEST를 사용함으로써, 중첩된 레코드를 평면화한다! 라고 생각하면 됩니다.
• 아래 사진은 UNNEST 적용 전과 후 비교 입니다.
  • 동일하게 적용되어 있던 컬럼이 UNNEST를 사용해 평면화 된 모습입니다.

https://zzsza.github.io/gcp/2020/04/12/bigquery-unnest-array-struct/#bigquery-array

 

---

 

Bigquery STRUCT

• STRUCT 구조는 하나 이상 필드를 가지며 Bigquery에서 RECORD 로 표현됩니다.

• 위와 같은 형태로 표기됩니다.
• 결과값을 보면 totals.컬럼명 으로 표기가 됩니다.
• struct 구조 안에 특정 필드를 지정하여 쿼리를 추출하고 싶은 경우 단독 select 형태로는 추출이 불가능합니다. 이러한 형태를 풀어주기 위해서는 UNNEST를 사용해줘야 합니다.

• STRUCT 구조의 예시

| id | name | address                                |
|----|------|----------------------------------------|
| 1  | John | {"street": "123 Main St", "city": "NY"}|
| 2  | Jane | {"street": "456 Elm St", "city": "LA"}|

 

• 위 예시에서 address 필드는 street와 city 두 가지 하위 필드를 가진 struct입니다. struct 내부의 데이터를 쿼리하려면 다음과 같이 점 표기법(dot notation)을 사용할 수 있습니다.

• Table명이 ex_table 일 때, 컬럼을 select하는 코드는 다음과 같습니다.

SELECT id, name, address.street, address.city
FROM my_table

 

 

---

 

UNNEST

• ARRAY를 일련의 행으로 변환(평면화) 하려면 UNNEST의 연산자를 사용해야 합니다.
• UNNSET에서 ARRAY를 가져와 ARRAY의 요소마다 단일 행이 포함된 테이블을 반환합니다.
• UNNEST에서는 ARRAY 요소의 순서를 무시하므로 테이블에 순서를 복원해야 하는 경우가 있습니다. 이렇게 하려면 선택사항인 WITH OFFSET 절을 사용하여 배열 요소마다 오프셋이 포함된 추가 열을 반환한 후 ORDER BY 절을 사용하여 오프셋을 기준으로 행을 정렬합니다.

• UNNSET 예시

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS column1
WITH OFFSET AS column2
ORDER BY column2;

/*----------+--------*
 | column1  | column2|
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

• 위 코드는 'foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred' 컬럼을 UNNSET를 사용해 평면화 한 SQL 입니다.

 

 

---

 

Bigquery ARRAY와 STRUCT에 대해서 조금 감이 오시나요!?

어려운 내용이지만 자주 사용해보면 익숙해질 것 같습니다 ~!

 

다음 포스팅에서도 유익한 내용을 가지고 오도록하겠습니다.

베빠 !