はじめに

こんにちは。SREの高場です。

フォースタートアップス株式会社のプロダクトは、一部を除きAWSで稼働しています。

外部からのアクセスはELB（Elastic Load Balancing）で処理していますが、先日アクセスログを集計する必要が生じました。

ELBログの集計といえばAWS Athenaですが、他にも方法はあります。 aws.amazon.com

今回は、様々な形式のデータをスマートに表示できるDuckDB + Grafanaの組み合わせでELBログの集計をしてみたいと思います。

DuckDBとは

DuckDBは、データ分析に特化した列志向データベースで、SQL標準に準拠しています。

ローカルで軽量に動作するDBという点では比較対象としてSQLiteが挙げられますが、SQLite等のDBに比べてデータ分析等の用途に向いているという特徴があります。

個人的にはS3やローカルのファイルを直接読み込んでテーブル化できるところが使いやすいです。またGzipファイルも読み込み可能で、非常に高機能です。

duckdb.org

Grafanaとは

データの分析や可視化に特化したオープンソースのダッシュボードツールです。

様々な形式のビジュアライゼーションに対応しており、データソースの豊富さとプラグインによる拡張性の高さが特徴です。

Grafanaを選んだ理由はDuckDBのプラグインが存在すること、AWSのサービスが存在することです。

AWSのマネージドサービスが存在することで、運用面での選択肢が増えるのは純粋に嬉しい点です。

grafana.com

aws.amazon.com

きっかけ

この記事を書こうと思ったきっかけは、弊社プロダクトのUserAgentの調査でした。

弊社プロダクトではECS Fargateを使用しており、ELBのアクセスログをS3に保存しています。

最近、1年分のアクセスログを調査しUserAgentごとに集計するという機会があったのですが、700万件のログを正確に調査する必要があり、頭を悩ませました。

当初CloudWatch Insightsで分析を行っていたのですが、コストの観点からローカルにログファイルをダウンロードすることにしました。

この時は自作ツールを作って対応したのですが、そもそもELBのログを分析する際にもっと楽な方法がないかと思ったことがきっかけです。

構成

今回は本格的に導入する前の検証段階のため、ローカル環境にDuckDB + Grafanaの環境を構築していきます。構成は下記のシンプルなものです。

DuckDBを試してみる

今回の構成ではGrafanaのDuckDBプラグインを使うことになります。

そこでまずは本家のDuckDBを触ってみて、使い勝手を見てみます。

ローカル「duckdb」ディレクトリを作成し、brewでDuckdbをインストールします。

ログファイルを配置したディレクトリでコンソールからDuckdbに入ります。

read_csv

まず検証として、ALBログをダウンロードしてローカルで読み込ませてみます。

ログファイルを確認して、カラムを追加しました。

SELECT * FROM read_csv(
   '01/*.gz',
   columns={
       'timestamp': 'VARCHAR',
       'client_ip': 'VARCHAR',
       'client_port': 'VARCHAR',
       'user_id': 'VARCHAR',
       'request_time': 'VARCHAR',
       'path': 'VARCHAR',
       'status': 'INTEGER',
       'bytes_sent': 'BIGINT',
       'user_agent': 'VARCHAR',
   },
   union_by_name=true,
   delim=' ',
   escape='"',
   strict_mode=false,
   ignore_errors=true,
   null_padding=true,
   max_line_size=10000000
);

これはうまくいきませんでした。ELBのログはところどころで不規則なパターンが出現するため、空の項目であってもカラムを設定しないとうまく解釈できないようです。

read_csv_autoを使ってみる

自動でカラムを検出してくれるというread_csv_auto関数を使ってみました。

COPY(SELECT * FROM read_csv_auto(
      'csv_path/*.log.gz',
      union_by_name=true,
      null_padding=true,
      quote='"',
      strict_mode=false,
      ignore_errors=true, delim=' ',
      max_line_size=10000000, escape='"'
  )) TO 'output.csv' (FORMAT CSV, HEADER true);

こちらも、ある程度は自動で検出してくれますが、上記の方法でこれらをすべてカバーするのは難しそうです。

