ALBやCloudTrailのログをAthena検索するならパーティション射影(Partition Projection)しよう

# 背景

今日も今日とて AWS で作ったサービスのお守りをしてるわけですが…

- Beanstalk がなぜか warning 状態になる。 "Environment health has transitioned from Ok to Warning. XX.Y % of the requests to the ELB are erroring with HTTP 4xx" とか言って。リクエストは ALB 層で打ち返されてるから、アプリのログは何も出てない。調べられない…
- あれ、動かない？…と思ったら、リソースの権限が変更されてる。いつ、誰が、何をやったのか？ CloudTrail は仕掛けてあるけど、直近ならともかく、あんなの手で調べられるもんじゃない…

そういうピンチ(?)に陥った人のためにあるのが Athena クエリーなわけですが(ELB や CloudTrail が S3 に吐くログを、しかるべきテーブル定義をしてやれば Athena は直接検索できる)、AWS 公式ドキュメントに書いてあるテーブル定義だと **パーティションが何もない** ので、いざ検索してみると **やたら時間がかかる** わけで(当たり前)。でも、ふつうにパーティション付けようとすると、 ALTER TABLE ADD PARTITION やら MSCK REPAIR TABLE やらを定期的に実行しないとパーティション情報をメンテナンスできない…めんどくさい…。

# Athena のパーティション射影 (Partition Projection) 機能

パーティション射影 (Partition Projection) とは、パーティションキーを設定から計算によって求めるというもの ( https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection.html )。パーティション自体が「設定から」「計算によって」自動的に生成されるようなものなので、メンテナンスの手間が要りません。

この機能自体は今から半年も前にすでに発表されていたものですが、恥ずかしながら今まで知りませんでした…。

https://dev.classmethod.jp/articles/20200627-amazon-athena-partition-projection/
https://aws.amazon.com/jp/about-aws/whats-new/2020/06/amazon-athena-supports-partition-projection/

# 設定してみよう

せっかくの便利機能なのでさっそく使おうと思ったのですが、実際のテーブル定義例のうまいものがなかなか見つからない。

[AWS 公式](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection-setting-up.html) だと table properties を追加設定すればできそうな感じに書いてありますが、そんなことはなくて、 `PARTITIONED BY` 句付きの `CREATE TABLE` 文で新しいテーブルを定義しなければパーティションテーブルにはならないわけで。

…仕方ない、自分で定義しましょう。

## ELB(ALB)ログの場合のテーブル定義

```sql 
CREATE EXTERNAL TABLE `alb_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` string,
  `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
)
PARTITIONED BY (
  `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://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/<REGION>'
TBLPROPERTIES (
  'projection.date.format'='yyyy/MM/dd',
  'projection.date.interval'='1',
  'projection.date.interval.unit'='DAYS',
  'projection.date.range'='NOW-5YEARS,NOW',
  'projection.date.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/<REGION>/${date}'
);
```

`<BUCKET_NAME>`, `<ACCOUNT_ID>`, `<REGION>` などはお使いの環境に合わせて実際の値を埋め込んでください。

ELB(ALB)ログの場合、S3 上のオブジェクトキーは

```
AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/<REGION>/yyyy/MM/dd/hogehoge.gz
```

という形式になっているので、オブジェクトキーの `yyyy/MM/dd` の部分をパーティションキー `date` として使う旨設定しています。

### (2024/12/27追記) ALB アクセスログのフォーマットが微妙に変わったらしいので…

ALB アクセスログのフォーマットが列の追加などで微妙に変わってるらしいので (SERDEPROPERTIES の input.regex あたり)、新フォーマット対応版も入れときます。

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS alb_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 (
  date 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]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)?'
)
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/<REGION>'
TBLPROPERTIES (
  'projection.date.format'='yyyy/MM/dd',
  'projection.date.interval'='1',
  'projection.date.interval.unit'='DAYS',
  'projection.date.range'='NOW-5YEARS,NOW',
  'projection.date.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/<REGION>/${date}'
);
```

[公式の説明はこちら](https://docs.aws.amazon.com/athena/latest/ug/create-alb-access-logs-table.html)

## CloudTrail ログの場合のテーブル定義

```sql
CREATE EXTERNAL TABLE `cloudtrail_logs` (
  `eventversion` string,
  `useridentity` struct<
    type:string,
    principalid:string,
    arn:string,
    accountid:string,
    invokedby:string,
    accesskeyid:string,
    username:string,
    sessioncontext:struct<
      attributes:struct<
        mfaauthenticated:string,
        creationdate:string
      >,
      sessionissuer:struct<
        type:string,
        principalid:string,
        arn:string,
        accountid:string,
        username:string
      >
    >
  >,
  `eventtime` string,
  `eventsource` string,
  `eventname` string,
  `awsregion` string,
  `sourceipaddress` string,
  `useragent` string,
  `errorcode` string,
  `errormessage` string,
  `requestparameters` string,
  `responseelements` string,
  `additionaleventdata` string,
  `requestid` string,
  `eventid` string,
  `resources` array<
    struct<
      arn:string,
      accountid:string,
      type:string
    >
  >,
  `eventtype` string,
  `apiversion` string,
  `readonly` string,
  `recipientaccountid` string,
  `serviceeventdetails` string,
  `sharedeventid` string,
  `vpcendpointid` string
)
PARTITIONED BY (
  `region` string,
  `date` string
)
ROW FORMAT SERDE
  'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT
  'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/CloudTrail'
