ElastisearchをSQLでクエリー可能にするプラグイン

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が廃れないのは本質的だからではないでしょうか。