Ch18 Mathematical Functions

2020-05-25

구글 클라우드, 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. Mathematical Functions

빅쿼리 내부에는 다양한 표준 SQL의 수학 함수를 제공하고 있다.
위 함수들은 integer & floating-point 유형에서 사용이 가능하니 참고하기를 바란다.
다음은 그 중에서 몇가지 예시를 통해 어떻게 사용하는지 알아보도록 한다.
개념적으로 어려운 것은 아니다. 다만, SQL에서는 어떻게 사용하는지 한번 보자는데 의의가 있다.

(1) ROUND

X만 존재하는 경우, ROUND는 X를 가장 가까운 정수로 반올림을 한다.
N이 존재하는 경우, ROUND는 X를 소수점 이하 N자리까지 반올림을 한다.
N이 음수이면 ROUND는 소수점 왼쪽의 자릿수를 반올림 한다.
중간값은 올림된다.
오버플로가 발생하면 오류를 생성한다.
사용방법은 아래와 같다.

ROUND(X [, N])

from google.cloud import bigquery
from tabulate import tabulate
import pandas as pd

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

temp = client.query('''
  WITH example AS (
      SELECT 
        'Sat' AS day
        , 1451 AS numrides
        , 1018 AS oneways
      UNION ALL SELECT 'SUN', 2376, 936
  )

  SELECT *
    , ROUND(oneways/numrides, 2) AS frac_oneway 
  from example
  ''').to_dataframe()

print(temp)

   day  numrides  oneways  frac_oneway
0  Sat      1451     1018         0.70
1  SUN      2376      936         0.39

(2) IEEE_DIVIDE

보통 나눗셈을 할 때, 0으로 나누기를 하면 overflow를 생성한다.
이 때, IEEE_DIVIDE를 사용하면 오버플로 오류를 생성하지 않는다.
이 때에는 FLOAT64를 결과값으로 반환한다.
특수한 경우에 대해 살펴보자.
- 결과가 오버플로되면 +/-inf를 반환한다.
- Y=0이고 X=0이면, NaN을 반환한다.
- Y=0이고 X!=0이면, +/-inf를 반환한다.
- X = +/-inf이고 Y = +/-inf이면 NaN을 반환한다.
사용방법은 아래와 같다.

IEEE_DIVIDE(X, Y)

temp = client.query('''
  WITH example AS (
      SELECT 
        'Sat' AS day
        , 1451 AS numrides
        , 1018 AS oneways
      UNION ALL SELECT 'SUN', 2376, 936
      UNION ALL SELECT 'Wed', 0, 0
  )

  SELECT *
    , ROUND(IEEE_DIVIDE(oneways, numrides), 2) AS frac_oneway 
  from example
  ''').to_dataframe()

print(temp)

   day  numrides  oneways  frac_oneway
0  Sat      1451     1018         0.70
1  SUN      2376      936         0.39
2  Wed         0        0          NaN

IEEE_DIVIDE의 특수한 경우에 대해 좀 더 살펴보자.
아래 표는 IEEE_DIVIDE의 특수한 경우를 나열한 것이다.

분자 데이터 유형(X)	분모 데이터 유형(Y)	결과 값
0을 제외한 모든 유형	0	+/-inf
0	0	NaN
0	NaN	NaN
0	NaN	NaN
NaN	0	NaN
+/-inf	+/-inf	NaN

(3) SAFE Functions

일반적으로 연산을 수행할 때, SAFE 함수를 사용하게 되면 기존에는 error를 발생하던 것이 NULL을 결과값으로 반환한다.
아래 코드와 같이 SAFE가 없다면 에러를 발생시킨다.

temp = client.query('''
  SELECT LOG(10, -3), LOG(10, 3)
  ''').to_dataframe()

print(temp)

ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 2))


---------------------------------------------------------------------------

BadRequest                                Traceback (most recent call last)

<ipython-input-6-40a71401de97> in <module>()
      1 temp = client.query('''
      2   SELECT LOG(10, -3), LOG(10, 3)
----> 3   ''').to_dataframe()
      4 
      5 print(temp)

.
.
.    

BadRequest: 400 GET https://bigquery.googleapis.com/bigquery/v2/projects/bigquerytutorial-274406/queries/35b716dc-77b7-46c4-acb5-9202ad15115c?maxResults=0&location=US: Floating point error in function: LOG(10, -3)

(job ID: 35b716dc-77b7-46c4-acb5-9202ad15115c)

   -----Query Job SQL Follows-----   

    |    .    |    .    |    .    |
   1:
   2:  SELECT LOG(10, -3), LOG(10, 3)
   3:  
    |    .    |    .    |    .    |

그런데, 위 코드에서 SAFE를 추가하면 코드는 강제 실행된다.
여기에서 주목할 것은 SAFE.LOG(10, -3)인데, 원래는 계산되지 않는 수이나, None로 반환된다.

temp = client.query('''
  SELECT SAFE.LOG(10, -3), SAFE.LOG(10, 3)
  ''').to_dataframe()

print(temp)

    f0_       f1_
0  None  2.095903

IV. 결론

SQL에서 사칙연산에서 발생하는 오류들은 대부분 일상적인 산수에서는 허용되지 않거나 무조건 0으로 결과값이 반환되는 것이다. (예: 분모가 0인 경우)
그런데, 그렇다고 해서 SQL 본연의 업무 중 하나인 쿼리조회가 되지 않는다면 일일이 계산식을 다 맞춰야 하는 불필요한 작업이 동반될 수 밖에 없다.
이러한 부분을 일부 해결해주는 것이 SAFE Function & NaN의 결과값 반환이 아닐까 한다.
다음 시간에는 비교 연산자와 소수점 연산에 대해서 배워보도록 한다.

V. Reference

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