Ch08_Creating_Arrays_with_Array_AGG

구글 클라우드, Google Cloud, 빅쿼리, BigQuery, Python
Page content

1. 구글 클라우드 설정

본격적인 빅쿼리 실습에 앞서서, Python과 연동하는 예제를 준비하였다. 빅쿼리 시작에 앞서서 선행적으로 클라우드 사용을 해야 한다.

  1. 만약 GCP 프로젝트가 없다면, 계정을 연동한다. Go to Cloud Resource Manager
  2. 그리고, 비용결제를 위한 카드를 등록한다. Enable billing
  3. 마지막으로 BigQuery API를 사용해야 하기 때문에 빅쿼리 API 사용허가를 내준다.Enable BigQuery

위 API를 이용하지 않으면 Python 또는 R과 연동해서 사용할 수는 없다. 자주 쓰는것이 아니라면 비용은 거의 발생하지 않으니 염려하지 않아도 된다. 비용관리에 대한 자세한 내용은 BigQuery 권장사항: 비용 관리에서 확인하기를 바란다.

2. 사용자 계정 인증

구글 코랩을 사용해서 인증 절차를 밟도록 한다. 아래 소스코드는 변경시키지 않는다. 아래 절차대로 진행하면 된다. Gmail 인증 절차와 비슷하다.

from google.colab import auth
auth.authenticate_user()
print('Authenticated')

Authenticated

3. 쿼리 기본

빅쿼리는 기본적으로 데이터 웨어하우스의 기능을 가지고 있다. Structured(데이터프레임) & Semi-Structured(JSON)과 같은 DB를 지원하고 있다. Create, Read, Update, Delete 기능을 지원한다.

빅쿼리는 데이터 분석을 위한 기본적인 도구이기 때문에, 웹/앱을 활용한 마케팅 분석이 필요한 업종에서는 어쩌면 반드시 가져가야할 일종의 언어이기도 하다.

기본적으로 SQL문법을 지원하며, BigQuery 나름의 문법을 또한 구성하고 있다. 이제 본격적으로 코드를 작성해보자.

현재 작성하는 모든 코드는 Python-BigQuery로 연동하여 작성하려고 한다. 이제 간단하게 쿼리를 작성해보자.

오늘 포스트에서는 ARRAY_AGG을 활용하도록 한다. 한국말로는 다중 집계함수, 배열 집계함수 라고 표현하는 것 같기도 하지만, 가급적 원어의 의미를 그대로 담아내고자 ARRAY_AGG를 사용하였다.

우선, gender와 year에 따른 trips이 개수 구하는 쿼리를 작성해본다.

(1) Without ARRAY_AGG

from google.cloud import bigquery
from tabulate import tabulate

project_id = 'your_project_id'
client = bigquery.Client(project=project_id)

temp = client.query('''
  SELECT 
    gender
    , EXTRACT(YEAR from starttime) AS year --
    , COUNT(*) AS numtrips
   
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE gender != 'unknown' and starttime IS NOT NULL
  GROUP BY gender, year
  HAVING year > 2016  
  ''').to_dataframe()

print(tabulate(temp, tablefmt="pipe", headers="keys"))

WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable


|    | gender   |   year |   numtrips |
|---:|:---------|-------:|-----------:|
|  0 | male     |   2018 |    3955871 |
|  1 | female   |   2017 |    3236735 |
|  2 | male     |   2017 |    9306602 |
|  3 | female   |   2018 |    1260893 |

(2) 쿼리 작성 Tip

SELECT 구문을 짤 때, ,가 쿼리문 맨 앞쪽에 오면 주석 처리 하기가 용이해지는 장점이 있다.

간단한 예를 들면,

SELECT 
  gender
  , EXTRACT(YEAR from starttime) AS year -- extract year
  , COUNT(1) AS numtrips  -- count the number of trips
FROM 
  etc

위에서 보는 것처럼, 주석 처리의 가독성이 한결 좋아진 것이 눈에 들어온다.

(3) WITH ARRAY_AGG

