Go to Qiita Advent Calendar 2024 Top
LoginSignup
63
74

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@kesuzuki(Kenichi Suzuki)

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

Posted at

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
63
74

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?