Groonga Meetup 2014/04/29

2014/04/29 に開催された Groonga Meetup の内容
（一部独習あり）

はじめに

環境

• CentOS 6.5

参考

• http://groonga.org/ja/docs/
• http://mroonga.org/ja/docs/

発表資料

• http://www.slideshare.net/kenhys/groonga-meetup-20140429

Groongaをインストールする

まずはgroonga-httpdをインストール

$ sudo rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
$ sudo yum makecache
$ sudo yum -y install groonga-httpd

トークナイザーmecabをインストール

$ sudo yum install -y groonga-tokenizer-mecab

IPA辞書をインストール

$ sudo yum install -y mecab-ipadic

ノーマライザーをインストール

$ sudo yum install -y install groonga-normalizer-mysql

Groongaを使ってみる

Groongaを起動

$ sudo service groonga-httpd start

Groonga DBを作成

$ sudo mkdir /tmp/groonga-databases
$ sudo groonga -n /tmp/groonga-databases/introduction.db

Groongaのステータスを確認してみる

$ groonga /tmp/groonga-databases/introduction.db
> status
[[0,1398756630.8737,0.00139641761779785],{"alloc_count":140,"starttime":1398756628,"uptime":2,"version":"4.0.1","n_queries":0,"cache_hit_rate":0.0,"command_version":1,"default_command_version":1,"max_command_version":2}]

Groongaにテーブルを作成

> table_create --name Site --flags TABLE_HASH_KEY --key_type ShortText
[[0,1398756749.4216,0.00644159317016602],true]

SELECTしてみる

> select --table Site
[[0,1398756771.4205,0.000464677810668945],[[[0],[["_id","UInt32"],["_key","ShortText"]]]]]

カラムを作成

> column_create --table Site --name title --type ShortText
[[0,1398756827.25259,0.0061037540435791],true]

データを投入してみる

> load --table Site
[
{"_key":"http://example.org/","title":"This is test record 1!"},
{"_key":"http://example.net/","title":"test record 2."},
{"_key":"http://example.com/","title":"test test record three."},
{"_key":"http://example.net/afr","title":"test record four."},
{"_key":"http://example.org/aba","title":"test test test record five."},
{"_key":"http://example.com/rab","title":"test test test test record six."},
{"_key":"http://example.net/atv","title":"test test test record seven."},
{"_key":"http://example.org/gat","title":"test test record eight."},
{"_key":"http://example.com/vdw","title":"test test record nine."},
]
[[0,1398756868.73675,0.00151777267456055],9]

投入されたか確認

> select --table Site
[[0,1398756991.26957,9.03606414794922e-05],[[[9],[["_id","UInt32"],["_key","ShortText"],["title","ShortText"]],[1,"http://example.org/","This is test record 1!"],[2,"http://example.net/","test record 2."],[3,"http://example.com/","test test record three."],[4,"http://example.net/afr","test record four."],[5,"http://example.org/aba","test test test record five."],[6,"http://example.com/rab","test test test test record six."],[7,"http://example.net/atv","test test test record seven."],[8,"http://example.org/gat","test test record eight."],[9,"http://example.com/vdw","test test record nine."]]]]

検索してみる

> select --table Site --query _id:1
[[0,1398757017.42761,0.00126171112060547],[[[1],[["_id","UInt32"],["_key","ShortText"],["title","ShortText"]],[1,"http://example.org/","This is test record 1!"]]]]

Mroongaをインストールする

インストール

$ sudo yum install centos-release-SCL
$ sudo yum -y install mysql mysql55-mysql-server
$ sudo /etc/init.d/mysql55-mysqld start
$ sudo yum -y install mysql55-mroonga

インストールされているか確認

$ mysql -u root test
$ SHOW ENGINES;

結果

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)


これでmroongaエンジンが見れていればOK

プラグインを確認

mysql> SELECT * FROM mysql.plugin;
+---------+---------------+
| name    | dl            |
+---------+---------------+
| mroonga | ha_mroonga.so |
+---------+---------------+
1 row in set (0.00 sec)

関数確認

mysql> SELECT * FROM mysql.func;
+--------------------+-----+---------------+----------+
| name               | ret | dl            | type     |
+--------------------+-----+---------------+----------+
| last_insert_grn_id |   2 | ha_mroonga.so | function |
| mroonga_snippet    |   0 | ha_mroonga.so | function |
| mroonga_command    |   0 | ha_mroonga.so | function |
| mroonga_escape     |   0 | ha_mroonga.so | function |
+--------------------+-----+---------------+----------+
4 rows in set (0.00 sec)

テーブルを作成する

mysql> CREATE DATABASE MroongaTest;
mysql> CREATE TABLE diaries (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content VARCHAR(255),
  FULLTEXT INDEX (content)
) ENGINE = mroonga DEFAULT CHARSET utf8
;

データをINSERTする

mysql> INSERT INTO diaries (content) VALUES ("It'll be fine tomorrow."); 
mysql> INSERT INTO diaries (content) VALUES ("It'll rain tomorrow"); 

全文検索をしてみる

mysql> SELECT * FROM diaries WHERE MATCH(content) AGAINST("fine");

結果

+----+-------------------------+
| id | content |
+----+-------------------------+
| 1 | It'll be fine tomorrow. |
+----+-------------------------+
1 row in set (0.01 sec)


サンプルプログラムで使ってみる

サンプルデータをダウンロードして解凍

$ wget http://packages.groonga.org/tmp/groonga-meetup-20140429.tar.gz
$ tar zxvf groonga-meetup-20140429.tar.gz
$ cd examples/

ロードしてみる

