はじめに

前回はMS365の監査ログをAthenaで見るためテーブルを作成しました。

Athenaの課金は、検索時にスキャンされたデータサイズに依存しますので、このままだと都度全データをスキャンします。
監査ログは直近の分だけ欲しいことがほとんどとおもわれますので、このままだと毎回スキャンの度に大昔の不要なデータ分まで課金することになってしまいます。

そこで、日付単位でパーティションを設けることで、スキャンするデータサイズをコントロールすることができますので、今回それを試してみました。

参考にしたページは以下になります。

またMS365の監査ログの取得方法は、以前の記事の途中にあるコンテナを使用しています。

やったこと

S3上のフォルダの作り方

パーティションは、S3上のフォルダの名前で表現します。

	Hive形式	非Hive形式
フォルダの形式	`<<キー>>=<<値>>` 例） ymd=2022-08-14/ region=ap-northeast-1/	`<<値>>`のみ例） 2022-08-14/ ap-northeast-1/
フォルダ追加時	認識させるクエリの実行が必要（毎回、同じクエリ）	認識させるクエリの実行が必要（都度、追加するパーティションを指定）
パーティションから除外時	除外させるクエリの実行が必要（都度、除外するパーティションを指定）

パーティションが一つの場合

Hive形式

フォルダ・ファイルの構造は以下のようにしました。

testwrite/usepart/hivestyle/
├── ymd=2022-08-01/
│   ├── 090000.json
│   └── 100000.json
├── ymd=2022-08-02/
│   └── 100000.json
├── ymd=2022-08-03/
│   └── 100000.json
├── ymd=2022-08-04/
│   └── 100000.json
└── ymd=2022-08-05/
    └── 100000.json

作成時には、以前作成したLambda用のPythonを用いました。

参考までに、実行させたスクリプトも記載しておきます。

コンテナ上で実行させたスクリプト

event = {
 'StartDate':'2022-08-01 09:00:00',
 'EndDate':'2022-08-01 09:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-01/090000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-01 10:00:00',
 'EndDate':'2022-08-01 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-01/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-02 10:00:00',
 'EndDate':'2022-08-02 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-02/100000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-03 10:00:00',
 'EndDate':'2022-08-03 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-03/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-04 10:00:00',
 'EndDate':'2022-08-04 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-04/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-05 10:00:00',
 'EndDate':'2022-08-05 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/hivestyle/ymd=2022-08-05/100000.json'
}
app.handler(event,None)

以下、各種クエリになります。

-- create table
CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_usepart_hivestyle(
pscomputername string ,
runspaceid string ,
psshowcomputername boolean ,
recordtype string ,
creationdate timestamp ,
userids string ,
operations string ,
auditdata string ,
resultindex int ,
resultcount int ,
identity string ,
isvalid boolean ,
objectstate string 
)
PARTITIONED BY (ymd DATE)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<<BUCKETNAME>>/testwrite/usepart/hivestyle/'
;

-- 以下クエリで、パーティション読み込み
----　作成しただけではパーティションは認識してくれない
MSCK REPAIR TABLE ms365auditlog_usepart_hivestyle;

-- 認識しているパーティション
SHOW PARTITIONS ms365auditlog_usepart_hivestyle;

-- sample select
SELECT * FROM "default"."ms365auditlog_usepart_hivestyle" 
where ymd < date_parse('2022-08-05','%Y-%m-%d');

-- delete partition
---- フォルダを消しても、パーティションは認識したまま（データはなくなる）
---- 再度、MSCKをやっても、以下のメッセージが出るだけ
---- Partitions missing from filesystem:	ms365auditlog_usepart_hivestyle:ymd=2022-08-01
ALTER TABLE ms365auditlog_usepart_hivestyle DROP PARTITION (ymd = '2022-08-01');

非Hive形式

フォルダ・ファイルの構造は以下のようにしました。

