Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

Elasticsearchを使う上で標準のDSLであるストラクチャードクエリーを書くことは避けて通れません。しかしこのJSON形式のDSLはもともと機械が理解するためのもの...人間が書くには辛すぎます。ネストが増えるたびに上に行ったり下に行ったり、目を凝らしてカッコの対応を確認したり...。使い慣れたSQLで問い合わせができたらどんなに楽だろうと思ったあなた!そんな便利なプラグインが存在します。

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

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away