Ch14 Cross Join

2020-05-15

구글 클라우드, 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에서 처음으로 두개의 다른 조직에서 온 데이터를 합쳐서 의미있는 데이터를 만들어가는 과정을 진행하였습니다.
Ch13 Inner Join에서 Table Join에 대해 학습하는 시간을 가졌습니다.

IV. CROSS JOIN

CROSS JOIN은 Cartesian(=카타시안) Product라고 불리기도 한다.
크로스 조인은 모든 경우의 수를 전부 표현해주는 방식입니다.
기준테이블이 A일경우 A의 데이터 한 ROW를 B테이블 전체와 JOIN하는 방식입니다.
행의 결과는 $N \times M$ 나타나게 됩니다.

(1) 가상 테이블 만들기

두개의 가상 테이블을 만들어 Table Join을 하도록 합니다.
먼저 cross 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 winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m'
  ),
  gifts AS (
    SELECT 'Google Home' as gift
    UNION ALL SELECT 'Google Hub'
    UNION ALL SELECT 'Pixel3'
  )
  SELECT person, gift
  FROM winners
  CROSS JOIN gifts
  ''').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     John   Google Hub
2     John       Pixel3
3  Hiroshi  Google Home
4  Hiroshi   Google Hub
5  Hiroshi       Pixel3
6     Sita  Google Home
7     Sita   Google Hub
8     Sita       Pixel3

(2) Step by Step - Winners

Winners 데이터는 어떻게 생겼을까?

temp = client.query('''
  WITH winners AS (
    SELECT 'John' as person, '100m' as event
    UNION ALL SELECT 'Hiroshi', '200m'
    UNION ALL SELECT 'Sita', '400m'
  )
  SELECT *
  FROM winners
  ''').to_dataframe()

print(temp)

    person event
0     John  100m
1  Hiroshi  200m
2     Sita  400m

총 3개의 데이터와 event로 구성된 것을 확인할 수 있다.

(3) Step by Step - gifts

gifts로 구성된 데이터를 확인해보자.

temp = client.query('''
  WITH gifts AS (
    SELECT 'Google Home' as gift
    UNION ALL SELECT 'Google Hub'
    UNION ALL SELECT 'Pixel3'
  )
  SELECT *
  FROM gifts
  ''').to_dataframe()

print(temp)

          gift
0  Google Home
1   Google Hub
2       Pixel3

위 데이터는 단순하게 3개의 데이터와 1개의 열로만 구성된 데이터임을 확인할 수 있다.

(4) 결과 생각해보기

이번에는 쿼리를 주어진 결과에 대해 다시한번 생각해보자.

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

print(temp)

    person         gift
0     John  Google Home
1     John   Google Hub
2     John       Pixel3
3  Hiroshi  Google Home
4  Hiroshi   Google Hub
5  Hiroshi       Pixel3
6     Sita  Google Home
7     Sita   Google Hub
8     Sita       Pixel3

우선 기준이 되는 winners 데이터가 3개가 있었고, 조인 시키려는 gifts 데이터가 3개가 있다.
즉, 위 데이터는 $3 \times 3 = 9$의 형태로 조회되었다.

V. 결론

간단하게 Cross Join에 대해서 익혀봤다. 다음 시간에는 Outer Join에 대해서 배워보도록 하자.

VI. Reference

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