[BigQuery] ARRAY, NESTED

- 이 글은 제가 공부를 하며, 이해한 것을 바탕으로 작성하는 글입니다.

- 그렇기에 틀리거나 잘못된 부분이 있을 수 있습니다.

- 글의 오류를 발견하시면, 댓글로 말씀해 주시면 정말 감사하겠습니다.

- 이 글은 구글의 교육 ARRAY 페이지(GSP416)와 DOCS를 정리한 것입니다.

일반적으로 아래와 같은 테이블들이 존재할 때, 우리는 불편하다. 뭔지 모를 쪼개야 할 것 같은 생각이 들기 때문이다. 정규화를 해야 하겠군과 같은 생각이 든다.

사람	좋아하는 과일
강박사	사과
강박사	수박
줄줄이	사과
줄줄이	바나나
용용이	레몬
용용이	사과

그러나 BIGQUERY에서는 RDB가 아니기에 정규화를 해야하겠다는 생각을 버려야 한다. 오히려 반정 규화를 해서 테이블을 합쳐 놓는 것이 성능이 가장 좋다.

BIGQUERY에서는 ARRAY를 활용하여 아래와 같이 처리할 수 있다. 마치 테이블 안에 테이블이 있는 것과 유사하다.

테이블 내부의 한 컬럼에 다른 테이블이 이미 조인되어 하나의 테이블이 되어있다는 식으로 이해하면 될 것이다.

  SELECT 
      ["사과", "수박"] AS 좋아하는 과일, "강박사" AS 사람
  UNION ALL
  SELECT 
      ["사과", "바나나"] AS 좋아하는 과일, "줄줄이" AS 사람
  UNION ALL
  SELECT 
      ["레몬", "사과"] AS 좋아하는 과일, "용용이" AS 사람

NESTED, 즉 중첩 필드(칼럼)이라는 것으로 처리한다. JSON 내부를 배열로 처리하는 것과 동일하다고 볼 수 있다.

이 떄 주의할 점은 배열 내부의 모든 값은 동일한 타입이 들어간다는 것이다.

이러한 ARRAY를 생성하는 방법은 아래와 같다. 자주 사용하는 것들만 정리하였다. 더 많은 내용은 공식 DOCS에 들어가서 확인하자.

ARRAY 칼럼 생성 방법

1. 대괄호

SELECT [A,B,C,D, . . .] AS column_name

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS ARRAY_컬럼;


결과 
+-----------+
| ARRAY_컬럼 |
+-----------+
| [1, 2, 3] |
+-----------+

2. ARRAY(TYPE)

SELECT ARRAY<STRING>[A,B,C,D, . . .] AS column_name

3.GENERATE_ARRAY

SELECT GENERATE_ARRAY(시작하는 숫자, 종료되는 숫자, 간격) AS column_name

- 간격 parameter의 default 값은 1이다.

- 이 함수에서는 숫자의 간격에 따른 증감으로 ARRAY 내부의 값을 표현하므로, 데이터의 유형이 아래와 같이 한정된다.

INT64
NUMERIC
BIGNUMERIC
FLOAT64

SELECT GENERATE_ARRAY(10, 0, -3) AS ARRAY_컬럼명;

+---------------+
| ARRAY_컬럼명 |
+---------------+
| [10, 7, 4, 1] |
+---------------+

- 시작 숫자가 종료 숫자보다 작거나, 동일하게 설정한 것과 같은 이상 상황에는 빈 array를 반환한다.

SELECT GENERATE_ARRAY(10, 0, 3) AS ARRAY_컬럼명;

+---------------+
| ARRAY_컬럼명  |
+---------------+
| []            |
+---------------+

ARRAY 컬럼이 없는 테이블에서 ARRAY 컬럼 생성 방법

ARRAY_AGG()

다음은 기존 테이블에 ARRAY 컬럼이없을 때, 생성하려는 경우이다. 함수는 ARRAY_AGG()를 사용한다.

아래 테이블을 보자. 이 테이블은 ARRAY 칼럼이 없는 일반 테이블의 일부를 가져온 것이다.

여기 테이블에서 v2ProductName, pageTitle칼럼을 ARRAY 칼럼으로 변경해 보자.

SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(v2ProductName) AS products_viewed,
  ARRAY_AGG(pageTitle) AS pages_viewed
FROM 테이블명
ORDER BY date

아래와 같은 결과가 도출 된다.

ARRAY_LENGTH(ARRAY 칼럼명)

이렇게 ARRAY로 데이터를 정리하면, 배열 내부 개수를 알아야 할 때가 있다. 이 때는 ARRAY_LENGTH()를 이용한다.

위와 동일한 query에 아래와 같이 추가해준다.

SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(v2ProductName) AS products_viewed,
  ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed,
  ARRAY_AGG(pageTitle) AS pages_viewed
  ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed
FROM 테이블명
ORDER BY date

ARRAY_AGG(DISTINCT 칼럼명)

개수를 세다보면, 고유 값의 개수가 필요할 때가 존재한다. 이럴 때에는 아래와 같이 DISTINCT를 사용하면 된다.

SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed,
  ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed
FROM 테이블명
ORDER BY date

ARRAY 칼럼이 있는 테이블에서 QUERY 하는 방법

그럼 ARRAY 칼럼에 쿼리를 날릴 때에는 어떻게 해야 하는가? 예를 들어 보자.

이 테이블은 Totals라는 칼럼이 ARRAY로 되어있고 그 내부에 또 다른 ARRAY가 존재한다. 여기서 내부의 값을 확인하려면 어떻게 해야 할까? 일반적으로 BIGQUERY에서는 어떤 값의 하위 범주를 표현할 때, 마침표(.)를 사용하는데 이와 같이 처리하면 될까?

SELECT
  totals.pageviews
FROM 테이블 명

잘 된다.

그러나, 중첩이 여러 번 되어있는 칼럼을 쿼리하려면 이와 다른 방법이 필요하다.

아래 예시를 보자. 아래는 ARRAY 컬럼 내부에 또 ARRAY 칼럼이 존재하는 경우이다.

아래와 같은 쿼리를 날리면, 에러가 나온다.

SELECT
  hits.page.pageTitle
FROM 테이블 명

Cannot access field page on a value with type

이 에러는 BIGQUERY를 사용하면서 상당히 많이 보게 될 에러 문이다. 이는 배열로 되어있는 것을 배열을 없애는 작업 후에, 쿼리를 처리해야 한다.

UNNEST() 함수 사용

사용 방법은 간단하다. ARRAY로 되어있는 칼럼을 기존 컬럼으로 바꾸어주면 된다. 이 떄, 해당 컬럼을 UNNEST해주면 된다. 그런데 뭔가 특이하다. 마치 하나의 테이블 취급을 해주듯, 기존의 테이블 명 뒤에 , 를 하고 붙이다니.

이는 ARRAY 컬럼을 처음 설명할 때 언급했던, 테이블 속의 테이블이라는 개념을 생각하면 될 것이다.

SELECT DISTINCT
  h.page.pageTitle
FROM 테이블명, UNNEST(hits) AS h

쿼리 결과는 아래와 같이 잘 나오게 된다.

저작자표시 비영리 변경금지

日日新又日新