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

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

https://qiita.com/a_b_/items/811dcd39f127e0f2333e

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

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

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection.html

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

# 参考

https://qiita.com/t-kataym/items/9a572b309eb717f69257

https://dev.classmethod.jp/articles/20200627-amazon-athena-partition-projection/

# やったこと

## S3上のフォルダ構造

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

https://qiita.com/a_b_/items/811dcd39f127e0f2333e#s3%E4%B8%8A%E3%81%AE%E3%83%95%E3%82%A9%E3%83%AB%E3%83%80%E3%81%AE%E4%BD%9C%E3%82%8A%E6%96%B9

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

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

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

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

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection-supported-types.html

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

### Hive形式

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

```sql
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バイトデータでした）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/e859196f-340e-1079-bd05-dec61358948e.png)

21日に実行すると、8月1日が対象外になっていました（フォルダ・ファイルは存在する）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/119f1541-93b5-1cf0-22b1-b7a92c8c08c3.png)


### 非Hive形式

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

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

```sql
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形式
以下のページを参考にしました。

https://dev.classmethod.jp/articles/20200727-amazon-athena-partition-projection-for-hive-partition/

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

- パーティションの型と、射影の際に指定するタイプが異なってもよい
- 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`に桁数を指定

```sql
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形式

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


https://dev.classmethod.jp/articles/20200627-amazon-athena-partition-projection/#toc-5

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

```sql
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テーブルを作成しました。
こちらの方が毎日ファイル追加してもパーティションを認識してくれるので、大変楽かと思います。

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