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;