$ cd 001-load/
$ ./load-fail.sh
[[0,1398757567.55438,0.00307273864746094],true]
[[0,1398757567.55753,0.00510597229003906],true]
[[0,1398757567.56268,0.00349569320678711],true]
[[0,1398757567.56621,0.00398659706115723],true]
[[0,1398757567.57024,0.00527501106262207],true]
[[0,1398757567.57555,0.00219011306762695],true]
[[0,1398757572.61219,0.00156879425048828],2]
$ ./load-succeed.sh
[[0,1398757587.44647,0.00412511825561523],true]
[[0,1398757587.45159,0.00542593002319336],true]
[[0,1398757587.45706,0.00334930419921875],true]
[[0,1398757587.46064,0.00371170043945312],true]
[[0,1398757587.46439,0.00915384292602539],true]
[[0,1398757587.47359,0.00220870971679688],true]
[[0,1398757592.53038,0.00335979461669922],3]

更新してみる

$ cd 002-update/
$ ./update-status.sh
[[0,1398757712.52183,0.00171470642089844],[[[3],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]],[1,"JR北海道",0],[2,"JR東日本",0],[3,"JR東海",0]]]]
[[0,1398757712.52369,0.000189065933227539],1]
[[0,1398757712.52393,0.000104427337646484],[[[3],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]],[1,"JR北海道",0],[2,"JR東日本",0],[3,"JR東海",1]]]]

検索してみる

$ cd 003-search/
$ ./search-name.sh
[[0,1398757796.71279,0.00573444366455078],[[[0],[["_key","UInt32"],["name","ShortText"]]]]]

検索できない。INDEXに追加されていないから。

$ ./add-index.sh
[[-22,1398757873.51991,0.000749111175537109,"table not found.",[["proc_table_remove","proc.c",1267]]],false]
[[0,1398757873.52077,0.0132768154144287],true]
[[0,1398757873.53409,0.019017219543457],true]

追加して

$ ./search-name.sh
[[0,1398757888.0436,0.00251364707946777],[[[2],[["_key","UInt32"],["name","ShortText"]],[2,"JR東日本"],[3,"JR東海"]]]]

正しく検索できた。

削除してみる

Key指定で削除

$ ./delete-by-key.sh
[[0,1398757988.83756,0.00121164321899414],[[[3],[["_key","UInt32"],["name","ShortText"]],[1,"JR北海道"],[2,"JR東日本"],[3,"JR東海"]]]]
[[0,1398757988.83935,0.00123763084411621],true]
[[0,1398757988.84063,0.000100374221801758],[[[2],[["_key","UInt32"],["name","ShortText"]],[1,"JR北海道"],[3,"JR東海"]]]]

内容で削除

$ ./delete-by-name.sh
[[0,1398758027.33819,0.00152397155761719],[[[2],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]],[1,"JR北海道",0],[3,"JR東海",1]]]]
[[0,1398758027.33984,0.00244832038879395],true]
[[0,1398758027.34233,6.98566436767578e-05],[[[1],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]],[3,"JR東海",1]]]]

Statusで削除

$ ./delete-by-status.sh
[[0,1398758043.58458,0.00207901000976562],[[[1],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]],[3,"JR東海",1]]]]
[[0,1398758043.5868,0.00520038604736328],true]
[[0,1398758043.59204,6.50882720947266e-05],[[[0],[["_key","UInt32"],["name","ShortText"],["status","UInt8"]]]]]

HTTP経由でテーブル作成

$ ./create-table.sh
[[-22,1398758111.06572,0.00279664993286133,"table not found.",[["proc_table_remove","proc.c",1267]]],false][[0,1398758111.07654,0.00604772567749023],true]table_create Company TABLE_HASH_KEY UInt32
$ ./register-normalizer.sh
[[0,1398758159.72772,0.000623226165771484],true]

HTTP経由でカラム追加

$ ./create-column.sh
[[-22,1398758237.59277,0.00063014030456543,"column not found.",[["proc_column_remove","proc.c",1408]]],false][[0,1398758237.59922,0.00746011734008789],true]table_create Company TABLE_HASH_KEY UInt32
column_create Company full_name COLUMN_SCALAR ShortText

POSTでデータ更新

$ ./post-data.sh
[[0,1398758285.00032,0.00106167793273926],true][[0,1398758285.00743,0.00356841087341309],true][[0,1398758285.01732,0.00328946113586426],true][[0,1398758285.02703,0.00469160079956055],true][[0,1398758285.03932,0.00430917739868164],true][[0,1398758285.05058,0.00552940368652344],true][[0,1398758285.06584,0.00384759902954102],true][[0,1398758285.07753,0.0031428337097168],true][[0,1398758285.08816,0.00513648986816406],true][[0,1398758285.10026,0.00368595123291016],true][[0,1398758285.11063,0.00301361083984375],true][[0,1398758285.12507,0.00776052474975586],165]

HTTP経由で検索

$ ./search-http.sh
[[-22,1398758329.08624,0.000628471374511719,"table not found.",[["proc_table_remove","proc.c",1267]]],false][[0,1398758329.09341,0.0181374549865723],true][[0,1398758329.12223,0.0185294151306152],true][[0,1398758329.14737,0.00194764137268066],[[[1],[["_id","UInt32"],["_key","UInt32"],["full_name","ShortText"],["hiragana","ShortText"],["katakana","ShortText"],["name","ShortText"],["romaji","ShortText"],["short_name","ShortText"],["sort","UInt32"],["status","UInt32"],["type","UInt32"]],[10,14,"京王電鉄株式会社","けいおうでんてつ","ケイオウデンテツ","京王電鉄","keioudentetsu","京王",14,0,2]]]]

Groonga管理画面

http://[HOST OR IP]:10041/