LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ryu-kiinTIS株式会社

【DuckDB】新人でもDuckDBでALBアクセスログを処理したい

Last updated at Posted at 2025-01-24

はじめに

タイトルの通り、ALBアクセスログを簡単に収集するために、DuckDBの検証などに取り組んだので、その内容を記事としてアウトプットしました。
DuckDBに関してはある程度広まっていて、n番煎じな内容になりますが、自分がやったことの記録として記事に残しておきたいと思います。

以下作業は、Cloud9上で実施しておりますのでご注意ください。

状況

私が保守を担当しているシステムでは（レスポンスタイムに関する）アラートが発生した際、原因を特定するために、まずALBアクセスログを確認しています。（その後、ECSのログを確認していますが、今回の話題とは関係ないので割愛します。）
ALBアクセスログはS3バケットに保管されています。

As-Is

現状では、ログ収集が以下のような手順の手作業になっており、時間がかかったり収集漏れがあったりする状態になっています。

  1. マネコンからS3バケットを開く
  2. オブジェクトの保存時間を見てログが入っていそうなオブジェクト（gzファイル）をダウンロードする
  3. ローカルで解凍する
  4. ログを確認する
  5. 圧縮ファイルを削除する
  6. 欲しいログがなければ、再度ダウンロードする（2に戻る...）

To-Be

そのため、ログ収集の簡単化を目指します。
（最終的にはアラートをトリガーに自動でログが集まっている状態が理想？）
※簡単化：（SQLを実行して結果を取得する）スクリプトを実行すれば、集めたい期間のログを集めることができる状態であること

このようなことから、DuckDBを用いたログ収集を試みることにしました。

DuckDBとは

DuckDBは、オープンソースの組み込み型OLAPデータベース管理システムです。
SQLiteのOLAP版と言われることが多いそうです。
※OLAP（Online Analytical Processing）：データを多次元的に分析し、その結果を迅速にユーザーに返す手法のこと（Online：リアルタイムに分析結果を返すこと）

また、公式では、以下のようなことが強みとして挙げられています。

  • シンプル（Simple）
  • ポータブル（Portable）
  • 豊富な機能（Feature-Rich）
  • 高速（Fast）
  • 拡張可能（Extensible）
  • 無料（Free）
  • 徹底したテスト（Thorough Testing）

では、ログ取得の仕組みについて説明する前に、まずは事前に必要な準備について簡単に説明します。

事前準備

ALBアクセスログの保存の有効化

今回は説明を省略しますので、以下の記事などを参考に実施してください。

S3バケットポリシーの追加

今回はCloud9で作業をしますので、Cloud9からS3バケットへアクセスできるようにポリシーを追加します。
以下のようなポリシーを追加しました。

{
    "Sid": "AlbAccessLogsPolicyForDuckDB",
    "Effect": "Allow",
    "Principal": {
        "AWS": "Cloud9にアタッチされているIAMロールのarn"
    },
    "Action": "s3:GetObject",
    "Resource": [
        "ALBアクセスログを保管しているS3バケットのarn"
    ]
}

DuckDBのインストール

以下を参考にインストールできます。（とても軽いです）

curl -LO https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip

※python APIも用意されており、pythonで扱いたい場合は以下のようにpip installします。

pip install duckdb

事前準備は以上です。
ここからアクセスログを取得する仕組みについて説明していきます。

ログを取得する仕組みのイメージ

  1. S3から指定した期間のログファイルをダウンロードする
  2. 取得したログファイルをDuckDBにインポートする（テーブル化する）
  3. クエリを叩き、結果をcsvファイルとして出力する

実際の実装例

実行するスクリプト（ラッパースクリプト）

  • get_alb_log.sh
    • yyyy/mm envを引数に取ります。
get_alb_log.sh
#!/bin/bash

set -e

# 引数の確認
if [ "$#" -ne 2 ]; then
    echo "Usage: $0 <yyyy/mm> <env>"
    exit 1
fi

# 引数の取得
DATE="$1"
ENV="$2"

