EmbulkでApacheのアクセスログをMySQLに取り込む

Apacheのアクセスログの解析をやりたいなーとずっと思っているサーバがあるのですが、リアルタイムに解析サーバに流すのが難しい環境なのでEmbulkを試してみようと思います。

# Embulkとは

作者の古橋さんによれば

>Embulk は、リアルタイムなログ収集では常識となった fluentd のバッチ版のようなツールで、ファイルやデータベースからデータを吸い出し、別のストレージやデータベースにロードするためのコンパクトなツールです。

参照元：http://frsyuki.hatenablog.com/entry/2015/02/16/080150

また、こちらの記事を参考にさせてもらいました。
http://qiita.com/hiroysato/items/397f36c4838a0a93e352


# インストール

環境は macOS Sierra です。

EmbulkはJavaアプリケーションなのでJavaをインストールしておいてください。

```console:javaのバージョン
$ java -version
java version "1.7.0_40"
Java(TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot(TM) 64-Bit Server VM (build 24.0-b56, mixed mode)
```

## Embulkのインストール

公式サイトに書いてあるとおりに、以下のコマンドを実行します。

```console:Embulkのインストール
$ curl --create-dirs -o ~/.embulk/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
$ chmod +x ~/.embulk/bin/embulk
$ echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
$ source ~/.bashrc
```

インストール完了後、バージョンを確認します。

```console:バージョン確認
$ embulk -v
Embulk v0.8.15
```

## プラグインのインストール

Embulk には様々な Input、Output に対応したプラグインがあります。

**公式のプラグインページ**
http://www.embulk.org/plugins/



今回は Input が Apache アクセスログで、Output が MySQL なので、以下のプラグインを利用します。

**Input:** `embulk-parser-apache-custom-log`
**Output:** `embulk-output-mysql`


```console:プラグインのインストール
$ embulk gem install embulk-parser-apache-custom-log
$ embulk gem install embulk-output-mysql
```

# サンプルの実行

