Ch05 Query Essentials(1)

2020-04-23

구글 클라우드, 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로 연동하여 작성하려고 한다. 이제 간단하게 쿼리를 작성해보자.

(1) Select Column

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, tripduration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  LIMIT 5
  ''').to_dataframe()

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

|    | gender   |   tripduration |
|---:|:---------|---------------:|
|  0 | male     |            432 |
|  1 | female   |           1186 |
|  2 | male     |            799 |
|  3 | female   |            238 |
|  4 | male     |            668 |

작성된 쿼리는 New York Bicycle Rentals dataset에서 gender, tripduration 에서 확인한다.

여기에서 주목해야 하는 것은 From이다. 일반적으로 SQL에서 FROM은 Table/View를 의미한다. 물론, BigQuery에서 의미는 같지만, Table/View이 크게 3가지로 구성되어 있음을 명심해야 한다. 이 부분은 문법이기 때문에 실무에서도 Table/View을 찾는데 시간을 낭비해서는 안되니 꼭 참고하기를 바란다.

빅쿼리 객체	이름	설명
Project	`bigquery-public-data`	데이터셋 및 해당 테이블과 연결된 영구 스토리지의 소유자로 볼 수 있다.
Dataset	`new_york_citibike`	데이터셋은 테이블과 뷰에 대한 액세스를 구성하고 제어하는 데 사용되는 최상위 컨테이너다. 사용자는 여러 데이터셋 소유가 가능하다.
Table/View	`citibike_trips`	무조건 데이터셋에 속해야 하므로 BigQuery에 데이터를 로드하기 전에 하나 이상의 데이터 집합을 생성해야 한다.

(2) Column 수정하기

이제 원하는 방법으로 Column명을 수정하도록 한다.

temp = client.query('''
  SELECT 
    gender, tripduration AS rental_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  LIMIT 5
  ''').to_dataframe()

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

|    | gender   |   rental_duration |
|---:|:---------|------------------:|
|  0 | male     |               432 |
|  1 | female   |              1186 |
|  2 | male     |               799 |
|  3 | female   |               238 |
|  4 | male     |               668 |

Column명을 수정하는 것은 AS new_name_of_column 형태로 기존 SQL문과 차이는 없다. 이 컬렴명을 지정하는 것이 중요한 이유는 SELECT문에서 간단한 수식을 적용하는데, 컬럼명을 지정하지 않으면 기대하지 않는 컬렴명이 나타나기 때문이다. 예를 들면 아래와 같다.

temp = client.query('''
  SELECT 
    gender, tripduration/60
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  LIMIT 5
  ''').to_dataframe()

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

|    | gender   |      f0_ |
|---:|:---------|---------:|
|  0 | male     |  7.2     |
|  1 | female   | 19.7667  |
|  2 | male     | 13.3167  |
|  3 | female   |  3.96667 |
|  4 | male     | 11.1333  |

보시다시피 f0_이라는 컬럼명이 나타난다. 이러한 것을 방지하기 위해 column명을 지정하는 것이다. 이번에는 AS ~를 활용한다.

temp = client.query('''
  SELECT 
    gender, tripduration/60 AS duration_minutes
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  LIMIT 5
  ''').to_dataframe()

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

|    | gender   |   duration_minutes |
|---:|:---------|-------------------:|
|  0 | male     |            7.2     |
|  1 | female   |           19.7667  |
|  2 | male     |           13.3167  |
|  3 | female   |            3.96667 |
|  4 | male     |           11.1333  |

이제 드디어 원하는 형태의 테이블이 나타났다.

다음 포스트에서는 WHERE, EXCEPT, REPLACE에 대해 학습하도록 한다.

4. Reference

“Getting Started with BigQuery.” Google, Google, colab.research.google.com/notebooks/bigquery.ipynb.