Ch05 Query Essentials(3)

2020-04-25

구글 클라우드, 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에 대해 배우는 시간을 가졌다.

오늘 포스트에서는 SubQuery, ORDER BY에 대해 학습을 진행하도록 한다.

(1) SUBQUERY

서브쿼리는 무엇일까? 서브쿼리는 메인쿼리가 서브쿼리를 포함하는데, 반복적인 쿼리를 수행해야 할 때 alias를 통해서 보다 간편하게 결과를 조회할 때 사용합니다.

실무에서는 자주 사용되기 때문에 꼭 실습을 통해서 개념을 익히시기를 바랍니다.

from google.cloud import bigquery
from tabulate import tabulate

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

temp = client.query('''
  SELECT 
    * 
  FROM (
    SELECT 
      gender, tripduration / 60 AS minutes
    FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  )
  WHERE minutes < 10
  LIMIT 5
  ''').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   |   minutes |
|---:|:---------|----------:|
|  0 | female   |   7.28333 |
|  1 | male     |   7.41667 |
|  2 | female   |   5.75    |
|  3 | male     |   4.58333 |
|  4 | male     |   6.43333 |

FROM 다음에 생성된 구절이 서브쿼리라고 보면 된다. 그런데, 복잡하다. R 또는 Python처럼 SQL도 다른 데이터셋으로 변경하여 저장시킬 수 있을까? 결론부터 말하면 가능하며, 이 때에는 With new_name AS (syntax) 형태로 작성해야 한다. 바로 코드를 확인하자.

temp = client.query('''
    WITH all_trips AS(
      SELECT 
      gender, tripduration / 60 AS minutes
      FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    )
    SELECT * from all_trips
    WHERE minutes < 10
    LIMIT 5
    ''').to_dataframe()

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

|    | gender   |   minutes |
|---:|:---------|----------:|
|  0 | male     |   7.2     |
|  1 | female   |   3.96667 |
|  2 | male     |   9.88333 |
|  3 | male     |   6.9     |
|  4 | male     |   7.9     |

(2) ORDER BY

그런데 한가지 문제가 있다. 보통 실무에서는 상위 또는 하위 이런식으로 결과보고서를 제출해야 의사결정에 도움이 된다. 이럴 때 자주쓰는 기술이 데이터 정렬(ORDER BY)을 하는 것이다. 내림차순으로 정렬을 하는데, 앞에서 배운 것을 그대로 적용한다.

temp = client.query('''
    WITH all_trips AS(
      SELECT 
      gender, tripduration / 60 AS minutes
      FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    )
    SELECT * from all_trips
    WHERE minutes < 10
    ORDER BY minutes DESC
    LIMIT 5
    ''').to_dataframe()

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

|    | gender   |   minutes |
|---:|:---------|----------:|
|  0 | male     |   9.98333 |
|  1 | male     |   9.98333 |
|  2 | female   |   9.98333 |
|  3 | male     |   9.98333 |
|  4 | male     |   9.98333 |

이 때, 여성 또는 남성, 즉 특정 값과 연계되어 내림차순을 진행하다면, ORDER BY 구문 앞에서 WHERE 구문을 통해서 작성하도록 한다.

temp = client.query('''
    WITH all_trips AS(
      SELECT 
      gender, tripduration / 60 AS minutes
      FROM `bigquery-public-data.new_york_citibike.citibike_trips`
    )
    SELECT * from all_trips
    WHERE gender = 'female'
    ORDER BY minutes DESC
    LIMIT 5
    ''').to_dataframe()

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

|    | gender   |   minutes |
|---:|:---------|----------:|
|  0 | female   |    250349 |
|  1 | female   |    226438 |
|  2 | female   |    207989 |
|  3 | female   |    159712 |
|  4 | female   |    154239 |

이번에는 여성 상위 5명을 추출하도록 해보자. 여기에서는 WHERE gender = 'female'구문을 추가해서 데이터를 조회할 수 있다.

이번 포스트에서는 With를 활용한 SubQuery 그리고 ORDER BY 대해서 배우도록 한다.

다음 포스트에서는 집계함수에 대해 배우도록 한다.

4. Reference

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