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 公式 だと table properties を追加設定すればできそうな感じに書いてありますが、そんなことはなくて、 PARTITIONED BY 句付きの CREATE TABLE 文で新しいテーブルを定義しなければパーティションテーブルにはならないわけで。

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

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

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 として使う旨設定しています。

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

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 公式 に挙げられているのですが、「格納サイズは節約したいし検索スピードも確保したいから形式は Parquet で」「パーティションは年/月/日/時まで切りたい」と思っていた私にとってはそのものズバリのものはなかったので、取捨選択して自分用のテーブル定義を書きました。

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 全消し！¹)のでベタッと直書きしてしまって、 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 を見れば、どこにどんな VPC が定義されているかは一目瞭然です。 ↩