Ch12 Join Explained

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

개요

  • 이번 시간에는 Table Join에 대해서 배우도록 한다. 이 때 Table Join을 할 때의 고려사항은 두개의 서로 다른 조직이 서로 다른 테이블을 가지고 있는데, 그 두개의 데이터를 연동하는 것에 있다.
from google.cloud import bigquery
from tabulate import tabulate
import pandas as pd

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

temp = client.query('''
  WITH bicycle_rentals AS (
    SELECT
      COUNT(starttime) as num_trips,
      EXTRACT(DATE from starttime) as trip_date
    FROM `bigquery-public-data`.new_york_citibike.citibike_trips GROUP BY trip_date
    ),
    rainy_days AS
   (
    SELECT
      date,
      (MAX(prcp) > 5) AS rainy
    FROM (
      SELECT
        wx.date AS date,
        IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp
      FROM
       `bigquery-public-data`.ghcn_d.ghcnd_2016 AS wx
      WHERE
        wx.id = 'USW00094728')
    GROUP BY  date
  )
  SELECT
    ROUND(AVG(bk.num_trips)) AS num_trips,
    wx.rainy
  FROM bicycle_rentals AS bk
  JOIN rainy_days AS wx
  ON bk.trip_date = wx.date
  GROUP BY wx.rainy 
  ''').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


|    |   num_trips | rainy   |
|---:|------------:|:--------|
|  0 |       39107 | False   |
|  1 |       32052 | True    |
  • 위 결과값에 대해 조금 디테일하게 공부하는 시간을 가져보도록 한다.
  • 우선, 위 bicycle_rentals과 rainy_days는 테이블 이기보다는 개념적으로 From_Item이라고 생각하는 것이 좋다.
  • rainy_days는 Global Historical Climate Network (GHCN) observation에서 데이터를 추출하였고, id=USW00094728 인 뉴욕의 한 지역만 추출하였다.
  • 여기에서 하려는 것은 날씨(외부데이터)를 통해서 num_trips를 계산하는 것인데, rainy가 있는 날과 없는 날을 구분해서 작성하는 것이다.

Step 1. WITH bicycle_rentals

우선 bicycle_rentals을 통해서 결과값을 먼저 확인해보자. 코드는 위와 동일하다.

temp = client.query('''
  WITH bicycle_rentals AS (
   SELECT
    COUNT(starttime) as num_trips,
    EXTRACT(DATE from starttime) as trip_date
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
   GROUP BY trip_date
)

SELECT * from bicycle_rentals LIMIT 5
''').to_dataframe() 

print(temp)

   num_trips   trip_date
0      30344  2015-08-16
1      38348  2016-03-31
2      65517  2016-09-23
3      26289  2018-04-16
4       9028  2014-01-19
  • 쿼리 결과값에 보는 것처럼, 각 date (날짜)별로 num_trips의 count를 진행한 것이다.

Step 2. Rainy Days

  • 이번에는 Rainy Days의 쿼리를 짜본다.
temp = client.query('''
  WITH rainy_days AS
   (
    SELECT
      date,
      (MAX(prcp) > 5) AS rainy
    FROM (
      SELECT
        wx.date AS date,
        IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp
      FROM
       `bigquery-public-data`.ghcn_d.ghcnd_2016 AS wx
      WHERE
        wx.id = 'USW00094728')
    GROUP BY  date
  )
  
  SELECT * from rainy_days LIMIT 5
  ''').to_dataframe()

print(temp)

         date  rainy
0  2016-10-11  False
1  2016-12-13  False
2  2016-09-28  False
3  2016-01-25  False
4  2016-05-24  False
  • Rainy Days의 쿼리 결과물은 위에서 보는 것처럼, date와 rainy로 조회가 된 것을 확인할 수 있다.

Step 3. Join

우선 쿼리문을 보자.

  SELECT
    ROUND(AVG(bk.num_trips)) AS num_trips,
    wx.rainy
  FROM bicycle_rentals AS bk
  JOIN rainy_days AS wx
  ON wx.date = bk.trip_date 
  GROUP BY wx.rainy
  • 우선 기준점은 bicycle_rentals가 된다.
  • JOIN 다음에는 두번째 테이블 rainy_days가 따라오는데, 두 테이블 모두 Alias 문법을 써서 글자수를 줄였다.
  • ON 다음에는 날짜를 기준으로 합치게 된다.
    • bk.trip_date = wx.date
  • rainy를 기준으로 AVG와 함께 GROUP BY를 사용하여 그룹화를 진행하여 테이블을 요약한다.

결론

  • 두개의 다른 테이블을 조인할 때 명심해야 하는 것 중 하나는 원 RAW 데이터는 그대로 살려야 하며, 가상의 임시 테이블을 만드는 것이 중요하다.
  • 또한, 처음 데이터를 불러올 때 부터 FROM 절에서 1차로 가공하는 것이 좋다.

이제 다음 포스트에서는 본격적인 3가지 경우의 수에 따른 쿼리문을 만들어 본다.

  • INNER JOIN
  • CROSS JOIN
  • OUTER JOIN

Reference

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