LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@bigbridge(大橋 正明)

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

Last updated at Posted at 2025-01-21
aws athena

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

データベースを作成する

Athena でのデータベースの作成 - Amazon Athena
コンソール上にファンクションはない。クエリエディタで作成する。
 

athena
CREATE DATABASE db-name;

 

テーブルを作成する

 

パーティション化

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

パーティション化するためにS3のログの格納ルールとテーブルDDLに定義が必要。
 

データをパーティションすることで、各クエリによってスキャンされるデータの量を制限できるようになるため、パフォーマンスが向上し、コストが削減されます。任意のキーでデータをパーティションに分割することができます。一般的な方法では、時間に基づいてデータをパーティションします。
Athena でのデータのパーティション化 - Amazon Athena

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 を使用して手動でパーティションを追加します。
Athena でのデータのパーティション化 - Amazon Athena

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 コマンドを使用します。
Athena でのデータのパーティション化 - Amazon Athena

テーブル

【初心者向け】Amazon Athenaのパーティションとは
 Amazon AthenaのPartition Projectionを使ったALBのアクセスログ解析環境をTerraformで構築する – PSYENCE:MEDIA

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

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

テーブル作成のスニペット

 

ALBアクセスログの場合

 Application Load Balancer ログのクエリ - Amazon Athena

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}'
)

 

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
 

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}'
)

 

athena
SELECT
  *
FROM vpc-flow-log
WHERE 
  -- パーティションを選択
  log_date >= '2023/05/09'
  -- 条件を実装
  AND dstport = 22

 

WAFログの場合

AWS WAF ログのクエリ - Amazon Athena
 

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'
)

 

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;
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?