内容、３行、マトメタ
- 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をオプションで設定する
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がデフォルトになるそうですが）
実行例はこんな感じです。
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を扱う