testwrite/usepart/flatstyle/
├── 2022-08-01/
│   ├── 090000.json
│   └── 100000.json
├── 2022-08-02/
│   └── 100000.json
├── 2022-08-03/
│   └── 100000.json
├── 2022-08-04/
│   └── 100000.json
└── 2022-08-05/
    └── 100000.json

コンテナ上で実行させたスクリプト

event = {
 'StartDate':'2022-08-01 09:00:00',
 'EndDate':'2022-08-01 09:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-01/090000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-01 10:00:00',
 'EndDate':'2022-08-01 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-01/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-02 10:00:00',
 'EndDate':'2022-08-02 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-02/100000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-03 10:00:00',
 'EndDate':'2022-08-03 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-03/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-04 10:00:00',
 'EndDate':'2022-08-04 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-04/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-05 10:00:00',
 'EndDate':'2022-08-05 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepart/flatstyle/2022-08-05/100000.json'
}
app.handler(event,None)

以下、各種クエリになります。

-- create table
CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_usepart_flatstyle(
pscomputername string ,
runspaceid string ,
psshowcomputername boolean ,
recordtype string ,
creationdate timestamp ,
userids string ,
operations string ,
auditdata string ,
resultindex int ,
resultcount int ,
identity string ,
isvalid boolean ,
objectstate string 
)
PARTITIONED BY (ymd DATE)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/'
;

-- 以下クエリで、パーティション読み込み
ALTER TABLE ms365auditlog_usepart_flatstyle ADD PARTITION (ymd='2022-08-01') location 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/2022-08-01';
ALTER TABLE ms365auditlog_usepart_flatstyle ADD PARTITION (ymd='2022-08-02') location 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/2022-08-02';
ALTER TABLE ms365auditlog_usepart_flatstyle ADD PARTITION (ymd='2022-08-03') location 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/2022-08-03';
ALTER TABLE ms365auditlog_usepart_flatstyle ADD PARTITION (ymd='2022-08-04') location 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/2022-08-04';
ALTER TABLE ms365auditlog_usepart_flatstyle ADD PARTITION (ymd='2022-08-05') location 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/2022-08-05';

-- 認識しているパーティション
SHOW PARTITIONS ms365auditlog_usepart_flatstyle;

-- sample select
SELECT * FROM "default"."ms365auditlog_usepart_flatstyle" 
where ymd < date_parse('2022-08-05','%Y-%m-%d');

--- delete partition
ALTER TABLE ms365auditlog_usepart_flatstyle DROP PARTITION (ymd = '2022-08-01');

パーティションが複数の場合

以下のページが参考になりました。

Hive形式

フォルダ・ファイルの構造は以下のようにしました。

testwrite/usepartmulti/hivestyle/
└── year=2022/
    └── month=08/
        ├── day=01/
        │   ├── 090000.json
        │   └── 100000.json
        ├── day=02/
        │   └── 100000.json
        ├── day=03/
        │   └── 100000.json
        ├── day=04/
        │   └── 100000.json
        └── day=05/
            └── 100000.json

コンテナ上で実行させたスクリプト

event = {
 'StartDate':'2022-08-01 09:00:00',
 'EndDate':'2022-08-01 09:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=01/090000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-01 10:00:00',
 'EndDate':'2022-08-01 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=01/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-02 10:00:00',
 'EndDate':'2022-08-02 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=02/100000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-03 10:00:00',
 'EndDate':'2022-08-03 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=03/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-04 10:00:00',
 'EndDate':'2022-08-04 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=04/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-05 10:00:00',
 'EndDate':'2022-08-05 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/hivestyle/year=2022/month=08/day=05/100000.json'
}
app.handler(event,None)

以下、各種クエリになります。

