Google BigQueryで今期アニメに関するツイートを分析するぞい！

#Google BigQuery概要

Google BigQueryとはGoogleが提供するビッグ データ分析用のSaaSです。
https://cloud.google.com/bigquery/?hl=ja

・・・と言ってもわかりにくいのでわかりやすいイメージはこんな感じです。

あなたがHadoop環境を用意してビックデータ解析をするとしましょう。
イメージ図の自社サーバーの場合、サーバーを調達し、Hadoopクラスタを構築し、インフラ系のトラブルに対処しつつ、それが終わったらやっとデータ分析の作業に入るという遠回りな作業が必要になります。

イメージ図のクラウド（例えばAWS EC2)の場合は自分でインスタンスを立ち上げてメモリを潤沢にもったサーバーを高額でレンタルしHadoopクラスタを構築しやっと本筋のデータ分析に入れます。

BigQueryでは、自分でサーバーを用意する必要もなく、サーバーのインスタンスを立ち上げる必要もなく、データCSVをサーバーにアップロードし、ブラウザからSQLを打ち込めばデータ解析ができるという超絶ラクチンなサービスになります。

一般的にビックデータを分析する場合はHadoop環境を用意する必要があるのですが、サーバーを数台数十台用意する必要があり、手間もかかるので「近くにHadoopマスターがいないけどビックデータ分析はやりたい・・」という方にはお薦めのサービスになります。

料金体系

入会時は$300までは無料なので試用で使う分には十分無料で試せます。
使用した分だけ料金がかかる課金制度です。
https://cloud.google.com/storage/?hl=ja

Google BigQuery始め方

当然のごとく、Googleアカウントが必要になります。
また、使用した分だけ課金されるしくみなので（一定量までは無料）クレジットカード登録も必要になります。

クレカの登録が終わったら、プロジェクトを作成しておきます。

アニメに関するツイート集める

ビックデータを集めるにはTwitterのツイートだろう・・ということでTwitterのツイートのデータを集めます。
TwitterのStreaming APIを利用し検索ワードに今年のアニメ(2014年冬春夏秋)の作品タイトル、公式サイトアカウント、ハッシュタグを検索ワードにぶちこみます。

Streamig APIに関しては記事がたくさんあるのでそれを参考にしてください。
以下にサンプルコードもおいておきます。
https://github.com/AKB428/maki/blob/master/src/akb428/maki/SearchMain.java
https://github.com/AKB428/maki

感覚値ですが 50万レコード/1Day程度は普通に集められます。

ツイートのデータは最終的にCSV化する

最初からCSVに格納してもいいですし、一度RedisやMySQLに保存した後CSV化してもいいですが、BigQueryにデータを読み込ませるため最終的にCSVフォーマットにする必要があります。
（JSONも読み込めるようですが今回はCSVを例とします）

今回はツイートのデータを以下の形でCSV化しました

id	name	tweet_text	source	retweet_count	favorite_count	created_at
524950302382768128	twitter_acount	講演会でも言ってたけどほんと富野御大は進撃の巨人大好きな。	~~~ a href Janetter ~~~~	0	0	Mon Oct 20 01:40:24 JST 2014

BigQueryでアニメに関するツイートを分析する

アニメツイートを記録したCSVをBigQueryに上げる

データ形式を選択する

CSV/JSON/AppEngine Backupが選べますが今回はCSVとします。
File Uploadでブラウザからファイルアップロード・・・といきたい所ですがファイルブラウザからのアップロードは10Mバイトに制限されています。
10Mバイト以上のファイルはGoogleのクラウドストレージにファイルをアップロードする必要があります。

Google Cloud Storage にデータを上げる

Cloud StorageにBucketsを作成しファイルをアップロードします。
こちらではブラウザから10M以上のファイルのもアップロードできますが、数百Gバイトなどのでかいファイルはブラウザがタイムアウトなどを起こすのでGoogleの用意するツール(gsutil)もしくはAPI経由でアップロードするのが通常のユースケースだと思われます。

gsutil
https://cloud.google.com/storage/docs/gsutil_install
エンジニアなら絶対使いたいgsutil（Google Cloud Storage）
http://blog.daisukeyamashita.com/post/2243.html

