查询ElasticSearch：用SQL代替DSL
Hollis
共 7652字，需浏览 16分钟
·
2020-09-19 03:55
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"
}
}
]
}
DDL查询如：
DESCRIBE table,SHOW COLUMNS IN table略显鸡肋，我们主要看下对SELECT,Function的DML查询支持。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 ] [, ...] ) ) ]
获取所有 FROM中的关键词，确定表名。如果有 WHERE条件，过滤掉所有不符合的行。如果有 GROUP BY条件，则分组聚合；如果有HAVING条件，则过滤聚合的结果。上一步得到的结果经过 select_expr运算，确定具体返回的数据。如果有 ORDER BY条件，会对返回的数据排序。如果有 LIMITorTOP条件，会返回上一步结果的子集。
与常用的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 可列举出支持的函数名称和所属类型。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：相当于DSL中的match and multi_match查询。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：相当于DSL中的 query_string 查询。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
SCORE()：返回输入数据和返回数据的相关度relevance.使用举例：
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
AVG(numeric_field) ：计算数字类型的字段的平均值。SELECT AVG(salary) AS avg FROM emp;
COUNT(expression)：返回输入数据的总数，包括COUNT(COUNT(ALL field_name)：返回输入数据的总数，不包括field_name对应的值为null的数据。COUNT(DISTINCT field_name)：返回输入数据中field_name对应的值不为null的总数。SUM(field_name)：返回输入数据中数字字段field_name对应的值的总和。MIN(field_name)：返回输入数据中数字字段field_name对应的值的最小值。MAX(field_name)：返回输入数据中数字字段field_name对应的值的最大值。HISTOGRAM：语法如下：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;
GROUP BY or HAVING 或者比SELECT X
FROM (SELECT ...) WHERE [simple_condition]这种结构复杂，都是可能执行不成功的。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));
如果一个字段不在source中存储，是无法查询到的。
keyword, date, scaled_float, geo_point, geo_shape这些类型的字段不受这种限制，因为他们不是从_source中返回，而是从docvalue_fields中返回。有道无术，术可成；有术无道，止于术
欢迎大家关注Java之道公众号
好文章，我在看❤️
评论