AthenaとQuickSightでセイバーメトリクス！？いや、MLB開催スタジアムを地図表示してみた

はじめに

データアナリティクス事業本部の藤川です。 セイバーメトリクス(SABRmetrics)ってご存知でしょうか？野球ファンなら一度は耳にしたことがあるのではないでしょうか。 野球に関するデータを蓄積し、分析して、チームの戦略に活かす分析手法のことです。 監督やコーチ、選手だけでなく、観戦する側にとっても、次の一球を占ったり、野球観戦をより一層楽しめるようになる情報ではないでしょうか。

野球の本場アメリカには、MLB(Major League Baseball)のデータを長年蓄積しているRetrosheetという著名なデータベースがあります。野茂、松井、イチロー、最近だと、ダルビッシュ、大谷、前田、筒香といった日本人メジャーリーガーももちろん収録されています。

このRetrosheetをデータ分析に使うとあらゆる分析ができるようなのですが、非常に使いにくい形式であり、すべてを活用するとなると、データ構造の理解が大変そうだなぁと...。

話しは変わって、AWSでBIツールを見ていたところ、QuickSightがジオコード機能を持っていることに気づきました。 ※参考：地理空間グラフ (マップ)

現行では、自動ジオコーディングは米国でのみ機能します。

これは、もう、アメリカの地理情報をQuickSightに表示するしかないだろうと。 この言葉に惹かれ、AthenaとQuickSightを使って、MLBの公式戦が開催されたスタジアムを地図上に表示してみました。

前提環境

• 特にありません

ローカル環境

まず始めに、Athenaで読み込むCSVファイルを用意します。
ここでは、RetrosheetのBallparksデータを使用しません。Retrosheetのデータをデータベース(MySQL)にまとめてくれた方がいらっしゃいますので、その方のお力を拝借します。 2019 – MySQL versionを参考にしました。

1. parksフォルダを作成し、parks.csvという名称で保存します。

S3

先ほどのCSVファイルをS3にアップロードし、Athenaで読み込めるようにします。 ここでは、次のような対応付けで、データベースとテーブルを用意します。

1.S3バケットを作成します。

aws s3 mb s3://hello-athena-seanlahman --profile HOGEHOGE

2.S3バケットにCSVファイルをアップロードします。

aws s3 sync ./parks s3://hello-athena-seanlahman/parks --profile HOGEHOGE

Athena

Athenaにデータベース、テーブルを作成します。

データベースを作成

1.AWSマネジメントコンソールを開きます。 2.AthenaのQuery Editorを開きます。 3.CREATE DATABASE文を実行し、データベースを作成します。

CREATE DATABASE IF NOT EXISTS lahmansbaseballdb

テーブルを作成

Athenaのテーブルを作成しなければなりませんが、Retrosheetからはテーブル定義情報が明示されていません。

wget https://raw.githubusercontent.com/WebucatorTraining/lahman-baseball-mysql/master/lahman-mysql-dump.sql

1.テーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS lahmansbaseballdb.`parks` (
    `ID` int,
    `parkalias` varchar(255),
    `parkkey` varchar(255),
    `parkname` varchar(255),
    `city` varchar(255),
    `state` varchar(255),
    `country` varchar(255)
    )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "\'", "escapeChar" = "\\" )
LOCATION 's3://hello-athena-seanlahman/parks/'

2.取り込んだデータを見てみましょう。

SELECT * FROM lahmansbaseballdb.parks

QuickSight

ここからは、QuickSightでの作業となります。

データセットを作成

1.AWSマネジメントコンソールを開きます。
2.QuickSightを開きます。
3.データセットをクリックします。
4.新しいデータセットをクリックします。
５．Athenaをクリックします。
６．新規Athenaデータソースで、次の通りに入力します。

7.接続を検証ボタンをクリックします。
8.問題なければ、データソースを作成ボタンをクリックします。
9.テーブルの選択ページが表示されるので、先ほど、Athenaで作成したlahmansbaseballdbデータベースを選択します。
10.テーブル一覧が表示されるので、先ほど作成したparksテーブルを選択します。
11.選択ボタンをクリックします。
12.データセットの作成を終了するページが表示されます。
13.今回は、S3に置いたCSVファイルのサイズが非常に小さいので、データクエリを直接実行を選択しました。
　※ファイルサイズが大きいと、フルスキャンが走ってしまい、高額請求とならないよう注意してください。
14.Visualizeボタンをクリックします。

アクセス権を設定

1.画面右上のユーザアイコンをクリックします。
2.QuickSightの管理をクリックします。
3.セキュリティとアクセス権限をクリックします。
4.追加または削除するボタンをクリックします。
5.Amazon Athenaをクリックします。
6.Amazon S3 バケットの選択が表示されます。
7.hello-athena-seanlahmanバケットのチェックボックスをクリックします。
8.Write permission for Athena Workgroupチェックボックスがアクティブ化されますが、Athenaへの書き込みは不要ですので、チェックを付けなくても良いでしょう。
9.バケットの選択ボタンをクリックします。
10.接続された製品とサービスに戻りますので、更新ボタンをクリックします。

ダッシュボードを作成

1.分析からparks analysisを選択します。
2.ビジュアルタイプから地図上のポイントを選択します。
3.フィールドリストに、Athenaのparksテーブルのカラムが表示されています。
4.フィールドリストのcityをフィールドウェルのGeospatialにドロップします。
5.アメリカの地図にMLBの公式戦が開催されたスタジアムが表示されます。

さいごに

アメリカ大陸以外にも、ハワイ(ホノルル)やプエルトリコのサンフアン(San Juan)にもMLBの公式戦が開催されたんですね。 もちろん、日本の東京ドームもありますが、ジオコードが日本に非対応であるため、プロットされません。

このように、CSVファイルがあれば、簡単にデータ分析できます。また、AthenaもQuickSightもAWSが提供するサービスなので、非常に簡単に連携できました。