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

  • 17
    いいね
  • 0
    コメント

FireShot Capture 15 - MLB Stats 2016(BigQuery Data_ - http___104.198.117.180_dashboard_ichiro-suzuki.png

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

この記事はre:dash Advent Calendar 2016 12/23の記事です.

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

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

Starting Member

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

自己紹介

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

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

Re:dashとDocker for Macでらくらく分析・可視化環境構築 | VISASQ TECH BLOG

BigQueryのMLBデータセット

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

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

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

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

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

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

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

Re:dash を GCE で試してみた

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

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

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

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

fabricでのUpgrade方法(How to Upgrade Redash)

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

Upgrade(ほぼ和訳)

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

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

$ pip install fabric requests

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

Upgrade用のfabfile.py(gist)

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

3. fabコマンドでアップグレード

$ 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

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

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

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

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

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