モチベーション
- 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 へ登録される
参考リンク
Embulk #Using variable
Embulk についてまとめてみた
Embulk でプラグインのインストール方法