gsutilのコマンド例

gsutil cp ソースファイル名 gs://バケット名

[sakura@data]# gsutil cp 201410_21to31.csv gs://test_002
Copying file://201410_21to31.csv [Content-Type=text/csv]...
==> NOTE: You are uploading one or more large file(s), which would run
significantly faster if you enable parallel composite uploads. This
feature can be enabled by editing the
"parallel_composite_upload_threshold" value in your .boto
configuration file. However, note that if you do this you and any
users that download such composite files will need to have a compiled
crcmod installed (see "gsutil help crcmod").
 
Retrying upload from byte 104857600 after exception. 95.92 MB/1.86 GB
Retrying upload from byte 314572800 after exception. 291.92 MB/1.86 GB
Retrying upload from byte 524288000 after exception. 491.92 MB/1.86 GB
Retrying upload from byte 734003200 after exception. 691.92 MB/1.86 GB
Retrying upload from byte 943718400 after exception. 891.92 MB/1.86 GB
Retrying upload from byte 1153433600 after exception. 1.07 GB/1.86 GB
Retrying upload from byte 1363148800 after exception. 1.26 GB/1.86 GB
Retrying upload from byte 1572864000 after exception. 1.46 GB/1.86 GB
Retrying upload from byte 1782579200 after exception. 1.65 GB/1.86 GB
Retrying upload from byte 1992294400 after exception. 1.85 GB/1.86 GB
Uploading gs://test_002/201410_21to31.csv: 1.86 GB/1.86 GB 

Cloud StrageにファイルをアップロードしたらCSVのアップロード画面に戻り、該当のBucketのファイルを選択します。

CSVのスキーマーを定義する

CSVをBigQueryのデータベースに格納するため型を定義します。
今回は以下のような形式にしました。

id: STRING ,name: STRING,tweet_text: STRING,source: STRING,retweet_count: INTEGER,favorite_count: INTEGER,created_at: STRING

CSVのファイル形式を定義する

カンマ区切りか、Tab区切りか、エラー行をいくつまで許容するかなどを設定します。
かなりエラーチェックが厳しいのでとりあえず試したい場合はチェックボックスはすべてONにするとすんなりデータが格納されます。

BigQueryにCSVデータが格納されました！

140M、50万行のデータが格納されました。

BigQueryでアニメに関するツイートを分析する

