内容、３行、マトメタ

Colaboratory、ベンリ。
Bigqueryカラ、データトッテ分析。ノゾム。
3ツ、ヤリカタカイタ。ツカエ。

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

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

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

もちろん、GCPのアカウントおよびBigqueryへのアクセスが許可されたユーザーアカウントが必要ですが、そこらへんは公式リファレンスをご覧ください。

Colaboratoryって何？状態であれば、下記の記事が詳しくまとめてくれてます。
【秒速で無料GPUを使う】深層学習実践Tips on Colaboratory

0.前準備

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

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

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がわかっていればすぐに使えます。

こんな感じ。。。

例) 公開データセットの件数を数える

# 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 : クエリの実行時間などのログを出力

オプションを設定

# 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

2. 公式のライブラリを使う

パッと実行するならばMagicCommandで良いと思っていますが、いくつか痒いところに手が届かないところがあるので、私は現時点では公式のライブラリを基本的に使うようにしてます。

現状、公式のライブラリを使っている理由

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

公式APIでデータを取得してみる

ColaboratoryではGCP関連のライブラリはプリインストールされているので、pip installなどは不要。

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

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

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が指定できるので、それで確認してみましょう。

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がデフォルトになるそうですが）

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

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に乗り換えて欲しそう。。。

オシマイ

参考

BigqueryのIAM
Bigqueryのクイックリファレンス
 BigQuery Storage API を使用して BigQuery データを pandas にダウンロードする
 Jupyter ノートブックで BigQuery データを可視化する
 【Qiita】pandasからBigQueryを扱う

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