查詢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中的關(guān)鍵詞，確定表名。
2. 如果有WHERE條件，過濾掉所有不符合的行。
3. 如果有GROUP BY條件，則分組聚合；如果有HAVING條件，則過濾聚合的結(jié)果。
4. 上一步得到的結(jié)果經(jīng)過select_expr運算，確定具體返回的數(shù)據(jù)。
5. 如果有 ORDER BY條件，會對返回的數(shù)據(jù)排序。
6. 如果有 LIMIT or TOP條件，會返回上一步結(jié)果的子集。

與常用的SQL有兩點不同，ES SQL 支持TOP [ count ]和PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) )子句。
TOP [ count ] ：如SELECT TOP 2 first_name FROM emp表示最多返回兩條數(shù)據(jù)，不可與LIMIT條件共用。
PIVOT子句會對其聚合條件得到的結(jié)果進行行轉(zhuǎn)列，進一步運算。這個我是沒用過，不做介紹。

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??????--匹配值表達(dá)式
????[,?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,????--數(shù)字表達(dá)式，通常是一個field_name
???????????numeric_interval????--數(shù)字的區(qū)間值
)

HISTOGRAM(
???????????date_exp,??????--date/time表達(dá)式，通常是一個field_name
???????????date_time_interval??????--date/time的區(qū)間值
)

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函數(shù)或者簡單的操作符運算。聚合后的復(fù)雜字段（比如包含聚合函數(shù)）也是不能用在排序條件上的。

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));