Elasticsearch:クエリ構文

基本概念

クエリを作成する前に、SQL の知識を Elasticsearch の用語にマッピングします：

このプラグインは Apache Calcite を基盤としており、実行時に SQL ステートメントを Elasticsearch のドメイン特化言語 (DSL) クエリに変換します。

基本クエリ

すべての基本クエリでは、POST /_alisql エンドポイントを使用します。

POST /_alisql?pretty
{
  "query": "select * from monitor where host='100.80.xx.xx' limit 5"
}

POST /_alisql?pretty
{
  "query": "select * from monitor",
  "fetch_size": 3
}

POST /_alisql?pretty
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}

{
  "columns": [
    {"name": "times", "type": "integer"},
    {"name": "value2", "type": "float"},
    {"name": "host", "type": "keyword"},
    {"name": "region", "type": "keyword"},
    {"name": "measurement", "type": "keyword"},
    {"name": "timestamp", "type": "date"}
  ],
  "rows": [
    [572575, 4649800.0, "100.80.xx.xx", "china-dd", "cpu", "2018-08-09T08:18:42.000Z"]
  ],
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

ページネーション付きクエリ

大規模な結果セットに対しては、カーソルベースのページネーションをご利用ください。

ステップ 1 — fetch_size を指定した初期リクエスト：

POST /_alisql?pretty
{
  "query": "select * from monitor",
  "fetch_size": 3
}

応答には、さらにデータが利用可能な場合に cursor トークンが含まれます。

ステップ 2 — カーソルを用いた継続処理：

前の応答から得られた cursor 値を指定して、次のページの結果を取得します：

POST /_alisql?pretty
{
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

カーソルクエリの応答では、ネットワーク遅延を低減するため、columns フィールドは省略されます。その他のフィールドは、初期応答と同じ構造で返されます。

ステップ 3 — 終了条件：

応答に cursor フィールドが含まれていないか、または rows が空になるまで、カーソルリクエストを繰り返します。この時点で、すべてのデータが取得済みとなります。

JSON 形式のクエリ

format=org を追加すると、ネイティブな Elasticsearch DSL クエリと同じ JSON 形式で結果が返されます。JOIN 文はサポートされていません。

POST /_alisql?format=org
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}

応答は標準的な Elasticsearch hits 構造を使用します：

{
  "_scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAsWYXNEdlVJZzJTSXFfOGluOVB4Q3Z****",
  "took": 18,
  "timed_out": false,
  "_shards": {"total": 1, "successful": 1, "skipped": 0, "failed": 0},
  "hits": {
    "total": 2,
    "max_score": 1.0,
    "hits": [
      {
        "_index": "monitor",
        "_type": "_doc",
        "_id": "2",
        "_score": 1.0,
        "_source": {
          "times": 572575,
          "value2": 4649800,
          "host": "100.80.xx.xx",
          "region": "china-dd",
          "measurement": "cpu",
          "timestamp": "2018-08-09T16:18:42+0800"
        }
      }
    ]
  }
}

この形式で作業する際には、ページネーションクエリには _scroll_id フィールドをご利用ください。その他のリクエストパラメーターは、基本クエリと同一です。

クエリの翻訳

translate API は、SQL ステートメントを同等の Elasticsearch DSL クエリに変換します。クエリのデバッグや、プラグインが SQL 条件を DSL フィルターにどのようにマッピングしているかを理解する際にご利用ください。JOIN 文はサポートされていません。

POST _alisql/translate
{
  "query": "select * from monitor where host= '100.80.xx.xx' "
}

応答は、SQL クエリに対応する DSL です：

{
  "size": 1000,
  "query": {
    "constant_score": {
      "filter": {
        "term": {
          "host": {
            "value": "100.80.xx.xx",
            "boost": 1.0
          }
        }
      },
      "boost": 1.0
    }
  },
  "_source": {
    "includes": ["times", "value2", "host", "region", "measurement", "timestamp"],
    "excludes": []
  }
}

特殊クエリ

aliyun-sql プラグインは、標準 SQL データベースでは直接クエリできないネスト型およびテキスト型のデータフィールドのクエリをサポートしています。

ステップ 1 — ネスト型およびテキスト型フィールドを含むインデックスの作成：

PUT user_info/
{
  "mappings": {
    "_doc": {
      "properties": {
        "addr": {"type": "text"},
        "age": {"type": "integer"},
        "id": {"type": "integer"},
        "name": {
          "type": "nested",
          "properties": {
            "first_name": {"type": "keyword"},
            "second_name": {"type": "keyword"}
          }
        }
      }
    }
  }
}

ステップ 2 — バルク挿入によるドキュメント登録：

PUT user_info/_doc/_bulk?refresh
{"index":{"_id":"1"}}
{"addr":"467 Hutchinson Court","age":80,"id":"1","name":[{"first_name":"lesi","second_name":"Adams"},{"first_name":"chaochaosi","second_name":"Aams"}]}
{"index":{"_id":"2"}}
{"addr":"671 Bristol Street","age":21,"id":"2","name":{"first_name":"Hattie","second_name":"Bond"}}
{"index":{"_id":"3"}}
{"addr":"554 Bristol Street","age":23,"id":"3","name":{"first_name":"Hattie","second_name":"Bond"}}

ステップ 3 — ネスト型フィールドのクエリ：

ドット表記を使用してネスト型フィールドを参照します：

POST _alisql
{
  "query": "select * from user_info where name.second_name='Adams'"
}

応答：

{
  "columns": [
    {"name": "id", "type": "integer"},
    {"name": "addr", "type": "text"},
    {"name": "name.first_name", "type": "keyword"},
    {"name": "age", "type": "integer"},
    {"name": "name.second_name", "type": "keyword"}
  ],
  "rows": [
    [1, "467 Hutchinson Court", "lesi", 80, "Adams"]
  ]
}

ステップ 4 — テキストフィールドのクエリ：

キーワード一致を使用してテキスト型フィールドをフィルターします：

POST _alisql
{
  "query": "select * from user_info where addr='Bristol'"
}

応答：

{
  "columns": [
    {"name": "id", "type": "integer"},
    {"name": "addr", "type": "text"},
    {"name": "name.first_name", "type": "keyword"},
    {"name": "age", "type": "integer"},
    {"name": "name.second_name", "type": "keyword"}
  ],
  "rows": [
    [2, "671 Bristol Street", "Hattie", 21, "Bond"],
    [3, "554 Bristol Street", "Hattie", 23, "Bond"]
  ]
}

ビルトイン関数および式