新井正貴

2014.12.19

BigQuery × Perfume × tweet分析(Advent Calendar 19日目)

この記事は Google Cloud Platform Advent Calendar 2014 – Qiita
の19日目の記事も兼ねています。

こんにちは。Perfumeエンジニアの@massa142です。
今回の記事は、Google BigQueryに関して前々から気になっていたんで、このAdvent Calendarをきっかけに遊んでみたよっていうのが趣旨です。

やることは、タイトル通り『Perfumeに関するtweet分析』です(｀･ω･´)ゞ
7年連続7回目の出場となるNHK紅白歌合戦を間近に控えた、この年の瀬の１週間分のTwitterデータを取得して分析していこうと思います。

Google BigQueryってなに？

BigQueryとは、Google Cloud Platformの大規模クエリサービスです。
詳しくは@naoya_itoさんの以下のスライドをご覧になって下さい。BigQueryの特徴と内部の仕組みがわかりやすくまとめられてます。
Google BigQuery の話 #gcpja // Speaker Deck

公式ドキュメントはこちらです。

本当にざっくりまとめると、こんな感じです。
・Googleが社内でビッグデータを処理するために使用していたDremelをサービス化したもの
・SQLっぽいクエリで分散処理
・処理めっちゃ速い

参考にした書籍 / 記事

今回BigQueryを扱っていくうえで、以下の書籍と記事を参考にしました。

Google BigQueryではじめる自前ビッグデータ処理入門

posted with amazlet at 14.12.18

清野克行
秀和システム
売り上げランキング: 116,223

Amazon.co.jpで詳細を見る

GoogleCloudPlatform – データインポートとDATASETとTABLEと。 – Qiita
Google Compute Engine を使うための準備（Google Cloud SDK インストール – Linux & OS X 編） – Qiita

Twitterデータ収集

今回Perfumeに関するTwitterデータを集めるために、上記で紹介した書籍でも取り上げられていたmentionというサービスを利用しました。
mentionでは管理画面で設定したキーワードで、SNSからお手軽にデータを引っ張ってきてくれます。
ここで取得したデータをcsvで吐き出して、BigQueryにアップロードします。

今回はこんな感じの設定でデータ取得しました。

検索キーワード：Perfume || prfm || perfume_um
(prfmとperfume_umはPerfumeに関する投稿で用いるハッシュタグです)

除外キーワード：RT

対象SNS：Twitter　言語：日本語

期間：12/12(金)〜12/18(木)

取得tweet総数：14,728件

サインアップ＆設定

あらかじめBigQueryを使えるように、以下からサインアップと設定をぽちぽち行って下さい。
https://cloud.google.com/bigquery/sign-up

ここの「Sign Up for BigQuery」の手順１〜５を辿っていけばOKです。

table作成＆データアップロード

1.先ほど作成したプロジェクトのDashboard画面からBigQueryの画面に遷移
（「BigQueryを試す」ボタンをクリックすればOK）
2.適当なDatasetを作成
3.Tableを作成
・ⅰ.Table IDを新規作成
・ⅱ.mentionからエクスポートしたcsvファイルをアップロード
・ⅲ.Schemaは以下を指定

id:integer,title:string,description:string,url:string,published_at:string,source_type:string,language:string,country:string,favorite:integer,important:string,tone:string,source_name:string,source_url:string,parent_url:string,parent_id:integer

1	id:integer,title:string,description:string,url:string,published_at:string,source_type:string,language:string,country:string,favorite:integer,important:string,tone:string,source_name:string,source_url:string,parent_url:string,parent_id:integer

※Schemaで指定できるData typeの種類はPreparing Data for BigQueryで列挙されてます。

・ⅳ.設定オプションは以下の通り

Source Format: CSV
Skip Leading Rows: 1
Number of errors allowed: 0
Allow Quoted Newlines: true
Allow Jagged Rows: true
Ignore Unknown Values: true

Source Format: CSV

Skip Leading Rows: 1

Number of errors allowed: 0

Allow Quoted Newlines: true

Allow Jagged Rows: true

Ignore Unknown Values: true

・ⅴ.Submit！！

これで無事、BigQueryにデータをアップロードできましたm(_ _)m
こんな感じの画面が表示されます。

tweet分析

