Nginx のアクセスログを Embulk で PostgreSQL に入れて分析する

PostgreSQL Embulk Nginx

モチベーション

  • SQL でアクセスログを集計可能にして「今週のアクセス数ランキング」的なページを作成するため
  • AWS であればマネージドサービスを使って CloudWatch -> Kinesis -> RDS でも実現可能であるが、汎用的に使える Embulk (or Fluentd) を触ってみたかったため

環境

  • Ubuntu 18.04

Nginx のログフォーマットを変更

log_format embulk '"$http_x_forwarded_for" "[$time_local]" "$request_method" "$request_uri" '
                  '$status $body_bytes_sent $request_time "$http_referer" '
                  '"$http_user_agent"';

access_log /var/log/nginx/access.log embulk;

Nginx のアクセスログを tsv 形式に変換

$ cat /var/log/nginx/access.log | nkf --url-input | perl -ne 'print join("\t", /^"(.*?)" "\[(.*?)\]" "(.*?)" "(.*?)" (.*?) (.*?) (.*?) "(.*?)" "(.*?)"/), "\n"' | grep -v '^\s*$' > access.log.csv

1. nkf --url-input

/search?q=検索 の URL にアクセスしたときにアクセスログ上には
URI エンコードされて /search?q=%E6%A4%9C%E7%B4%A2 になっていたのでデコード

2. print join("\t", /^"(.?)" "[(.?)]" "(.?)" "(.?)" (.?) (.?) (.?) "(.?)" "(.*?)"/), "\n"'

アクセスログを tsv 形式に変換

3. grep -v '^\s*\$'

2 で指定した tsv 形式に一致しないアクセスログは空白行になるので削除

Embulk の環境設定

Embulk は Java で動作するアプリケーションなので Java をインストール

$ sudo apt-get install openjdk-8-jre
$ java -version
openjdk version "1.8.0_242"
OpenJDK Runtime Environment (build 1.8.0_242-8u242-b08-0ubuntu3~18.04-b08)
OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)

Embulk をインストール

$ curl --create-dirs -o ~/.embulk/bin/embulk -L "https://dl.embulk.org/embulk-latest.jar"
$ chmod +x ~/.embulk/bin/embulk
$ echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
$ source ~/.bashrc
$ embulk -version
embulk 0.9.23

Embulk の設定ファイルを生成

seed.yml を作成
アクセスログから作成した .csv ファイルのパスを指定

in:
  type: file
  path_prefix: "./access.log.csv"
out:
  type: stdout

embulk guess コマンドで config.yml を作成

$ embulk guess seed.yml -o config.yml
in:
  type: file
  path_prefix: ./access.log.csv
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: "\t"
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 0
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: c0, type: string}
    - {name: c1, type: timestamp, format: '%d/%b/%Y:%H:%M:%S %z'}
    - {name: c2, type: string}
    - {name: c3, type: string}
    - {name: c4, type: long}
    - {name: c5, type: long}
    - {name: c6, type: double}
    - {name: c7, type: string}
    - {name: c8, type: string}
out: {type: stdout}

生成されたカラム名をアクセスログの内容に合わせて修正

in:
  type: file
  path_prefix: ./access-log.csv
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: "\t"
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 0
    allow_extra_columns: false
    allow_optional_columns: false
    default_timezone: Asia/Tokyo
    columns:
    - {name: ip, type: string}
    - {name: time, type: timestamp, format: '%d/%b/%Y:%H:%M:%S %z'}
    - {name: request_method, type: string}
    - {name: request_uri, type: string}
    - {name: status_code, type: long}
    - {name: body_bytes_sent, type: long}
    - {name: request_time, type: double}
    - {name: referer, type: string}
    - {name: user_agent, type: string}
out: {type: stdout}

embulk-output-postgresql をインストール

Embulk 本体はデータを読み込んで変換を行う基本機能のことを指すので
input, output で利用する各サービスに合わせプラグインのインストールが必要になる

$ embulk mkbundle bundle

./bundle/Gemfile が作成されるので embulk-output-postgresql を追記

source 'https://rubygems.org/'

gem 'embulk'
gem 'embulk-output-postgresql' # 追記

./bundle 配下で embulk bundle コマンドを実行してプラグインをインストール

$ embulk bundle

PostgreSQL の設定追記

設定ファイル内で環境変数を利用可能にするため config.yml を config.yml.liquid に変更

$ mv config.yml config.yml.liquid

config.yml.liquid の out: 以下を変更
PostgreSQL への接続情報は環境変数から取得しテーブル名は access_logs とする場合の例

in:
  type: file
  path_prefix: ./access-log.csv
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: "\t"
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 0
    allow_extra_columns: false
    allow_optional_columns: false
    default_timezone: Asia/Tokyo
    columns:
    - {name: ip, type: string}
    - {name: time, type: timestamp, format: '%d/%b/%Y:%H:%M:%S %z'}
    - {name: request_method, type: string}
    - {name: request_uri, type: string}
    - {name: status_code, type: long}
    - {name: body_bytes_sent, type: long}
    - {name: request_time, type: double}
    - {name: referer, type: string}
    - {name: user_agent, type: string}
out:
  type: postgresql
  host: {{ env.DB_HOST }}
  user: {{ env.DB_USER }}
  password: "{{ env.DB_PASSWORD }}"
  database: {{ env.DB_NAME }}
  table: access_logs
  mode: insert

Embulk を実行

$ embulk run -b bundle config.yml.liquid

テーブルが存在しない場合は自動で生成され、tsv 形式のアクセスログが PostgreSQL へ登録される

参考リンク