Elasticsearchを使う上で標準のDSLであるストラクチャードクエリーを書くことは避けて通れません。しかしこのJSON形式のDSLはもともと機械が理解するためのもの...人間が書くには辛すぎます。ネストが増えるたびに上に行ったり下に行ったり、目を凝らしてカッコの対応を確認したり...。使い慣れたSQLで問い合わせができたらどんなに楽だろうと思ったあなた！そんな便利なプラグインが存在します。
elasticsearch-sqlの導入
Elasticsearchのプラグインの導入はコマンド一発なので簡単です。ESのバージョンによってバイナリが違うので注意してください。導入後はESの再起動が必要です
- Elasticsearch 1.X
./bin/plugin -u https://github.com/NLPchina/elasticsearch-sql/releases/download/1.4.7/elasticsearch-sql-1.4.7.zip --install sql
- Elasticsearch 2.0.0
./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.0.2/elasticsearch-sql-2.0.2.zip
- Elasticsearch 2.1.0
./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.0/elasticsearch-sql-2.1.0.zip
基本的な使い方
このプラグインはウェブUIからアクセスする方法と、GETメソッドのクエリストリングを使う方法があります。ウェブ画面のURLはこちら。
http://localhost:9200/_plugin/sql/
GETメソッドのクエリーストリングによる問い合わせは次のように行います。
http://localhost:9200/_sql?sql=select * from indexName limit 10
またSQLをESのDSLに変換して表示することもできます。
http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10
SQLの実例
- 簡単なSELECT文
SELECT * FROM bank WHERE age >30 AND gender = 'm'
インデックスとドキュメントは/で区切ります。インデックスのワイルドカードに*を使うことも多いと思いますが、クォートすることなくそのまま書けます。スペースが含まれている時はバッククォートで括ります。この辺りMySQLのパーサーがベースになっているそうです。
SELECT `field with space` FROM my-index-*/my-doc WHERE age >30 AND gender = 'm'
- GROUP BYを使ったSELECT文
select COUNT(*),SUM(age),MIN(age) as m, MAX(age),AVG(age)
FROM bank GROUP BY gender ORDER BY SUM(age), m DESC
- DELETE文
DELETE FROM bank WHERE age >30 AND gender = 'm'
Elasticsearchに合わせた拡張SQL
ESの能力を引き出すために、ES固有の機能を呼び出す関数が装備されています。Elasticsearchを使っている人なら「あれか」と分かるかと思います。
matchクエリー
SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3
rangeクエリーと集約の合わせ技
- ageフィールドを年齢層でグループ化して、カウント。
SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)
- date_histogramアグリゲーションを使って日次の集計
SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')
range date group by your config
SELECT online FROM online GROUP BY date_range(field='insert_time','format'='yyyy-MM-dd' ,'2014-08-18','2014-08-17','now-8d','now-7d','now-6d','now')
- ジオロケーションによる集約 しっかりESの能力を引き出せていますね！
SELECT * FROM locations WHERE GEO_BOUNDING_BOX(fieldname,100.0,1.0,101,0.0)
このほかの機能
LIMIT/OFFSETによるページング、ネストしたフィールドへのアクセス、なんと限定的ながらJOINもできます。なお、JOINはESの機能ではなくこのプラグインが計算しています。
時代はSQL 〜ポエム〜
NoSQLが叫ばれて久しい昨今ですが、データ操作の共通言語としてSQLが再度見直されています。Spark SQL, Apache Drill, PrestoなどあえてSQLを採用している例はかなりあります。関数型言語のように宣言的で集合の操作が容易なSQLは言語としては洗練されているのでしょう。実行計画から有向グラフを作り、分散環境へ問い合わせをするのが最近のアーキテクチャですね。Hiveのmap-reduceアーキテクチャの時代から一歩進みましたが、それでもSQLが廃れないのは本質的だからではないでしょうか。