2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

AWS Elasticsearch でも SQL でリクエストを投げる

Last updated at Posted at 2019-07-18

👉 最近 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 でクエリを投げたいと思います:

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

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?