-- create table
CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_usepartmulti_hivestyle(
pscomputername string ,
runspaceid string ,
psshowcomputername boolean ,
recordtype string ,
creationdate timestamp ,
userids string ,
operations string ,
auditdata string ,
resultindex int ,
resultcount int ,
identity string ,
isvalid boolean ,
objectstate string 
)
PARTITIONED BY (year string, month string, day string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<<BUCKETNAME>>/testwrite/usepartmulti/hivestyle/'
;

-- 以下クエリで、パーティション読み込み
MSCK REPAIR TABLE ms365auditlog_usepartmulti_hivestyle;

-- 認識しているパーティション
SHOW PARTITIONS ms365auditlog_usepartmulti_hivestyle;

-- sample select
SELECT * FROM "default"."ms365auditlog_usepartmulti_hivestyle" 
where
     year  = '2022'
 and month = '08'
 and day   < '05'
;

--- delete partition
ALTER TABLE ms365auditlog_usepartmulti_hivestyle DROP PARTITION (year=2022,month=08,day=01);

非Hive形式

フォルダ・ファイルの構造は以下のようにしました。

testwrite/usepartmulti/flatstyle/
└── 2022/
    └── 08/
        ├── 01/
        │   ├── 090000.json
        │   └── 100000.json
        ├── 02/
        │   └── 100000.json
        ├── 03/
        │   └── 100000.json
        ├── 04/
        │   └── 100000.json
        └── 05/
            └── 100000.json

コンテナ上で実行させたスクリプト

event = {
 'StartDate':'2022-08-01 09:00:00',
 'EndDate':'2022-08-01 09:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/01/090000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-01 10:00:00',
 'EndDate':'2022-08-01 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/01/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-02 10:00:00',
 'EndDate':'2022-08-02 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/02/100000.json'
}
app.handler(event,None)

event = {
 'StartDate':'2022-08-03 10:00:00',
 'EndDate':'2022-08-03 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/03/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-04 10:00:00',
 'EndDate':'2022-08-04 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/04/100000.json'
}
app.handler(event,None)
event = {
 'StartDate':'2022-08-05 10:00:00',
 'EndDate':'2022-08-05 10:59:59',
 'BucketName':os.environ['BUCKETNAME'],
 'Key':'testwrite/usepartmulti/flatstyle/2022/08/05/100000.json'
}
app.handler(event,None)

以下、各種クエリになります。

-- create table
CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_usepartmulti_flatstyle(
pscomputername string ,
runspaceid string ,
psshowcomputername boolean ,
recordtype string ,
creationdate timestamp ,
userids string ,
operations string ,
auditdata string ,
resultindex int ,
resultcount int ,
identity string ,
isvalid boolean ,
objectstate string 
)
PARTITIONED BY (year string, month string, day string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/'
;

--  以下クエリで、パーティション読み込み
ALTER TABLE ms365auditlog_usepartmulti_flatstyle ADD PARTITION (year=2022,month=08,day=01) location 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/2022/08/01';
ALTER TABLE ms365auditlog_usepartmulti_flatstyle ADD PARTITION (year=2022,month=08,day=02) location 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/2022/08/02';
ALTER TABLE ms365auditlog_usepartmulti_flatstyle ADD PARTITION (year=2022,month=08,day=03) location 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/2022/08/03';
ALTER TABLE ms365auditlog_usepartmulti_flatstyle ADD PARTITION (year=2022,month=08,day=04) location 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/2022/08/04';
ALTER TABLE ms365auditlog_usepartmulti_flatstyle ADD PARTITION (year=2022,month=08,day=05) location 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/2022/08/05';

-- 認識しているパーティション
SHOW PARTITIONS ms365auditlog_usepartmulti_flatstyle;

-- sample select
SELECT * FROM "default"."ms365auditlog_usepartmulti_flatstyle" 
where
     year  = '2022'
 and month = '08'
 and day   < '05'
;

--- delete partition
ALTER TABLE ms365auditlog_usepartmulti_flatstyle DROP PARTITION (year=2022,month=08,day=01);

おわりに

以前作成したMS365の監査ログを取得するLambdaを使った想定で、日付ごとにパーティションを設けたAthenaのテーブルを作成してみました。
パーティション追加の手間を考えるとHive形式のほうが楽なので、運用の際はフォルダ名に注意して使いたいと思います。