今回とりこんだCSVは、1日分のツイートから2014年1期〜4期のアニメ作品120タイトルの作品タイトル、ハッシュタグ、ツイッター公式アカウントでつぶやかれているものを格納してあります。(約50万レコード）

Queryブラウザ

CSVからBigQueryにデータが格納されるとSQLクエリブラウザが試用できます。
SQLのGUIクライアントツールを使ってる方は得に取っ付きやすい画面になっています。

1日にアニメに関するツイートした回数が多いアカウントをソート

BigQueryで使用できるSQL記法は以下
https://cloud.google.com/bigquery/query-reference

本当に基本的なSQLは使用できます。
例えばツイートが多いアカウントを多い順にソートする場合

name_count.sql

SELECT name, count(*) as count from [2014_1023.maki_v1b] 
 GROUP BY name 
 order by count desc;

今期(2014年秋期)のアニメ作品（約30タイトル）に関するつぶやきをカウントしソートする

少し長いですがSQLで以下のように書けば一発SQLで可能です。
実際はスクリプトでアニメタイトルをマスター化したDBをselectしつつ以下のSQLファイルを出力してあります。

2014_4Q_anime_titile_count.sql

SELECT
  title, COUNT(*) AS count
FROM
(select
  CASE
  -- アニメタイトル、ツイッターアカウント、ツイッターハッシュタグで検索
    WHEN tweet_text contains '天体のメソッド' OR tweet_text contains 'sora_no_method' OR tweet_text contains 'sorameso'
    THEN '天体のメソッド'
    WHEN tweet_text contains '四月は君の嘘' OR tweet_text contains 'shigatsuhakimi' OR tweet_text contains '君嘘'
    THEN '四月は君の嘘'
    WHEN tweet_text contains 'Fate/stay night' OR tweet_text contains 'Fate_SN_Anime' OR tweet_text contains 'fate_sn_anime'
    THEN 'Fate/stay night'
    WHEN tweet_text contains '大図書館の羊飼い' OR tweet_text contains 'daito_anime' OR tweet_text contains 'daitoanime'
    THEN '大図書館の羊飼い'
    WHEN tweet_text contains 'PSYCHO-PASS サイコパス 2' OR tweet_text contains 'psychopass_tv' OR tweet_text contains 'pp_anime'
    THEN 'PSYCHO-PASS サイコパス 2'
    WHEN tweet_text contains '甘城ブリリアントパーク' OR tweet_text contains 'amaburiANIME' OR tweet_text contains 'amaburi'
    THEN '甘城ブリリアントパーク'
    WHEN tweet_text contains 'ログ・ホライズン(2期)' OR tweet_text contains 'nhk_loghorizon' OR tweet_text contains 'loghorizon'
    THEN 'ログ・ホライズン(2期)'
    WHEN tweet_text contains 'オレん家のフロ事情' OR tweet_text contains 'orefuroTV' OR tweet_text contains 'orefuroTV'
    THEN 'オレん家のフロ事情'
    WHEN tweet_text contains '棺姫のチャイカ(2期)' OR tweet_text contains 'ChaikaTrabant' OR tweet_text contains '棺姫のチャイカ'
    THEN '棺姫のチャイカ(2期)'
    WHEN tweet_text contains '牙狼〈GARO〉-炎の刻印-' OR tweet_text contains 'anime_garo' OR tweet_text contains 'anime_garo'
    THEN '牙狼〈GARO〉-炎の刻印-'
    WHEN tweet_text contains '愛・天地無用！' OR tweet_text contains 'ai_tenchi' OR tweet_text contains 'ai_tenchi'
    THEN '愛・天地無用！'
    WHEN tweet_text contains 'テラフォーマーズ' OR tweet_text contains '_terraformars' OR tweet_text contains 'terraformars '
    THEN 'テラフォーマーズ'
    WHEN tweet_text contains 'デンキ街の本屋さん' OR tweet_text contains 'anime_denkigai' OR tweet_text contains '#denkigai'
    THEN 'デンキ街の本屋さん'
    WHEN tweet_text contains '弱虫ペダル GRANDE ROAD' OR tweet_text contains 'yowapeda_anime' OR tweet_text contains 'yp_anime'
    THEN '弱虫ペダル GRANDE ROAD'
    WHEN tweet_text contains 'Hi ☆ sCoool ! セハガール' OR tweet_text contains 'SHG_Official' OR tweet_text contains 'セハガール'
    THEN 'Hi ☆ sCoool ! セハガール'
    WHEN tweet_text contains 'ガンダムビルドファイターズトライ' OR tweet_text contains 'buildfighters' OR tweet_text contains 'g_bf'
    THEN 'ガンダムビルドファイターズトライ'
    WHEN tweet_text contains '繰繰れ！コックリさん' OR tweet_text contains 'gugukoku_anime' OR tweet_text contains 'gugukoku'
    THEN '繰繰れ！コックリさん'
    WHEN tweet_text contains 'selector spread WIXOSS' OR tweet_text contains 'selector_anime' OR tweet_text contains 'selector_anime'
    THEN 'selector spread WIXOSS'
    WHEN tweet_text contains 'ガンダム Gのレコンギスタ' OR tweet_text contains 'gundam_reco' OR tweet_text contains 'gレコ'
    THEN 'ガンダム Gのレコンギスタ'
    WHEN tweet_text contains 'トリニティセブン' OR tweet_text contains 'TrinitySeven_PR' OR tweet_text contains 'トリニティセブン '
    THEN 'トリニティセブン'
    WHEN tweet_text contains '異能バトルは日常系のなかで' OR tweet_text contains 'inou_PR' OR tweet_text contains '異能バトル'
    THEN '異能バトルは日常系のなかで'
    WHEN tweet_text contains 'ガールフレンド（仮）' OR tweet_text contains 'gf_anime' OR tweet_text contains 'gf_anime'
    THEN 'ガールフレンド（仮）'
    WHEN tweet_text contains '弾の王と戦姫' OR tweet_text contains 'madan_anime' OR tweet_text contains 'blade_anime'
    THEN '弾の王と戦姫'
    WHEN tweet_text contains '曇天に笑う' OR tweet_text contains 'donten_pr' OR tweet_text contains 'donten'
    THEN '曇天に笑う'
    WHEN tweet_text contains '七つの大罪' OR tweet_text contains '7_taizai' OR tweet_text contains ' 七つの大罪'
    THEN '七つの大罪'
    WHEN tweet_text contains '失われた未来を求めて' OR tweet_text contains 'waremete_anime' OR tweet_text contains 'waremete_anime'
    THEN '失われた未来を求めて'
    WHEN tweet_text contains '寄生獣 セイの格率' OR tweet_text contains 'kiseiju_anime' OR tweet_text contains 'kiseiju_anime'
    THEN '寄生獣 セイの格率'
    WHEN tweet_text contains 'グリザイアの果実' OR tweet_text contains 'grisaia_anime' OR tweet_text contains 'grisaia'
    THEN 'グリザイアの果実'
    WHEN tweet_text contains '結城友奈は勇者である' OR tweet_text contains 'anime_yukiyuna' OR tweet_text contains 'yuyuyu'
    THEN '結城友奈は勇者である'
    WHEN tweet_text contains 'クロスアンジュ 天使と竜の輪舞' OR tweet_text contains 'anime_crossange' OR tweet_text contains 'クロスアンジュ'
    THEN 'クロスアンジュ 天使と竜の輪舞'
    WHEN tweet_text contains 'SHIROBAKO' OR tweet_text contains 'shirobako_anime' OR tweet_text contains 'musani'
    THEN 'SHIROBAKO'
    WHEN tweet_text contains '俺、ツインテールになります。' OR tweet_text contains 'ore_twi_anime' OR tweet_text contains 'ore_twi'
    THEN '俺、ツインテールになります。'
    WHEN tweet_text contains 'オオカミ少女と黒王子' OR tweet_text contains 'ookamishojo_tv' OR tweet_text contains 'ookamishojo'
    THEN 'オオカミ少女と黒王子'
    WHEN tweet_text contains 'ワールドトリガー' OR tweet_text contains 'Anime_W_Trigger' OR tweet_text contains 'ワールドトリガー'
    THEN 'ワールドトリガー'
    WHEN tweet_text contains '神撃のバハムート GENESIS' OR tweet_text contains 'bahamut_genesis' OR tweet_text contains 'bahamut_genesis'
    THEN '神撃のバハムート GENESIS'
    WHEN tweet_text contains '旦那が何を言っているかわからない件' OR tweet_text contains 'anime_danna' OR tweet_text contains 'ダンナ'
    THEN '旦那が何を言っているかわからない件'
    WHEN tweet_text contains '蟲師 続章 (後半エピソード)' OR tweet_text contains 'mushishi_anime' OR tweet_text contains 'zantero'
    THEN '蟲師 続章 (後半エピソード)'
    WHEN tweet_text contains '暁のヨナ' OR tweet_text contains 'yona_anime' OR tweet_text contains '暁のヨナ'
    THEN '暁のヨナ'
    ELSE 'not hit'
  END as title,
 from [V1.2014_1026]
) group by title order by count desc;

上記のようにBigQueryではWHEN節やIF節も使用できます。

実行結果は以下

10月26日（日）のデータなので関東圏でアニメ放送があったタイトルが多く呟かれているのがわかります。

クエリーの結果はテーブルとして保存したり、CSVとしてダウンロードできるのでグラフにて加工したり
できます。

#BigQueryの使いどころ

今回は150Mバイトの少量のデータを扱いましたがBigQueryはビックデータが対象なので1T級のデータなどRDBでは取り扱えない規模を分析するときに真価を発揮します。
それ以外にも中規模の分析用RDBを取り扱うためにわざわざVPSをレンタルしてMySQLを構築したりしたくない・・・といった場合の代替案としても有益でしょう。
バッチでデータ収集→GoogleAPIでCloudStrageに格納、APIでBigQuery集計、結果をGoogleAPI等で自社サイトのレポートページに載せてJSで動的にグラフ化・・といったコンボ技が最も冴えたやり方かなと思います。