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)
```


## テーブルを作成する
```sql
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する
```sql
mysql> INSERT INTO diaries (content) VALUES ("It'll be fine tomorrow."); 
mysql> INSERT INTO diaries (content) VALUES ("It'll rain tomorrow"); 
```

### 全文検索をしてみる
```sql
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/