Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
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?

More than 1 year has passed since last update.

@a_b_

Athenaでパーティションを使って、Microsoft365の監査ログを見る

Posted at

はじめに

前回は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形式のほうが楽なので、運用の際はフォルダ名に注意して使いたいと思います。

2
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
2
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?