Ch09 Struct, Tuple

2020-04-30

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

Page content

1. 구글 클라우드 설정

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

만약 GCP 프로젝트가 없다면, 계정을 연동한다. Go to Cloud Resource Manager
그리고, 비용결제를 위한 카드를 등록한다. Enable billing
마지막으로 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로 연동하여 작성하려고 한다. 이제 간단하게 쿼리를 작성해보자.

Ch05-Query_Essentials(1)에서는 SELECT와 AS에 대해 배우는 시간을 가졌다.
Ch05-Query_Essentials(2)에서는 WHERE와 EXCEPT, 그리고 REPLACE에 대해 배우는 시간을 가졌다.
Ch05-Query_Essentials(3)에서는 SubQuery에 대해서 집중적으로 배우는 시간을 가졌다.
Ch06 SQL Aggregates에서는 집계함수를 사용해서 데이터를 조금 더 디테일하게 요약하는 것을 배웠다.
Ch07 Arrays & Structs에서는 빅쿼리에서 가상의 테이블과 그리고 배열을 만드는 법을 익혔다.
Ch08_Creating_Arrays_with_Array_AGG

오늘 포스트에서는 STRUCT & Tuple을 익혀보도록 한다. 한국말로는 구조체라고 표현하는 것 같다.

(1) Struct

구글 빅쿼리에서는 STRUCT의 정의를 각각 유형(필수)과 필드 이름(선택사항)이 있는 순서가 지정된 필드의 컨테이너로 명명하고 있다.

ARRAY와 비교했을 때, 자주 쓰이지는 않으나 가끔 필요할 때가 있다.
파이썬의 데이터 클래스와 비슷함. (C Structure와 유사)

우선 가상의 Struct 테이블을 만들어 본다.

from google.cloud import bigquery
from tabulate import tabulate

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

temp = client.query('''
  SELECT
    [
      STRUCT('male' as gender, [930660, 3955871] as numtrips)
      , STRUCT('female' as gender, [3236735, 1260893] as numtrips) 
    ] AS bikerides
  ''').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


|    | bikerides                                                                                                 |
|---:|:----------------------------------------------------------------------------------------------------------|
|  0 | [{'gender': 'male', 'numtrips': [930660, 3955871]}, {'gender': 'female', 'numtrips': [3236735, 1260893]}] |

참고로 이번 포스트에서는 각각의 개념에 대해서만 살피고, 실 데이터에서 어떤 문제에서 Struct, ARRAY_AGG 등을 활용해서 쿼리를 작성해야 하는지는 추후에 포스팅 하겠다. 특히, 시계열 데이터를 다룰 때 매우 유용하게 접근 할 수 있음만 기억하자.

(2) Tuple

만약에 위 구문에서 struct과 별도의 필드명을 주지 않으면 어떻게 될까? 그러나 결과값은 위와 동일하게 나오게 하고싶다면 어떻게 작성해야 할까? 이 코드를 통해서 구조가 어떻게 되는지 이해하는 것이 중요하다.

temp = client.query('''
  SELECT
    [
      ('male', [930660, 3955871])
      , ('female', [3236735, 1260893]) 
    ]
  ''').to_dataframe()

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

|    | f0_                                                                                                           |
|---:|:--------------------------------------------------------------------------------------------------------------|
|  0 | [{'_field_1': 'male', '_field_2': [930660, 3955871]}, {'_field_1': 'female', '_field_2': [3236735, 1260893]}] |

f0_라는 Cell안에 _field_1과 field_2라는 이름으로 구분되어 출력됨을 확인할 수 있다.

(3) Working with Array

이제, 여기에서 우리가 구하고 싶은 것은 ARRAY안에 있는 numtrips의 갯수와, gender의 두번째 값을 구한다고 가정합니다.

temp = client.query('''
  SELECT 
    ARRAY_LENGTH(bikerides) as num_items
    , bikerides[OFFSET(1)].gender as second_gender
  FROM 
    (SELECT
      [
        STRUCT('male' as gender, [930660, 3955871] as numtrips)
        , STRUCT('female' as gender, [3236735, 1260893] as numtrips) 
      ] AS bikerides
    ) 
  ''').to_dataframe()

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

|    |   num_items | second_gender   |
|---:|------------:|:----------------|
|  0 |           2 | female          |

Python처럼 OFFSET()는 0부터 시작한다.

다음 시간에는 UNNEST와 ARRAY에 대해 익히도록 한다.

4. Reference

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