MySQL
mariadb
Elasticsearch
全文検索
centos7

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

More than 3 years have passed since last update.

とはいっても直接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万件くらいあります。というわけで、書いてみました。

全国の郵便番号データを最速でMySQL(MariaDB)に入れてみる


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は全国の郵便番号データを最速でMySQL(MariaDB)に入れてみるから来てます。

重要なところは、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文で、変更してみます。


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'