More than 5 years have passed since last update.

Elasticsearch 6.3.0 リリース記念　X-Pack SQL Accessをやってみよう

Elasticsearch

Last updated at 2018-06-14Posted at 2018-06-14

概要

Elasticsearchに検索条件を投げるとき、Elasticsearchのクエリを組み立てる必要がありました。
このクエリを（みんなが書き慣れてるだろう）SQLで書けないか、というような位置づけなんだろうと思いますが、
SQLをクエリとして発行したり、Elasticsearchのクエリに変換できる機能が6.3.0でX-Pack）に入りました。

それを試してみます。

環境

product	version
Elasticsearch	6.3.0
Kibana	6.3.0

Dockerで起動しますと、X-Pack(Basic)で使えるモジュールが入っているので、それを使うこととします。
起動時のログでsqlのモジュールがロードされているのが分かります。

テストデータ

日経225

数値やMAXなど集計関数を試す用。　stockというindexに入れます。

項目	フィールド
始値	start_value
終値	end_value
安値	low_value
高値	high_value
日付	timestamp

日経225のデータを突っ込んでます。

テキストデータ

文字列の一致 like や　= 　を試す用。　texttestというindexに入れます。

PUT texttest/doc/1
{
 "content": "I have a pen."
}

項目	フィールド
テキスト	content

いざ確認

マニュアルはこちら
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-getting-started.html

SQL REST API

1. format txt

SQL REST APIでは、SQLクエリを発行すると結果が得られます。

POST /_xpack/sql?format=txt
{
  "query": "SELECT * FROM stock order by timestamp desc limit 5"
}

のようなクエリを発行すると、結果がテキスト形式でテーブルが返ってきます。

   end_value   |  high_value   |   low_value   |  start_value  |       timestamp        
---------------+---------------+---------------+---------------+------------------------
22966.380859375|22993.259765625|22895.310546875|22896.169921875|2018-06-13T00:00:00.000Z
22878.349609375|23011.5703125  |22797.73046875 |22977.220703125|2018-06-12T00:00:00.000Z
22804.0390625  |22856.080078125|22667.30078125 |22686.94921875 |2018-06-11T00:00:00.000Z
22694.5        |22879.0        |22694.5        |22799.380859375|2018-06-08T00:00:00.000Z
22823.259765625|22856.369140625|22732.1796875  |22748.720703125|2018-06-07T00:00:00.000Z

2. format json

フォーマットをjsonにしてみましょう。

POST /_xpack/sql?format=json
{
  "query": "SELECT * FROM stock order by timestamp desc limit 5"
}

結果がjsonで返されますが、従来のElasticsearchのクエリを発行したときの結果のjsonとは違うので、そこは注意が必要です。

SQLでよくある while (結果が無くなるまで) みたいなカーソルを使ったアクセスだと、fetch_sizeを指定すると良いです。

POST /_xpack/sql?format=json
{
  "query": "SELECT * FROM stock order by timestamp desc",
  "fetch_size": 1
}

3. format csv

フォーマットをcsvにしてみましょう。

cliからも同様のことができるので、Excelでｸﾚ！というような方にcsvをさっと渡すときなどに向いてそうです。

4. csvの列名をasで変えてみる

POST /_xpack/sql?format=csv
{
  "query": """SELECT 
     high_value as hoge,
     low_value as bar,
     start_value as piyo,
     end_value as fuga
     FROM stock order by timestamp desc
  """,
  "fetch_size": 1
}

asも有効に効いて、列名が変わっていることが確認できました。

5. 集計関数 MAX, MIN, AVG

よく使うMAX, MIN, AVGなどの関数も、もちろん使えます。

POST /_xpack/sql?format=json
{
  "query": "SELECT MAX(high_value), MIN(low_value), AVG(start_value) FROM stock "  
}

うっかり、ExcelのつもりでAVGをAverageなんたら？？？　そんな関数はない、とエラーが返ってきます。

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:41: Unknown function [AVERAGE]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:41: Unknown function [AVERAGE]"
  },
  "status": 400
}

たとえば、AVGを間違えて、AVEなんてしてしまった場合は、もっと親切です。
ちょっとした優しさが身に沁みます。

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:41: Unknown function [AVE], did you mean [AVG]?"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:41: Unknown function [AVE], did you mean [AVG]?"
  },
  "status": 400
}

6. = とか like

