LoginSignup
11
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

NTTテクノクロスAdvent Calendar 2024

Day 5
@watany(y watanabe)

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

Last updated at Posted at 2024-12-04

はじめに

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

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

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

DuckDBとは

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

vs 分析クエリサービス

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

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

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

DuckDBの注目すべき点

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

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

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

注意

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

使い方

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

1. Install

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

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として設定します。

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

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を扱えます。そのため、このように素朴な扱い方をしたくなります。

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が登録されます。これでは上手くクエリできません。

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を使うのが正解です。

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

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;

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

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を作成します。

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;

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

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

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

Parquet形式で出力。

EXPORT DATABASE 'ct_raw_20241125' (FORMAT PARQUET);

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

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はこう。

IMPORT DATABASE 'ct_raw_20241125' (FORMAT PARQUET);

to S3

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

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

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

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

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

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

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

所感

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

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

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

11
5
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?