re:dashとBigQueryでMLBのオープンデータを可視化する

![FireShot Capture 15 - MLB Stats 2016(BigQuery Data_ - http___104.198.117.180_dashboard_ichiro-suzuki.png](https://qiita-image-store.s3.amazonaws.com/0/54986/c13c009e-33e4-4bb9-4b90-b0c763e665a9.png)


思いっきり遅刻しました.

この記事は[re:dash Advent Calendar 2016](http://qiita.com/advent-calendar/2016/redash) 12/23の記事です.

仕事およびプライベートでも徐々に使い始めているre:dashとBigQueryを使ってサクッと野球分析基盤を作ったので軽く紹介したいと思います.

なお, **BigQueryに関するノウハウ・各論はすっ飛ばすのでBigQueryは知ってる前提で話を進めます.**

# Starting Member

* 自己紹介
* BigQueryのMLBデータセット
* re:dashをGoogle Compute Engine(GCE)に立てる
* re:dashとBigQueryを繋ぐ
* イチローとダルビッシュの2016年を可視化してみる

# 自己紹介

* [shinyorke](https://twitter.com/shinyorke)(しんよーく、と読みます)
* HR(人事領域)系のスタートアップでエンジニアしてます.
* 「野球 Python」で探すと色々出てきます(成果が)
* re:dashは今年から触り始めました

なお,仕事でやった際の成果はこちらで発表させていただきました(沢山反響もらって嬉しいありがとうございます！！！).

[Re:dashとDocker for Macでらくらく分析・可視化環境構築 | VISASQ TECH BLOG](https://tech.visasq.com/redash-docker/)

# BigQueryのMLBデータセット

まず,野球のデータセットですが大変ありがたいことにGoogleさんがpublic datasetとして公開してくれています！

* [Major League Baseball Data](https://cloud.google.com/bigquery/public-data/baseball?hl=ja)
* [野球のマイクロモーメント : “試合が語ること” を GCP で解読する](https://cloudplatform-jp.googleblog.com/2016/11/gcp.html)

[SPORTRADAR](https://www.sportradar.com/choose_region/)という,スポーツデータをAPIで配信している会社(日本で言えばデータスタジアムっぽい会社)のデータセットを元にGoogleさんがBigQueryで使える形で置いてくれています.

なおデータはMLBの2016シーズン(プレーオフ含む)です.

特にカラムの説明が無いので若干使いにくいですが,どうやら一球速報のデータっぽく,必要な情報は揃っているようにみえるので先に進めます.

# re:dashをGoogle Compute Engine(GCE)に立てる

今回はBigQueryを使う(&個人的にどこでも使える分析基盤にする)ため,GCP上に構築しました.

構築の手順はこちらのブログを参考にさせてもらいました.

[Re:dash を GCE で試してみた](http://blog.a-know.me/entry/2016/07/18/131456)

注意するポイントとしては,BigQuery対応のイメージを使うことぐらいです.

基本的にはブログの順序に従いましたが,二箇所ほど手を入れました.

## re:dashは最新版を使う(fabricでUpgrade)

使うならメンテナンスされてる最新版を使いたいので,re:dashのオンプレ構築手順を参考に, fabric(Pythonの方)を使ってアップデートしました.

[fabricでのUpgrade方法(How to Upgrade Redash)](https://redash.io/help-onpremise/maintenance/how-to-upgrade-redash.html)

元々fabricは使ってたので抵抗なかったことに加え,gist上にfabfile.pyもあったので瞬殺で終了しました.

### Upgrade(ほぼ和訳)

#### 1. fabricとrequestsをインストール(初回のみ)

なおPython3では使えないので注意

```bash
$ pip install fabric requests
```

#### 2. gistからfabfile.pyをダウンロード

[Upgrade用のfabfile.py(gist)](https://gist.github.com/arikfr/440d1403b4aeb76ebaf8)

fabコマンドのパスが通ってる所にダウンロード

#### 3. fabコマンドでアップグレード
```bash
$ fab -H{your Redash host} -u{the ssh user for this host} -i{path to key file for passwordless login} deploy_latest_release
```

私はSSHでやりました(事前にubuntuユーザーに自分の公開鍵を入れた)

## basic認証をかける

イメージはnginxで動いているのでそこにbasic認証をかけました.

一瞬使うだけなのでSSL化まではしませんでしたが流石に穴が開きっぱなしは...ね

# re:dashとBigQueryを繋ぐ

Google Cloud ConsoleのAPI Manager→認証情報からサービスアカウントキーのJSONを発行,re:dashのデータソースにアップロードします.

ここは割とアッサリできました.

# イチローとダルビッシュの2016年を可視化してみる

色々とクエリーを書いて試した結果です.

真似したい方の為に,クエリーも貼っておきますね.

## イチローに放られたボールの球種割合

ホントはヒットしたボールの割合を出したかったのですがカラムで絞れず...orz

```sql
SELECT PITCHTYPEDESCRIPTION,
       count(PITCHTYPEDESCRIPTION) as pitched
FROM [bigquery-public-data:baseball.games_wide]
WHERE HITTERLASTNAME = 'Suzuki'
  AND HITTERFIRSTNAME = 'Ichiro'
  AND PITCHTYPEDESCRIPTION != ''
GROUP BY PITCHTYPEDESCRIPTION
```

![FireShot Capture 16 - Ichiro Suzuki(Pitched)I Redash - http___104.198.117.180_queries_1_source#2.png](https://qiita-image-store.s3.amazonaws.com/0/54986/4663ba84-9eb9-a5f2-3a30-4209e267e754.png)


## ダルビッシュのファストボール(直球)の月別平均球速

他のピッチャーと比較しても良かったかな...

```sql
SELECT MONTH(STARTTIME) AS MONTH,
       AVG(PITCHSPEED) AS pitch_speed_avg
FROM [bigquery-public-data:baseball.games_wide]
WHERE PITCHERFIRSTNAME='Yu'
  AND PITCHERLASTNAME='Darvish'
  AND PITCHTYPE = 'FA'
GROUP BY MONTH
ORDER BY MONTH
```

![FireShot Capture 17 - Yu Darvish Pitch Speeds(Fast Ball_ - http___104.198.117.180_queries_2_source#4.png](https://qiita-image-store.s3.amazonaws.com/0/54986/ad2fee9a-cb4c-8d9d-2c5d-7923aec01aba.png)

気になる方は是非真似してみてください！