クエリを投げる: `_sql`

AWS でのエンドポイント: /_opendistro/_sql

SQL でドキュメントのクエリを行う API です。
(👉 https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-overview.html)

例として以下のような SQL でクエリを投げたいと思います:

test.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:

request

{
  "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 のときと同じように該当したドキュメントの結果が返りました:

response

{
  "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 を変換したいと思います:

test.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:

request

{
  "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'"
}

変換された結果が返ります:

response

{
  "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 ですっきりと書けるようになったのは嬉しいですね。