S3に格納されたマネージドログをAthenaで解析する

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/9362fc28-852c-1965-b839-f63b00223139.png" alt="aws" width="80">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/417736/6f065ec0-f0ab-758c-4fe3-cde58b63537f.png" alt="athena" width="80">

AthenaでS3バケットにストックしているマネージドのログに対してクエリ検索する。

## データベースを作成する
[Athena でのデータベースの作成 - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-databases.html)
コンソール上にファンクションはない。クエリエディタで作成する。
 
```sql:athena
CREATE DATABASE db-name;
```
 
## テーブルを作成する
 
### パーティション化

クエリ対象のスキャン量は料金コストに直結するため、パーティションでデータを絞り込んでコスト節約する。

パーティション化するためにS3のログの格納ルールとテーブルDDLに定義が必要。
 
> データをパーティションすることで、各クエリによってスキャンされるデータの量を制限できるようになるため、パフォーマンスが向上し、コストが削減されます。任意のキーでデータをパーティションに分割することができます。一般的な方法では、時間に基づいてデータをパーティションします。
[Athena でのデータのパーティション化 - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html)

#### S3格納ルール
 
対象ログが日付等の一定ルールに基づいたパーティションに格納されていること。フォーマットには「Hive形式」と「Hiveではない形式」がある。

Hive形式はパーティションが追加される度にテーブルのリペアが必要なので、ログのエクスポート設定はHiveではない方式にする。

**Hiveではない形式**

```
s3://path/to/2021/01/26/
```

> Athena では、Hive 以外のスタイルのパーティション化スキームを使用することも可能です。例えば、 CloudTrail ログと Firehose 配信ストリームは、 などの日付部分に対して個別のパスコンポーネントを使用しますdata/2021/01/26/us/6fc7845e.json。これらの Hive スタイルではないパーティションの場合、[ALTER TABLE ADD PARTITION](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/alter-table-add-partition.html) を使用して手動でパーティションを追加します。
[Athena でのデータのパーティション化 - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html)

**Hive形式**

- ディレクトリが「key=value」形式
- パーティションが追加される度にテーブルのリペアが必要

```
s3://path/to/year=2021/month=01/day=26/
```

> Athena では Apache Hive スタイルのパーティションを使用できます。このパーティションのデータパスには、等号で連結されたキーと値のペア (例えば country=us/... または year=2021/month=01/day=26/...) が含まれています。つまり、それぞれのパスにより、パーティションのキーと値、両方の名前が表されます。新しい Hive パーティションをパーティションされたテーブルにロードするには、(Hive スタイルのパーティションのみで機能する) [MSCK REPAIR TABLE](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/msck-repair-table.html) コマンドを使用します。
[Athena でのデータのパーティション化 - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html)

