MySQL(MariaDB)のデータをElasticsearchで全文検索

とはいっても直接MySQLのデータを全文検索を使うのではなく、MySQLのデータをElasticsearchにインポートしてElasticsearch側で全文検索します。CentOS7です。

# 最初に
* Elasticsearchは各種プラグインなどマイナーバージョンの組み合わせでも**かなりシビア**です。トラブル回避のためにも、すべてのバージョンは**完全に**合わせましょう。
* この投稿も将来的に動かない可能性もあります。その際は、**最新版のドキュメント**を。私は適当にググってバージョン違いでハマったので…。

# 大きな流れ
1. 準備
2. Elasticsearch 1.7.1 のインストール(2015/08/19 最新版)
3. Elasticsearchのプラグインのインストールと設定
4. MariaDBでデータの準備
5. jdbc連携
6. MariaDBでのデータ変更

※今回は、クラスタは組みません。全部1台でやります。

## 1. 準備
CentOS7です。
elasticsearchはjavaが必要なのでjavaをインストール。ちなみに以下でjdkは1.8が入る。

```
# yum install java curl wget unzip
```

## 2. Elasticsearch 1.7.1 のインストール(2015/08/19 最新版)

リポジトリを追加。バージョン指定に注意。

```
# vi /etc/yum.repos.d/elasticsearch.repo
-----
[elasticsearch-1.7]
name=Elasticsearch repository for 1.7.x packages
baseurl=http://packages.elastic.co/elasticsearch/1.7/centos
gpgcheck=1
gpgkey=http://packages.elastic.co/GPG-KEY-elasticsearch
enabled=1
-----
# yum install elasticsearch
```

ちなみに、elasticsearchは **/usr/share/elasticsearch** に入る。


## 3. Elasticsearchのプラグインのインストールと設定

### 各種プラグイン

※kuromojiのインストールすべきバージョンはここで確認。バージョンは**シビアに合わせる**こと。
https://github.com/elastic/elasticsearch-analysis-kuromoji

```
# /usr/share/elasticsearch/bin/plugin -install elasticsearch/elasticsearch-analysis-kuromoji/2.7.0
# /usr/share/elasticsearch/bin/plugin -install mobz/elasticsearch-head
```

### Elasticsearchの起動と自動起動設定

```
# systemctl start elasticsearch.service
# systemctl enable elasticsearch.service
# systemctl daemon-reload
```

### Elasticsearchの動作確認

```
# curl http://localhost:9200
```

### Webブラウザで外部からアクセスできることを確認
(※IPは192.168.0.33で、selinuxとかfirewalldとか注意)
http://192.168.0.33:9200/_plugin/head/ 

### Elasticsearchのプラグインの確認

```
# curl -XGET 'http://localhost:9200/_nodes?plugin=true&pretty'
   :
      "plugins" : [ {
        "name" : "analysis-kuromoji",
        "version" : "2.7.0",
        "description" : "Kuromoji analysis support",
        "jvm" : true,
        "site" : false
      }, {
        "name" : "head",
        "version" : "NA",
        "description" : "No description found.",
        "url" : "/_plugin/head/",
        "jvm" : false,
        "site" : true
      } ]
  :
```

### Elasticsearchのログを確認

エラーとか出てませんように…。

```
# less /var/log/elasticsearch/elasticsearch.log
```

### kuromoji をデフォルトのトークナイザとして利用しちゃう設定

```
# vi /etc/elasticsearch/elasticsearch.yml
-----
index.analysis.analyzer.default.type: custom
index.analysis.analyzer.default.tokenizer: kuromoji_tokenizer
-----
# systemctl restart elasticsearch.service
```

## 4. MariaDBでデータの準備

### MariaDBとの連携
さて、ここまでで準備が整いました。ようやくMariaDB(MySQL)との連携です。と、その前に元データを用意しなくてはなりません。サンプルデータがある人は次の項目は不要です。select文だけ準備しておいてください。

### データの準備
全国の郵便番号のデータを使ってみます。12万件くらいあります。というわけで、書いてみました。
<a href="http://qiita.com/kesuzuki/items/6d6c89a876d7b8ea820c">全国の郵便番号データを最速でMySQL(MariaDB)に入れてみる</a>

## jdbc連携の前に

https://github.com/jprante/elasticsearch-jdbc
を参考に、必要なバージョンをダウンロードするのですが、これがelasticsearch本体と**シビアに合わせないとダメ**で、ハマりました。
かつてはriverプラグインとして提供されていたんだと思いますが、これがまたハマりどころ。ググると情報が錯綜します。
このjdbc連携はプラグインではなく、単独のツールとして提供されているようです。

## 5. jdbc連携

### elasticsearch 1.7.1用のjdbc連携ツールのダウンロード
2015/8/19現在は↓でwget。

```
# wget http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/1.7.1.0/elasticsearch-jdbc-1.7.1.0-dist.zip
# unzip elasticsearch-jdbc-1.7.1.0-dist.zip
# cd elasticsearch-jdbc-1.7.1.0/
# ls lib
```

lsで、mysql-connector-java-5.1.33.jar が入っていることが確認できるかと。
なので、別途ダウンロードする必要はありません。

### jdbc連携スクリプト(syncスクリプト)の作成

```
# cd bin/
# pwd 
/root/elasticsearch-jdbc-1.7.1.0/bin
# ls
```

何やら色々なスクリプトがありますが、一番シンプルだけどちょっと凶悪そうな「mysql-delete-document.sh」をベースに使います。

```
# cp mysql-delete-document.sh mysql-oreore.sh
# vi mysql-oreore.sh
-----
    "jdbc" : {
        "url" : "jdbc:mysql://localhost:3306/sampledb",
        "user" : "root",
        "password" : "",
        "sql" :  "select id as _id, jiscode, zipcode_old, zipcode, pref_kana, city_kana, street_kana, pref, city, street from zipcode"
    }
-----
```

