CloudTrailのクエリをDuckDBに置き換えてクラウド破産と無縁な分析基盤を作る

## はじめに

NTTテクノクロスの渡邉 洋平です。

最近DuckDBでS3バケット上のデータをロードする記事をよく見ますね。

https://road288.hatenablog.com/entry/2024/11/06/113954

https://qiita.com/kaminchu/items/f78adca3eae6cfa1e727

私も使い方を勉強したく、今回はDuckDB × S3を試すことにしました。今回はCloudTrailのデータを対象にします。理由として、このググって出てくる記事が手元で失敗した経験もモチベーションとしてあります。

https://dev.to/aws-builders/tactical-cloud-audit-log-analysis-with-duckdb-aws-cloudtrail-2amk

## DuckDBとは

https://duckdb.org/

ざっくりいうと、SQLiteのOLAP版です。S3などに置かれたオブジェクト群をロードし、データに対し気軽に分析クエリを発行できます。

### vs 分析クエリサービス

この「気軽に分析クエリを発行できる」点はかなり強いモチベーションになります。AWSにはAthenaやRedshiftなど、分析クエリを発行するサービスが多数存在します。しかし、Redshiftクラスターを建てるとコストがしっかりかかりますし、Athenaなどのアドホッククエリサービスもスキャンするデータ量を計算しながら慎重にクエリすることもありました。

具体例を挙げると、Athenaで大量ファイルへの複数回クエリを発行する場合、スキャン量だけでなく大量のS3アクセスが発生し、付帯してGuardDutyやCloudTrailも反応することで請求が大きくなります。詳細は下記が詳しいです。

https://dev.classmethod.jp/articles/cost-matters-when-querying-large-s3-objects-with-amazon-athena/

Duck DBでは一度ロードしたデータを、テーブルとしてインメモリやストレージで扱います。そのため前述の高額請求のようなケースもあまりなく、小規模ながら費用対効果の高い分析基盤を実現できます。言わばS3リクエストのプロキシとしても機能するイメージです。

### DuckDBの注目すべき点

コスト以外の観点でも、DuckDBは以下が魅力的です。

- OSSでシングルバイナリ。単にダウンロードしクライアントに配置すれば無料で機能します 
- ロード機能の強さ。S3パスにワイルドカードも効くので、スキーマ定義を作りこまなくても案外読み込めてしまう点が便利です。まずパーティションを切って……という大仰な運用から解放されます

データの処理速度も十分に速く、実用的なプロダクトといえるでしょう。

### 注意

https://github.com/duckdb/duckdb-wasm

DuckDB-WASMというプロダクトもありますが、こちらは本家DuckDBとの互換性が担保されていない点に注意が必要です。フロントから分析クエリを発行するには便利そうなので、どこかでキャッチアップしたいですね。


## 使い方

それでは早速、DuckDBでCloudTrailのデータが含まれるS3 Bucketをロードし、クエリする手順を公開します。

### 1. Install

基本的にバイナリを配置するだけです。今回はLinuxを前提とした手順を参考にします。

https://duckdb.org/docs/installation/index?version=stable&environment=cli&platform=linux&download_method=direct&architecture=x86_64

```bash
wget https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
rm duckdb_cli-linux-amd64.zip
```

### 2. Credential

このあたりを参考に、AWS IAMをDuckDBのSecretとして設定します。

https://duckdb.org/docs/configuration/secrets_manager.html

https://duckdb.org/docs/extensions/httpfs/s3api.html#credential_chain-provider

セッションごとに以下を入力する形になります。

```sql
CREATE SECRET s3_secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
); 
```

`CREDENTIAL_CHAIN`は通常のAWS SDKと同じロジックでAWS IAMを参照するので、AWSユーザとしては直観的に扱えます。

### 3. S3 Load

#### 失敗編

DuckDBは`read_json`でS3上のjson.gzを扱えます。そのため、このように素朴な扱い方をしたくなります。

