今回必要なのはBigQueryを操作するためのgcp.bigqueryと、dataframeを使用するためのpandasなので、それらをimportします。
import gcp.bigquery as bq
import pandas as pd
bq.Query('Query内容').results()で簡単に結果を表示することができます。 今回はBigQueryでサンプルとして提供されているgithub_timelineというテーブルを使用しています。
bq.Query('SELECT * FROM [publicdata:samples.github_timeline] LIMIT 3').results()
%%sql で行頭を始めると、sqlのクエリを入力することができます。また、 --module モジュール名 と指定することで、クエリを保存することができます。
今回はgithub_timelineというテーブルから、レポジトリ作成日、閲覧数、言語を作成日が新しいものから上位百万件をとってきています。
%%sql --module requests
SELECT repository_created_at, repository_watchers, repository_language
FROM [publicdata:samples.github_timeline]
order by repository_created_at desc
limit 1000000
%%bigquery sample --count 5 --query requests
df = bq.Query('query内容').to_dataframe()とすることで、dataframeにqueryを投げた結果を出力することができます。
df = bq.Query(requests).to_dataframe()
len(df)
1000000
df.head(5)
repository_created_at | repository_watchers | repository_language | |
---|---|---|---|
0 | 2012-05-01 21:01:20 | 1 | None |
1 | 2012-05-01 21:01:00 | 1 | None |
2 | 2012-05-01 21:00:57 | 1 | None |
3 | 2012-05-01 21:00:43 | 1 | None |
4 | 2012-05-01 21:00:43 | 1 | None |
例えば日ごとのレポジトリ閲覧数合計の推移を図示すると以下のようになります
df['timestamp'] = pd.to_datetime(df['repository_created_at'])
data = pd.Series(df['repository_watchers'].values, index=df['timestamp'])
data.resample('d', how='sum').plot(figsize=(20,10))
<matplotlib.axes._subplots.AxesSubplot at 0x7ff12ff1c190>
他にも例えば言語ごとにレポジトリの投稿数を合計して図示することもできます(上位20言語)
df['repository_language'].value_counts().head(20).plot(kind='bar', figsize=(20,10))
<matplotlib.axes._subplots.AxesSubplot at 0x7ff134608690>
言語ごとに閲覧数を合計して図示することも簡単にできます。(上位20言語)
groups = df.groupby('repository_language')
groups['repository_watchers'].sum().order(ascending=False).head(20).plot(kind='bar', figsize=(20,10))
<matplotlib.axes._subplots.AxesSubplot at 0x7ff13426ab10>