S3に格納されたマネージドログをAthenaで解析する ~ Big Bridge I/O

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

データベースを作成する

Athena でのデータベースの作成 - Amazon 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.{パーティション名}.*にパーティションのルールを定義する。

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

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

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

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

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

WAFログの場合

AWS WAF ログのクエリ - Amazon 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'
)

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;

Big Bridge I/O

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

データベースを作成する

テーブルを作成する

パーティション化

S3格納ルール

テーブル

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

ALBアクセスログの場合

VPCフローログの場合

WAFログの場合

0 Comments:

コメントを投稿

Profile

Popular Posts

Pages

Blog Archive

Categories