```sql
CREATE TABLE ct_raw_20241125 AS 
SELECT * FROM read_json('s3://aws-cloudtrail-logs-************/AWSLogs/************/CloudTrail/ap-northeast-1/2024/11/25/*.json.gz', maximum_depth=2);
```

しかしこの場合、ネストされたJSONが登録されます。これでは上手くクエリできません。

```sql
select * from ct_raw_20241125 limit 10;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                  Records                                                                                  │
│                                                                                  json[]                                                                                   │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{"eventVersion":"1.08","userIdentity":{"type":"AWSService","invokedBy":"config.amazonaws.com"},"eventTime":"2024-11-25T04:52:53Z","eventSource":"sts.amazonaws.com","e…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AWSService","invokedBy":"cloudtrail.amazonaws.com"},"eventTime":"2024-11-25T04:54:34Z","eventSource":"s3.amazonaws.com"…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWS…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AWSService","invokedBy":"cloudtrail.amazonaws.com"},"eventTime":"2024-11-25T05:04:19Z","eventSource":"s3.amazonaws.com"…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AWSService","invokedBy":"cloudtrail.amazonaws.com"},"eventTime":"2024-11-25T05:09:57Z","eventSource":"s3.amazonaws.com"…  │
│ [{"eventVersion":"1.09","userIdentity":{"type":"IAMUser","principalId":"AIDAYDMMLIB4B3J4AIADF","arn":"arn:aws:iam::************:user/w-youhe","accountId":"************…  │
│ [{"eventVersion":"1.09","userIdentity":{"type":"IAMUser","principalId":"AIDAYDMMLIB4B3J4AIADF","arn":"arn:aws:iam::************:user/w-youhe","accountId":"************…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AWSService","invokedBy":"cloudtrail.amazonaws.com"},"eventTime":"2024-11-25T05:14:33Z","eventSource":"s3.amazonaws.com"…  │
│ [{"eventVersion":"1.08","userIdentity":{"type":"AWSService","invokedBy":"im.amazonaws.com"},"eventTime":"2024-11-25T05:19:52Z","eventSource":"sts.amazonaws.com","event…  │
│ [{"eventVersion":"1.10","userIdentity":{"type":"AWSService","invokedBy":"config.amazonaws.com"},"eventTime":"2024-11-25T05:25:51Z","eventSource":"s3.amazonaws.com","ev…  │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                  10 rows                                                                                  │

```

#### 成功編

このような場合はunnestを使うのが正解です。

https://duckdb.org/docs/sql/query_syntax/unnest.html

WITH句を使用してread_jsonのデータを読み込み、その後unnestで中間テーブルを作らずにネストしないテーブルを作成できます。

```sql
CREATE TABLE ct_20241125 AS
  WITH raw_data AS (
      SELECT * 
      FROM read_json(
          's3://aws-cloudtrail-logs-xxxxxxxxxxxx/AWSLogs/xxxxxxxxxxxx/CloudTrail/ap-northeast-1/2024/11/25/*.json.gz',
          maximum_depth=2
      )
  )
  SELECT unnest(Records) AS Event
  FROM raw_data;
```

先ほどより良くなりました。

```sql
SELECT 
    *
  FROM ct_20241125
  LIMIT 10;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                MaskedEvent                                                                                │
│                                                                                  varchar                                                                                  │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ {"eventVersion":"1.08","userIdentity":{"type":"AWSService","invokedBy":"config.amazonaws.com"},"eventTime":"2024-11-25T04:52:53Z","eventSource":"sts.amazonaws.com","ev…  │
│ {"eventVersion":"1.08","userIdentity":{"type":"AWSService","invokedBy":"config.amazonaws.com"},"eventTime":"2024-11-25T04:52:54Z","eventSource":"sts.amazonaws.com","ev…  │
│ {"eventVersion":"1.08","userIdentity":{"type":"AWSService","invokedBy":"config.amazonaws.com"},"eventTime":"2024-11-25T04:52:55Z","eventSource":"sts.amazonaws.com","ev…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
│ {"eventVersion":"1.10","userIdentity":{"type":"AssumedRole","principalId":"AROAYDMMLIB4DTHMKA27B:AWSConfig-Describe","arn":"arn:aws:sts::************:assumed-role/AWSS…  │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                  10 rows                                                                                  │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```


