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

TL;DR

このクエリを実行するだけなので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が必要です)

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は成功し、無事テーブルは作成できたのですが...

SELECT文を実行しても、結果が空っぽです。

SELECT * FROM alb_logs.test_partition_01
LIMIT 10;

ALBのアクセスログにフィールドが足されていたことでAthenaでのクエリが失敗していた

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

traceability_id と unknown_fields というフィールドが足されたっぽい

どうやらフィールド（GlueTable的に言うとカラム）が追加されたらしいのですが、ドキュメントの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できます。

DROP alb_logs.test_partition_01

DROPせずともCREATE TABLEは成功するのでついつい削除するのを忘れてしまっていました。
（そもそもCREATE TABLE以外（ALTERとか？）が良いのかもしれませんが、そこは検証してません！）

partition追加でどのくらい速くなったのか？

速度、スキャン量ともに大きく改善が見られました。partition様様です。

単純なSELECT文

単純なSELECT文（Athenaのpreviewで実行される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の効果が如実に現れました。

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を取得が必要です。こんな形式です。

s3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/