Amazon Athena

## Athenaの動作概要
SQLを実行するには、テーブルや列名などのスキーマ情報をメタデータとして持つことが必要。  
AthenaはS3上のファイルにSQLを実行するために、AWS Glueのデータカタログに格納したスキーマ情報を利用する。

## AWS のサービス ログをクエリする
[AWS のサービス ログをクエリする](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/querying-aws-service-logs.html)  
    * [Application Load Balancer ログをクエリする](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html)

### 前提作業
* Athena用のデータベースを作成
[Athena でデータベースを作成する](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-databases.html)

    * [クエリ出力の場所を作成する](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-databases-prerequisites.html)  
        クエリ結果保存先のS3バケットを設定する
    
    * [データベースを作成する](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-databases-query-editor.html)  
        DDLを実行してデータベースを作成する
        ```
        CREATE DATABASE testDataBase;
        ```
### ALB アクセスログ用テーブルを作成する
[ALB アクセスログ用のテーブルを作成する](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-alb-access-logs-table.html)  
> LOCATION 句内のパスを Amazon S3 アクセスログフォルダの場所に置き換えます。
```
CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs ( 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 ) 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://your-alb-logs-bucket/AWSLogs//elasticloadbalancing//'
```

### パーティション射影を使用して Athena で ALB アクセスログ用テーブルを作成する
[パーティション射影を使用して Athena で ALB アクセスログ用テーブルを作成する](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-alb-access-logs-table-partition-projection.html)  
> ALB アクセスログにはパーティションスキームを事前に指定できる既知の構造があるため、Athena のパーティション射影機能を使用することで、クエリの実行時間を短縮し、パーティション管理を自動化することが可能。
> LOCATION 句および storage.location.template 句では、プレースホルダーを ALB アクセスログの Amazon S3 バケットの場所を特定する値に置き換えます。
```
CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs ( 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://your-alb-logs-bucket/AWSLogs//elasticloadbalancing//' 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://your-alb-logs-bucket/AWSLogs//elasticloadbalancing//${day}" )
```

### ALB アクセスログのクエリ例
[ALB アクセスログのクエリ例](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/query-alb-access-logs-examples.html)
次の例は、指定期間のログを抽出（time列はUTC）
```
SELECT *
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
    BETWEEN parse_datetime('2024-12-18-01:00:00','yyyy-MM-dd-HH:mm:ss') 
    AND parse_datetime('2024-12-18-03:00:00','yyyy-MM-dd-HH:mm:ss') 
ORDER BY time DESC
LIMIT 10
```
### アクセスログのエントリ
[アクセスログのエントリ](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html#access-log-entry-format)

### WAF アクセスログのクエリ例
[WAF アクセスログのクエリ例](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/query-examples-waf-logs.html)
```
SELECT to_iso8601(from_unixtime(timestamp / 1000)) as time_ISO_8601, *
FROM waf_logs
LIMIT 10;  
```