TBLPROPERTIES (
  'classification'='cloudtrail',
  'projection.date.format'='yyyy/MM/dd',
  'projection.date.interval'='1',
  'projection.date.interval.unit'='DAYS',
  'projection.date.range'='NOW-5YEARS,NOW',
  'projection.date.type'='date',
  'projection.enabled'='true',
  'projection.region.type'='injected',
  'storage.location.template'='s3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/CloudTrail/${region}/${date}'
);
```

`<BUCKET_NAME>`, `<ACCOUNT_ID>` はお使いの環境に合わせて実際の値を埋め込んでください。

CloudTrail ログの場合、S3 上のオブジェクトキーは

```
AWSLogs/<ACCOUNT_ID>/CloudTrail/<REGION>/yyyy/MM/dd/hogehoge.gz
```

という形式になっているので、オブジェクトキーの `<REGION>`, `yyyy/MM/dd` の部分をそれぞれパーティションキー `region`, `date` として使う旨設定しています。

`region` は `date` と違って特定のフォーマットがあるわけではないので `injected` 型としています。制約があって、 Athena クエリーを書くときにはこの列を必ず where 条件で `where region = 'hogehoge'` のように含めて記述しなければなりません。が、まぁ、制約とは言っても、ふつうは含めて書くでしょうから、大した制約ではありません。

…みなさんの AWS ライフがより快適なものでありますように♪

## (2022/08/13 追記) VPC Flow Log の場合

いくつかのテーブル定義の例が [AWS 公式](https://docs.aws.amazon.com/athena/latest/ug/vpc-flow-logs.html) に挙げられているのですが、「格納サイズは節約したいし検索スピードも確保したいから形式は Parquet で」「パーティションは年/月/日/時まで切りたい」と思っていた私にとってはそのものズバリのものはなかったので、取捨選択して自分用のテーブル定義を書きました。

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` (
  `version` int,
  `account_id` string,
  `interface_id` string,
  `srcaddr` string,
  `dstaddr` string,
  `srcport` int,
  `dstport` int,
  `protocol` bigint,
  `packets` bigint,
  `bytes` bigint,
  `start` bigint,
  `end` bigint,
  `action` string,
  `log_status` string,
  `vpc_id` string,
  `subnet_id` string,
  `instance_id` string,
  `tcp_flags` int,
  `type` string,
  `pkt_srcaddr` string,
  `pkt_dstaddr` string,
  `region` string,
  `az_id` string,
  `sublocation_type` string,
  `sublocation_id` string,
  `pkt_src_aws_service` string,
  `pkt_dst_aws_service` string,
  `flow_direction` string,
  `traffic_path` int
)
PARTITIONED BY (
  `date` string
)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/vpcflowlogs/ap-northeast-1'
TBLPROPERTIES (
  'EXTERNAL'='true',
  'skip.header.line.count'='1',
  'projection.date.format'='yyyy/MM/dd/HH',
  'projection.date.interval'='1',
  'projection.date.interval.unit'='HOURS',
  'projection.date.range'='NOW-5YEARS,NOW',
  'projection.date.type'='date',
  'projection.enabled'='true',
  'storage.location.template'='s3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/vpcflowlogs/ap-northeast-1/${date}'
);
```

`<BUCKET_NAME>`, `<ACCOUNT_ID>` はお使いの環境に合わせて実際の値を埋め込んでください。

VPC Flow Log (Hourly partitions あり) の場合、S3 上のオブジェクトキーは

```
AWSLogs/<ACCOUNT_ID>/vpcflowlogs/<REGION>/yyyy/MM/dd/HH/hogehoge.gz
```

という形式になっています。私の場合、 `<REGION>` は東京リージョンしかログを取る設定をしていない(というか、東京リージョン以外は VPC 全消し！[^1])のでベタッと直書きしてしまって、 `yyyy/MM/dd/HH` の部分のみパーティションキー `date` として使う旨設定しています。

複数リージョンの VPC に Flow Log をとる設定をしていて `region` もパーティションキーにしたい場合、テーブルの列名にも `region` が出現するので、パーティション射影の設定としては名前を変えて、下記のように定義した `awsregion` をパーティションキーとすることになるのかなと(試したい勇者募集)。

```
'projection.awsregion.type'='injected',
'storage.location.template'='s3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/vpcflowlogs/${awsregion}/${date}'
```

[^1]: アカウントを作った初期状態だと、使いもしないリージョンでもデフォルト VPC が 1個ずつ用意されています。セキュリティ向上のためにも、使わない VPC は全部消してしまいましょう。 [EC2 Global View](https://ap-northeast-1.console.aws.amazon.com/ec2globalview/home) を見れば、どこにどんな VPC が定義されているかは一目瞭然です。