👉 最近 AWS の Elasticsearch でも SQL を投げられるようになりました: https://aws.amazon.com/jp/about-aws/whats-new/2019/05/amazon-elasticsearch-service-sql-support/
バージョン 6.5 以降でないと使えませんが、いままで Query DSL の入れ子が難しすぎて悩んでいたので朗報です。
ただし、AWS の SQL 用のエンドポイントは、公式のリファレンスページのエンドポイントと異なるようなので、使い方を交えて紹介したいと思います。
(確認に使った環境は AWS Elasticsearch 6.7 です)
クエリを投げる: _sql
AWS でのエンドポイント: /_opendistro/_sql
SQL でドキュメントのクエリを行う API です。
(👉 https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-overview.html)
例として以下のような SQL でクエリを投げたいと思います:
SELECT
_id,
description
FROM
my_index
WHERE
posted_at BETWEEN '2019-06-01T00:00:00+09:00' AND '2019-07-01T00:00:00+09:00'
;
GET
or POST
でエンドポイント /_opendistro/_sql
にリクエストを投げます:
GET /_opendistro/_sql
:
{
"query": "SELECT _id, description FROM my_index WHERE posted_at BETWEEN '2019-06-01T00:00:00+09:00' AND '2019-07-01T00:00:00+09:00'"
}
(※ SQL の末尾に ;
があると Invalid SQL query
エラーになります)
Query DSL のときと同じように該当したドキュメントの結果が返りました:
{
"took" : 26,
"timed_out" : false,
"_shards" : {
"total" : 6,
"successful" : 6,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 11893,
"max_score" : 0.0,
"hits" : [
// 略...
]
}
}
使い勝手的には通常のクエリより楽で、慣れ親しんだ RDB のように SQL でクエリを投げられるのはものすごい便利ですね。
変換した結果を得る (_sql/translate
)
AWS でのエンドポイント: /_opendistro/_sql/_explain
SQL から Query DSL に変換した結果を返してくれる API です。
(👉 https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html)
これは、Query DSL を使いたいがうまく書けない場合や、どんな風に解釈されるか確認したいとき便利です。
取得とおなじように以下のような SQL を変換したいと思います:
SELECT
_id,
description
FROM
my_index
WHERE
posted_at BETWEEN '2019-06-01T00:00:00+09:00' AND '2019-07-01T00:00:00+09:00'
;
同様に GET
or POST
でエンドポイント /_opendistro/_sql/_explain
にリクエストを投げます:
GET /_opendistro/_sql/_explain
:
{
"query": "SELECT _id, description FROM my_index WHERE posted_at BETWEEN '2019-06-01T00:00:00+09:00' AND '2019-07-01T00:00:00+09:00'"
}
変換された結果が返ります:
{
"from": 0,
"size": 200,
"query": {
"bool": {
"filter": [
{
"bool": {
"must": [
{
"range": {
"posted_at": {
"from": "2019-06-01T00:00:00+09:00",
"to": "2019-07-01T00:00:00+09:00",
"include_lower": true,
"include_upper": true,
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": {
"includes": [
"_id",
"description"
],
"excludes": []
}
}
若干 bool
気味っぽいですが、いい感じに変換してくれましたね。
しかしながら、そのフィールドが存在するかや、日付のフォーマット等の妥当性の検証は行われず、単純に SQL から変換した結果を返すだけのため、実際にクエリとして有効かは投げてみないとわかりません。
いままで複雑な条件のクエリで、鬼のように入れ子になった Query DSL を悩みながら書いてたこともありましたが、MySQL などと同じように SQL ですっきりと書けるようになったのは嬉しいですね。