#### テーブル
[【初心者向け】Amazon Athenaのパーティションとは](https://staff-blog.faith-sol-tech.com/%E3%80%90%E5%88%9D%E5%BF%83%E8%80%85%E5%90%91%E3%81%91%E3%80%91amazon-athena%E3%81%AE%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E3%81%A8%E3%81%AF/)
 [Amazon AthenaのPartition Projectionを使ったALBのアクセスログ解析環境をTerraformで構築する – PSYENCE:MEDIA](https://blog.recruit.co.jp/rmp/infrastructure/alb-athena-partition-projection/)

テーブル作成のDDLに`PARTITIONED BY`句と`TBLPROPERTIES`句を追加して、パーティション射影を有効にする

- `LOCATION`句にS3のパーティション構造の上位ディレクトリまで指定する
- `PARTITIONED`句にパーティション名を定義する
- `TBLPROPERTIES`句にパーティションルールを定義する。`projection.{パーティション名}.*`にパーティションのルールを定義する。

```sql:athena
CREATE EXTERNAL TABLE `table-name`( ... )
PARTITIONED BY (`log_date` string)
LOCATION 's3://{バケット名}/AWSLogs/{アカウントID}/elasticloadbalancing/{リージョン}' TBLPROPERTIES (
  -- パーティション有効
  'projection.enabled'='true', 
  -- パーティションのデータ型 enum,integer,date,injectedをサポートしている
  'projection.log_date.type'='date',
  -- パーティションのフォーマット=S3のパーティション
  'projection.log_date.format'='yyyy/MM/dd',
  -- パーティション間隔 = 1日おき   'projection.log_date.interval'='1',
  'projection.log_date.interval.unit'='DAYS',
  -- パーティションのレンジ
  'projection.log_date.range'='NOW-1YEARS,NOW',
  -- パーティションを含むS3バケットのロケーション
  'storage.location.template'='s3://{バケット名}/AWSLogs/{アカウントID}/elasticloadbalancing/{リージョン}/${log_date}'
)
```

[Application Load Balancer ログのクエリ - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html#create-alb-access-logs-table-partition-projection)

### テーブル作成のスニペット
 
#### ALBアクセスログの場合

 [Application Load Balancer ログのクエリ - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html#create-alb-access-logs-table-partition-projection)

```sql:athena
CREATE EXTERNAL TABLE `alb-access-log`(
  `type` string COMMENT '',
  `time` string COMMENT '',
  `elb` string COMMENT '',
  `client_ip` string COMMENT '',
  `client_port` int COMMENT '',
  `target_ip` string COMMENT '',
  `target_port` int COMMENT '',
  `request_processing_time` double COMMENT '',
  `target_processing_time` double COMMENT '',
  `response_processing_time` double COMMENT '',
  `elb_status_code` string COMMENT '',
  `target_status_code` string COMMENT '',
  `received_bytes` bigint COMMENT '',
  `sent_bytes` bigint COMMENT '',
  `request_verb` string COMMENT '',
  `request_url` string COMMENT '',
  `request_proto` string COMMENT '',
  `user_agent` string COMMENT '',
  `ssl_cipher` string COMMENT '',
  `ssl_protocol` string COMMENT '',
  `target_group_arn` string COMMENT '',
  `trace_id` string COMMENT '',
  `domain_name` string COMMENT '',
  `chosen_cert_arn` string COMMENT '',
  `matched_rule_priority` string COMMENT '',
  `request_creation_time` string COMMENT '',
  `actions_executed` string COMMENT '',
  `redirect_url` string COMMENT '',
  `lambda_error_reason` string COMMENT '',
  `target_port_list` string COMMENT '',
  `target_status_code_list` string COMMENT '',
  `classification` string COMMENT '',
  `classification_reason` string COMMENT '')
PARTITIONED BY (
  `log_date` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  '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]+)\" \"([^ ]*)\" \"([^ ]*)\"')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://{バケット名}/AWSLogs/{アカウントID}/elasticloadbalancing/{リージョン}'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.log_date.format'='yyyy/MM/dd',
  'projection.log_date.interval'='1',
  'projection.log_date.interval.unit'='DAYS',
  'projection.log_date.range'='NOW-1YEARS,NOW',
  'projection.log_date.type'='date',
  'storage.location.template'='s3://{バケット名}/AWSLogs/{アカウントID}/elasticloadbalancing/{リージョン}/${log_date}'
)
```
 
```sql:athena
SELECT
  *
FROM "alb-access-log"
WHERE
  -- パーティションを選択
  log_date BETWEEN '2023/09/01' AND '2023/09/24'
  -- 条件を実装
  AND elb_status_code = '504'
```
 
#### VPCフローログの場合
[Amazon VPC フローログのクエリ - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/vpc-flow-logs.html)
 
```sql:athena
CREATE EXTERNAL TABLE `vpc-flow-log`(
  `version` int,
  `accountid` string,
  `interfaceid` string,
  `srcaddress` string,
  `dstaddress` string,
  `srcport` int,
  `dstport` int,
  `protocol` int,
  `packets` int,
  `bytes` bigint,
  `timefrom` int,
  `timeto` int,
  `action` string,
  `logstatus` string,
  `instanceid` string,
  `subnetid` string,
  `vpcid` string)
PARTITIONED BY (
  `log_date` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://{バケット名}/AWSLogs/{アカウントID}/vpcflowlogs/{リージョン}'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.log_date.format'='yyyy/MM/dd',
  'projection.log_date.interval'='1',
  'projection.log_date.interval.unit'='DAYS',
  'projection.log_date.range'='NOW-1YEARS,NOW',
  'projection.log_date.type'='date',
  'skip.header.line.count'='1',
  'storage.location.template'='s3://{バケット名}/AWSLogs/{アカウントID}/vpcflowlogs/{リージョン}/${log_date}'
)
```
 
```sql:athena
SELECT
  *
FROM vpc-flow-log
WHERE 
  -- パーティションを選択
  log_date >= '2023/05/09'
  -- 条件を実装
  AND dstport = 22
```
 
#### WAFログの場合
[AWS WAF ログのクエリ - Amazon Athena](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/waf-logs.html)
 
```sql:athena
CREATE EXTERNAL TABLE `waf_acl-log`(
  `timestamp` bigint COMMENT 'from deserializer',
  `formatversion` int COMMENT 'from deserializer',
  `webaclid` string COMMENT 'from deserializer',
  `terminatingruleid` string COMMENT 'from deserializer',
  `terminatingruletype` string COMMENT 'from deserializer',
  `action` string COMMENT 'from deserializer',
  `terminatingrulematchdetails` array<struct<conditiontype:string,location:string,matcheddata:array<string>>> COMMENT 'from deserializer',
  `httpsourcename` string COMMENT 'from deserializer',
  `httpsourceid` string COMMENT 'from deserializer',
  `rulegrouplist` array<struct<rulegroupid:string,terminatingrule:struct<ruleid:string,action:string>,nonterminatingmatchingrules:array<struct<action:string,ruleid:string>>,excludedrules:array<struct<exclusiontype:string,ruleid:string>>>> COMMENT 'from deserializer',
  `ratebasedrulelist` array<struct<ratebasedruleid:string,limitkey:string,maxrateallowed:int>> COMMENT 'from deserializer',
  `nonterminatingmatchingrules` array<struct<action:string,ruleid:string>> COMMENT 'from deserializer',
  `httprequest` struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string> COMMENT 'from deserializer')
PARTITIONED BY (
  `date` string
)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://{バケット名}/${date}'
TBLPROPERTIES (
  'classification'='csv',
  'compressionType'='gzip',
  'delimiter'='|',
  'projection.date.format'='yyyy/MM/dd',
  'projection.date.interval'='1',
  'projection.date.interval.unit'='DAYS',
  'projection.date.range'='NOW-1YEARS,NOW',
  'projection.date.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://{バケット名}/${date}',
  'typeOfData'='file'
)
```
 
```sql:athena
SELECT from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, *
FROM waf_acl-log
WHERE
  -- パーティションを選択
  date BETWEEN '2023/11/01' AND '2023/11/1'
  AND action = 'BLOCK'
--    AND action = 'ALLOW'
--    AND terminatingruleid = 'AWS-AWSManagedRulesSQLiRuleSet'
--    AND terminatingruleid = 'AWS-AWSManagedRulesPHPRuleSet'
ORDER BY timestamp ASC
LIMIT 100;
```