ColaboratoryでBigQueryにアクセスする3つの方法

# 内容、３行、マトメタ
 - Colaboratory、ベンリ。
 - Bigqueryカラ、データトッテ分析。ノゾム。
 - 3ツ、ヤリカタカイタ。ツカエ。

ということで、ColaboratoryからBigqueryにアクセスする方法を説明してみます。

主要な３つのパターンを記載します。

1. ColaboratoryのMagic Command
2. 公式のBigQueryのクライアントAPI
3. pandas経由のAPI

もちろん、GCPのアカウントおよびBigqueryへのアクセスが許可されたユーザーアカウントが必要ですが、そこらへんは[公式リファレンス](https://cloud.google.com/bigquery/docs/quickstarts/quickstart-web-ui?hl=ja)をご覧ください。

Colaboratoryって何？状態であれば、下記の記事が詳しくまとめてくれてます。
[【秒速で無料GPUを使う】深層学習実践Tips on Colaboratory](https://qiita.com/tomo_makes/items/b3c60b10f7b25a0a5935)

# 0.前準備
ColaboratoryはGoogleアカウントさえあれば使えますが、GCPアカウントと直接紐づいているわけではないのでユーザーの認証を事前に通しておく必要があります。

まぁ、下記のコードを実行して出力セルにVerifyCodeを貼り付ければいいだけですが。。。

```python
from google.colab import auth
auth.authenticate_user()
```
＊ColaboratoryでGoogleアカウントを認証するだけなので、対象のGoogleアカウントがGCPのBigQueryにアクセスする権限を持っていなければクエリも不可能

もしくはGCPのIAM機能を使って認証用jsonを作成、アップロードして`GOOGLE_APPLICATION_CREDENTIALS`の環境変数に設定する方法もありますが、面倒なので省略です。


# 1. ColaboratoryのMagic Commandを使う
最も手っ取り早い方法は、ColaboratoryのMagic Commandの`%%bigquery`を使ってSQLをクエリする方法。

自分がアクセス可能なGCPプロジェクトのIDがわかっていればすぐに使えます。

こんな感じ。。。
## 例) 公開データセットの件数を数える

```python
# BigQueryの公開データセットの野球に関するテーブルの件数を数えてpd.DataFrameにdfという名称で突っ込む
%%bigquery --project {GCPのProjectID} df
SELECT  
  COUNT(*) as cnt
FROM 
  `bigquery-public-data.baseball.games_post_wide` 
;

```
### 出力
```
df.head()
```
|cnt|
|:-:|
|8676|

件数を調べるだけなら課金されないので、ご安心を。。。
ただし、Group化したりすると課金されます。（**月あたり１TB以内ならば無料だけど**）

## クエリの出力サイズを制限するオプションを設定する
かなり簡単にBigQueryを使えるので、パッとデータを確認する場合などはこのMagicCommandでアクセスしたいのだけれど、下手にクエリして凄く課金されるのは避けたいので、安心して使えるように以下のオプションを設定するようにする

`maximum_bytes_billed` : 課金される最大バイト数を指定（指定バイト数を越えるとエラーになる）
`verbose` : クエリの実行時間などのログを出力


```python:オプションを設定
# 100MB以上の結果ならエラーになるように設定する
%%bigquery --project {GCPのProjectID} --verbose --maximum_bytes_billed 100000000 df
SELECT  
   gameId
  ,gameStatus
  ,homeTeamName
  ,awayTeamName
  ,hitterLastName
  ,hitterFirstName
FROM 
  `bigquery-public-data.baseball.games_post_wide` 
LIMIT 100
;

```

あ、BigQueryは列課金なので`Limit`を設定しても課金額は変動しません。


詳細なリファレンスは -> [IPython Magics for BigQuery](https://googleapis.dev/python/bigquery/latest/magics.html)

# 2. 公式のライブラリを使う
パッと実行するならばMagicCommandで良いと思っていますが、いくつか痒いところに手が届かないところがあるので、私は現時点では[公式のライブラリ](https://googleapis.dev/python/bigquery/latest/index.html)を基本的に使うようにしてます。

## 現状、公式のライブラリを使っている理由
- dry_runオプションが実行できる（どれくらいボリュームがあるかを気にせず実行すると想定外のボリュームになりかねないから重要）
- Colaboratory以外の環境でも同じように使える
- BigQueryのヴァージョンアップに同期する形でAPIも更新される

## 公式APIでデータを取得してみる
ColaboratoryではGCP関連のライブラリはプリインストールされているので、`pip install`などは不要。

Colaboratoryでは不要ですが、Colaboratory以外で実行する場合には下記を実行しておきませう。
`pip install --upgrade google-cloud-bigquery`

では、先ほどと同じく公開データの野球テーブルを取得してみます。

```python
from google.cloud import bigquery

project_id = '{ProjectID}'

# authが終わっていたら普通にクライアント認証は通るはず
client = bigquery.Client(project=project_id)

# 実行するクエリ
query =  """
                SELECT  
                COUNT(*) as cnt
                FROM 
                `bigquery-public-data.baseball.games_post_wide` 
                ;
         """
# メソッドに`to_dataframe()`をつけるとpd.DataFrameで結果を受け取れる
df_official = client.query(query).to_dataframe()

```
これで、MagicCommandと同じことが出来ます。
うむ、実に簡単！！

## dry_runしてどれくらいの容量をクエリするのか事前に調べる
データの取得は上記コードを記載するだけですが、アクセスするテーブルが想定外に大きかったりするとColaboratoryのメモリでは処理しきれないほどのボリュームのデータが流れてきてしまうかもしれません。

そういうことを防ぐためにもdry_runで事前にどれくらいのボリュームになるか知っておきたいですね。

APIのオプションでdry_runが指定できるので、それで確認してみましょう。

```python:dry_run
# dry_runをオプションで設定する
job_config = bigquery.QueryJobConfig()
job_config.dry_run = True
query_job = client.query(query, job_config=job_config)
print("This query is {} bytes.".format(query_job.total_bytes_processed))

# 出力結果
>>> This query is 0 bytes.

```

`dry_run=False`にすれば普通にクエリされるので、このオプションを使い分けてデータを取得しましょう。

# 3. Pandasを使う
最後にpandas経由でBigQueryにアクセスする方法も紹介しておきます。

Jupyterで分析する場合には、とりあえずpandasはimportしていることが多いので、使い勝手はいいのですが公式のAPIではないので仕様改修のスケジュールはBigQueryの仕様変更と一致はしてません。

また、細かい点ですが現在BigQueryはStandardSQLがデフォルト、pandasはレガシーSQLがデフォルトの仕様です。（将来的にはStandardSQLがデフォルトになるそうですが）

実行例はこんな感じです。

```python:pandasでのアクセス
import pandas as pd

project_id = '{ProjectID}'

# 実行するクエリ
query =  """
                SELECT  
                COUNT(*) as cnt
                FROM 
                `bigquery-public-data.baseball.games_post_wide` 
                ;
         """

df = pd.read_gbq(
    query
  , project_id=project_id
  , verbose=False
  , dialect='standard' # StandardSQL
  )

```
pandasでも意外とシンプルな仕様で利用できるので、捨てがたい。。。
ただ、dry_runは出来ない。。。はず。。。

Google的にはpandasから`google-cloud-bigquery`に[乗り換えて欲しそう](https://cloud.google.com/bigquery/docs/pandas-gbq-migration?hl=ja)。。。


オシマイ


# 参考
[BigqueryのIAM](https://cloud.google.com/bigquery/docs/access-control?hl=ja)
[Bigqueryのクイックリファレンス](https://cloud.google.com/bigquery/docs/quickstarts/quickstart-web-ui?hl=ja)
[BigQuery Storage API を使用して BigQuery データを pandas にダウンロードする](https://cloud.google.com/bigquery/docs/bigquery-storage-python-pandas?hl=ja)
[Jupyter ノートブックで BigQuery データを可視化する](https://cloud.google.com/bigquery/docs/visualize-jupyter?hl=ja)
[【Qiita】pandasからBigQueryを扱う](https://qiita.com/itkr/items/09836b0c908b2c3cdf3a)