まずは[こちら](https://github.com/embulk/embulk#running-example)の手順に従って、Exampleを実行してみます。

適当なディレクトリを用意して

```console:ディレクトリ作成
$ mkdir embulk
$ cd embulk
```

サンプルファイルを作成すると

```console:サンプルファイルの作成
$ embulk example ./try1
2016-12-03 14:38:30.921 +0900: Embulk v0.8.15
Creating ./try1 directory...
  Creating ./try1/
  Creating ./try1/csv/
  Creating ./try1/csv/sample_01.csv.gz
  Creating ./try1/seed.yml

Run following subcommands to try embulk:

   1. embulk guess ./try1/seed.yml -o config.yml
   2. embulk preview config.yml
   3. embulk run config.yml
```

こんな感じでファイルが作成されます。

```console:
$ tree
.
├── config.yml
└── try1
    ├── csv
    │   └── sample_01.csv.gz
    └── seed.yml
```

続いて `embulk guess` を実行すると、ファイルを解析して、設定ファイルに足りていないパラメータを推測してくれるようです。

```
$ embulk guess   ./try1/seed.yml -o config.yml
  :
in:
  type: file
  path_prefix: /path/to/embulk/try1/csv/sample_
  decoders:
  - {type: gzip}
  parser:
    charset: UTF-8
    newline: CRLF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
out: {type: stdout}
Created 'config.yml' file.
```

`embulk preview` でドライランができます。

```console:ドライラン
$ embulk preview config.yml
  :
+---------+--------------+-------------------------+-------------------------+----------------------------+
| id:long | account:long |          time:timestamp |      purchase:timestamp |             comment:string |
+---------+--------------+-------------------------+-------------------------+----------------------------+
|       1 |       32,864 | 2015-01-27 19:23:49 UTC | 2015-01-27 00:00:00 UTC |                     embulk |
|       2 |       14,824 | 2015-01-27 19:01:23 UTC | 2015-01-27 00:00:00 UTC |               embulk jruby |
|       3 |       27,559 | 2015-01-28 02:20:02 UTC | 2015-01-28 00:00:00 UTC | Embulk "csv" parser plugin |
|       4 |       11,270 | 2015-01-29 11:54:36 UTC | 2015-01-29 00:00:00 UTC |                            |
+---------+--------------+-------------------------+-------------------------+----------------------------+
```

最後に実行します。

```console:実行
$ embulk run     config.yml
  :
1,32864,2015-01-27 19:23:49,20150127,embulk
2,14824,2015-01-27 19:01:23,20150127,embulk jruby
3,27559,2015-01-28 02:20:02,20150128,Embulk "csv" parser plugin
4,11270,2015-01-29 11:54:36,20150129,
  :
```

csvが解凍され、標準出力に出力されました。
これでだいたい何をすればいいかわかりましたね。

# Inputの設定

## 設定ファイル

`embulk-parser-apache-custom-log` は `embulk guess` に対応していないようなので、`config.yml` に直接設定を記述します。

```yaml:config.yml
in:
  type: file
  path_prefix: /path/to/embulk/try1/access_log/sample.access_
  decoders:
  - {type: gzip}
  parser: 
    type: apache-custom-log
    format: "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\""
out: 
  type: stdout
```

## ドライラン

これで、実行してみたところ1つ問題が、、、
`embulk-parser-apache-custom-log` はJava1.7では動作しないようです。

```console:
$ embulk preview config.yml
  :
2016-12-03 15:37:04.469 +0900 [INFO] (0001:preview): Loading files [/path/to/embulk/try1/access_log/www01.log_.access_20160106]
LoadError: load error: embulk/parser/apache-custom-log -- java.lang.UnsupportedClassVersionError: org/embulk/parser/ApacheCustomLogParserPlugin : Unsupported major.minor version 52.0
  :
```

[こちら](http://qiita.com/hiroysato/items/7b5f77c6ef752c4cd9ca)を参考に、Java8をインストールし、JAVA_HOMEを変更します。

```console:JAVA_HOMEの変更
$ /usr/libexec/java_home -V
Matching Java Virtual Machines (3):
    1.8.0_111, x86_64:	"Java SE 8"	/Library/Java/JavaVirtualMachines/jdk1.8.0_111.jdk/Contents/Home
    1.8.0_73, x86_64:	"Java SE 8"	/Library/Java/JavaVirtualMachines/jdk1.8.0_73.jdk/Contents/Home
    1.7.0_40, x86_64:	"Java SE 7"	/Library/Java/JavaVirtualMachines/jdk1.7.0_40.jdk/Contents/Home

/Library/Java/JavaVirtualMachines/jdk1.8.0_111.jdk/Contents/Home

$ JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0_111.jdk/Contents/Home

$ java -version
java version "1.8.0_111"
Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)
```

再度、実行してみます。

```console:
$ embulk preview config.yml
  :
2016-12-03 15:49:59.571 +0900 [INFO] (0001:preview): LogFormat : %h %l %u %t "%r" %>s %b "%{Referer}i" "%{User-Agent}i"
2016-12-03 15:49:59.571 +0900 [INFO] (0001:preview): RegExp    : (.*) ([^\s]*) (.*) \[([^\]]+)\] "(.*)" ([1-9]\d{2}) (-?\d+|-) "(.*)" "(.*)"
2016-12-03 15:49:59.572 +0900 [INFO] (0001:preview): replacement : 9
+--------------------+------------------------+---------------------+-------------------------+----------------------------------+----------------------+---------------------+-------------------------------+------------------------------------+
| remote-host:string | remote-log-name:string | request-user:string |  request-time:timestamp |              request-line:string | response-status:long | response-bytes:long | request-header-Referer:string |   request-header-User-Agent:string |
+--------------------+------------------------+---------------------+-------------------------+----------------------------------+----------------------+---------------------+-------------------------------+------------------------------------+
|    XXX.XXX.XXX.XXX |                        |                     | 2016-01-05 15:00:04 UTC | GET /server-status?auto HTTP/1.1 |                  200 |                 701 |                               |   munin/2.0.21 (libwww-perl/5.833) |
```

Inputは問題なさそうです。


# Outputの設定

つづいてOutputの設定をします。

## データベースの作成

データ投入先のMySQLデータベースを作成します。

```console:MySQLのバージョン
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.16, for osx10.12 (x86_64) using  EditLine wrapper
```

```console:データベースを作成
mysql> CREATE DATABASE embulk DEFAULT CHARACTER SET utf8;
mysql> CREATE USER embulk_adm@localhost IDENTIFIED BY 'password';
mysql> GRANT ALL ON embulk.* TO embulk_adm@localhost;
```

テーブルは作成しません。テーブルが存在しない場合、プラグインが作成してくれます。

## 設定ファイル

設定ファイルの `out` の部分をMySQLに書き変えます。

```yaml:config.yml
in:
  type: file
  path_prefix: /path/to/embulk/try1/access_log/sample.access_
  decoders:
  - {type: gzip}
  parser: 
    type: apache-custom-log
    format: "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\""
out: 
  type: mysql
  host: localhost
  user: embulk_adm
  password: embulk
  database: embulk
  table: apache_access_log
  mode: insert_direct
```

# データ

データは下記のように配置しました。

```console:
$ tree
.
├── config.yml
└── try1
    ├── access_log
    │   └── sample.access_20160106.gz
    └── seed.yml
```

# 実行

それでは実行してみます。

```console:実行（1回目）
$ embulk run config.yml
  :
2016-12-03 17:44:32.439 +0900 [ERROR] (0001:transaction): Operation failed (1146:42S02)
org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'performance_schema.session_variables' doesn't exist
  :
```

エラー！
`performance_schema` を見ているようですね。
root ユーザーでアクセスするように変更します。（いろいろいじっていたら権限付与がエラーになってしまったので）

```yaml:config.yml
  :
out: 
  type: mysql
  host: localhost
  user: root
  password: password
  database: embulk
  table: apache_access_log
  mode: insert_direct
```

これで行けるかな？

```console:実行（2回目）
$ embulk run config.yml
  :
org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'performance_schema.session_variables' doesn't exist
  :
```

またエラー。。。
確認したら session_variables テーブルが存在しませんでした。

どうも以前に MySQL5.7 をインストールしていたのを忘れていて、再インストールしたのがよくなかったようです。
下記のコマンドでMySQLをアップグレードし、再起動します。

```console:MySQLをアップグレード
$ mysql_upgrade -u root -p --force
$ mysql.server restart
```

今度こそ。

```console:実行（3回目）
$ embulk run config.yml
  :
2016-12-03 17:59:03.254 +0900 [INFO] (main): Committed.
  :
```

無事完了したようです。

# 結果確認

```console:テーブルが作成されています
mysql> show tables;
+-------------------+
| Tables_in_embulk  |
+-------------------+
| apache_access_log |
+-------------------+
1 row in set (0.00 sec)
```

```console:テーブル定義
mysql> desc apache_access_log;
+---------------------------+------------+------+-----+-------------------+-----------------------------+
| Field                     | Type       | Null | Key | Default           | Extra                       |
+---------------------------+------------+------+-----+-------------------+-----------------------------+
| remote-host               | text       | YES  |     | NULL              |                             |
| remote-log-name           | text       | YES  |     | NULL              |                             |
| request-user              | text       | YES  |     | NULL              |                             |
| request-time              | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| request-line              | text       | YES  |     | NULL              |                             |
| response-status           | bigint(20) | YES  |     | NULL              |                             |
| response-bytes            | bigint(20) | YES  |     | NULL              |                             |
| request-header-Referer    | text       | YES  |     | NULL              |                             |
| request-header-User-Agent | text       | YES  |     | NULL              |                             |
+---------------------------+------------+------+-----+-------------------+-----------------------------+
9 rows in set (0.00 sec)
```

```console:件数
mysql> select count(*) from apache_access_log;
+----------+
| count(*) |
+----------+
|    29416 |
+----------+
1 row in set (0.01 sec)
```

```console:データ
mysql> select * from apache_access_log limit 1;
+--------------+-----------------+--------------+---------------------+----------------------------------+-----------------+----------------+------------------------+----------------------------------+
| remote-host  | remote-log-name | request-user | request-time        | request-line                     | response-status | response-bytes | request-header-Referer | request-header-User-Agent        |
+--------------+-----------------+--------------+---------------------+----------------------------------+-----------------+----------------+------------------------+----------------------------------+
| XXX.XX.XX.XX | NULL            | NULL         | 2016-01-06 00:00:04 | GET /server-status?auto HTTP/1.1 |             200 |            701 | NULL                   | munin/2.0.21 (libwww-perl/5.833) |
+--------------+-----------------+--------------+---------------------+----------------------------------+-----------------+----------------+------------------------+----------------------------------+
1 row in set (0.00 sec)
```

今回はここまで。