GAE/Python→BigQueryへのデータ投入、Spreadsheetからのクエリ実行＆集計

13910 ワード
GAE AppEngine BigQuery GAE テキストリンク

表題の通りのことをやってみたので手順を書いておきます。

GAE/PythonからBigQueryへのデータ投入

ライブラリ群をダウンロード

https://code.google.com/p/google-api-python-client/downloads/list
からライブラリ群（google-api-python-client-gae-1.2.zip）をダウンロード。
何も考えずにやる場合は、これらをプロジェクトフォルダ直下に放り込んでおいて読み込みましょう。

必要なものは、

  • httplib2
  • apiclient
  • oauth2client

の３つだと思います。

ってかこれ配布元がgoogle codeなので2016年１月にはなくなっちゃう。githubにそれらしきもの(https://github.com/google/google-api-python-client )はあったけど、全部は揃ってない気がする。このあたりもう少し調査が必要です。

開発サーバー用のp12キーを作成

GAE SDKのdev_appserverで、サービスアカウントのP12キーを使うにある通り、GAEの開発サーバーからBigQueryを使う場合は、GCPのコンソールからp12キーをダウンロードして変換する必要があります。

そして開発サーバー起動時に読み込ませます。以下は、MACでGAEのランチャーを使っている場合の起動方法。


$ python /usr/local/google_appengine/dev_appserver.py .  --appidentity_email_address [email protected] --appidentity_private_key_path ./path-to-converted-key.pem

mailアドレスには、Google APIサービスアカウントというのを使います。

Stack Overflowでもほぼ同様の内容の解説がありますので参考にしてください。
http://stackoverflow.com/questions/20349189/unable-to-access-bigquery-from-local-app-engine-development-server

BigQueryへの接続

テーブルを作る際にも、データを挿入する際にもBigQueryへの接続が必要なので関数を用意しておきます。


import httplib2
from apiclient.discovery import build
from oauth2client.appengine import AppAssertionCredentials
SCOPE = 'https://www.googleapis.com/auth/bigquery'

def bq_auth():
    credentials = AppAssertionCredentials(scope=SCOPE)
    http = credentials.authorize(httplib2.Http())
    bigquery = build('bigquery', 'v2', http=http)
    return bigquery

テーブルのスキーマの作成

今回使うテーブルの上位概念であるDataSetについては予め手動で作っておきます。

その上で、データを投入するテーブルのスキーマを作成します。

DATASET_ID = 'dataset_id'
PROJECT_NUMBER = '00000000000' # 各GCPプロジェクトに割り当てられている数字の番号
TABLE_ID = 'hoge_table'
HOGE_SCHEMA = [
    {'name': 'title', 'type': 'STRING'},
    {'name': 'data_type', 'type': 'STRING'},
    {'name': 'created', 'type': 'TIMESTAMP'},
]


bigquery = bq_auth()
dataset_ref = {'datasetId': DATASET_ID,
               'projectId': PROJECT_NUMBER}
table_ref = {'tableId': TABLE_ID,
             'datasetId': DATASET_ID,
             'projectId': PROJECT_NUMBER}
table = {'tableReference': table_ref,
         'schema': {'fields': HOGE_SCHEMA}}
response = bigquery.tables().insert(
    body=table, **dataset_ref).execute()

データの挿入

この例では、バッチではなく、イベント発生ごとにログを挿入します。
おそらく、GAEではちょいちょいAPI接続エラーが起きるとは思いますが、例外処理とか端折っています。乱暴かもしれませんが、taskqueに突っ込んで、例外処理せずに成功するまでリトライしてもらうようなやり方もあるでしょう。

import datetime
import uuid

now = datetime.datetime.now()
created = now.strftime("%Y-%m-%d %H:%M:%S")
insert_json = {'title': 'hoge_title',
            'data_type': 'hoge',
            'created': created}
insert_id = now.strftime("%Y%m%d%H%M%S")+uuid.uuid4().hex
insert_data = {'kind': 'bigquery#tableDataInsertAllRequest',
       'rows': [{'insertId': insert_id, 'json': insert_json}]}
bigquery = bq_auth()
response = bigquery.tabledata().insertAll(
    projectId=PROJECT_NUMBER, datasetId=DATASET_ID, tableId=TABLE_ID, body=insert_data).execute()

Spreadsheetからクエリを実行

実行スクリプトの設定

トレタの増井さんが配布しているGASのスクリプトのコピペで行けました。

参考：BigQueryを簡単にグラフにするGoogle Apps Script

  1. メニューのツールからスクリプトエディタを起動
  2. 増井さんのスクリプトをコピペして、プロジェクトナンバーの設定を変更します

  3. メニューのリソース＞Googleの拡張サービス、からBigQueryのライブラリを有効にします

  4. メニューのリソース＞DeveloperConsoleプロジェクト、からGAEのプロジェクトの番号を入れて関連付けをします

クエリを作る

Queriesという名前のシートを作成して、A列にクエリの名称＝作成されるシートの名称、B列にクエリ自体を書いていきます。１行目は項目名称に使います。

１行しか返り値がない場合は、Single row queriesに入れます。

日付ごとにまとめるクエリ

割とよくありそうなので、自分が書いた日付ごとに件数をカウントするクエリの例を書いておきます。
ちなみに、クーオーテーションはシングルクオート使ったほうが、コピペの時に便利です。

SELECT STRFTIME_UTC_USEC(DATE_ADD(created, 9, 'HOUR'), '%Y-%m-%d') as date,  
count(*) as count FROM [dataset_id.hoge_id]
GROUP EACH BY date ORDER by date
LIMIT 100

TIMESTAMPのデータは、UTCで入れておいたほうが汎用性高いと思いますが、実際に日本に関係するプロジェクトで集計する場合は、日本時間に補正したいことがあると思います。９時間の足し算をしてくれるのがDATE_ADD()になります。

また、TIMESTAMPデータを、日ごとにまとめるためにSTRFTIME_UTC_USEC()を使っています。

しかし、SQLはまともに書いたことないのでより良い書き方はあるかもしれません。

クエリを実行

増井さんのスクリプトを使うと、BigQueryというカスタムメニューが追加されます。そこから「全シートの内容を更新」を実行すると、QueriesとSingle row queriesの内容が実行されて、Quriesの場合は、A列の文字列でシートが作成され、そこにクエリの実行結果が入ってきます。

集計、グラフ化

集計はについては、GASでごりごり書くというてもあると思いますが、そんなにごりごり書かずともダッシュボード的なシートを作って、集計結果が入ってくるシートの値を参照させるというお手軽なやり方で済ませませています。参照で集計されてきたデータを使ってグラフを描くとかも、普通にグラフを描くのと同じようにやればいいだけです。