前回の記事 で、Scrapy で Webスクレイピングしたデータを CSV形式で S3 に格納しました。
今回は、S3 に格納した CSVファイルに対して、Amazon Athena を使ってデータ分析用のテーブルに取り込みたいと思います。
＜過去記事＞
akiyoko.hatenablog.jp
Amazon Athena とは
Amazon Athena については、先日開催された AWS Black Belt Online Seminar の「Amazon Athena」回の資料が詳しいです。
Athena は OLAP（OnLine Analytical Processing）ツールとして簡単な分析向けには使えるが、ETL（Extract, Transform, Load）用途としては向かないとのこと。ただし、大規模でないデータに対して、低頻度で ETL 処理をするユースケースには使えるとのことです。
- 高速な分散クエリエンジン「Presto」を使用
- インメモリで処理するので、バッチ処理ではなくてインタラクティブ向け
- データをフルスキャン＆変換する用途で使うと高コストになるので、バッチ処理には向かない
- 大規模データに対してフルスキャンを定期的に行いたいなら、EMR を使う
- サブクエリや JOIN を駆使した複雑な主計や高頻度なレポーティングをしたいなら、Redshift を使う
アクセス手段は Amazon Management Console、JDBLドライバ経由のみ。API や CLI は未提供とのことです。JDBC 経由で直接クエリを投げられるので、自前の BIツールとかも使えるようですね。
料金はクエリ単位の従量課金で、S3 のデータスキャンに対して「$5/1TB」で課金されます。注意点としては、
- リージョンをまたぐ場合は、データ転送時間と転送料金が掛かる
- パーティションをうまく設定するとスキャンするデータ量を効果的に減らすことができる
などが挙げられていました。
なお、Athena はディレクトリを指定して検索するので、バケット直下に直接ファイルを置くのは間違いとのことです。
目的
「AWS でお手軽データ分析」の全体像は、下図のように Scrapy → Amazon S3 → Amazon Athena → Amazon QuickSight という流れで AWS のいろいろなサービスを使ってデータ分析をすることを想定していますが、今回は、S3 に格納された CSVファイルを Amazon Athena のテーブルに流し込むところまでを試してみます。
取り込むデータの考慮点
Amazon Athena は CSV、JSON、ORC、Avro、Parquet などの形式のデータを取り込むことができます。
（参考）よくある質問 - Amazon Athena | AWS
今回は、S3上の CSVデータを取り込むことを想定していますが、その際に考慮しなければいけない点がいくつかありました。
CSVデータの内容
１）カンマを取り除く
CSVデータの値として、例えば「18,900」のようにデータの途中に半角カンマ（,）が存在する場合は、最初に出現したカンマの位置までで切られてしまい、値が「18」と認識されてしまうので、データの値から半角カンマ（,）を取り除いておく必要があります。
（参考）Remove comma from value · akiyoko/marketstat@82abc4b · GitHub
２）ヘッダ行
回避策があるかもしれませんが、Athena で を取り込んだ際に、CSV のヘッダ行も１データとして取り込まれてしまうという問題があります。
今のところ解決策が無いので、次の（QuickSight での）分析フェーズでデータをフィルタリングして除去するようにしています。
パーティション
Athena を利用する際は、「パーティション」という概念が非常に重要になります。
（参考）Amazon Athenaのパーティションを理解する #reinvent ｜ Developers.IO
例えば、S3 に保存する際のディレクトリを以下のような形式にすることで、Athena でデータを取り込む際に、「year」「month」というパーティションが使えるようになります。
marketstat（バケット）
└── boj
├── year=2014
├── year=2015
├── year=2016
└── year=2017
├── month=01
├── month=02
└── month=03
├── 170301.csv
├── 170302.csv
├── 170303.csv
・
・
Amazon Athena はスキャンしたデータ量に対して課金されるため、頻繁に絞り込むカラムをパーティションのキーに指定することで、読み込むデータ量を効果的に減らすことができます。パーティションに設定した項目は、SQL の WHERE句の条件に指定して絞り込むことができます。
テーブルを作成
いよいよ Amazon Athena の実践です。
AWS Management Console のサービス一覧から「Amazon Athena」を選択します。
現在のところ、Amaozn Athena は「N. Virginia」「Ohio」「Oregon」の3つのリージョンしか対応していません。 *1
今回は「N. Virginia」を選択します。
「Query Editor」で「Add table」をクリックして、ウィザードを使ってテーブルを作成していきます。
| 項目 | 設定値 |
|---|---|
| Database: | marketstat（任意） |
| Table Name: | boj（任意） |
| Location of Input Data Set: | s3://marketstat/boj/ |
なお、S3 のロケーションは、スラッシュ（/）で終了する文字列でなければいけません。
データフォーマットに「CSV」を選択します。
CSV のカラム名とタイプを一つ一つ入力していくのは面倒なので、「Bulk add columns」で一括設定します。
今回は、
date int, expected_value int, preliminary_value int, name string, confirmed_value int
と設定します。
注意点としては、CSVファイルに出力された順番にカラム名を書かないといけません。なおパーティションに含めるフィールドは、ここには含めなくてよいです。
最後にパーティションを設定します。
パーティションは、
| カラム名 | タイプ |
|---|---|
| year | int |
| month | int |
としました。タイプを「int」形式にしないと WHERE 句で「 > 」などの演算子が使えないため、上記のように設定しました。
エラーが出なければ、テーブル作成は完了です。
ちなみに、今回作成したテーブルの DDL は以下のようになっています。
CREATE EXTERNAL TABLE IF NOT EXISTS marketstat.boj ( `date` int, `expected_value` int, `preliminary_value` int, `name` string, `confirmed_value` int ) PARTITIONED BY ( year int, month int ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = ',', 'field.delim' = ',' ) LOCATION 's3://marketstat/boj/';
パーティションを使用した場合は、最後に
MSCK REPAIR TABLE marketstat.boj;
を忘れずに実行しなければいけません。 非常に重要です！！！
テスト
Amazon Athena は内部で分散処理基盤「Presto」を使用していて、標準の ANSI SQL が利用できます。
以下のSQLを実行してみます。
SELECT * FROM boj WHERE year = 2016 AND month BETWEEN 1 AND 3 limit 100;
WHERE句でパーティションを指定しているのがポイントです。これにより、スキャンする容量を節約することができます。
例えば、全検索するとスキャン量が「1MB」だったのが・・
WHERE句の条件として「year = 2016」を指定すると、「360 KB」ほどにスキャン量が削減されているのが分かります。
結果一覧の右上のファイルアイコンをクリックすることで、結果のCSVをダウンロードすることもできます。
"date","expected_value","preliminary_value","name","confirmed_value","year","month"
,,,"name",,"2016","2"
"160210",,,"",,"2016","2"
"160210","-400","-400","銀行券要因",,"2016","2"
"160210","-28800","-29900","財政等要因",,"2016","2"
"160210","-29200","-30300","資金過不足",,"2016","2"
"160210",,," ",,"2016","2"
"160210",,," ",,"2016","2"
・
・
まとめ
知識ゼロから Amazon Athena を使って、S3 に格納した CSVファイルを元にデータ分析用のテーブルを作成してみました。
少し難解だったのはパーティションの概念でしたが、それが理解できればあとは直感で何とかなりそうな感じでした。
次は、QuickSight を使って実際にデータ分析していきたいと考えています。
