查询ElasticSearch：用SQL代替DSL

SQL REST API

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

    author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z

SQL CLI

./bin/elasticsearch-sql-cli https://some.server:9200

sql> SELECT * FROM library WHERE page_count > 500 ORDER BY page_count DESC;
     author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+---------------
Peter F. Hamilton|Pandora's Star      |768            |1078185600000
Vernor Vinge     |A Fire Upon the Deep|613            |707356800000
Frank Herbert    |Dune                |604            |-144720000000

SQL To DSL

POST /_sql/translate
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 10
}

{
  "size": 10,
  "docvalue_fields": [
    {
      "field": "release_date",
      "format": "epoch_millis"
    }
  ],
  "_source": {
    "includes": [
      "author",
      "name",
      "page_count"
    ],
    "excludes": []
  },
  "sort": [
    {
      "page_count": {
        "order": "desc",
        "missing": "_first",
        "unmapped_type": "short"
      }
    }
  ]
}

SELECT

SELECT [TOP [ count ] ] select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

1. 获取所有 FROM中的关键词，确定表名。
2. 如果有WHERE条件，过滤掉所有不符合的行。
3. 如果有GROUP BY条件，则分组聚合；如果有HAVING条件，则过滤聚合的结果。
4. 上一步得到的结果经过select_expr运算，确定具体返回的数据。
5. 如果有 ORDER BY条件，会对返回的数据排序。
6. 如果有 LIMIT or TOP条件，会返回上一步结果的子集。

与常用的SQL有两点不同，ES SQL 支持TOP [ count ]和PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )子句。
TOP [ count ] ：如SELECT TOP 2 first_name FROM emp表示最多返回两条数据，不可与LIMIT条件共用。
PIVOT子句会对其聚合条件得到的结果进行行转列，进一步运算。这个我是没用过，不做介绍。

FUNCTION

SHOW FUNCTIONS;

      name       |     type
-----------------+---------------
AVG              |AGGREGATE
COUNT            |AGGREGATE
FIRST            |AGGREGATE
FIRST_VALUE      |AGGREGATE
LAST             |AGGREGATE
LAST_VALUE       |AGGREGATE
MAX              |AGGREGATE
MIN              |AGGREGATE
SUM              |AGGREGATE
........

MATCH(
    field_exp,       --字段名称
    constant_exp,       --字段的匹配值
    [, options])       --可选项

SELECT author, name FROM library WHERE MATCH(author, 'frank');

    author     |       name
---------------+-------------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah

SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |11.443176
Frank Herbert  |Dune Messiah       |9.446629

QUERY(
    constant_exp      --匹配值表达式
    [, options])       --可选项

SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)');

      author      |       name        |  page_count   |    SCORE()
------------------+-------------------+---------------+---------------
Frank Herbert     |Dune               |604            |3.7164764
Frank Herbert     |Dune Messiah       |331            |3.4169943

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z

SELECT AVG(salary) AS avg FROM emp;

HISTOGRAM(
           numeric_exp,    --数字表达式，通常是一个field_name
           numeric_interval    --数字的区间值
)

HISTOGRAM(
           date_exp,      --date/time表达式，通常是一个field_name
           date_time_interval      --date/time的区间值
)

ELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;


           h            |       c
------------------------+---------------
null                    |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1

ES SQL局限性

[nested_field_name].[sub_field_name]

SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;

SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);

• 排序字段必须是聚合桶中的字段，ES SQL CLI突破了这种限制，但上限不能超过512行，否则在sorting阶段会抛异常。推荐搭配Limit子句使用，如：

SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;

• 聚合排序的排序条件不支持Scalar函数或者简单的操作符运算。聚合后的复杂字段（比如包含聚合函数）也是不能用在排序条件上的。

SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;

SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);

SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h

SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));