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

Last updated at 2025-04-23Posted at 2025-04-23

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

概要

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

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

Amazon Athenaのデータベース作成

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

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

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

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からデータの抽出、検索を行うことが可能です。

◆結果のサンプル◆
{
    "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 構文を用いてテーブルを作成します。

Athena コンソールのクエリエディタを開きます。

データベースを作成するためクエリエディタから「CREATE EXTERNAL TABLE」を利用します。

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://[バケット名]/[パス]'

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

Amazon Athenaを利用した抽出と整形

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

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

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状態のパッチ数を集計

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

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

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などを利用することで確認用のダッシュボードを作成することもできると思います。

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