Ch13 Inner Join

2020-05-08

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

Page content

I. 구글 클라우드 설정

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

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

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
Ch09_Array_Of_Struct Struct을 활용하여 독특한 쿼리 결과문을 확인하였다.
Ch10 UNNEST an Array 에서, Struct을 만든 임시 테이블을 보다 가독성 좋게 필드로 구분한 것을 확인하였다.
Ch11 Powerful SQL Pattern에서 ARRAY, Struct, UNNEST을 활용하여 Powerful한 쿼리를 작성하였습니다.
Ch12 Join Explained에서 처음으로 두개의 다른 조직에서 온 데이터를 합쳐서 의미있는 데이터를 만들어가는 과정을 진행하였습니다.

IV. INNER JOIN

INNER JOIN은 빅쿼리에서는 default된 문법입니다.
우선 두개의 가상 테이블을 만든후 양쪽 테이블의 state가 동일한 것을 조회합니다.

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 from_item_a AS (
    SELECT 'Dalles' as city, 'OR' as state
    UNION ALL SELECT 'Tokyo', 'Tokyo'
    UNION ALL SELECT 'Mumbai', 'Maharashtra'
  ),
  from_item_b AS (
    SELECT 'OR' as state, 'USA' as country
    UNION ALL SELECT 'Tokyo', 'Japan'
    UNION ALL SELECT 'Maharashtra', 'India'
  )

  SELECT from_item_a.* , country
  FROM from_item_a
  JOIN from_item_b
  ON from_item_a.state = from_item_b.state 
  ''').to_dataframe()

print(temp)

     city        state country
0  Dalles           OR     USA
1   Tokyo        Tokyo   Japan
2  Mumbai  Maharashtra   India

보통 내부조인이라고 하기도 한다.
두개의 데이터 중 교집합($A\cap B$)에 해당하는 행의 개수만 조회한다.
첫 번째 from_item은 도시 목록을 가지고 있고, 두 번째 from_item은 각 주가 속한 국가를 의미하고 있다. 두개의 열을 결합하면 세 개의 열이 있는 데이터 집합이 생성된다.
에러 기준으로, 빅쿼리에서는 기본적으로 key값과 일치하면 가급적 값을 반환한다.
테이블의 조건이 꼭 일치해야 하는 것은 아니다.
만약 효과적으로 Join할 수 없는 조건이라면 데이터는 에러를 반환한다.
이번에는 state가 !=일 때, 데이터를 조회하면, 출력은 되지만, 각각의 행이 2개씩 생겼습니다만, 결과값에서 보는 것처럼 state와 country가 일치하지 않고, 중복된 결과처럼 보이는 경우가 있습니다.

temp = client.query('''
  WITH from_item_a AS (
    SELECT 'Dalles' as city, 'OR' as state
    UNION ALL SELECT 'Tokyo', 'Tokyo'
    UNION ALL SELECT 'Mumbai', 'Maharashtra'
  ),
  
  from_item_b AS (
    SELECT 'OR' as state, 'USA' as country
    UNION ALL SELECT 'Tokyo', 'Japan'
    UNION ALL SELECT 'Maharashtra', 'India'
  )
  
  SELECT from_item_a.*, country AS surcharge
  FROM from_item_a
  JOIN from_item_b
  ON from_item_a.state != from_item_b.state 
''').to_dataframe() 

print(temp)

     city        state surcharge
0  Dalles           OR     Japan
1  Dalles           OR     India
2   Tokyo        Tokyo       USA
3   Tokyo        Tokyo     India
4  Mumbai  Maharashtra       USA
5  Mumbai  Maharashtra     Japan

V. 결론

간단하게 INNER JOIN에 대해서 배우는 시간을 가졌다. 그런데, 주의해야 하는 것은 부등호 연산자를 잘못 사용하게 되면 테이블의 불일치가 생기는 것을 볼 수 있었다.

VI. Reference

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