この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
データアナリティクス事業本部の藤川です。 セイバーメトリクス(SABRmetrics)ってご存知でしょうか？野球ファンなら一度は耳にしたことがあるのではないでしょうか。 野球に関するデータを蓄積し、分析して、チームの戦略に活かす分析手法のことです。 監督やコーチ、選手だけでなく、観戦する側にとっても、次の一球を占ったり、野球観戦をより一層楽しめるようになる情報ではないでしょうか。
野球の本場アメリカには、MLB(Major League Baseball)のデータを長年蓄積しているRetrosheetという著名なデータベースがあります。野茂、松井、イチロー、最近だと、ダルビッシュ、大谷、前田、筒香といった日本人メジャーリーガーももちろん収録されています。
このRetrosheetをデータ分析に使うとあらゆる分析ができるようなのですが、非常に使いにくい形式であり、すべてを活用するとなると、データ構造の理解が大変そうだなぁと...。
話しは変わって、AWSでBIツールを見ていたところ、QuickSightがジオコード機能を持っていることに気づきました。 ※参考：地理空間グラフ (マップ)
現行では、自動ジオコーディングは米国でのみ機能します。
これは、もう、アメリカの地理情報をQuickSightに表示するしかないだろうと。 この言葉に惹かれ、AthenaとQuickSightを使って、MLBの公式戦が開催されたスタジアムを地図上に表示してみました。
前提環境
- 特にありません
ローカル環境
まず始めに、Athenaで読み込むCSVファイルを用意します。
ここでは、RetrosheetのBallparksデータを使用しません。Retrosheetのデータをデータベース(MySQL)にまとめてくれた方がいらっしゃいますので、その方のお力を拝借します。
2019 – MySQL versionを参考にしました。
parksフォルダを作成し、parks.csvという名称で保存します。
S3
先ほどのCSVファイルをS3にアップロードし、Athenaで読み込めるようにします。 ここでは、次のような対応付けで、データベースとテーブルを用意します。
| 項目 | S3 | Athena |
|---|---|---|
| データベース | s3://hello-athena-seanlahman/ | lahmansbaseballdb |
| テーブル | s3://hello-athena-seanlahman/parks/* | parks |
1.S3バケットを作成します。
aws s3 mb s3://hello-athena-seanlahman --profile HOGEHOGE2.S3バケットにCSVファイルをアップロードします。
aws s3 sync ./parks s3://hello-athena-seanlahman/parks --profile HOGEHOGEAthena
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.sql1.テーブルを作成します。
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データソースで、次の通りに入力します。
| 項目 | 値 |
|---|---|
| データソース名 | lahmansbaseballdb ※任意の名称を指定してください |
| Athena workgroup | [primary] |
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が提供するサービスなので、非常に簡単に連携できました。
3