mysqlの接続設定とsqlを編集します。すると、jdbcのところは、上のようになりました。
このsqlは<a href="http://qiita.com/kesuzuki/items/6d6c89a876d7b8ea820c">全国の郵便番号データを最速でMySQL(MariaDB)に入れてみる</a>から来てます。
重要なところは、select文の『id as **_id**』でユニークなidとして**_id**を指定しているところです。

### syncスクリプトの実行
さて、準備も整ったところで、mysql → elasticsearch のデータインポートを実行します。↓のsyncスクリプト実行してちょっと待ちます。

```
# ./mysql-oreore.sh
```

### Elasticsearchで検索実行 

```
# curl -XGET 'http://localhost:9200/jdbc/_search?pretty=true' -d '
{
  "query": {
    "match": {
      "street": "鈴木"
    }
  }
}
'
```

検索結果は、3件でてきます。

```
 :
  "hits" : {
    "total" : 3,
    "max_score" : 10.423999,
    "hits" : [ {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "26915",
      "_score" : 10.423999,
      "_source":{"jiscode":"08443","zipcode_old":"30003","zipcode":"3000334","pref_kana":"イバラキケン","city_kana":"イナシキグンアミマチ","street_kana":"スズキ","pref":"茨城県","city":"稲敷郡阿見町","street":"鈴木"}
    }, {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "41720",
      "_score" : 6.5149994,
      "_source":{"jiscode":"14131","zipcode_old":"210  ","zipcode":"2100801","pref_kana":"カナガワケン","city_kana":"カワサキシカワサキク","street_kana":"スズキチョウ","pref":"神奈川県","city":"川崎市川崎区","street":"鈴木町"}
    }, {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "40372",
      "_score" : 6.514974,
      "_source":{"jiscode":"13211","zipcode_old":"187  ","zipcode":"1870011","pref_kana":"トウキョウト","city_kana":"コダイラシ","street_kana":"スズキチョウ","pref":"東京都","city":"小平市","street":"鈴木町"}
    } ]
  }
 :
```

無事検索できましたね。素晴らしい。

## 6. MariaDBでのデータ変更
さて、郵便番号は1日何回も変わるわけではありませんが、実は結構変わったりします。
データが変更された場合はどうなるのでしょうか？

### MariaDB側のデータの変更

update文で、変更してみます。

```lang:mysql
# mysql sampledb
MariaDB [sampledb]> select * from zipcode where id = 26915 \G
MariaDB [sampledb]> update zipcode set street = '鈴木さん' where id = 26915;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [sampledb]> \q
```

もう一度さっきのコマンドでクエリーを確認してみますが、当然ですが同じです。
そこで、もう一度syncプログラムを実行します。

```
# ./mysql-oreore.sh
```

もう一度検索。

```
# curl -XGET 'http://localhost:9200/jdbc/_search?pretty=true' -d '
{
  "query": {
    "match": {
      "street": "鈴木"
    }
  }
}
'
```

すると、、、

```
{
 :
  "hits" : {
    "total" : 3,
    "max_score" : 6.5149994,
    "hits" : [ {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "26915",
      "_score" : 6.5149994,
      "_source":{"jiscode":"08443","zipcode_old":"30003","zipcode":"3000334","pref_kana":"イバラキケン","city_kana":"イナシキグンアミマチ","street_kana":"スズキ","pref":"茨城県","city":"稲敷郡阿見町","street":"鈴木さん"}
    }, {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "41720",
      "_score" : 6.5149994,
      "_source":{"jiscode":"14131","zipcode_old":"210  ","zipcode":"2100801","pref_kana":"カナガワケン","city_kana":"カワサキシカワサキク","street_kana":"スズキチョウ","pref":"神奈川県","city":"川崎市川崎区","street":"鈴木町"}
    }, {
      "_index" : "jdbc",
      "_type" : "jdbc",
      "_id" : "40372",
      "_score" : 6.514974,
      "_source":{"jiscode":"13211","zipcode_old":"187  ","zipcode":"1870011","pref_kana":"トウキョウト","city_kana":"コダイラシ","street_kana":"スズキチョウ","pref":"東京都","city":"小平市","street":"鈴木町"}
    } ]
  }
 :
```

おお、最初のデータが『鈴木』から『鈴木さん』に変更が反映された！

**『_id』**が重要で、これを指定しておかないと勝手に_idがふられて、jdbcのsyncスクリプトを動かすたびに追加されて、重複データができてしまいます。

## 7.まとめ

一番シンプルなやり方で、動くまでをやってみました。

複雑なテーブル構成であってもselect文をいじれば、色々と検索できて、crontabでsyncスクリプトを定期的に動かせば、あまり変更のないデータで数万件くらいなら、もうこれでOKな感じ。

実際には、Elasticsearch側でスキーマ設定をしたり、クラスタ組んだりすることが必要です。

また、binディレクトリにあるschedule関連のスクリプトとか https://github.com/jprante/elasticsearch-jdbc を見ると、もっと効率的に動かせそう。※今回のは全件取得しているので、潔いですが、若干重い。

Elasticsearchで検索してidを拾って、それをmysql側でINで絞り込むとかは、たぶん止めた方がいいです。検索条件に関わらない表示に必要なデータも含めて、すべてElasticsearch側へ持ってきて、Elasticsearchの検索結果だけで画面を作った方がいいです。ただし、タイムラグにシビアな要件ではタイムラグに気を付けましょう。

## おまけ 全部やり直す場合は、インデックスを削除する

```
# curl -XDELETE 'http://localhost:9200/jdbc'
```