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

  • 45
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

とはいっても直接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'