Athenaのパーティションスキームを参考にする

AWSの用意したアクセスログ形式のドキュメントがありました。

参考：https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-alb-access-logs-table-partition-projection.html

こちらを参考にして、きれいなテーブル形式にすることができました。

検証には、こちらの記事を大いに参考にさせていただきました。

road288.hatenablog.com

Grafanaプラグインで表示する

コンソールではクエリの編集や複数ページにまたがる結果の確認がしづらいため、GrafanaからGUIで編集できるようにします。

Grafanaは多様なビジュアライゼーションに対応していますが、今回は単純なテーブル形式で表示します。

Grafanaをローカルで動かしてみる

下記のようなファイルを用意します。

ポイントは下記です。

ローカルのログディレクトリをマウントする
GF_DEFAULT_APP_MODE=developmentを指定する
grafana.iniに下記を指定する
- allow_loading_unsigned_plugins = motherduck-duckdb-datasource

これらを踏まえて、Dockerfileを作成します。

FROM grafana/grafana-enterprise:main-ubuntu

COPY grafana.ini /etc/grafana/grafana.ini

EXPOSE 3000

services:
 grafana_duckdb:
   build: ./
   container_name: grafana_duckdb
   user: root
   ports:
     - "3010:3000"
   volumes:
     - grafana_data:/var/lib/grafana
     - ./grafana.ini:/etc/grafana/grafana.ini
     - ./plugins/:/var/lib/grafana/plugins/
     - ./logs/:/var/lib/grafana/csv/
   environment:
     - GF_DEFAULT_APP_MODE=development

volumes:
 grafana_data:

上記の構成にあたっては下記の記事を大いに参考にさせていただきました。

heraction.hatenablog.com

今回、大量のログを分析するため一度ローカルにダウンロードしてから検証する必要があります。

そのため、Grafanaのコンテナからローカルのログディレクトリを参照できるようにマウントします。

実際のクエリは下記のようになります。

CREATE TABLE alb_log_2025_01 AS SELECT * FROM read_csv_auto(
   '/var/lib/grafana/csv/01/*.gz',
…
);

Grafanaの初期セットアップ

GrafanaのGUIを立ち上げていきます。

初期ログインは下記のような画面で、固定のID/Passwordです。

DataSourceを追加します。

Grafanaから読み込めるDataSourceは多岐に渡りますが、下記のようなSaaSに対応しています。

Datadog
Salesforce

Duckdb-Datasourceを選択します。

grafana.iniに設定を追加していれば、DataSource一覧にDuckdb-Datasourceが表示されるようになっています。

ダッシュボードを作成します。

テーブルを作成します。

クエリ編集画面で、DuckDBのクエリを入力します。

UA別にカウントする

作成したテーブルに対してSELECTします。

下記のような結果になりました。

検証のため一部のログのみを読み込ませていますが、しっかりUserAgentごとに分類できていることがわかります。

今後検討したいこと

今回はローカルでの検証でしたが、本格的に運用する場合には色々と検討すべき点があります。

例えばECSでGrafanaをホスティングするか、前述のAWSマネージドGrafanaを使うという選択肢があります。

またサーバレス構成にするなら、Lambda LayerでDuckDBを利用し、S3からログファイルを直接読み込んでGrafanaで表示させる構成も試してみたいです。

実際に運用に持っていくには、これらの構成とAthenaの場合でコストや運用面を比較することになりそうです。

これらの検討は今後の課題にしたいとおもいます。

まとめ

DuckDB + Grafanaで、ELBログをSQLライクに集計することができました。

一度ローカルに環境を作ってしまえば、ちょっとしたログの加工や集計が簡単にできるようになります。

既存の環境にETL基盤がなかったり、作成するまでもない場合には、このような方法で確認することでログ集計の手間を軽減できそうです。

ここまでお読みいただきありがとうございました！

for Startups Tech Blog

フォースタ社員のエンジニアたちが思い思いのことを書き綴ります。

DuckDB + GrafanaでELBのログをSQLライクに集計する

目次