Amazon Elasticsearch Serviceの検索でSQLが使えるようになったので使ってみました

開発しているサービスの一部でElasticearch使っているのですが、ElasticsearchのクエリDSLって少し触ってないだけで忘れてしまいます。

昨日もGroupByに当たる、Aggregationを複数フィールドでやるのどうやるんだっけと、素人みたいなことで悩んでググっていました。

こういうの調べるたびに、SQL打てたらなーと思っていました。実はSQLでElasticsearchのクエリを実行する機能は、(※) ~~Elastic Stackの有償オプションにはあります。~~

※この部分認識間違いありまして、以下のコメントいただいたので引用をもって、訂正させていただきます。ありがとうございました。

Elasticが提供してるElasticsearch SQLの実行エンジン部分は無償の範囲であるベーシックに含まれてます。 https://www.elastic.co/jp/subscriptions 有償なのはJDBCやODBCになります。　　

Elasticsearch SQL | ElasticsearchインデックスをSQLでクエリする

ただ、今回はAWSがOpen Distro for Elasticsearchという形で開発していたSQLオプションがAmazon Elasticsearch Serviceに組み込まれたという話です。

Open Distro for Elasticsearch
リリース情報
早速開発機をアップグレード
使えるSQL句
SQLクエリの実行方法
STATSもできます
Explain機能
Hint句
CSVで出力
ネストのAggregationをSQLのGroupByに書き換えてみる
まとめ
関連

Open Distro for Elasticsearch

AWSがOpen Distro for Elasticsearchというのをいくらか前に発表しました。このディストリビューションでは、データの検索にSQLが利用できます。

Open Distro for ElasticsearchはDockernizeされており、誰でもお手軽に触ってみることできるので、私も少し触っていたりします。

hub.docker.com

公式のページ見ると、このOpen Distro for Elasticsearchにて提供される拡張として以下が紹介されています。

Security
Alerting
SQL
Performance Analyzer

特にSecurityとSQLは心惹かれるところがあったので、Amazon Elasticsearch Serviceに組み込まれるのを心待ちにしていました。

昨日のこのリリース情報から待望のSQL機能がリリースされたことを、同僚から聞いたので早速さわってみました。

aws.amazon.com

リリース情報

リリース情報にも確かに Adds SQL support と書いてあります。

f:id:yomon8:20190529142642p:plain

https://docs.aws.amazon.com/elasticsearch-service/latest/developerguide/release-notes.html

早速開発機をアップグレード

バージョン6.3だった開発機を6.5にアップグレードしました。データ量は大したこと無いのですが、30分くらいでした。

ソフトウェアのバージョンもしっかり R20190418 になっています。

f:id:yomon8:20190529143752p:plain

使えるSQL句

ここ見るのが一番早いです。

github.com

が、一応コピペだけしておきます。

SQL Select
SQL Delete
SQL Where
SQL Order By
SQL Group By
SQL Having
SQL Inner Join
SQL Left Join
SQL Show
SQL Describe
SQL AND & OR
SQL Like
SQL COUNT distinct
SQL In
SQL Between
SQL Aliases
SQL Not Null
SQL(ES) Date
SQL avg()
SQL count()
SQL max()
SQL min()
SQL sum()
SQL Nulls
SQL isnull()
SQL floor
SQL trim
SQL log
SQL log10
SQL substring
SQL round
SQL sqrt
SQL concat_ws
SQL union and minus

SQLクエリの実行方法

POSTでqueryを流し込みます。

POST elasticsearch_domain/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

とても簡単です。

AWSのドキュメントはここですが、特に詳しいこと書いてないです。現状、githubが一番のドキュメントに見えます。

https://docs.aws.amazon.com/elasticsearch-service/latest/developerguide/sql-support.html

STATSもできます

Elasticsaerchっぽいstatsとかもできますよ。

POST _opendistro/_sql
{
  "query": "SELECT STATS(age) FROM my-index"
}

Explain機能

便利とは言ってもSQLが内部でElasticsearchのクエリに変換されているとして、細かいチューニング時には実際のクエリを見たくなるものだと思います。

そういう場合に役立ちそうなのがexplain機能です。以下のように _opendistro/_sql/_explain にSQLをPOSTすると、裏で実行されるElasticsaerchのクエリの情報が返ってきます。

