読者です 読者をやめる 読者になる 読者になる

akiyoko blog

akiyoko の IT技術系ブログです

ゼロからはじめる Amazon Athena(AWS でお手軽データ分析 その2/3)

前回の記事 で、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 のテーブルに流し込むところまでを試してみます。

f:id:akiyoko:20170311224916p:plain



取り込むデータの考慮点

Amazon Athena は CSV、JSON、ORC、Avro、Parquet などの形式のデータを取り込むことができます。

(参考)よくある質問 - Amazon Athena | AWS


今回は、S3上の CSVデータを取り込むことを想定していますが、その際に考慮しなければいけない点がいくつかありました。

CSVデータの内容

1)カンマを取り除く

CSVデータの値として、例えば「18,900」のようにデータの途中に半角カンマ(,)が存在する場合は、最初に出現したカンマの位置までで切られてしまい、値が「18」と認識されてしまうので、データの値から半角カンマ(,)を取り除いておく必要があります。

(参考)Remove comma from value · akiyoko/marketstat@82abc4b · GitHub


2)ヘッダ行

回避策があるかもしれませんが、Athena で を取り込んだ際に、CSV のヘッダ行も1データとして取り込まれてしまうという問題があります。
今のところ解決策が無いので、次の(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」を選択します。
f:id:akiyoko:20170311165842p:plain

現在のところ、Amaozn Athena は「N. Virginia」「Ohio」「Oregon」の3つのリージョンしか対応していません。 *1

今回は「N. Virginia」を選択します。
f:id:akiyoko:20170311165926p:plain


f:id:akiyoko:20170311170013p:plain


「Query Editor」で「Add table」をクリックして、ウィザードを使ってテーブルを作成していきます。

f:id:akiyoko:20170311170030p:plain

項目 設定値
Database: marketstat(任意)
Table Name: boj(任意)
Location of Input Data Set: s3://marketstat/boj/

なお、S3 のロケーションは、スラッシュ(/)で終了する文字列でなければいけません。

f:id:akiyoko:20170311170127p:plain


データフォーマットに「CSV」を選択します。
f:id:akiyoko:20170311170203p:plain


CSV のカラム名とタイプを一つ一つ入力していくのは面倒なので、「Bulk add columns」で一括設定します。
f:id:akiyoko:20170311170321p:plain

今回は、

date int, expected_value int, preliminary_value int, name string, confirmed_value int

と設定します。

注意点としては、CSVファイルに出力された順番にカラム名を書かないといけません。なおパーティションに含めるフィールドは、ここには含めなくてよいです。
f:id:akiyoko:20170311195617p:plain

f:id:akiyoko:20170311170441p:plain


最後にパーティションを設定します。
f:id:akiyoko:20170311170617p:plain

パーティションは、

カラム名 タイプ
year int
month int

としました。タイプを「int」形式にしないと WHERE 句で「 > 」などの演算子が使えないため、上記のように設定しました。
f:id:akiyoko:20170311170657p:plain


エラーが出なければ、テーブル作成は完了です。

ちなみに、今回作成したテーブルの 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;

を忘れずに実行しなければいけません。 非常に重要です!!!

f:id:akiyoko:20170311170758p:plain



 

テスト

Amazon Athena は内部で分散処理基盤「Presto」を使用していて、標準の ANSI SQL が利用できます。

以下のSQLを実行してみます。

SELECT * FROM boj WHERE year = 2016 AND month BETWEEN 1 AND 3 limit 100;

f:id:akiyoko:20170311200800p:plain

WHERE句でパーティションを指定しているのがポイントです。これにより、スキャンする容量を節約することができます。


例えば、全検索するとスキャン量が「1MB」だったのが・・

f:id:akiyoko:20170314204257p:plain

WHERE句の条件として「year = 2016」を指定すると、「360 KB」ほどにスキャン量が削減されているのが分かります。

f:id:akiyoko:20170314204625p:plain



結果一覧の右上のファイルアイコンをクリックすることで、結果のCSVをダウンロードすることもできます。

f:id:akiyoko:20170311200817p:plain

"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 を使って実際にデータ分析していきたいと考えています。