Amazon AthenaでWebサーバがどのぐらいGet Wildか調べる

# はじめてのGet Wild 駆動開発

先日発表された[Amazon Athena](https://aws.amazon.com/jp/blogs/news/amazon-athena-interactive-sql-queries-for-data-in-amazon-s3/)を使えばWebサーバのログもそのまま分析にかけられるということなので、手始めにApacheのログを分析してどれだけGETでリクエストが来ているか調べてみました。

GETリクエストは即ちGet Wildリクエストです(暴論)。

# 下準備

Athenaで分析するためのログが必要ですが、自前でサーバを用意してログがたまるのを待ってると時間がかかるので[Processing Logs in Hive](http://hadooptutorial.info/processing-logs-in-hive/)にあったサンプルデータを拝借しました。

* [サンプルデータ](http://hadooptutorial.info/wp-content/uploads/2014/11/combined_access_log.txt)

こいつをS3に放り込んでおきます。
今回はこのためにget-wild-athena161208というバケットを作り、apache-logsフォルダに保存しました。
バケット名はグローバルで一意である必要がありますが、日付を入れることで他の人もget-wild-athenaYYMMDDの命名ができます。
今思えばHHMMぐらいまで入れときゃよかったな。

それはさておき、これでデータの準備は完了です。

# Amazon Athenaで分析

さて、ここからAthenaを操作します。2016-12-08時点では北バージニアかオレゴンでしか使えないのでご注意ください。

![スクリーンショット 2016-12-08 20.24.30.png](https://qiita-image-store.s3.amazonaws.com/0/116434/5a83f27d-4ea6-3540-c643-1d6b657f19e3.png)

Athenaのコンソールはこんな感じ。なんかスッキリしていていいですね。

## テーブルの作成

先ほどS3に保存したログからテーブルを作成します。
コンソール左にある「Add tables...」をクリックしたくなりますが、今回は使いません。
なぜなら[Processing Logs in Hive](http://hadooptutorial.info/processing-logs-in-hive/)の「Combined Log Format File」という項目にこのログをHiveに取り込むための文が載っているからです。
あ、AthenaはHiveを使ってるみたいです。

```sql
DROP TABLE IF EXISTS apache_combined_log;

CREATE TABLE apache_combined_log (
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "/home/user/combinedlog" INTO TABLE apache_combined_log;

SELECT * FROM apache_combined_log ORDER BY time LIMIT 5;
```
Processing Logs in Hiveにこんな文が書いていたので、必要な箇所だけ書き換えます。

```sql
CREATE EXTERNAL TABLE get_wild_log (
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
) LOCATION 's3://get-wild-athena161208/apache-logs/'
```

こいつをコンソール右のテキストエリアに書いて(LOCATIONはお使いのバケットに合わせてください)「Run Query」をクリックしてしばらく待ちますと、

![スクリーンショット 2016-12-08 20.33.04.png](https://qiita-image-store.s3.amazonaws.com/0/116434/8949103f-0096-51ad-47ae-39edbcd4b6a0.png)

無事にget_wild_logテーブルが作られます。

Apache Web Logをそのまま取り込めるのも便利ですが、正規表現がクソめんどくさいので実用するならCSVとかにしとくのが良さそうです。

## Get Wild分析

テーブルができたのでこのログがどれくらいGet Wildか見たいと思います。
AthenaのクエリはSQLと同じ感じでできます。

```sql
SELECT 
SUM(case WHEN request LIKE '%GET%' then 1 else 0 end) AS get, 
COUNT(*) AS wild,
SUM(case WHEN request LIKE '%GET%' then 1 else 0 end) * 100 / COUNT(*) AS get_wild_and_tough 
FROM get_wild_log;
```

GETの件数をget、全件数をwild、GETのパーセンテージをget_wild_and_toughとしてクエリを投げます。
結果はこちら

![スクリーンショット 2016-12-08 20.49.35.png](https://qiita-image-store.s3.amazonaws.com/0/116434/94582fe3-9ef9-8300-6f13-a6dfeb3c5eac.png)

**Get Wild率95%**

これはかなりGet Wildですね。

ELBでヘルスチェックとかしてる場合はその分を排除しとかないと不当に高いGet Wild率になるので注意しといたほうが良さそうですね。

# 今後の展望

というわけでWebサーバのログからGet Wild率を求めることができましたが、これだけだと物足りないですね。
ていうかGet Wild駆動開発たるもの音を鳴らさないと、というのが個人的な思いです。

今考えている案としては、

1. 簡単なWebページを用意してボタンを設置する
2. ボタンを押すとAPI経由でAthenaで分析し、ログ中のGETの回数を返す
3. GETの回数に応じた長さでGet Wildをリピート再生する(300回なら300秒)

というのがありますが、他にも何か素敵なアイディアがありましたらコメントいただけると幸いです。

今回初めてGet Wild駆動開発をしてみましたが、
**めっちゃ楽しい**。

# 参考ページ

* [Amazon Athena](https://aws.amazon.com/jp/blogs/news/amazon-athena-interactive-sql-queries-for-data-in-amazon-s3/)
* [Processing Logs in Hive](http://hadooptutorial.info/processing-logs-in-hive/)