Ch05 Query Essentials(2)

2020-04-24

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

Page content

1. 구글 클라우드 설정

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

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

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

2. 사용자 계정 인증

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

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

Authenticated

3. 쿼리 기본

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

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

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

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

Ch05-Query_Essentials(1)에서는 SELECT와 AS에 대해 배우는 시간을 가졌다.

오늘 포스트에서는 WHERE, REPLACE, EXCEPT 구문에 대해 학습하는 시간을 갖도록 한다.

(1) WHERE

먼저 tripduration이 10분 이내인 것에 관한 데이터를 찾도록 한다. FROM 구문 다음에는 WHERE 절을 추가하면 된다.

from google.cloud import bigquery
from tabulate import tabulate

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

temp = client.query('''
  SELECT 
    gender, tripduration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration < 600
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt="pipe", headers="keys"))

|    | gender   |   tripduration |
|---:|:---------|---------------:|
|  0 | male     |            432 |
|  1 | female   |            238 |
|  2 | male     |            593 |
|  3 | male     |            414 |
|  4 | male     |            474 |

기대한대로 600 이상은 보이지가 않는다. 이번에는 AND조건문을 활용하여 5분과 10분 사이에서 female이 빌린 rental 현황을 파악해보자. 이 때 WHERE절이 어떻게 변하는지 주의깊에 보기를 바란다.

temp = client.query('''
  SELECT 
    gender, tripduration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration >= 300 AND tripduration < 600 AND gender ='female'
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt="pipe", headers="keys"))

|    | gender   |   tripduration |
|---:|:---------|---------------:|
|  0 | female   |            309 |
|  1 | female   |            304 |
|  2 | female   |            476 |
|  3 | female   |            406 |
|  4 | female   |            365 |

그런데, 만약에 Gender의 값이 female, male, unknown, etc 이렇게 구성이 되어 있을 때, NOT female의 값은 어떻게 될까? 이 때에는 nonfemale은 3개의 값이 된다. 이럴 경우에는 빅쿼리에서는 WHERE tripduration < 600 AND NOT gender='female' 소스를 작성할 수 있다.

또한 ()을 사용해서 값을 통제할 수 있다. 우선 아래코드를 보자.

temp = client.query('''
  SELECT 
    gender, tripduration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE (tripduration < 600 AND gender ='female') or gender = 'male'
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt="pipe", headers="keys"))

|    | gender   |   tripduration |
|---:|:---------|---------------:|
|  0 | male     |            432 |
|  1 | male     |            799 |
|  2 | female   |            238 |
|  3 | male     |            668 |
|  4 | male     |            593 |

위 코드에서 (tripulation < 600 AND gender='female') gender = female 이면서 tripulation < 600 이하인 것을 1차적으로 조회하고, 덧붙여서 gender = male인 것을 추가하는 코드가 된다.

WHERE 구문에서 ()을 꼭 사용해야 하는 경우가 있다. 우선 아래 에러부터 경험해보자.

temp = client.query('''
  SELECT
    gender, tripduration/60 AS minutes
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE minutes < 10
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers="keys"))

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-13-36c47fd332d3> in <module>()
      5   WHERE minutes < 10
      6   LIMIT 5
----> 7   ''').to_dataframe()
      8 
      9 print(tabulate(temp, tablefmt='pipe', headers="keys"))


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in to_dataframe(self, bqstorage_client, dtypes, progress_bar_type)
   3103             ValueError: If the `pandas` library cannot be imported.
   3104         """
-> 3105         return self.result().to_dataframe(
   3106             bqstorage_client=bqstorage_client,
   3107             dtypes=dtypes,


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in result(self, timeout, page_size, retry, max_results)
   2972         """
   2973         try:
-> 2974             super(QueryJob, self).result(timeout=timeout)
   2975 
   2976             # Return an iterator instead of returning the job.


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in result(self, timeout, retry)
    766             self._begin(retry=retry)
    767         # TODO: modify PollingFuture so it can pass a retry argument to done().
--> 768         return super(_AsyncJob, self).result(timeout=timeout)
    769 
    770     def cancelled(self):


/usr/local/lib/python3.6/dist-packages/google/api_core/future/polling.py in result(self, timeout)
    125             # pylint: disable=raising-bad-type
    126             # Pylint doesn't recognize that this is valid in this case.
--> 127             raise self._exception
    128 
    129         return self._result


BadRequest: 400 Unrecognized name: minutes at [5:9]

(job ID: 6e11f75b-208a-4f23-832d-9f1af16d9a1d)

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

    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:
   2:  SELECT
   3:    gender, tripduration/60 AS minutes
   4:  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
   5:  WHERE minutes < 10
   6:  LIMIT 5
   7:  
    |    .    |    .    |    .    |    .    |    .    |    .    |

BadRequest: 400 Unrecognized name: minutes at [5:9] 이 에러 문구를 보면 minutes에서 에러가 나는 것을 확인할 수 있는데, AS(=Alias)를 쓰게 되는 Column명은 WHERE절에 쓸 수 없다.

이럴 경우에는 원 column명을 작성해서 ()에서 처리를 해줘야 한다. 예를 들면 아래와 같다.

temp = client.query('''
  SELECT
    gender, tripduration / 60 AS minutes
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE (tripduration / 60) < 10
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers="keys"))

