ALBのログ分析でAthenaが遅いのでpartitionを追加した

# TL;DR
 
[このクエリ](https://qiita.com/KMtnst/items/90639181ec494f6e8143#sql%E3%81%AB%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%99%E3%82%8B%E3%81%A8%E6%88%90%E5%8A%9F%E3%81%97%E3%81%9F)を実行するだけなので1分あれば終わります。
公式ドキュメントに書いてあるSQLは古いので注意しましょう（2024/9現在)。
やり直すとき、一度テーブルを削除してから作り直すのは忘れずに。




# きっかけ：ALBのログ分析のAthenaが遅すぎる
きっかけはALBのログ分析をしたい時、Athenaのクエリが遅すぎることでした。
約4年以上ALBのログを保存し続けたことで、データ量がかなり増えている状況で、
ただのPreviewでも1分ほどかかっており、クエリによっては10分ほどかかってしまっていました。
1個のクエリに10分も要していては、到底アドホックな分析などできません。

結論としてはpartitionを追加すれば解決したのですが、思いのほか詰まってしまったので備忘録として残しておきます

# 公式ブログのSQLでparition追加を試みるも、空っぽのクエリが帰ってくる？
まず、現在のGlueTableがなぜ遅いのかを調査しました。
クエリパフォーマンスのチューニングの鉄板といえば、partitionの追加ですが、
今回のGlueTableにはpartitionが追加されていませんでした。
（そもそも前提として、今回のALBのログ分析用のGlueTableは私が作ったものではなく、4、5年以上前の過去の先人が作ったものを運用していたところです。）

さらに調査したところ、公式ドキュメントにpartitionなしの`CREATE TABLE`のSQLが掲載されており、こちらのSQLでテーブルを作成したのだろうと推察しました。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html#create-alb-access-logs-table

そして、ドキュメントをよく読むとpartition追加のSQLも載っていました。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-alb-connection-logs-table-partition-projection.html

ドキュメントのSQLを参考に、S3のURIとテーブル名を書き換えた後、最終的に以下のSQLを実行してみました。[(注1:ALBのログが保存されるS3のURIが必要です)](https://qiita.com/drafts/fa1889cddc921eadaf31/edit#%E8%A3%9C%E8%B6%B3alb%E3%81%AE%E3%83%AD%E3%82%B0%E3%81%8C%E4%BF%9D%E5%AD%98%E3%81%95%E3%82%8C%E3%82%8Bs3%E3%81%AEuri%E3%82%92%E5%8F%96%E5%BE%97)



```SQL
CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs.test_partition_01 (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code int,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason string,
            conn_trace_id string
            )
            PARTITIONED BY
            (
             day STRING
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = 
        '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\\s]+?)\" \"([^\\s]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)?( .*)?'
            )
            LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2022/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
            )

```
こちらのSQLは成功し、無事テーブルは作成できたのですが...
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2950855/66128023-437d-514f-5388-bec25fc298e9.png)


SELECT文を実行しても、結果が空っぽです。
```sql
SELECT * FROM alb_logs.test_partition_01
LIMIT 10;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2950855/3ec8298f-a730-bf6c-685f-9e3360a46b2a.png)


# ALBのアクセスログにフィールドが足されていたことでAthenaでのクエリが失敗していた
「公式ドキュメントのクエリが失敗する」という事態に対し調査を続けていたところ、幸運なことに2024/5に投稿されたこちらのブログを見つけることができました。（本当に感謝です）
[ALBのアクセスログにフィールドが足されていたことでAthenaでのクエリが失敗していた - kefi550のブログ](https://blog.kefiwild.com/posts/athena_when_alb_log_field_added/)
>traceability_id と unknown_fields というフィールドが足されたっぽい

どうやらフィールド（GlueTable的に言うとカラム）が追加されたらしいのですが、ドキュメントのSQLは更新されてないことが原因だったようです。

## SQLにフィールドを追加すると成功した
以下のSQLを修正し実行したところ、無事成功しました。
```SQL
CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs.test_partition_01 (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code int,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason string,
            traceability_id string,
            unknown_fields string
            )
            PARTITIONED BY
            (
             day STRING
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = 
        '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)?')
            LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2022/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
            )
```
## 注意： GlueTableを作り直す場合、毎回削除（DROP）しましょう
当然と言えば当然なのですが...私は途中でDROPをすることを忘れてしまい、上記の修正後SQLを実行しても空っぽの結果が返って来てしまい、無駄な時間を要してしまいました...
以下のSQLを実行すればDROPできます。
```SQL
DROP alb_logs.test_partition_01
```
DROPせずともCREATE TABLEは成功するのでついつい削除するのを忘れてしまっていました。
（そもそもCREATE TABLE以外（ALTERとか？）が良いのかもしれませんが、そこは検証してません！）


# partition追加でどのくらい速くなったのか？
速度、スキャン量ともに大きく改善が見られました。partition様様です。
## 単純なSELECT文
単純なSELECT文（Athenaのpreviewで実行されるSQL）で比較したところ、明らかに差が出ました。
```SQL
SELECT * FROM "<データベース名>"."<テーブル名>" LIMIT 10;
```
Run Timeは10倍速くなりました。
|Run Time  |partitionなし |partitionあり  |
|---|---|---|
|1回目  |64 sec  |4.9 sec  |
|2回目  |58 sec  |6.1 sec  |
|3回目  |60 sec  |6.8 sec  |

一方、スキャン量が増加してました。（追加で調査してないです）
|Data scanned  |partitionなし |partitionあり  |
|---|---|---|
|1回目  |9.37 MB  |65.20 MB	| 
|2回目  |7.46 MB  |95.34 MB  |
|3回目  |10.35 MB  |55.29 MB  |

## Where句やORDER BY句を使ったSQLの場合
SQLは割愛しますが、Where句やORDER BY句を使ったSQLの場合、partitionの効果が如実に現れました。
```SQL
SELECT * FROM "<データベース名>"."<テーブル名>" limit 10;
```
Run Timeはこちらでも10倍速くなりました。
3分の待ち時間が20秒に短縮されたことは大きいです。（欲を言えば20秒もやや長く感じるが、大きな進歩）
|Run Time  |partitionなし |partitionあり  |
|---|---|---|
|1回目  |3min 47sec  |20 sec  |

スキャン量も減っていたので、Athenaのコストカットにもつながりそうです！
|Data scanned  |partitionなし |partitionあり  |
|---|---|---|
|1回目  |235.56GB  |998.40 MB	| 

# まとめ
やったことはpartition追加のみでシンプルなのですが、ドキュメントのSQLが古かったり、テーブルの削除を忘れていたりで変に時間を使ってしまいました。
しかし、この検証で費やした時間は、今回のpartition追加でこれから浮くであろう待ち時間の短縮で十分回収できるでしょう。

partition追加で、よきログ分析ライフを！


# 補足：ALBのログが保存されるS3のURIを取得
下準備として、 ALBのログが保存されるS3のURIを取得が必要です。こんな形式です。
```URI
s3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/
```
![スクリーンショット 2024-09-06 15.36.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2950855/63be1fa4-ab17-bd7c-caba-49149a491be4.png)