とはいえ、まだ扱いづらいので、もう一段フィールドを抽出して、新しいテーブルcloudtrail_detailedを作成します。

```sql
CREATE TABLE ct_detailed_20241125 AS
SELECT
    -- 基本的なイベント情報
    json_extract_string(Event, '$.eventVersion') AS eventVersion,
    json_extract_string(Event, '$.eventTime') AS eventTime,
    json_extract_string(Event, '$.eventSource') AS eventSource,
    json_extract_string(Event, '$.eventName') AS eventName,
    json_extract_string(Event, '$.awsRegion') AS awsRegion,
    json_extract_string(Event, '$.sourceIPAddress') AS sourceIPAddress,
    json_extract_string(Event, '$.userAgent') AS userAgent,

    -- ユーザー情報
    json_extract_string(Event, '$.userIdentity.type') AS userType,
    json_extract_string(Event, '$.userIdentity.principalId') AS principalId,
    json_extract_string(Event, '$.userIdentity.arn') AS userArn,
    json_extract_string(Event, '$.userIdentity.accountId') AS accountId,
    json_extract_string(Event, '$.userIdentity.accessKeyId') AS accessKeyId,
    json_extract_string(Event, '$.userIdentity.userName') AS userName,

    -- セッション情報
    json_extract_string(Event, '$.userIdentity.sessionContext.attributes.creationDate') AS sessionCreationDate,
    json_extract_string(Event, '$.userIdentity.sessionContext.attributes.mfaAuthenticated') AS mfaAuthenticated,

    -- リクエストパラメータ（インスタンスIDセットの展開）
    json_extract_string(json_extract(Event, '$.requestParameters.instancesSet.items[0]'), '$.instanceId') AS instanceId1,
    json_extract_string(json_extract(Event, '$.requestParameters.instancesSet.items[1]'), '$.instanceId') AS instanceId2,

    -- レスポンス要素（インスタンスの状態）
    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[0]'), '$.instanceId') AS responseInstanceId1,
    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[0]'), '$.currentState.name') AS responseCurrentState1,
    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[0]'), '$.previousState.name') AS responsePreviousState1,

    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[1]'), '$.instanceId') AS responseInstanceId2,
    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[1]'), '$.currentState.name') AS responseCurrentState2,
    json_extract_string(json_extract(Event, '$.responseElements.instancesSet.items[1]'), '$.previousState.name') AS responsePreviousState2,

    -- その他のフィールド
    json_extract_string(Event, '$.requestID') AS requestID,
    json_extract_string(Event, '$.eventID') AS eventID,
    json_extract_string(Event, '$.readOnly') AS readOnly,
    json_extract_string(Event, '$.eventType') AS eventType,
    json_extract_string(Event, '$.managementEvent') AS managementEvent,
    json_extract_string(Event, '$.recipientAccountId') AS recipientAccountId,
    json_extract_string(Event, '$.eventCategory') AS eventCategory,

    -- TLSの詳細
    json_extract_string(Event, '$.tlsDetails.tlsVersion') AS tlsVersion,
    json_extract_string(Event, '$.tlsDetails.cipherSuite') AS cipherSuite,
    json_extract_string(Event, '$.tlsDetails.clientProvidedHostHeader') AS clientProvidedHostHeader

FROM ct_20241125;

```

だいぶ使いやすくなってきました。

