ES 6.3 SQL 使用方法

ES 6.3 支持了 SQL 功能，但是这个是试验性的，未来可能面临被取消。
This functionality is experimental and may be changed or removed completely in a future release.

通过 restful 使用 SQL

放入测试数据

PUT /library/book2/_bulk?refresh
{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}

简单Select ，返回文本格式

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM library"
}

response

     author     |     name      |  page_count   |      release_date      
----------------+---------------+---------------+------------------------
Dan Simmons     |Hyperion       |482            |1989-05-26T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z
Frank Herbert   |Dune           |604            |1965-06-01T00:00:00.000Z

简单Select ，返回json格式

POST /_xpack/sql?format=json
{
    "query": "SELECT * FROM library"
}

response

{
  "columns": [
    {
      "name": "author",
      "type": "text"
    },
    {
      "name": "name",
      "type": "text"
    },
    {
      "name": "page_count",
      "type": "long"
    },
    {
      "name": "release_date",
      "type": "date"
    }
  ],
  "rows": [
    [
      "Dan Simmons",
      "Hyperion",
      482,
      "1989-05-26T00:00:00.000Z"
    ],
    [
      "James S.A. Corey",
      "Leviathan Wakes",
      561,
      "2011-06-02T00:00:00.000Z"
    ],
    [
      "Frank Herbert",
      "Dune",
      604,
      "1965-06-01T00:00:00.000Z"
    ]
  ]
}

SQL 排序

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 2"
}

response

     author     |     name      |  page_count   |      release_date      
----------------+---------------+---------------+------------------------
Frank Herbert   |Dune           |604            |1965-06-01T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z

SQL CLI 命令行方式

启动运行

./bin/elasticsearch-sql-cli

输入查询语句

sql> SELECT * FROM library;

返回

sql> SELECT * FROM library;
     author     |     name      |  page_count   |      release_date      
----------------+---------------+---------------+------------------------
Dan Simmons     |Hyperion       |482            |1989-05-26T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z
Frank Herbert   |Dune           |604            |1965-06-01T00:00:00.000Z

相关sql函数使用详见
https://www.elastic.co/guide/en/elasticsearch/reference/6.3/xpack-sql.html

总结

SQL 用作ES查询，功能一般，相比 restful 接口直接查询ES，没有什么特别的优势。

restful 接口本身已经很简洁优雅了，使用SQL还多一次语义解析。SQL功能有点鸡肋，后期ES有可能会取消。