그런데, ARRAY_AGG를 활용하지 않을 때 문제점은 gender의 구분이 조금 불명확다는 데 있다. 이러한 단점을 ARRAY_AGG를 활용하면 만회할 수 있다.

temp = client.query('''
  SELECT
    gender
    , ARRAY_AGG(numtrips order by year) AS numtrips
  FROM (
    SELECT
      gender
      , EXTRACT(YEAR from starttime) AS year --
      , COUNT(*) AS numtrips
    FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    WHERE gender != 'unknown' and starttime IS NOT NULL
    GROUP BY gender, year
    HAVING year > 2016  
  )
  GROUP BY gender 
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    | gender   | numtrips           |
|---:|:---------|:-------------------|
|  0 | male     | [9306602, 3955871] |
|  1 | female   | [3236735, 1260893] |

ARRAY Type의 장점은 쿼리의 결과값에 제한이 없다는 점이다. 또한, 결과값을 JSON 형태로 출력함으로써, 업무의 형태를 다양하게 가져갈 수 있다는 장점이 있다.

그런데, 한가지 고려해야 할 사항이 있다. 테이블 형태로 저장하지 않는한 NULL 요소는 일반적으로 Array에서는 허용이 된다. 즉, (예:, [1, NULL, 2]) 형태로 임시저장하는 형태의 코드는 작동하지 않는다.

다음 코드를 예드를 들어보자.

temp = client.query('''
  WITH example AS (
    SELECT true AS is_vowel, 'a' as letter, 1 as position
    UNION ALL SELECT false, 'b', 2
    UNION ALL SELECT false, 'c', 3
  )
  SELECT ARRAY_AGG(IF(position=2, NULL, position)) as positions from example
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 2))




---------------------------------------------------------------------------

BadRequest                                Traceback (most recent call last)

<ipython-input-7-227bc549bdfe> in <module>()
      6   )
      7   SELECT ARRAY_AGG(IF(position=2, NULL, position)) as positions from example
----> 8   ''').to_dataframe()
      9 
     10 print(tabulate(temp, tablefmt='pipe', headers='keys'))


BadRequest: 400 GET https://bigquery.googleapis.com/bigquery/v2/projects/bigquerytutorial-274406/queries/6e2204e1-25e3-4bb7-9146-383ba6dd14f8?maxResults=0&location=US: Array cannot have a null element; error in writing field positions

(job ID: 6e2204e1-25e3-4bb7-9146-383ba6dd14f8)

                         -----Query Job SQL Follows-----                         

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:
   2:  WITH example AS (
   3:    SELECT true AS is_vowel, 'a' as letter, 1 as position
   4:    UNION ALL SELECT false, 'b', 2
   5:    UNION ALL SELECT false, 'c', 3
   6:  )
   7:  SELECT ARRAY_AGG(IF(position=2, NULL, position)) as positions from example
   8:  
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

그런데, NULL 요소가 저장되지 않으면 쿼리는 정상적으로 작동한다.

temp = client.query('''
  WITH example AS (
    SELECT true AS is_vowel, 'a' as letter, 1 as position
    UNION ALL SELECT false, 'b', 2
    UNION ALL SELECT false, 'c', 3
  )
  SELECT ARRAY_LENGTH(ARRAY_AGG(IF(position=2, NULL, position))) as positions from example
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    |   positions |
|---:|------------:|
|  0 |           3 |

위 코드는 [1, NULL, 3]의 반환값의 길이를 구하는 코드다. 다시 말하면, [1, NULL, 3]의 형태로 저장하는 것이 아니기 때문에 쿼리가 작동했다는 뜻이다.

매우 짧은 포스트이지만, ARRAY_AGG의 활용법과 그 주의해야 할 점에 대해 익혔다.

다음 시간에는 STRUCT에 대해 배우는 시간을 가지도록 한다.

4. Reference

Lakshmanan, V., & Tigani, J. (2020). Google BigQuery: the definitive guide: data warehousing, analytics, and machine learning at scale. Beijing: OReilly.