|    | gender   |   minutes |
|---:|:---------|----------:|
|  0 | male     |   7.2     |
|  1 | female   |   3.96667 |
|  2 | male     |   9.88333 |
|  3 | male     |   6.9     |
|  4 | male     |   7.9     |

우리가 원하는 값이 나왔고, column명도 tripduration에서 minutes로 바뀌는 것을 확인할 수 있다.

(2) EXCEPT, REPLACE

이 문법을 쓰는 이유는 BigQuery의 가격정책과도 연관이 있다. 클라우드에서 비용은 만만하지 않은데, 조회비용은 가급적 아끼는 것이 중요하다.

따라서, 원칙적으로는 SELECT * 문법은 쓰지 말아야 한다. 그럼에도 불구하고, 데이터 분석일을 하다보면 어쩔 수 없이 써야 하는 경우가 올 수도 있다. 이 때, EXCEPT 문법을 활용한다. 이 문법은 SELECT 구문에서 사용이 가능하다.

아래 구문을 확인해보자.

temp = client.query('''
  SELECT
    *
  FROM `bigquery-public-data.new_york_citibike.citibike_stations`
  WHERE name LIKE '%Riverside'
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers="keys"))

| station_id   | name   | short_name   | latitude   | longitude   | region_id   | rental_methods   | capacity   | eightd_has_key_dispenser   | num_bikes_available   | num_bikes_disabled   | num_docks_available   | num_docks_disabled   | is_installed   | is_renting   | is_returning   | eightd_has_available_keys   | last_reported   |
|--------------|--------|--------------|------------|-------------|-------------|------------------|------------|----------------------------|-----------------------|----------------------|-----------------------|----------------------|----------------|--------------|----------------|-----------------------------|-----------------|

WHERE LIKE %Riverside 문법은 일종의 와일드카드라고 불리운다. 즉, 0개이상의 문법과 일치해야 하는데, 여기에서는 Riverside문자와 일치하는 값(row)을 찾아서 관련있는 모든 열(column)을 조회한다.

이번에는 EXCEPT문법을 추가해본다.

temp = client.query('''
  SELECT
    * EXCEPT(short_name, last_reported)
  FROM `bigquery-public-data.new_york_citibike.citibike_stations`
  WHERE name LIKE '%Riverside'
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers="keys"))

| station_id   | name   | latitude   | longitude   | region_id   | rental_methods   | capacity   | eightd_has_key_dispenser   | num_bikes_available   | num_bikes_disabled   | num_docks_available   | num_docks_disabled   | is_installed   | is_renting   | is_returning   | eightd_has_available_keys   |
|--------------|--------|------------|-------------|-------------|------------------|------------|----------------------------|-----------------------|----------------------|-----------------------|----------------------|----------------|--------------|----------------|-----------------------------|

첫번째 조회값과 비교할 때, short_name과 last_reported값이 없어진 것을 확인할 수 있다. 특정 Column에 계산값을 바꿔서 업데이트 하는 경우 REPLACE 구문을 추가하기도 한다. 다음 예를 확인해보자.

