Ch15 Outer Join

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

I. 구글 클라우드 설정

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

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

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

II. 사용자 계정 인증

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

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

Authenticated

III. 쿼리 기본

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

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

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

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

IV. Outer Join

  • 조인하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 모두 출력한다.
  • 즉, 조건에 맞지 않아도 해당하는 행을 출력하고 싶을 때 사용할 수 있다.

(1) Outer Join의 종류

  • Outer Join에는 크게 3가지의 조인 종류가 있다. (Full Outer Join, Left Outer Join, Right Outer Join)
  • 순차적으로 확인해보도록 한다.

(2) Full Outer Join

  • 코드를 작성한다.
from google.cloud import bigquery
from tabulate import tabulate
import pandas as pd

project_id = 'bigquerytutorial-274406'
client = bigquery.Client(project=project_id)

temp = client.query('''
  WITH winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m', 
    UNION ALL SELECT 'Kwame', '50m'
  ),
  gifts AS (
    SELECT 'Google Home' as gift, '100m' as event
    UNION ALL SELECT 'Google Hub', '200m'
    UNION ALL SELECT 'Pixel3', '400m'
    UNION ALL SELECT 'Google Mini', '5000m'
  )
  SELECT person, gift
  FROM winners
  FULL OUTER JOIN gifts on winners.event = gifts.event
  ''').to_dataframe()

print(temp)

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


    person         gift
0     John  Google Home
1  Hiroshi   Google Hub
2     Sita       Pixel3
3    Kwame         None
4     None  Google Mini
  • 각 컬럼마다 None값을 볼 수 있는데, 이는 조건이 맞지 않아도 조회됨을 의미힌다.

(3) Left Outer Join

  • Winners table은 모두 조회가 되지만, 조건에 맞지 않는 gifts는 버리게 됨을 알 수 있다.
temp = client.query('''
  WITH winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m', 
    UNION ALL SELECT 'Kwame', '50m'
  ),
  gifts AS (
    SELECT 'Google Home' as gift, '100m' as event
    UNION ALL SELECT 'Google Hub', '200m'
    UNION ALL SELECT 'Pixel3', '400m'
    UNION ALL SELECT 'Google Mini', '5000m'
  )
  SELECT person, gift
  FROM winners
  LEFT OUTER JOIN gifts on winners.event = gifts.event
  ''').to_dataframe()

print(temp)

    person         gift
0     John  Google Home
1  Hiroshi   Google Hub
2     Sita       Pixel3
3    Kwame         None

(4) Right Outer Join

  • 이번에는 반대로 모든 gifts의 값이 조회되고, 조건에 만족하지 않는 winners는 조회되지 않는다.
temp = client.query('''
  WITH winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m', 
    UNION ALL SELECT 'Kwame', '50m'
  ),
  gifts AS (
    SELECT 'Google Home' as gift, '100m' as event
    UNION ALL SELECT 'Google Hub', '200m'
    UNION ALL SELECT 'Pixel3', '400m'
    UNION ALL SELECT 'Google Mini', '5000m'
  )
  SELECT person, gift
  FROM winners
  RIGHT OUTER JOIN gifts on winners.event = gifts.event
  ''').to_dataframe()

print(temp)

    person         gift
0     John  Google Home
1  Hiroshi   Google Hub
2     Sita       Pixel3
3     None  Google Mini

V. Inner Join

  • Inner Join은 두 테이블의 조건이 맞는 값만 추출된다. 위 Outer Join과의 차이점에 대해 확인한다.
temp = client.query('''
  WITH winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m', 
    UNION ALL SELECT 'Kwame', '50m'
  ),
  gifts AS (
    SELECT 'Google Home' as gift, '100m' as event
    UNION ALL SELECT 'Google Hub', '200m'
    UNION ALL SELECT 'Pixel3', '400m'
    UNION ALL SELECT 'Google Mini', '5000m'
  )
  SELECT person, gift
  FROM winners
  INNER JOIN gifts on winners.event = gifts.event
  ''').to_dataframe()

print(temp)

    person         gift
0     John  Google Home
1  Hiroshi   Google Hub
2     Sita       Pixel3

V. 결론

지금까지 테이블 조인에 대해 배웠다. 그리고 그 외의 다양한 방식은 아래 그림을 참조하기를 바란다.

VI. Reference

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