More than 1 year has passed since last update.

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

Posted at 2022-08-21

はじめに

前回、パーティションを使ったAthenaで、Microsoft365の監査ログを見る内容を記事にしました。

ただパーティションを都度追加・削除する必要があるため、毎日ログ収集するような運用では、ファイル追加と共にパーティションを追加する処理を実行する必要がありました。

Athenaには、パーティション管理を自動で行ってくれるパーティション射影という機能があります。

今回はそれを用いてテーブルを作成しました。

参考

やったこと

S3上のフォルダ構造

前回と同じように、4パターン作りました。

パーティションが一つのHive形式
- ymd=yyyy-MM-dd/というフォルダ名
パーティションが一つの非Hive形式
- yyyy-MM-dd/というフォルダ名
パーティションが複数のHive形式
- year=yyyy/month=MM/day=dd/というフォルダ階層
パーティションが複数の非Hive形式
- yyyy/MM/dd/というフォルダ階層

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

前回のCreate Table文と、大きく以下の点が異なります。

TBLPROPERTIESの中に設定を記述
パーティションごとに、タイプやパーティションの範囲などを設定

サポートされているタイプについての詳細は以下のページにあります。

今回は年月日なので、日付型と、一部整数型を用いました。

Hive形式

テーブルを作成するクエリと、Selectのサンプルは以下になります。前のパーティションとは異なり、作成後（パーティションを読み込ませる必要なく）すぐにデータが読めます。

CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_partproj_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/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.ymd.type' = 'date',
  'projection.ymd.range' = 'NOW-19DAYS,NOW+9HOUR',
  'projection.ymd.format' = 'yyyy-MM-dd'
)
;

-- 作成しただけで（パーティション読み込み不要で）、検索できる

-- パーティションに何があるのかはわからない。以下のクエリも結果無しとなる
SHOW PARTITIONS ms365auditlog_partproj_hivestyle;

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

rangeの設定で、閲覧範囲を指定できます。これを用いれば前回のようなパーティション削除も不要になります。
日時はUTCなので、日本時間に合わせ9時間加算しています。

実際、rangeが聞いているのか確認してみました。
20日にSELECT distinct ymd FROM "default"."ms365auditlog_partproj_hivestyle";を実行すると以下のような結果に（2日、3日は0バイトデータでした）。

21日に実行すると、8月1日が対象外になっていました（フォルダ・ファイルは存在する）。

非Hive形式

非Hive形式だと、以下の点を設定する必要があります。

storage.location.templateに、フォルダパスと、どこの部分をパーティションの扱いにするか
- ${パーティション名}で記載

CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_partproj_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/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.ymd.type' = 'date',
  'projection.ymd.range' = 'NOW-19DAYS,NOW+9HOUR',
  'projection.ymd.format' = 'yyyy-MM-dd',
  'storage.location.template' = 's3://<<BUCKETNAME>>/testwrite/usepart/flatstyle/${ymd}/'
)
;

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

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

複数になる場合もstorage.location.templateを記述し、どこの部分をパーティションとして認識させるかが必要になります。

Hive形式

以下のページを参考にしました。

実際に色々試して、以下のような知見を得られました。

パーティションの型と、射影の際に指定するタイプが異なってもよい
year
- 日付型が使用可能
  - ログデータの自動削除などと絡め、自動で対象外のパーティションが作れそう
Month,day
- 日付型は使用不可
  - テーブル作成はできるが、Selectで以下のようなエラー
    - "GENERIC_INTERNAL_ERROR: Text '08' could not be parsed: Unable to obtain Year from TemporalAccessor: {MonthOfYear=8},ISO of type java.time.format.Parsed"
- 整数型か列挙型なら使用可能な模様
  - 今回は整数型を使用
    - ゼロパディングに対応するにはprojection.<<パーティション名>>.digitsに桁数を指定

CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_partprojmulti_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/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'date',
  'projection.year.range' = 'NOW-19DAYS,NOW+9HOUR',
  'projection.year.format' = 'yyyy',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.month.digits' = '2', 
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'projection.day.digits' = '2',
  'storage.location.template' = 's3://<<BUCKETNAME>>/testwrite/usepartmulti/hivestyle/year=${year}/month=${month}/day=${day}/'
)
;

-- sample select
select * from ms365auditlog_partprojmulti_hivestyle
where
     year = '2022'
 and month = '08'
 and day < '05'
;

非Hive形式

年月日の区切りと、年月日のフォルダの区切りが両方とも/のため、年月日フォルダをまとめて日付のフォーマットとして認識してくれました。以下のページを参考にしました。

ただしパーティションを日付型で作ると、Selectの際に”HIVE_INVALID_PARTITION_VALUE: Invalid partition value '2022/08/01' for DATE partition key: ymd=2022%2F08%2F01”とエラーになったため、文字列型にしています。

CREATE EXTERNAL TABLE IF NOT EXISTS default.ms365auditlog_partprojmulti_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 string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.ymd.type' = 'date',
  'projection.ymd.range' = 'NOW-19DAYS,NOW+9HOUR',
  'projection.ymd.format' = 'yyyy/MM/dd',
  'storage.location.template' = 's3://<<BUCKETNAME>>/testwrite/usepartmulti/flatstyle/${ymd}/'
)
;

-- sample select
select * from ms365auditlog_partprojmulti_flatstyle
where ymd  < '2022/08/05';

おわりに

前回作成したS3のファイル構造に対して、パーティション射影を使ったAthenaテーブルを作成しました。
こちらの方が毎日ファイル追加してもパーティションを認識してくれるので、大変楽かと思います。

ただ前回のパーティションと機能が異なるので、公式のページ等でユースケースを確認して、目的に応じたほうを選択する必要ありそうです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up