データのアップロードが完了してTableができたので、実際にクエリを投げてみようと思います。
今回やることは、
『Pefumeのメンバー３人の名前が、各々どれくらいtweetされているを集計してみよう！』
というものです。

（´-`）.｡oO（データ分析と銘打っておきながら、大したことやってなくてすみません…）

各クエリの結果と実行時間は以下のようになりました。

Name	Query	Count	Time
あ〜ちゃん	SELECT COUNT(*) FROM [myDataset.Perfume] where title CONTAINS “あ〜ちゃん” or description CONTAINS “あーちゃん”;	270	2.0s
かしゆか	SELECT COUNT(*) FROM [myDataset.Perfume] where title CONTAINS “かしゆか”;	582	1.6s
のっち	SELECT COUNT(*) FROM [myDataset.Perfume] where title CONTAINS “のっち”;	3432	1.9s

1週間しか集計していないのでデータに偏りがあるかもしれませんが、のっちの人気が圧倒的(ФωФ)

コマンドラインでもできるよ

これまではwebブラウザからBigQueryをいじってきましたが、コンソールからできたほうが楽だと思うんで、bq commandについても触れておきます。

コンソールで扱うためには、まずGoogle Cloud SDKをインストールする必要があります。
このインストール方法に関しては、以下の記事が詳しいので参照して頂ければと思います。
Google Compute Engine を使うための準備（Google Cloud SDK インストール – Linux & OS X 編） – Qiita

※ちなみにbashではない方は、別途pathを通す必要があります。僕はzshを使っているので、zshrcに以下を記述しました。

export PATH=/<hoge>/google-cloud-sdk/bin:$PATH

1	export PATH=/<hoge>/google-cloud-sdk/bin:$PATH

インストールが完了したら、google cloudにauthログインします。

$ gcloud auth login

1	$ gcloud auth login

これを叩くと自動でブラウザが表示されると思うので、それに従って認証を通していって下さい。

Saved Application Default Credentials.

You are now logged in as [arai.masataka@aainc.co.jp].
Your current project is [None].  You can change this setting by running:
  $ gcloud config set project PROJECT

Saved Application Default Credentials.

You are now logged in as [arai.masataka@aainc.co.jp].

Your current project is [None]. You can change this setting by running:

$ gcloud config set project PROJECT

ここでようやく、bq commandの登場です。
まずはbigqueryrcファイル初期化のために、おもむろに

$ bq init

$ bq init

と叩いて下さい。

Welcome to BigQuery! This script will walk you through the
process of initializing your .bigqueryrc configuration file.

1 2	Welcome to BigQuery! This script will walk you through the process of initializing your .bigqueryrc configuration file.

こんな感じで歓迎してくれます。

初期化が完了して、作業を行っていくPROJECTを指定すればOKです。
さっきブラウザでやっていたことは、以下のようなコマンドになります。

$ bq ls
  datasetId
 -----------
  myDataset

$ bq query 'select count(*) from myDataset.Perfume where description contains "のっち"'
Waiting on bqjob_r5f3a350bb2d75667_0000014a5eab5241_1 ... (0s) Current status: RWaiting on bqjob_r5f3a350bb2d75667_0000014a5eab5241_1 ... (0s) Current status: DONE
+------+
| f0_  |
+------+
| 1716 |
+------+

$ bq ls

datasetId

-----------

myDataset

$ bq query 'select count(*) from myDataset.Perfume where description contains "のっち"'

Waiting on bqjob_r5f3a350bb2d75667_0000014a5eab5241_1 ... (0s) Current status: RWaiting on bqjob_r5f3a350bb2d75667_0000014a5eab5241_1 ... (0s) Current status: DONE

+------+

| f0_ |

+------+

| 1716 |

+------+

最後に

以上、Google BigQueryに関して色々見てきました。
うちの会社ではまだ業務でBigQueryを利用していませんが、Google Analyticsのログ解析としてGoogle BigQueryと連携できるプレミアムプランを導入しようという声もあるので、はやくそうなってくれるといいですね…|ω・)ﾁﾗｯ

この記事は以下の２つのAdvent Calendar 19日目の記事を兼ねています。
Google Cloud Platform Advent Calendar 2014 – Qiita
Allied ArchitectsEngineer Blog Advent Calendar 2014

弊社で行っているAdvent Calendarの明日は@cobonasの番です！