temp = client.query('''
  SELECT
    *
  FROM
    `bigquery-public-data.new_york_citibike.citibike_stations` 
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    |   station_id | name                          |   short_name |   latitude |   longitude |   region_id | rental_methods   |   capacity | eightd_has_key_dispenser   |   num_bikes_available |   num_bikes_disabled |   num_docks_available |   num_docks_disabled | is_installed   | is_renting   | is_returning   | eightd_has_available_keys   | last_reported       |
|---:|-------------:|:------------------------------|-------------:|-----------:|------------:|------------:|:-----------------|-----------:|:---------------------------|----------------------:|---------------------:|----------------------:|---------------------:|:---------------|:-------------|:---------------|:----------------------------|:--------------------|
|  0 |          144 | Nassau St & Navy St           |      4812.02 |    40.6984 |    -73.9807 |          71 | KEY,CREDITCARD   |         58 | False                      |                    50 |                    1 |                     7 |                    0 | True           | True         | True           | False                       | 2020-04-14 00:00:00 |
|  1 |          482 | W 15 St & 7 Ave               |      6030.06 |    40.7394 |    -73.9993 |          71 | KEY,CREDITCARD   |         77 | False                      |                    69 |                    0 |                     8 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  2 |         2005 | Railroad Ave & Kay Ave        |      4990.01 |    40.7053 |    -73.971  |          71 | KEY,CREDITCARD   |         12 | False                      |                     4 |                    0 |                     8 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  3 |          461 | E 20 St & 2 Ave               |      5971.08 |    40.7359 |    -73.9821 |          71 | KEY,CREDITCARD   |         56 | False                      |                    46 |                    1 |                     9 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  4 |           83 | Atlantic Ave & Fort Greene Pl |      4354.07 |    40.6838 |    -73.9763 |          71 | KEY,CREDITCARD   |         62 | False                      |                    51 |                    0 |                    11 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |

temp = client.query('''
  SELECT
    * REPLACE(capacity + 5 AS capacity)
  FROM
    `bigquery-public-data.new_york_citibike.citibike_stations` 
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    |   station_id | name                          |   short_name |   latitude |   longitude |   region_id | rental_methods   |   capacity | eightd_has_key_dispenser   |   num_bikes_available |   num_bikes_disabled |   num_docks_available |   num_docks_disabled | is_installed   | is_renting   | is_returning   | eightd_has_available_keys   | last_reported       |
|---:|-------------:|:------------------------------|-------------:|-----------:|------------:|------------:|:-----------------|-----------:|:---------------------------|----------------------:|---------------------:|----------------------:|---------------------:|:---------------|:-------------|:---------------|:----------------------------|:--------------------|
|  0 |          144 | Nassau St & Navy St           |      4812.02 |    40.6984 |    -73.9807 |          71 | KEY,CREDITCARD   |         63 | False                      |                    50 |                    1 |                     7 |                    0 | True           | True         | True           | False                       | 2020-04-14 00:00:00 |
|  1 |          482 | W 15 St & 7 Ave               |      6030.06 |    40.7394 |    -73.9993 |          71 | KEY,CREDITCARD   |         82 | False                      |                    69 |                    0 |                     8 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  2 |         2005 | Railroad Ave & Kay Ave        |      4990.01 |    40.7053 |    -73.971  |          71 | KEY,CREDITCARD   |         17 | False                      |                     4 |                    0 |                     8 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  3 |          461 | E 20 St & 2 Ave               |      5971.08 |    40.7359 |    -73.9821 |          71 | KEY,CREDITCARD   |         61 | False                      |                    46 |                    1 |                     9 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |
|  4 |           83 | Atlantic Ave & Fort Greene Pl |      4354.07 |    40.6838 |    -73.9763 |          71 | KEY,CREDITCARD   |         67 | False                      |                    51 |                    0 |                    11 |                    0 | True           | True         | True           | False                       | 2020-04-15 00:00:00 |

column명 capacity 행을 보면 5씩 늘어나는 것을 볼 수 있다.

이번시간에는 WHERE 구문과 함께, EXCEPT, REPLACE구문에 대해 배웠다. 도움이 되기를 바란다. 다음 포스트에서는 With를 활용한 SubQuery 그리고 ORDER BY 대해서 배우도록 한다.

4. Reference

“Getting Started with BigQuery.” Google, Google, colab.research.google.com/notebooks/bigquery.ipynb.