POST _opendistro/_sql/_explain
{
  "query": "SELECT xxxx"
}

Hint句

クエリの動作を調整するのにHint句をクエリに組み込めるようです。マニュアルには書いてる場所見つからないのです、コードの該当部分書いておきます。

現時点 v0.9.0 で使えるヒント句は以下になりそうです。

public enum HintType
{
    HASH_WITH_TERMS_FILTER,
    JOIN_LIMIT,
    USE_NESTED_LOOPS,
    NL_MULTISEARCH_SIZE,
    USE_SCROLL,
    IGNORE_UNAVAILABLE,
    DOCS_WITH_AGGREGATION,
    ROUTINGS,
    SHARD_SIZE,
    HIGHLIGHT,
    MINUS_FETCH_AND_RESULT_LIMITS,
    MINUS_USE_TERMS_OPTIMIZATION,
    COLLAPSE,
    POST_FILTER,
    JOIN_ALGORITHM_BLOCK_SIZE,
    JOIN_ALGORITHM_USE_LEGACY,
    JOIN_SCROLL_PAGE_SIZE,
    JOIN_CIRCUIT_BREAK_LIMIT,
    JOIN_BACK_OFF_RETRY_INTERVALS,
    JOIN_TIME_OUT
}

sql/HintType.java at v0.9.0.0 · opendistro-for-elasticsearch/sql · GitHub

例として以下のクエリにヒント追加してみます。

POST _opendistro/_sql/_explain
{
  "query": "SELECT count(*) FROM my-index"
}

まず普通に実行した場合のexplainの情報です。

{"from":0,"size":0,"_source":{"includes":["COUNT"],"excludes":[]},"aggregations":{"COUNT(*)":{"value_count":{"field":"_index"}}}}

ヒント句はSELECT句の後に /*! HINT */ のように入れて使うようです。

POST _opendistro/_sql/_explain
{
  "query": "SELECT /*! DOCS_WITH_AGGREGATION(10000) */ count(*) FROM my-index"
}

explainの結果からsizeが 0 → 10000 に変更されたことがわかります。

{"from":0,"size":10000,"_source":{"includes":["COUNT"],"excludes":[]},"aggregations":{"COUNT(*)":{"value_count":{"field":"_index"}}}}

CSVで出力

これもSolrであって、Elasticsearchでもあれば嬉しいと思っていた機能なので重宝しそうです。

?format=csv をURLパラメータで指定するだけです。

POST _opendistro/_sql?format=csv
{
  "query": "select xxxx"
}

ネストのAggregationをSQLのGroupByに書き換えてみる

ちょっと単純化していますが、昨日実行していた、もともとのElasticsearchクエリです。

aggsをネストして複雑だし、インデントわからなくなったり、構造入れ子で複雑だったり。私のレベルでは調べながらでないとクエリ書けません・・・

GET my-index/_search
{
  "size": 0,
  "sort": [],
  "aggs": {
    "org_agg": {
      "terms": {
          "field": "org",
          "size": 10000
      },
      "aggs": {
        "app_agg":{
          "terms": {
              "field": "application",
              "size": 10000
          },
          "aggs": {
            "name_agg":{
              "terms": {
                  "field": "name",
                  "size": 10000
              },
              "aggs": {
                "sum_agg":{
                  "sum":{
                    "field": "value"
                  }
                }
              }
            }
          }
        }

      }
    }
  }
}

SQLだとこんなに簡単に書けます。CSVで出力までできてしまいます！

POST _opendistro/_sql?format=csv
{
  "query": "SELECT org,application,name,SUM(value) FROM my-index GROUP BY (terms('field'='org','size'=10000,'alias'='org'),terms('field'='application','size'=10000,'alias'='app'),terms('field'='name','size'=10000,'alias'='name')) LIMIT 10000"
}

実は最初Group Byの指定で group by org,application,name のように指定していたのですが、2個め移行のフィールドのsizeがデフォルト10になってしまい、全レコードの集計が取れませんでした。

これをどう増やすか苦戦したのですが、テストコードに答えが載っていました。

この辺りとか。

sql/AggregationTest.java at v0.9.0.0 · opendistro-for-elasticsearch/sql · GitHub