S3オブジェクトへのリクエストをCloudTrail, Athenaで識別する(パーティショニング)

2021/05/31 2021/07/21

Amazon S3オブエジェクトへのリクエストをCloudTrail, Athenaで識別では、クエリーのために1.44GBのデータをスキャンして、1分32秒かかっていてコストもパフォーマンスもよくないのでパーティショニングを試してみました。

テーブルの作成

こちらCloudTrail を使用した Amazon S3 リクエストの識別の「署名バージョン 2 データのパーティション化」記載のテーブル作成クエリを実行しました。

CREATE EXTERNAL TABLE s3_cloudtrail_events_db.cloudtrail_yamamugi_partiion_table(
        eventversion STRING,
        useridentity STRUCT&lt;
            type:STRING,
            principalid:STRING,
            arn:STRING,
            accountid:STRING,
            invokedby:STRING,
            accesskeyid:STRING,
            userName:STRING,
            sessioncontext:STRUCT&lt;
                attributes:STRUCT&lt;
                        mfaauthenticated:STRING,
                        creationdate:STRING&gt;,
                        sessionissuer:STRUCT&lt;  
                        type:STRING,
                        principalId:STRING,
                        arn:STRING, 
                        accountId:STRING,
                        userName:STRING&gt;
                &gt;
             &gt;,
        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&lt;STRUCT&lt;
            ARN:STRING,
            accountId:STRING,
            type:STRING&gt;&gt;,
        eventtype STRING,
        apiversion STRING,
        readonly STRING,
        recipientaccountid STRING,
        serviceeventdetails STRING,
        sharedeventid STRING,
        vpcendpointid STRING
    )
PARTITIONED BY (region string, year string, month string, day 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://bucketname/AWSLogs/123456789012';

CREATE EXTERNAL TABLE s3_cloudtrail_events_db.cloudtrail_yamamugi_partiion_table(

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, year string, month string, day 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://bucketname/AWSLogs/123456789012';

PARTITIONED BYでリージョン、年、月、日でパーティションを設定しました。
テーブル名に(Partitined)がつきました。

パーティションの追加

ALTER TABLE s3_cloudtrail_events_db.cloudtrail_yamamugi_partiion_table ADD
    PARTITION (region= 'ap-northeast-3', year= '2021', month= '06', day= '01') LOCATION 's3://yamamugi-blog-log/AWSLogs/142639723455/CloudTrail/ap-northeast-3/2021/06/01/'
    PARTITION (region= 'ap-northeast-3', year= '2021', month= '06', day= '02') LOCATION 's3://yamamugi-blog-log/AWSLogs/142639723455/CloudTrail/ap-northeast-3/2021/06/02/';

ALTER TABLE s3_cloudtrail_events_db.cloudtrail_yamamugi_partiion_table ADD

PARTITION (region= 'ap-northeast-3', year= '2021', month= '06', day= '01') LOCATION 's3://yamamugi-blog-log/AWSLogs/142639723455/CloudTrail/ap-northeast-3/2021/06/01/'

PARTITION (region= 'ap-northeast-3', year= '2021', month= '06', day= '02') LOCATION 's3://yamamugi-blog-log/AWSLogs/142639723455/CloudTrail/ap-northeast-3/2021/06/02/';

テーブルができのでパーティションを追加しました。

クエリの実行

SELECT
  eventTime, 
  eventName, 
  eventSource, 
  sourceIpAddress, 
  userAgent, 
  json_extract_scalar(requestParameters, '$.bucketName') as bucketName, 
  json_extract_scalar(requestParameters, '$.key') as object,
  userIdentity.arn as userArn
FROM
  s3_cloudtrail_events_db.cloudtrail_yamamugi_partiion_table
WHERE
  eventName = 'GetObject'
  AND region= 'ap-northeast-3'
  AND year= '2021'
  AND month= '06'
  AND day= '01'