# pythonスクリプト用の変換(yyyy/mm → yyyymm)
DATE_FOR_PY=${DATE//\//}

# s3からALBログをダウンロード
./get_alb_log_from_s3.sh "$DATE" "$ENV"

# ログをテーブルへインポートする
echo "import table"
python3 create_tables.py "$DATE_FOR_PY"

# クエリ結果をcsvに出力する
echo "outout result"
python3 get_result.py "$DATE_FOR_PY"

# DBファイルを削除する
rm -f alb_log.db

1. ログファイルのダウンロード

  • get_alb_log_from_s3.sh
    • yyyy/mm envを引数に取ります。
get_alb_log_from_s3.sh
#!/bin/bash

# 引数の確認
if [ "$#" -ne 2 ]; then
    echo "Usage: $0 <yyyy/mm> <env>"
    exit 1
fi

# 引数の取得
DATE_PATH=$1
ENV=$2

ACCOUNT_ID="0"

# 環境ごとのアカウントidを設定
# ※S3バケットURIの指定時に利用
if [ "$ENV" = "dev" ]; then
    ACCOUNT_ID="YOUR_DEV_ACCOUNT_ID"
elif [ "$ENV" = "prd" ]; then
    ACCOUNT_ID="YOUR_PRD_ACCOUNT_ID"
else
    echo "Error: <env> must be dev or prd."
    exit 1
fi

# yyyy/mm/dd フォーマットの確認
if ! [[ "$DATE_PATH" =~ ^[0-9]{4}/[0-9]{2}$ ]]; then
    echo "Error: Date must be in yyyy/mm format."
    exit 1
fi

# ダウンロード元とダウンロード先のパスの作成
S3_PATH="アクセスログを保存しているS3バケットURI"
LOCAL_DIR="ダウンロードするログファイルの、Cloud9上での保存場所"

# フォルダが存在するかを確認
if [ -d "$LOCAL_DIR" ]; then
  echo "'$LOCAL_DIR' already exists."
else
  # フォルダを作成
  mkdir -p "$LOCAL_DIR"
  if [ $? -eq 0 ]; then
    echo "'$LOCAL_DIR' has been created."
  else
    echo "Failed to create '$LOCAL_DIR'"
    exit 1
  fi
fi

# aws s3 cp コマンドの実行
echo "Downloading from $S3_PATH to $LOCAL_DIR..."
if aws s3 cp "$S3_PATH" "$LOCAL_DIR" --recursive; then
    echo "Download completed successfully."
else
    echo "Error during download."
    exit 1
fi

2. ログファイルをDuckDBへインポート

  • create_tables.py
    • yyyymmを引数に取ります。

こちらで行われている処理は、パーティション射影と呼ばれ、こちらの記事を参考にさせていただきました。

create_tables.py
import duckdb
import argparse

# 引数をパース
parser = argparse.ArgumentParser(description="引数の指定する期間のALBログをテーブルへインポートする")
parser.add_argument("date", type=str, help="yyyy/mm を指定してください")
args = parser.parse_args()

# データベースファイルを作成または接続
con = duckdb.connect(database='alb_log.db', read_only=False)

# テーブルの作成
con.execute(f"""
    CREATE TABLE alb_log AS
    SELECT *
    FROM read_csv(
        '[Cloud9上での保存場所]/*/*.gz',
        columns={{
            'type': 'VARCHAR',
            'timestamp': 'TIMESTAMP',
            'elb': 'VARCHAR',
            'client_ip_port': 'VARCHAR',
            'target_ip_port': 'VARCHAR',
            'request_processing_time': 'DOUBLE',
            'target_processing_time': 'DOUBLE',
            'response_processing_time': 'DOUBLE',
            'elb_status_code': 'INTEGER',
            'target_status_code': 'VARCHAR',
            'received_bytes': 'BIGINT',
            'sent_bytes': 'BIGINT',
            'request': 'VARCHAR',
            'user_agent': 'VARCHAR',
            'ssl_cipher': 'VARCHAR',
            'ssl_protocol': 'VARCHAR',
            'target_group_arn': 'VARCHAR',
            'trace_id': 'VARCHAR',
            'domain_name': 'VARCHAR',
            'chosen_cert_arn': 'VARCHAR',
            'matched_rule_priority': 'VARCHAR',
            'request_creation_time': 'TIMESTAMP',
            'actions_executed': 'VARCHAR',
            'redirect_url': 'VARCHAR',
            'error_reason': 'VARCHAR',
            'target_port_list': 'VARCHAR',
            'target_status_code_list': 'VARCHAR',
            'classification': 'VARCHAR',
            'classification_reason': 'VARCHAR',
            'conn_trace_id': 'VARCHAR'
        }},
        delim=' ',
        quote='"',
        escape='"',
        header=False,
        auto_detect=False
    )
""")

# 接続を閉じる
con.close()

3. クエリの実行・結果の出力

  • get_result.py
    • yyyymmを引数に取ります。
    • 本例では、レスポンスタイムが0より大きいものだけを抽出しています。
      • WHERE response_processing_time > 0
get_result.py
import duckdb
import argparse

# 引数をパース
parser = argparse.ArgumentParser(description="引数の指定する期間のALBログをクエリした結果を返す")
parser.add_argument("date", type=str, help="yyyymm を指定してください")
args = parser.parse_args()

# データベースファイルに接続
con = duckdb.connect(database='alb_log.db', read_only=True)

# クエリ結果をCSVファイルへのエクスポート
con.execute(f"""
    COPY (
        SELECT
            timestamp, 
            elb, 
            client_ip_port, 
            target_ip_port, 
            response_processing_time, 
            elb_status_code, 
            target_status_code, 
            request
        FROM
            alb_log
        WHERE
            response_processing_time > 0        
    )
    TO 'output_{args.date}.csv' (HEADER, DELIMITER ',')
""")

# 接続を閉じる
con.close()

実行すると以下のようなcsvファイルが取得できています。
（timestampとresponse_processing_time以外の情報はマスキングしています）
duck_query_result_example_masking.png

今後の展望

以上で、自動化が（いったん）実現できました。
次は以下のようなことに取り組みたいと考えています。

  • GUIを導入し、簡単にクエリを叩くことができるようにする
    • DBeaverなどを利用すれば簡単に実現できそう
  • ALBアクセスログの各メトリクスの可視化
    • DuckDBのテーブルにインポートできているので、PowerBIのようなBIツールなどを用いれば実現可能と考えられる
  • 他サービス（ECS）のログとの紐づけ
    • うまく利用できる外部キーを用意することができれば（ここがポイントではあるが）実現できそうに感じる

おまけ（Athenaについて）

ここまでDuckDBでの実装について書いてきましたが、アクセスログがS3バケットに保存されているのであればAthenaを使わないのかと思われた方もいるかと思います。
私も実際にAthenaについて調べ、触ってみたのですが、以下のようなことが理由で採用を見送りました。（Athenaのいいところについてご存じの方がいらっしゃれば教えてください）

  • スクリプトで実施する際に優位性を感じる
    • 使い慣れているpythonでAPIが提供されているため、比較的簡単に実装可能（イメージとしてはSQLiteのようなもの）
    • Athenaも不可能ではないと思われるが、情報があまりなく、ハードルを感じる
  • Cloud9上で作業を実施する（IAMロールでアクセス制御する）ため、セキュリティリスクはAthenaと同等程度と思われる
  • 以下の条件に合致するのでデータ転送に料金はかからない認識である
    • Amazon S3 バケットから S3 バケットと同じ AWS リージョン内の任意の AWS のサービスに転送されたデータ (同じ AWS リージョン内の別のアカウントに転送されたデータを含む)

比較の際に作成した表

比較観点 DuckDB Athena
アーキテクチャ ローカル動作の列指向データベースエンジン サーバーレスの分散クエリサービス
利用用途 小～中規模データ処理、ローカル分析 大規模データ分析、データレイククエリ
デプロイメント シンプルなローカルセットアップ AWSアカウントとS3データの設定が必要
※現環境であればほぼ不要
パフォーマンス メモリ内計算で小～中規模データに最適 分散処理で大規模データセットに対応
スケーラビリティ 1台のマシンに依存
※本ケースではCloud9上で利用		 分散クエリエンジンでスケーラブル
コスト 無料（オープンソース）
※ただし、S3からデータダウンロードあり		 データスキャン量に基づく課金（$5/1TB）
対応データフォーマット CSV、Parquet、JSON、SQLiteなど CSV、Parquet、ORC、Avro、JSONなど
接続性と統合 Python、R、CLIなどから利用可能 JDBC/ODBCでBIツールやAWSサービスと統合
データセキュリティ ローカル管理でネットワーク依存が少ない
※本ケースではCloud9上で利用
（→ IAMロールによるアクセス制御）		 IAMによるアクセス制御、データ暗号化可能
ユースケースの柔軟性 機械学習前処理、ローカルETL、データ分析
※ECSなど他サービスのログも活用可能		 データレイク分析、ログ解析、サーバーレスETL
※S3に保存・蓄積したログに対してのみ分析可能

おわりに

以上、DuckDBを用いて自動でALBアクセスログをクエリし、その結果をcsvファイルで取得する仕組みの説明を行いました。
DuckDBは敷居が低く、比較的簡単にここまでの実装ができたと思います。
今後の展望に書いたようなことを進めながら、保守作業がより簡単で漏れのないようなものにできればと思います。
ありがとうございました。

参考にさせていただいたサイト

0
0
0

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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?