Help us understand the problem. What is going on with this article?

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

内容、3行、マトメタ

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

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

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

  1. ColaboratoryのMagic Command
  2. 公式のBigQueryのクライアントAPI
  3. 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 {GCPProjectID} df
SELECT  
  COUNT(*) as cnt
FROM 
  `bigquery-public-data.baseball.games_post_wide` 
;

出力

df.head()
cnt
8676

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

クエリの出力サイズを制限するオプションを設定する

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

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

オプションを設定
# 100MB以上の結果ならエラーになるように設定する
%%bigquery --project {GCPProjectID} --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を扱う

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away