```sql
select * from ct_raw_20241125 limit 10;
┌──────────────┬──────────────────────┬───────────────────┬──────────────────────┬───┬────────────────────┬───────────────┬────────────┬─────────────┬──────────────────────┐
│ eventVersion │      eventTime       │    eventSource    │      eventName       │ … │ recipientAccountId │ eventCategory │ tlsVersion │ cipherSuite │ clientProvidedHost…  │
│   varchar    │       varchar        │      varchar      │       varchar        │   │      varchar       │    varchar    │  varchar   │   varchar   │       varchar        │
├──────────────┼──────────────────────┼───────────────────┼──────────────────────┼───┼────────────────────┼───────────────┼────────────┼─────────────┼──────────────────────┤
│ 1.08         │ 2024-11-25T04:52:53Z │ sts.amazonaws.com │ AssumeRole           │ … │ ************       │ Management    │            │             │                      │
│ 1.08         │ 2024-11-25T04:52:54Z │ sts.amazonaws.com │ AssumeRole           │ … │ ************       │ Management    │            │             │                      │
│ 1.08         │ 2024-11-25T04:52:55Z │ sts.amazonaws.com │ AssumeRole           │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketAcl         │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketCors        │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketLifecycle   │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketLogging     │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketTagging     │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketNotificat…  │ … │ ************       │ Management    │            │             │                      │
│ 1.10         │ 2024-11-25T04:52:54Z │ s3.amazonaws.com  │ GetBucketVersioning  │ … │ ************       │ Management    │            │             │                      │
├──────────────┴──────────────────────┴───────────────────┴──────────────────────┴───┴────────────────────┴───────────────┴────────────┴─────────────┴──────────────────────┤
│ 10 rows                                                                                                                                              33 columns (9 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

#### Appendix.

CloudShell環境だとハングするのですが、もう少し大きめの環境だとワイルドカードをこのくらい使っても動きます。使い勝手のいい非常に便利な機能です。

`'s3://aws-cloudtrail-logs-xxxxxxxxxxxx/AWSLogs/xxxxxxxxxxxx/CloudTrail/ap-northeast-1/*/*/*/*.json.gz'`


### 4. Table Export & Import

最新の情報が常に必要でないのなら、Tableはファイル化してキャッシュとして便利に扱いましょう。

#### local

公式ドキュメントに書いてある通りですが、試してみます。

https://duckdb.org/docs/sql/statements/export.html


Parquet形式で出力。

```sql
EXPORT DATABASE 'ct_raw_20241125' (FORMAT PARQUET);
```

Dumpしたテーブルは、以下のように保存される。

```bash
ls -ltr ct_raw_20241125/
total 740
-rw-r--r--. 1 cloudshell-user cloudshell-user    845 Nov 25 09:07 schema.sql
-rw-r--r--. 1 cloudshell-user cloudshell-user    178 Nov 25 09:07 load.sql
-rw-r--r--. 1 cloudshell-user cloudshell-user 602345 Nov 25 09:07 ct_________.parquet
-rw-r--r--. 1 cloudshell-user cloudshell-user 139895 Nov 25 09:07 ct_detailed_________.parquet
```

Importはこう。

```sql
IMPORT DATABASE 'ct_raw_20241125' (FORMAT PARQUET);
```

#### to S3

CloudShellのストレージに置くのは嫌だと思っていたら、S3を使ったExport/Importも対応しているようです。

https://duckdb.org/docs/guides/network_cloud_storage/s3_export.html

COPY構文でこのように書くとS3へ格納できます。折角なので圧縮もしています。

```sql
COPY ct_raw_20241125 TO 's3://bucket/file.parquet' (COMPRESSION 'zstd');
```


いったん加工済みのデータはS3に格納すれば、このように直接クエリしてもいいです。

```sql
SELECT * FROM read_parquet('s3://bucket/file.parquet') limit 10;
```

もちろん、再びTABLEとして読み込んでもいいのも便利です。

```sql
CREATE TABLE ct_imported AS SELECT * FROM read_parquet('s3://bucket/file.parquet');
SELECT * FROM ct_imported limit 10;
```

## 所感

DuckDBでのS3ロードから分析クエリを発行するまでの流れを試してみました。
今回はCLIでの操作でしたが、SDKもあるためシステムに組み込むのも容易です。

https://duckdb.org/docs/api/overview

開発時のログ分析など、細かな用途にフィットする使い方が見つけやすそうなので、今後も注目していきたいです。

次回は@Da_neon_to_toraの記事です。明日もどうやらAmazon Bedrockネタみたいですよ…？！