Ch22 Cleaner Null Handling with Coalesce

구글 클라우드, 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. Null Handling with Coalesce

  • 우선 아래 데이터를 확인합니다.
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 catalog AS (
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT 30.0, 0.30, NULL
    UNION ALL SELECT 30.0, NULL, NULL
  )

  SELECT * 
  FROM catalog
  ''').to_dataframe()

print(temp)

   costPrice  markup  taxRate
0       30.0    0.15     0.10
1        NaN    0.21     0.15
2       30.0     NaN     0.09
3       30.0    0.30      NaN
4       30.0     NaN      NaN
  • 위 3개의 column을 활용해서 salesPrice 새로운 column을 만든다.
temp = client.query('''
  WITH catalog AS (
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT 30.0, 0.30, NULL
    UNION ALL SELECT 30.0, NULL, NULL
  )

  SELECT 
    *, ROUND(
      costPrice * markup * taxRate, 2) as salesPrice
  FROM catalog
  ''').to_dataframe()

print(temp)

   costPrice  markup  taxRate  salesPrice
0       30.0    0.15     0.10        0.45
1        NaN    0.21     0.15         NaN
2       30.0     NaN     0.09         NaN
3       30.0    0.30      NaN         NaN
4       30.0     NaN      NaN         NaN
  • 위의 결과처럼, 첫번째의 행만 정상적으로 추가되는 것을 확인할 수 있다.
  • 이 때, taxRate에 0.1를 추가하는데, 만약에 markup의 값도 NULL이라면 추가하지 않도록 하는 쿼리를 작성한다.
    • 이 때, Coalesce 함수를 사용한다.
temp = client.query('''
  WITH catalog AS (
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT 30.0, 0.30, NULL
    UNION ALL SELECT 30.0, NULL, NULL
  )

  SELECT 
    *, ROUND(COALESCE(
      costPrice * (1+markup) * (1+taxRate), 
      costPrice * 1.05 * (1+taxRate), 
      costPrice * (1+markup) * 1.10, 
      NULL), 2) AS salesPrice
  FROM catalog
  ''').to_dataframe()

print(temp)

   costPrice  markup  taxRate  salesPrice
0       30.0    0.15     0.10       37.95
1        NaN    0.21     0.15         NaN
2       30.0     NaN     0.09       34.34
3       30.0    0.30      NaN       42.90
4       30.0     NaN      NaN         NaN
  • 다만, 이 COALESCE 함수의 문제점은 구문이 복잡하기도 하지만, 두개의 참조 column이 모두 NULL이면 값을 구할수가 없다.
    • 마지막 row를 확인해보자.
  • 빅쿼리는 IFNULL 함수는 지원한다. COALESCE를 보다 단순화 하는데 있다.
  • IFNULL(a,b)는 COALESCE(a,b)와 같고, 결과물은 만약 a가 NULL이라면 b와 같다.
    • IF(a is null, b, a)와 같다.
temp = client.query('''
  WITH catalog AS (
    SELECT 30.0 AS costPrice, 0.15 AS markup, 0.1 AS taxRate
    UNION ALL SELECT NULL, 0.21, 0.15
    UNION ALL SELECT 30.0, NULL, 0.09
    UNION ALL SELECT 30.0, 0.30, NULL
    UNION ALL SELECT 30.0, NULL, NULL
  )

  SELECT 
    *, ROUND(
      costPrice * 
      (1 + IFNULL(markup, 0.05)) * 
      (1 + IFNULL(taxRate, 0.10)), 2) AS salesPrice
  FROM catalog
  ''').to_dataframe()

print(temp)

   costPrice  markup  taxRate  salesPrice
0       30.0    0.15     0.10       37.95
1        NaN    0.21     0.15         NaN
2       30.0     NaN     0.09       34.34
3       30.0    0.30      NaN       42.90
4       30.0     NaN      NaN       34.65
  • 보시다시피, 마지막 결과값도 같이 출력됨을 확인할 수 있다.

IV. Reference

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