Amazon Athenaを利用してS3に保管したJSON形式のログを抽出

実現したいこと
[前回](https://qiita.com/yiz00247/items/8e68c50ff65f0452d1b3)、パッチ適用状況の定期取得をStep Functionで実装し、S3に"aws ssm describe-instance-patches"の結果を保存する実装を試しました。AWS CLIの結果はJSON形式であることが多く、応答内容によっては結果が分割されてしまいます。
内容の確認には不便なため、Amazon Athenaによる抽出と整形について試してみました。

# 概要
Amazon Athenaを利用して、S3に保管したJSON形式のログを抽出、整形する。

1. Amazon Athenaのデータベース作成
2. Amazon Athenaのテーブル作成
3. Amazon Athenaを利用した抽出と整形

# Amazon Athenaのデータベース作成
Athena を利用するためには、データベースを作成する必要があります。

:::note info
Athena コンソールを初めて使用する場合は、クエリ結果の保存場所（S3）を設定する必要があります。
:::

1. Athena コンソールのクエリエディタを開きます。
2. データベースを作成するためクエリエディタに「CREATE DATABASE」を入力します。
    ``` SQL
    CREATE DATABASE [DB名]
    ```
3. [Run] (実行) を実施しデータベースを作成します。
4. 左メニューのデータベースリストから、現在のデータベースとして作成したデータベースを選択します。

# Amazon Athenaのテーブル作成
作成したデータベースに今回検索用のAthena テーブルを作成します。

## S3の保管先
前回保存したS3バケットとフォルダ構造は下記のようになっています。

```
s3://[バケット名]/[パス]/year=[yyyy]/month=[mm]/day=[dd]/instanceid=[instance-id]/(ランダムなファイル名)
```
このうち[year]、[month]、[day]、[instanceid]についてデータのパーティション化を実施します。パーティションにすることで、各クエリ実行でスキャンされる範囲を制限できパフォーマンス向上とコスト削減が見込めます。今回は日付とインスタンスIDを抽出条件にも利用します。パーティション化については、パス形式をKey=Valueで登録しているので、Apache Hive スタイルのパーティションが使用できます。

## S3に保管しているJSONファイル
"aws ssm describe-instance-patches"の結果は以下のような結果で保管しています。AthenaではJSONからデータの抽出、検索を行うことが可能です。

```json
◆結果のサンプル◆
{
    "Patches": [
        {
            "Classification": "Security",
            "InstalledTime": "yyyy-mm-ddThh:mm:ssZ",
            "KbId": "〇〇〇",
            "Severity": "required",
            "State": "Installed",
            "Title": "〇〇〇"
        },
        {
            "Classification": "Security",
            "InstalledTime": "yyyy-mm-ddThh:mm:ssZ",
            "KbId": "〇〇〇",
            "Severity": "Critical",
            "State": "Installed",
            "Title": "〇〇〇"
        },
        {
            "Classification": "Security",
            "InstalledTime": "yyyy-mm-ddThh:mm:ssZ",
            "KbId": "〇〇〇",
            "Severity": "optional",
            "State": "Missing",
            "Title": "〇〇〇"
        }
    ]
}
```
## Amazon Athenaのテーブル作成
S3のデータに対してテーブルを作成する. CREATE EXTERNAL TABLE 構文を用いてテーブルを作成します。
1. Athena コンソールのクエリエディタを開きます。
2. データベースを作成するためクエリエディタから「CREATE EXTERNAL TABLE」を利用します。
    ``` SQL
    CREATE EXTERNAL TABLE IF NOT EXISTS `[DB名]`.`[テーブル名]` (
        `patches` array < struct < classification: string,
        installedtime: string,
        kbid: string,
        severity: string,
        state: string,
        title: string >>
    )
    PARTITIONED BY (
        `year` string,
        `month` string,
        `day` string,
        `instanceid` string
    )
    ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
    STORED AS INPUTFORMAT 
        'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
        'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION 's3://[バケット名]/[パス]'
    ```

3. [Run] (実行) を実施しテーブルを作成します。
4. テーブルを作成後、Hive パーティションをロードするために「MSCK REPAIR TABLE」コマンドを使用します。
    ``` SQL
    MSCK REPAIR TABLE `[DB名]`.`[テーブル名]`;
    ```
5. 左メニューのテーブルリストから作成したテーブルを選択し、メニューから「テーブルをプレビュー」を選択します。
6. 右画面のクエリにSELECT文が入力されるので[Run]を実行します 。
7. 結果欄に10件のデータが表示されることを確認します。 

#  Amazon Athenaを利用した抽出と整形
作成したテーブルに対して、「SELECT」文で抽出することができます。いくつかの目的合わせたSQL分を紹介します。

## 特定日において各インスタンスごとのステータスがMissing状態のパッチ数を集計

``` SQL
SELECT "instanceid",
	COUNT(*) AS Count
FROM "[DB名]"."[テーブル名]",
	UNNEST(patches) AS t(patche)
where patche.State = 'Missing'
    and month = '03'
	and day = '20'
GROUP BY instanceid
 ```

|instanceid|Count|
|:---|:---|
|i-00000000000000000|1|
|i-00000000000000001|10|

## 各インスタンスごとのステータスがMissing状態のパッチ数を集計

``` SQL
SELECT "instanceid",
    concat_ws('/', "year", "month", "day") as date,
	count(*) as patches_count
FROM "[DB名]"."[テーブル名]",
	UNNEST(patches) AS t(patche)
where patche.State = 'Missing'
GROUP BY "year",
	"month",
	"day",
	"instanceid"
ORDER BY "instanceid" ASC, "date" ASC
```

|instanceid|date|patches_count|
|:---|:---|:---|
|i-00000000000000000|2025/03/01|1|
|i-00000000000000000|2025/03/08|10|
|i-00000000000000000|2025/03/15|3|
|i-00000000000000001|2025/03/01|10|
|i-00000000000000001|2025/03/08|10|
|i-00000000000000001|2025/03/15|3|

## 日付、インスタンスを指定した詳細の確認

``` SQL
SELECT concat_ws('/', "year", "month", "day") as date,
    instanceid,
	patche.title,
	patche.State,
	patche.Severity,
	patche.Classification
FROM "test"."describeinstancepatches",
	UNNEST(patches) AS t(patche)
where patche.State = 'Missing'
	and instanceid = 'i-0b325700f96b11fd0'
	and month = '03'
	and day = '20'
```
|date|instanceid|title|State|Severity|Classification|
|:---|:---|:---|:---|:---|:---|
|2025/03/20|i-00000000000000000|(パッチタイトル)|Missing|optional|Security|
|2025/03/20|i-00000000000000000|(パッチタイトル)|Missing|important|Security|

# 終わり
Athenaによる抽出と整形をすることで、ログファイルを１つ１つ確認することなく状態を確認することができます。表示についてはQuickSightなどを利用することで確認用のダッシュボードを作成することもできると思います。