文字列の場合は、SQLのときと同様シングルクオーテーションで囲むのを忘れないようにしましょう。

POST /_xpack/sql?format=json
{
  "query": "SELECT * from texttest WHERE content = 'I have a pen.'"  
}

POST /_xpack/sql?format=json
{
  "query": "SELECT * from texttest WHERE content like  'I have %'"  
}

もし、''で囲まなかったらこういうエラーが出ます。

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:44: mismatched input ''' expecting {'?', STRING}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:44: mismatched input ''' expecting {'?', STRING}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

likeで結果が0件になるようなクエリを投げてみたところ、結果１件返ってきてしまいました。

POST /_xpack/sql?format=json
{
  "query": "SELECT * from texttest WHERE content like  'I have % a book.'"
}

あれ？　mappingがよくなかった？　クエリ書き間違えた？　SQLだとどういうクエリを実際に投げてんの？　という疑問がここで生まれます。
そこで、Translate APIです。

SQL Translate API

SQLをElasticsearchのクエリに変換してくれるAPIです。
実際にどういうクエリになっているかが見ることができれば、期待する検索結果にならなかったときなどの調査に役立つこともあるでしょう。

POST /_xpack/sql/translate
{
  "query": "SELECT * from texttest WHERE content like  'I have % a book.'"
}


{
  "size": 1000,
  "query": {
    "query_string": {
      "query": "I have * a book.",
      "fields": [
        "content^1.0"
      ],
      "type": "best_fields",
      "default_operator": "or",
      "max_determinized_states": 10000,
      "enable_position_increments": true,
      "fuzziness": "AUTO",
      "fuzzy_prefix_length": 0,
      "fuzzy_max_expansions": 50,
      "phrase_slop": 0,
      "escape": false,
      "auto_generate_synonyms_phrase_query": true,
      "fuzzy_transpositions": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "content"
    ],
    "excludes": []
  },
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

馴染みのクエリがでてきましたね！
今回のは default_operatorがORになっていたことが良くなかったんだ！　ということに、ここで気づくことができます。

返されたjsonを、searchのクエリにそのまま書くと、いつもの形式で結果がとれます。

SQLでのクエリを書いた際のデバッグ用途に活躍しそうです。

その他　遭遇したエラーや試したこと

FROM違い

FROMの後は、indexを指定します。ないindexを指定すると、エラーとなります。

POST /_xpack/sql/translate
{
  "query": "SELECT * from hogehoge"
}

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:15: Unknown index [hogehoge]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:15: Unknown index [hogehoge]"
  },
  "status": 400
}

JOINしてみる

POST /_xpack/sql/translate
{
  "query": "SELECT * from stock as a LEFT OUTER JOIN stock as s ON  a.timestamp = s.timestamp"
}

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:27: Queries with JOIN are not yet supported"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:27: Queries with JOIN are not yet supported"
  },
  "status": 400
}

JOIN are not yet supported ということは、そのうちサポートされるのか！？　と期待は膨らみますが、現時点では未サポートのようです。

SQLコメント

-- 文字列　の形式で書かれるSQLコメントはエラーとなりません。

POST /_xpack/sql?format=csv
{
  "query": """SELECT 
     high_value as hoge,  -- hogeです
     low_value as bar,    -- barです
     start_value as piyo, -- piyoです
     end_value as fuga    -- 以下ry
     FROM stock order by timestamp desc
  """,
  "fetch_size": 1
}

その他

SQL Accessが、どのサブスクリプションでどこまで使えるのか？　は、以下をご確認ください。
https://www.elastic.co/jp/subscriptions

まとめに代えての雑感

Elasticsearchのクエリ、ましてやLuceneのクエリは分からないが、SQLなら分かる！　という数は一定数いると思いますが、
そういった方でも使えるようになる、というのは裾野が広がって良いと思います。

また上位のサブスクリプションで有効なJDBC Clientも、いろんなものとのデータ連携を考えると使えると喜ぶ人が多そうですね。
6.3では他にもいろいろな改良が入っているようなので、おいおい試していきたいと思います。

最近、エラーといえばスタックトレースしか出ないような製品に囲まれていたので、エラー内容がちゃんと返ってくる、
しかもこういう間違いではないです？と指摘してくれる機能に触れただけでも、心が和みます。ありがとうございました。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Elasticsearch 6.3.0 リリース記念 X-Pack SQL Accessをやってみよう

概要

環境